Proxy user for dynamic row level security with organizational hierarchy

[PoojaG]

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!

[PoojaG · Accepted solution]

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]

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.

[PoojaG]

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?  

[lbendlin]

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

[PoojaG]

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? 

[lbendlin]

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.