Advanced Permissioning Using User Attributes

Advanced Permissioning Using User Attributes

About This Pattern:

This analytical pattern can be used to set a user’s access on one field OR another field in complex permissioning use cases. For example, if you have hierarchical permissioning or referral systems.

User attributes are key-value pairs of metadata that describe a particular user. In addition to basic user attributes like first name, last name, and timezone, we can create our own user attributes to describe data permissions at the user level. We can refer to the values of user attributes in our LookML model, enabling us to set row level permissioning in a robust and scalable way.

Generally, a user’s data access needs to be filtered to match the value of their user attribute in a single field (for example, the user can only see rows of data for their brand), or simultaneously match multiple user attributes from different fields (for example, the user can only see data for their brand AND department) If this is what you’re looking to do, check out the access_filters parameter.

In more complex situations, a user’s access needs to be filtered by one field OR another field matching their user attribute value. Fear not! We’ll discuss a pattern that enables you to set user attributes on two different dimensions using an OR operator.

Note: The pattern below leverages SQL_ALWAYS_WHERE at the Explore level to enforce permissioning as well as liquid to refer to the user attributes in the model.

Try It Out!

You can reference User Attributes via liquid in a SQL Always Where clause to execute an OR operator with user attributes.

explore: view_1 {
sql_always_where: ${field_1} in ({{ _user_attributes[‘user_attribute_1’] }}) or ${field_2} in ({{ _user_attributes[‘user_attribute_2’] }}) ;;
}

Example Use Cases

Hierarchical Permissions:

Perhaps a regional manager can see data specific to an entire region as well as a few standalone stores in other regions.

The store manager has access to Store IDs 1,2,3 and has access to Region ID 5. When she logs in, she should see all rows of sales where Store IDs = 1,2,3, as well as all rows where Region ID = 5 (which may include stores other than 1,2 and 3). To accomplish this, we’ll use an OR clause in the sql_always_where to grant access for when a store_id user attribute is met OR when a region_id user attribute is met.

explore: sales {
sql_always_where: ${store_id} in ({{ _user_attributes[‘store_id'] }}) or ${region_id} in ({{ _user_attributes[‘region_id'] }}) ;;
}

Referral systems:

Imagine a healthcare management system used by doctors to manage their appointments and any referrals they make for additional care. An individual doctor needs to see:

  1. All their patients’ appointments
  2. Any appointments made with other doctors as a result of this doctor’s referrals
  3. Any appointments that have been referred to this doctor by other doctors

This is important in tracking a patient’s health plan.

Each row in the appointments table (illustrated below) has a column for the appointment doctor id and a column for recommender doctor id (which maps to a doctor’s id). We want doctor 1 to see all rows where the appointment doctor id = 1 OR where the recommending doctor id = 1(outlined in red).

5b70cd46cfb451529d7c7a37ce063608654f693b.png

explore: appointments {
sql_always_where: ${doctor_id} in ({{ _user_attributes[‘doctor_id'] }}) or ${recommending_id} in ({{ _user_attributes[‘doctor_id'] }}) ;;
}
9 5 6,166
5 REPLIES 5

Rui_Zhang1
Participant I

This is exactly what we are looking for. One problem I run into is that for Admins, it’s now hard to do “All” because we use “IN” as described in this article - RETIRED: Access Filters (with User Attributes)
Andrew’s solution of adding another “can_see_all” user attribute works.

acrobat
Participant I

This is exactly what we are looking for. One problem I run into is that for Admins, it’s now hard to do “All” because we use “IN” as described in this article - RETIRED: Access Filters (with User Attributes)

Andrew’s solution of adding another “can_see_all” user attribute works.

I think it depends on how your database handles wild cards. In Snowflake, setting the default of the user attribute to %% worked for admins.

Arnaldo
Participant I

If the user attribute is a list of string values, using {{ _user_attributes[‘doctor_id'] }} becomes a problem.

For example, if the user attributes are country codes as GB, US and DE, then {{ _user_attributes[‘country_codes'] }} returns (GB, US, DE) and not an array of strings as access filters do. This means that we’re not able to do:

sql_always_where: ${item.countries} && ({{_user_attributes['country_codes']}}) ;;

Because it will generate the following SQL query:

...
WHERE ((item."countries") && (GB,US,DE))


Any tips?

Hi @Arnaldo - you can always try an access_filter with a “Advanced (text)” user attribute with comma separated values. 


Or if you need to do it manually here’s some liquid that might help:
 

sql_always_where: 
{% assign attributes = _user_attributes['country_codes'] | split: ',' %}
{% assign sql_field = "item.countries" %}

{% if attributes.size == 1 %}
{% if attributes contains "%" %}
{{sql_field}} IS NOT NULL
{% else %}
{{sql_field}} = '{{ attributes }}'
{% endif %}
{% else %}
{{sql_field}} IN
{% for attribute in attributes %}
{% if forloop.first == true %}
('{{attribute }}',
{% elsif forloop.last == true %}
'{{attribute }}')
{% else %}
'{{attribute }}',
{% endif %}
{% endfor %}
{% endif %}
;;

Hi, 

How to implement for those super user who can see all data with out restrictions. Could you please share the code or thought in about example. As it is not working in always_where: statement

Top Labels in this Space
Top Solution Authors