RLS security not working

[Ben1981]

Hi,

Having trouble getting RLS working on my report. 

• I have two tables, one called HR Employees and the other is Managers. They are connected via a one way relationship from Manager to Employees.
• I have created a role called Manager in the PBI file using the email from the manager table and USERPRINCIPALNAME. e.g.  [Email Address] == USERPRINCIPALNAME(), Also tried [Email Address] = USERPRINCIPALNAME()
• I published the report to a pro workspace, opened security and added a manager email to the relavant group.
  What I expect to happen is that when a manager opens the report, they should only see employees that are related to them. 
  However, it doesn't work and they see everyone and I can't figure out why.

Can anyone help please?

[Ben1981 · Accepted solution]

I was able to solve the issue, I had done everything correctly and the initial user I had testing my data was originally a member of the workspace but I downagraded them to a viewer for for this test because I believed it should not have impacted the RLS as far as I know. However, it never worked for them and they were able to see all data.

So, I found another test subject who was not a member or viewer of the workspace and I shared the report with them, they requested access which I provided and the RLS seem to work just fine for them. 

So yeah a weird one but it works!

Thanks,
Ben

[v-agajavelly]

HI @Ben1981 ,

I think the main reason your RLS isn’t working is because your relationship between Managers → HR Employees is one-way.

RLS filters only propagate across relationships if the direction allows it. Since your current setup is one-way, the filter on the Managers table doesn’t flow down to Employees that’s why your manager sees all employees.

 If you can’t change relationship. Use a calculated table or measure to enforce RLS.

Employee_RLS = FILTER('HR Employees', RELATED(Managers[Email Address]) = USERPRINCIPALNAME())

Apply RLS on this new table instead.

This approach ensures the manager only sees their own employees, even with a one-way relationship.

Thanks,
Akhil.

[Tahreem24]

@Ben1981 , Try validating the RLS with Test as a Role feature in both Desktop and PBI Service:

https://learn.microsoft.com/en-us/fabric/security/service-admin-row-level-security#validating-the-ro...

[rohit1991]

Hi @Ben1981 

1. In Model view, set the relationship between Managers and HR Employees to Both directions.

2. In Manage Roles, apply this filter on the Managers table:

[Email Address] = USERPRINCIPALNAME()

3. Don’t put RLS on the Employees table - it won’t filter properly.

4. Test it in View as Role - Manager in Power BI Desktop.

5. After publishing, open Dataset - Security and add the managers’ email IDs under that role.

6. Republish if you make any changes to roles or relationships.

7. Each manager will now only see their own employees. 

[Ben1981]

Thanks but that is litterally what I've done with the one exception being my relationship between tables is one way, Manager to Employee. Anything else that I need to try?

[danextian]

Hi @Ben1981 

Could there be other roles that override the manager filter? For instance, a role that grants access to all rows would take precedence over any manager-level RLS. Additionally, remember that RLS only applies to users with the Viewer role in the workspace. Users with Contributor or higher permissions will still see all rows.

[Ben1981]

No other roles added, just the one for a manager and the person I'm using as a test subject is only a viewer to the workspace. I'm at a total loss, it should work! 😞