Filter data using profile email

[MaddyMS]

• I have a security dashboard wherein I want users to review only the data that belongs to them.
  Best way I can think of is by using the login profile email. 
  Also, Possible to add exceptions to few admin accounts that can be bypassed? 
  
  anyone how I can achieve this? Appreciate your help! 

[sam8]

I think the way I'd go about this is by using referencing user attributes in Liquid in a sql_always_where in the explore.

(note: Community doesn't like liquid tags, so please replace all of my parentheses with curly braces {})

If you have an email field, you could write something like this:

sql_always_where: (( _user_attributes['email'] )) = ${user.email} ;;

You can use Liquid to write in a few admin exceptions: 

sql_always_where: 
(% if user_attributes['email'] == 'superuser@example.com' or user_attributes['email'] == 'admin@example.com' %) 1=1 (% else %) (( _user_attributes['email'] ))
= ${user.email} (% endif %);;

[Ramakanthkrovi]

Hi Sam,

this was helpful. I am an admin and I see the full table as I used my email in the exception list above. However, when I sudo as the user it just says "Trouble Loading Data" and does not load the dashboard. I tried changing the user's access to both the model and the dashboard and still does not work. the user is present in the list. 
When someone who is neither an admin nor the user in the list tries to access it, it throws a different error which is fine for now. 

  
    "error": "Model Not Found"
 

Need the first scenario resolved, are we missing something here, please advise?

[sam8]

Good progress!

When you're sudo'd as the other user, can you "Explore from here" on the dashboard? I'm wondering if there's a SQL error that we can troubleshoot.

[Ramakanthkrovi]

When I sudo as the user, I cannot see "explore from here" on the dashboard. 

[sam8]

Sounds like that user doesn't have explore permissions. Can you try testing with a user that does have explore permissions? You could create a test user with the necessary permissions, and similar user attribute values.

[sam8]

Or maybe even easier, change the LookML to remove the admin exceptions: 

sql_always_where: (( _user_attributes['email'] ))= ${user.email} ;;

Then you can explore from the dashboard as your admin user in dev mode.  

[Ramakanthkrovi]

Currently it works using an access filter.

access_filter: {
field: email_address_field #this comes from the view
user_attribute: email #this is the user attribute
}

The problem is we are not able to override the user here as admins. it just says "no results".

Is there a way to override an access filter based on group/department or some other user attribute?

[sam8]

Not that I know of. Using sql_always_where is the recommended way to add that overriding logic.

Were you able to test using one of my suggestions above? I'm really curious why it's returning an error and not "No results", and I think the SQL would show us the answer.