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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors