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

Proxy user for dynamic row level security with organizational hierarchy

Hello,

Overview: I created a report which uses Path function to get the org hierarchy structure and based on that I created a User role with the below dax formula: 

pathcontains([path],userprincipalname()) && [EMAIL_ADDR] <> userprincipalname()

This allows the user to see data only under his org.

Problem: There are few users at CEO level who may or may not open this report even though they have access but have their Chief of Staff to view the report for them. I have the manual list of such users which I can import.

Question: How do I incorporate another condition in the above dax query to allow proxy users (Chief of staff) to access the report only for their corresponding CEO's?

thank you!

1 ACCEPTED SOLUTION
PoojaG
Helper II
Helper II

I was able to find a solution. 

  • I created a Proxy users table with the list of email ids who needed a proxy users to access on thier behalf. it's a simple table with two columns. Email id and Proxy email id. 
  • On the Master user table where I already had a path function, I created a calculated column as below: Level 2 = LOOKUPVALUE(Master[EMAIL_ADDR], Master[EMAIL_ADDR], PATHITEM(Master[Path],2,TEXT))This basically breaks down the Path and returns the 2nd level from the path where the proxy users will be needed.
  • created a relationship between the Proxy user and Master table (1:M)
  • On othe master table, created a calculated column Proxy user = IF(PATHCONTAINS(Master[path],RELATED('Proxy users'[email address])) && Master[EMAIL_ADDR] <> RELATED('Proxy users'[email address]),RELATED('Proxy users'[Proxy email]),RELATED('Proxy users'[email address]))
  • Finally, created a new role called Proxy user and filtered on the master table where [Proxy user] = userprincipalname()

It works perfectly!

 

 

 

View solution in original post

6 REPLIES 6
PoojaG
Helper II
Helper II

I was able to find a solution. 

  • I created a Proxy users table with the list of email ids who needed a proxy users to access on thier behalf. it's a simple table with two columns. Email id and Proxy email id. 
  • On the Master user table where I already had a path function, I created a calculated column as below: Level 2 = LOOKUPVALUE(Master[EMAIL_ADDR], Master[EMAIL_ADDR], PATHITEM(Master[Path],2,TEXT))This basically breaks down the Path and returns the 2nd level from the path where the proxy users will be needed.
  • created a relationship between the Proxy user and Master table (1:M)
  • On othe master table, created a calculated column Proxy user = IF(PATHCONTAINS(Master[path],RELATED('Proxy users'[email address])) && Master[EMAIL_ADDR] <> RELATED('Proxy users'[email address]),RELATED('Proxy users'[Proxy email]),RELATED('Proxy users'[email address]))
  • Finally, created a new role called Proxy user and filtered on the master table where [Proxy user] = userprincipalname()

It works perfectly!

 

 

 

lbendlin
Super User
Super User

That sounds more like static RLS.  In dynamic RLS scenarios you usually have a mapping table that list all permissions for each user separately, and allows you to handle such proxy scenarios with ease.

I want the dynamic rls to be still in-tact. So perhaps create a separate report for proxy users and apply static rls. Do you have any document I can follow to execute this?  

That's what I am saying - go full dynamic RLS. Have a reference table with all the permissions for all the users.

I'm sorry, I'm not following. Currently I have created one role as "User" with the below formula:

pathcontains([path],userprincipalname()) && [EMAIL_ADDR] <> userprincipalname()

query for path is simple = path(EMAIL_ADDR, manager_email)

Access is provided only to leadership roles. but for some leaders, we want to provide access to their chief of staff (proxy users) to view the report on their behalf if they are not able to for some reason. 

How do I embed this additional condition to the dax for the role created? 

or shall I create another role for proxy users? 

What you are doing is not dynamic RLS. It's something in between.  Dynamic RLS maps a USERPRINCIPALNAME() to a reference table that holds all permissions.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

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