There are numerous strategies for addressing Row Level Security (RLS) where users have multiple entitlements.
Consider this simple example where Steve needs access to North and South regions and Bob needs access to North and West.
In this data model – it’s easiest to consider applying RLS directly to the UserEntitlements table since that’s where the role assignments are. The goal is to have changes in this UserEntitlements table filter the Region table – but because of the duplicate Emails and Regions in the UserEntitlements table – it sits on the “many” side of the relationship – so filtering this table doesn’t impact the Regions table (note the direction of the relationship arrow).
This lack of filtering is quickly observed using this sample Role titled “UPN_ONLY”
Selecting View as Role – and explicitly typing an email in the model – will be sufficient to test this:
With the “Role” active – we can navigate back to the Table View and see our UserEntitlements table has been filtered as expected.
But the change does not pass to the Region Table (recall the direction of relationship).
Most people would approach this problem and simply think:
“Just make it a bidirectional relationship”
And with a quick change…
We can see that adding a bidirectional relationship still has no impact on the Region table.
So now what?
Well in this case – we have to change our focus from the UserEntitlements table to the Region table. RLS filters written directly on the Region table will ultimately filter the Sales table and give the desired results to the report viewer.
But how can we pass two (or more) regions from the UserEntitlements table to the Region table?
Enter LOOKUPVALUE’s second search term!
LOOKUPVALUE( <result_columnName>, <search_columnName>, <search_value> [,<search2_columnName>, <search2_value>, ]… [,<alternateResult> ] )
For those who cut their teeth on Excel – LOOKUPVALUE is very similar to a VLOOKUP or (INDEX/MATCH). For that reason – it traditionally only works when one consistent value is returned.
We can use the formula on the Regions table in the following manner:
When we only use one search term – the formula (while technically written correctly) – throws an error because of the multiple regions (North and South) that are returned for Steve.
Here’s where the second search term comes in:
Think of it as acting like an “AND” operator and essentially concatenating Email + Region.
The formula looks like this:
And with that one little addition to our RLS formula – everything starts to work as expected:
And this trick can work with more than two operators (although I haven’t come across a good example of needing 3 or more yet).
Of course – remember if this were an actual model you plan to publish – make sure to also implement a USERPRINCIPALNAME() filter on the UserEntitlements table.