Knowledge Drop

How can I merge user attribute values from multiple groups?

  • 26 March 2021
  • 3 replies
  • 398 views

Userlevel 5
Badge
  • Looker Staff
  • 171 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.

ambDFEIKIlP6e3_dfORMzy5GNbqzgYBUKChW3K7WkBeidsomFFSBlQb-d_JTb9Z44HJsSjSZ5ICm5sygJ1_NVU8x37Qjyfp7E9B8GVQ73mtU4dbTACjt0z8EEZCYiHPMqNAHJ-D5YQ

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

A2fRSUCTWzV_dvzg1rjXBSvoMLmeyXTFEKauHZh25f2TAPXZlFlgvu9BB9NC8fcm_gdWj8ONA3GLHDRWbloxJThM84eHElCVmzfLVEPcKNLMdUxrw2cnjhXhW4hnlT4obBKTW21TNA

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

sql_always_where: ${users.country} 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.

ZG0l-x_ou3fGFbvCr66CKb2GFTNHjrZq1NPOqHZDLY88Yh33_9-GQUjqAVAiSLJQk9thXOg6CqXZUPY4qqKwRMK31SqbaJ7sawwiPbZcaPDXihbrY-cb-M_pOL75rNzsMm59SkTu5A

RESULT:

COqm17PN6l9nvoeVYUERJc0GKUYD5KVp-TCKZ719ckf-rWPfbSA9n0BoOUP5uW7T1EqEOptAExtPI9THyYnGybdwrzSL-XwL3FeAUHT0pwN0zHJN0prg6gHJSIYcvjsjcOsV-rK0Ug

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

IKMRGCU_5u1UjD_U7mL7L47TzWE8-lvGqNEKHSvdx3XsZDXeNFPMhbIPzyo0_s49fAeQp1xOGwSLsTIbdqvkGKESCD2CVA8EwhuEY-fcGll9VFQ2-yFXvHIBOqKDUFIMOfm9aCJuvQ

RESULT

:yoxBOR2En4yMUTfI1AWsOQK2-c7S_FPZOasn_V8FpGEFnphrJNlH9d28OzXgF6yBY63U2kw4hwswyJCK9BvqTsvTwWev5QHHEia-HG_75AftrX0ZwHJcj8oOfma2-Xv5eP-CoXnI7A

 

This content is subject to limited support.                


3 replies

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 5
Badge

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!

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”?

Reply