Knowledge Drop

How can I merge user attribute values from multiple groups?

  • 26 March 2021
  • 4 replies

Userlevel 5
  • Looker Staff
  • 173 replies

Last tested: March 2021


Background: Groups are given user attribute values. For example, group_USA might be given the value for the “country” user attribute as “USA”. With an access filter, users in that group will get WHERE country = “USA” for their queries.

The issue: If a user belongs to two groups, like group_USA and group_UK, each with different “country” user attribute values, this user will not inherit both user attribute values. Other types of permissions are additive with groups, but user attributes are not: one will “win out”.

To solve the scenario where someone would need access to both countries, you might think you need to create a user attribute for each combination of user attribute values. While that would work, managing all the combinations might get unwieldy. The following pattern is an alternative.

1. Create a user attribute for each possible individual value. Ex: a user attribute for every count

Screen Shot 2021-03-23 at 14.08.55.png

2. Create a group for each user attribute.


3. Assign groups to user attributes with a value to match. (User Attributes > Edit user attribute > Group values). Ex: country_uk (user attribute) gets country_uk (group) default value = “UK” cgWx8AVAPD7-JHxUioKz7ksGI2_hS8G9VGuae-GM_NDnHbCP8l4ncdmiHvSvO-LOYk2yWnlLXuuyYGDJ7CDv3WAYYEsB_cLCmZY0cqGqrbGFOWfxZrapFJOfuSy5q_Xsa_-lYbVYMQ


4. Add a sql_always_where that checks the database value against a list of each of the user attributes.

sql_always_where: ${} IN ('{{ _user_attributes['country_usa'] }}', '{{ _user_attributes['country_uk'] }}') ;; 

Screen Shot 2021-03-23 at 14.16.57.png


5. TEST STEP 1 - Edit a user and put them in multiple groups.




6. TEST STEP 2 - Put that user only in one group.





This content is subject to limited support.                

4 replies

Hi @sam - this is very helpful! In this framework, it seems that users who should have access to *all* values (‘US’, ‘UK,’ ‘MX’, etc.) should be members of all of the groups. Is there an easier way to assign the default to be “match all values”?

Userlevel 5

Hi @Noa - that’s exactly right. That user would need to be a member of all the groups. 


One workaround would be to set the default values of these user attributes to their appropriate values. In this example, that would mean setting the default value of the country_uk user attribute to UK. This way, each new user would have access to each country’s values by default. Then you would need to manually remove them from any groups that they should not have access to. This changes the restrictive “opt-in” restrictive example from the article into a more open “opt-out” scenario.


If, instead, you had a single user attribute country which had all of the possible values (‘US’, ‘UK,’ ‘MX’, etc.), then you could assign that user the wildcard (%) for string (advanced) user attributes. This article assumes that such re-architecture of your user attribute framework is prohibitively complex. However, it might be worth it if your user attribute framework is flexible or in-progress!

Thanks @sam! I think “opt-in” in the safest when it comes to access management; users are much more likely to notice (and complain) if they are missing access, versus if they have extra access.


Re: your second suggestion, that’s what we had set up previously (wildcard for users who have all access), but the architecture wasn’t handling the case where a user should have had access to multiple (but not all) values, e.g. US and UK but not MX.

Userlevel 2

How many access_filters can i have active at once in an explore ?


I have a profile escenario in which for each type of profile , a user should be able to see certain stuff. example, if a user is on profile1 he should be be able to see 3 business units and a sales_channel. So , the way i am doing it right now that is 2 user attributes , each based on a field , is that the best practice ?