Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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?
Solved! Go to 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.
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.
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.