Setting up an access_filter that applies to only specific users

[LookerExperts]

Knowledge Drop

Last tested: Mar 27, 2019
 

Every user who accesses an Explore that has an access_filter must have a value in the referenced user attribute, including administrators. More information can be found in this Community post.

One way we can work around this is by using a CASE WHEN in a sql_always_where of an Explore, like so:

sql_always_where: CASE WHEN '{{ _user_attributes['attribute_name'] }}' = '' THEN 1=1 ELSE ${view_name.dim_name} = '{{ _user_attributes['attribute_name'] }}' END

What this does is it applies the condition 1=1 for users who do not have a value set for that user attribute. If we want a more secure where clause that uses a default value instead of no filter, we can do something like this:

sql_always_where:

${view_name.dim_name} = (

CASE WHEN '{{ _user_attributes['attribute_name'] }}' = '' THEN <default_value>

ELSE '{{ _user_attributes['attribute_name'] }}'

END

)

This content is subject to limited support.