RETIRED: Access Filters (with User Attributes)

The content in this article has been added to Looker documentation for user attributes.

1 Like

Worth mentioning, if you are using user attributes as access filters, the embed syntax is almost identical as to when using access_filters.
With access filters (example):
access_filters={"powered_by":{"products.brand":"Levi’s"}}
With user attributes as access filters (general case):
user_attributes={"attr1": "value1", "attr2": "val2", ...}

Hey Nate,

Is there a way to create OR statements with user_attributes? For example, what I’d like to do is give users access to accounts they manage and/or any other manually specified accounts.

I.e. I’d like to have 2 access filters, 1 with their username which is linked to the accounts they manage and a 2nd with additional access. As far as I can see, defining multiple access filters with user_attributes results in AND conditions between the filters. How can I create an OR condition between the filters?

Thanks

Hi @chris.laumans

If the access filter is based on the same dimension, you can simply separate values in the user attribute’s default value. For example London, Paris. This will create the following WHERE clause:

WHERE (users.city IN ('London', 'Paris'))

It’s currently not possible to use OR in-between two different access_filters that are referencing different dimensions. I’ll be happy to pass your idea to our product team on your behalf.

Hi @aleks,

Thanks. Indeed, would be great if you can pass the idea along to your product team. Ideally what we’d want is for example:

1 access filter on city
1 access filter on country

So, 1 user may for example have access to all cities in France and in addition to that also access to city Berlin. This would then give the OR statement:

WHERE (user.city IN (‘Berlin’) OR user.country IN (‘France’))

Thanks for detailed explanation @chris.laumans! I’ll definitely pass that along for you!

Ya, @aleks we have nearly the exact same use case as @chris.laumans

Thanks for your feedback @Jonathan_Samples, I will let the product team know this is a feature you would like to see as well!

Hi @chris.laumans , @Jonathan_Samples

Appreciate this is old so it might not be so relevant, but I believe you could generate the following access control

WHERE (user.city IN (‘Berlin’) OR user.country IN (‘France’))

Using the following sql_always_where clause

  sql_always_where: 
                  (${user.city} = '{{ _user_attributes["city"] }}'
                  OR
                  ${user.country} = '{{ _user_attributes["country"] }}');;

Hope that helps,
Andy

1 Like

Hi @powellandy,

Thanks for thinking along! I gave this a try and it works, but it is a bit of a hack. There are 2 minor issues that I ran into so far with this solution:

  • Admins should have access to all cities and countries. Normally, you would define “%” as the user_attribute. The access_filter parameter knows how to deal with this, but with sql_always_where you get the query: “WHERE user.city IN (’%’) and user.country IN (’%’)”, which gives no results.
  • If you need to give access to multiple cities or countries, the user attribute needs to be entered as follows: Berlin’, ‘Paris’, 'Amsterdam . Note the inclusion of quotes on the inside but not the outside of the full string. The access_filter paramter knows how to deal with this, but sql_always_where does not. Therefore if you do not include the quotes you’ll end up with the following statement “WHERE user.city IN (‘Berlin, Paris, Amsterdam’)” (i.e. 1 long string instead of 3 separate ones).

Either way though, it’s a good workaround for now, thanks!

Glad it helped!

A solution to the first problem could be to set an extra user attribute for admins saying can_see_all = "Yes", and set it to “No” for other groups. This is a solution I’ve recommended to others before. Then you just need an if condition in the sql_always_where:

sql_always_where: {% if _user_attributes["can_see_all"] = "Yes" %}
                                   1=1
                  {% else %}
                       (${user.city} = '{{ _user_attributes["city"] }}'
                       OR
                       ${user.country} = '{{ _user_attributes["country"] }}')
                  {% endif %}
                  ;;

The second one is a bit of a pain I agree.

Best,
Andy

Actually, what you want is quite doable with regularaccess_filters and using filter: in the model (instead of a dimension). Once you have it working, you can hide the filters. Here is an example model that shows how to create a pair of filters that work in concert, how it works in the explore and the SQL generated.

explore: test_users {
  sql_always_where: ${is_city_or_state} ;;
}

view: test_users {
  sql_table_name: thelook_web_analytics.users ;;

  filter: filter_country {
    # hidden: yes
  }
  filter: filter_state {
    # hidden: yes
  }
  dimension: is_city_or_state {
    sql:  ({% condition test_users.filter_country %} ${country} {% endcondition %})
      OR ({% condition test_users.filter_state %} ${state} {% endcondition %})   ;;
  }

  dimension: state {}
  dimension: country {}
  dimension: email {}

  measure: count {type:count}
}

And the SQL generated:

SELECT 
	test_users.country AS test_users_country,
	test_users.state AS test_users_state,
	COUNT(*) AS test_users_count
FROM thelook_web_analytics.users  AS test_users

WHERE ((test_users.country = 'UK'))
      OR ((test_users.state = 'New York' OR test_users.state = 'California')) 
GROUP BY 1,2
ORDER BY 3 DESC
LIMIT 500

Just one remark that I found out. You can use %% as user_attribute value to ignore the filter. So management can get %% values and others one or more values separated by comma.

%% translates to WHERE user.city IN (’%%’)
California translates to WHERE user.city = (‘California’)
New York, California translates to WHERE user.city IN (‘New York’,‘California’)

I just retired this article, adding a note with links to that information in Looker Docs.

As of Looker versions 6.6+ you will need to change a user attribute’s data type to “string filter (advanced)” if you are using a symbol like a wildcard (%), dash, underscore, etc. in an access filter. A normal string type will cause equal to logic in your SQL= vs LIKE or IN logic which is applied by a string filter (advanced) type.

2 Likes