How to apply access_filters to views

[LookerExperts]

Knowledge Drop

Last tested: May 6, 2020

The Problem

I have an access_filter for one of my explores. But I'd like to use it for a view instead so that it will automatically come through for all explores which use this view.

A Workaround

We can replicate the functionality of access filters in views by referencing the user attribute in the WHERE clause of the sql_table_name or derived table sql parameter.

1. Assume we have the following explore-level access filter that we want to replicate:

explore: customer {

access_filter: {

field: customer.name

user_attribute: allowed_customers

}

}

2. If the view is not a derived table, we can add a WHERE clause to the sql_table_name parameter like so:

view: customers {

# old version

# sql_table_name: public.customers;;

# new version with added WHERE clause

sql_table_name: (SELECT * FROM public.users

WHERE customer.name = {{ _user_attributes['allowed_customers']}}) ;;

}

Q: This works, but what if I have multiple values inside that user attribute? Can I dynamically change between '=' and 'IN' based on the number of values inside the user attribute?
Sure! We can use Liquid to check whether the value has a comma. If so, then we can use IN instead of =.

view: customers {

# old version

# sql_table_name: public.customers;;

# new version with added WHERE clause

sql_table_name: (SELECT * FROM public.users

WHERE customer.name

# check for comma (multiple user attribute values)

{% if _user_attributes['allowed_customers'] contains ","%}

# if comma, use IN

IN

{% else %}

=

{% endif %}

{{ _user_attributes['allowed_customers']}}) ;;

}

This content is subject to limited support.