Row Level Security with Explores & SQL "Injected" Dimensions

[kirkg5]

We currently use user attributes to filter out other clients information in our Looker dashboards. Originally, this was designed by using a derived table that took the user attributes and only gave client access to what was passed in those fields. This seemed rather unnecessary to build something new each time for our users.

I figured there had to be a better way to do this and was roughly able to accomplish the same thing by moving that logic to an explore and a sql_always_where statement that used those user attribute fields. This works great when applying this logic to filters, but I ran into an issue when I wanted to apply that logic to a different explore to filter results on the first load of the dashboard that it’s included in.

Eventually, I settled upon using the explore (which I named the permitted_client explore) and adding some dimensions to the new table. These dimensions have a sql block that has a rough template like so: {% if _user_attributes["attribute"] != '%' %} ${client} IN ({{ _user_attributes["attribute"]}}) %} where attribute is a string resembling something like "'client1','client2','client3'" or if not filled then '%'.

This feels a little counterintuitive as it seems like I’m just injecting some SQL to get around having a layer of abstraction.

Is there no way that I could just extend an explore in a different explore to have it automatically apply this logic? Something like that would be ideal and reusable for me as I’ll need to apply this logic to every new explore I make in my model

[Cyril_MTL_Analy]

kirkg5:

permitted_client

Hi @kirkg5 and Welcome.

You may want to look into the access_filter documentation here, looks like it’s what you need.

[kirkg5]

Wow that looks like someone copied almost exactly what I wrote and built it out. Thanks!

Is there some type of specific format or configuration that allows for multiple clients to be added to one of these access filters?

It seems rather vague past the fact that you can just use % for everything.

[Cyril_MTL_Analy]

It’s using the same format as Looker Filter Expressions

[kirkg5]

In case anyone else tries to work with this, make sure when setting a new user attribute that you make it an advanced string filter and not just a “string”. Otherwise the filter expressions won’t work.

[annirasushma199]

How to implement row level security by passing liquid variables