Allowing User Higher Security

[jengwt]

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]
         )
     )

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.

[jengwt · Accepted solution]

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.

[v-xjiin-msft]

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.