Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ybarsabal
Resolver I
Resolver I

RLS with Multiple Roles

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?

1 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. 

View solution in original post

3 REPLIES 3
GilbertQ
Super User
Super User

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




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors