RLS with Multiple Roles

[ybarsabal]

Consider a data set that contains sales data at the levels of USA, States, Sales Person Name.

Here are my desired roles:

SalesPerson = can see only his or her data.

StateManager = can see all data for a State.

I've set up the RLS for this, and it appears to work correctly. However, the security appears to break when a person is in both roles. 

1. First I assigned Mary to SalesPerson. The report showed her only her rows. Good!

2. Then I removed Mary from the SalesPerson role and assigned her to StateManager. The report showed her only her States. Good!

3. Then I assigned Mary to both SalesPerson and StateManager. The report showed her all states (all data). Bad!

What is going on?

[GilbertQ]

Hi there

By design RLS shows the least restrictive when a person is in multiple roles.

What this means is if a person is in 2 roles, it will show the combination of both of those roles.

What should be done is you would need to create a Dynamic RLS which has based off the most granular data, which in your example would be the Sales Person

[ybarsabal · Accepted solution]

Ok, I think I've identified the issue. The SalesPerson role was only filtering on the Fact table, and applying the filter to the State-to-Manager table (although unnecessary when only a SalesPerson) was necessary so that table was filtered when it became relavant in the StateManager role. 

[ybarsabal]

In my example, RLS is not showing the least restrictive of the two roles. It's ignoring both roles and just showing everything (neither role shows everything). 

The SalesPerson role is dynamic, filtering to rows where the UPN = SalesPerson in the main fact table.

The StateManager role uses a State-to-Manager table, filtering to States where UPN = Manager. This table has a relationship to the fact table joined on State.