Looker Row Level Security issue

[Falak001]

There is one requirement of Looker Hierarchical Row level security As per attached Data available. Like When HOD will login looker he can only see the data under him the managers and employees. When Any mangers login to the dashboard can able see only those employees under him. Please find the attached Data. We have tried with user attributes and access filter but not working in hierarchy level. Could you please help to resolve.

Thanks

[fabio1]

Access filters work well when there are one or more restrictions that are logically "AND"-ed together. When you have restrictions that are more complex, such as "the data is for this user OR the data is for users that report to this user", you'll generally need to write that SQL condition by hand and place it inside a sql_always_where statement (leveraging liquid to insert user attributes)

Take a look at this post for more details: https://www.googlecloudcommunity.com/gc/Modeling/Advanced-Permissioning-Using-User-Attributes/td-p/5...

[a_shah]

Hi @Falak001 

Your requirement of filter the data by dynamically changing column to be filtered can be achieved using below steps. I have tested it in the looker/BQ environment. Let me know for any further queries.

Step 1: We need two user attributes- first "name" and second "org_role". (To avoid duplicate name issues, it will be better to take email id or some unique field in place of "name" as it will be used to filter the data.). Value of user attribute "name" for the user "Avik" should be "avik", for the user "Ashoka" the value should be "ashoka".  Next, Value of org_role for each user should be set based on the column name from the database table. E.g. from your dataset above - for the user "Avik" the value of org_role should be "HOD", for the user "Ashoka" is should be "Manager" and so on.

Step 2: Define a dimension in the view to fetch the value of user attribute "org_role" which is also the name of the column in the table.

Step 3: Define the always_filter parameter in the explore and equate the field created in step 1 with the user attribute "name". This will create a forced where-clause in the query like where HOD='avik' ( "HOD" will come from "org_role" and "avik" will come from "name" in user attributes and both are dynamic). This where clause will change dynamically based on the user who runs the report.

This setup makes the filtering dynamic as each user will have their own "name" and "org_role" set as per the database table and the data will get filtered dynamically on different columns based on which user is running the report.

Hope this helps!

~Ashish

[nddi]

Please do try the latest feature that GCP has come up with ROW Level access security in Bigquery. 

For more details here is the URL

https://cloud.google.com/bigquery/docs/row-level-security-intro

This is one of good features in BQ

[fabio1]

Hi @nddi - all BigQuery features that rely on user identity should automatically work in Looker when Looker connects to BigQuery using OAuth:

https://cloud.google.com/looker/docs/db-config-google-bigquery#oauth_for_bigquery_connections