cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
jengwt Resolver I
Resolver I

Allowing User Higher Security

Community,

 

I've got our security roles for a report set up like so:

We have a table with employees, their managers, and their directors. A sample of what this table looks like is included below. I've got security set up with this formula:

OR(USERPRINCIPALNAME() = 'Table'[Employee]
     , OR(USERPRINCIPALNAME() = 'Table'[MGR]
         , USERPRINCIPALNAME() = 'Table'[DIR]
         )
     )

hierTableSample.PNG

 

This table is connected to others by the IDs of employees. It appears to correctly work for each level in the hierarchy; directors can only see people under them, managers can only see the people under them, employees in this role would only be able to see activity related to themselves.

 

However, recently we discussed this with some the end-using directors, and they said that they would like to allow their employees to see the activities of others on their teams; effectively giving them the same access as their managers.

 

MY QUESTION is, if security is based off of USERPRICIPALNAME, how can I make a new role that will basically ignore that and allow an employee to view what their manager views?

 

For instance, if I am Julie, then I should also be able to see Lauren's activities because we are both on William's team.

 

There may be some compliacations to this, but those should be subjects for other threads. Thank you for your suggestions and assistance.

1 ACCEPTED SOLUTION

Accepted Solutions
jengwt Resolver I
Resolver I

Re: Allowing User Higher Security

You can use the LOOKUPVALUE formula to get around using userprincipalname() as the basis for your RLS by making another field of the table the key off of which you're basing your RLS.

In this case, you would set up a new Role with RLS as:

 

'Table'[MGR] = LOOKUPVALUE('Table'[MGR], 'Table'[Employee], USERPRINCIPALNAME())

 

In other words, "give access to the records where the manager is that matching the manager of the user."

 

This would give an employee the same access as their managers.

You can apply this method to all kinds of things.

View solution in original post

2 REPLIES 2
v-xjiin-msft Solution Sage
Solution Sage

Re: Allowing User Higher Security

Hi @jengwt,

 

=> recently we discussed this with some the end-using directors, and they said that they would like to allow their employees to see the activities of others on their teams; effectively giving them the same access as their managers.

 

You can set Dynamic Row Level Security with Organizational Hierarchy in Power BI. However for the lowest level, the RLS just works for the current given user. It also means even one other user has the same level with the current given user, it is still unable to display data of the other user. So I'm afraid your requirement cannot be achieved.

 

By the way, as you said that the new RLS is just same as the managers. Why don't you just use the manager level RLS?

 

Thanks,
Xi Jin.

jengwt Resolver I
Resolver I

Re: Allowing User Higher Security

You can use the LOOKUPVALUE formula to get around using userprincipalname() as the basis for your RLS by making another field of the table the key off of which you're basing your RLS.

In this case, you would set up a new Role with RLS as:

 

'Table'[MGR] = LOOKUPVALUE('Table'[MGR], 'Table'[Employee], USERPRINCIPALNAME())

 

In other words, "give access to the records where the manager is that matching the manager of the user."

 

This would give an employee the same access as their managers.

You can apply this method to all kinds of things.

View solution in original post

Helpful resources

Announcements
Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

April 2020 Community Highlights

April 2020 Community Highlights

Info on our Super Users, MBAS content and badges, and updates to our support articles. - Read the full Community Highlights.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

Top Solution Authors
Top Kudoed Authors