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
LD2022
Helper II
Helper II

Row Level Security - Admin Users not in security tables

We have implemented row level security based on below requirement :

 

one AD group XYZ that has below user levels based on security predicate as follows :

1. Executive : [Executive Email]= userprincipalname()

2. Head of : [Headof Email]= userprincipalname()

3. Sales Mgr : [SM Email]= userprincipalname()

4. Specialist : [Email]= userprincipalname()

 

This works perfectly fine when AD group is assigned to all the roles in Power BI service.

However, there are few users in the AD group that needs to see everything irrespective of RLS filter but are not part of the Users Hierarchy  table. I created a role "All Roles" where 1=1 and added AD group to it.

Although it works for "All Roles", the RLS roles filters aren't working anymore.

 

Kindly suggest.

4 REPLIES 4
jaweher899
Super User
Super User

It seems like the problem is caused by the creation of the "All Roles" role that doesn't have any restrictions. When a user is a member of this role, they can see everything in the report, regardless of the RLS filters defined for other roles. To resolve this issue, you can create a separate role for those users who need to see everything, and give them access to all the data in the report without using RLS.

Alternatively, you can modify the security predicates of the existing roles to include the conditions that allow the desired users to see everything. For example, you can add an OR clause in each security predicate that checks if the current user is one of the users who need to see everything.

Here is an example of how you can modify the security predicate for the "Executive" role:

[Executive Email]= userprincipalname() OR [Email]= "<specific email of the user who needs to see everything>"

Note: Replace <specific email of the user who needs to see everything> with the actual email of the user who needs to see everything. You can do the same for other roles as well.

By modifying the security predicates in this way, you can ensure that the desired users can see everything while still using RLS to restrict access to the other users.

Hi @jaweher899 

Thank you for your response. As I mentioned in my request, I have already created "AllRole" with no RLS i.e 1=1but it seems to apply the 1=1 for all users and show everything to executives, Head of , etc without checking username.

 

Basically, if the login user is not in the User Hierarchy table then he should see everything.

What's not exactly working please? May be you've some users exist in more than one AD.

We have only 1 AD group with executives, Headof,SM, Specialist as well as admin users.

 

As the admin users are also members of the same AD group , the condition 1=1 overrides the other RLS filters and displays everything to other users.

 

Sales AD Group         
          
User RoleExists in User Table ExecutiveHead OfSMSpecialistAllRole
Aa@sales.comExecutiveY[Executive Email]=userprincipalname()TRUEFALSEFALSEFALSETRUE
Bb@sales.comHead OfY[Headof Email]=userprincipalname()FALSETRUEFALSEFALSETRUE
Cc@sales.comSMY[SM Email]=userprincipalname()FALSEFALSETRUEFALSETRUE
Dd@sales.comSpecialistY[Email]=userprincipalname()FALSEFALSEFALSETRUETRUE
Ee@sales.comAllRoleN1=1TRUETRUETRUETRUETRUE

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