Row Level Security for company hierarchy

[akhatri]

Hi 

I am trying to implement row level security but for it to work based on the Geo, Region and District (GRD) they look after and for Senior VP's for them to have open access to all data.

I have a tab which contains all the GRD structure which is used to filter all data, and a table with all Users and the GRD they should have access to.

SEC TABLE

User Email | Key

A | EMEANorthern Region

B | EMEA

C | EMEANorthern RegionUK & Ireland

D | 

GRD TABLE

Geo | Region | District | Key

EMEA | Northern Region | | EMEANorthern Region

EMEA | Northern Region | UK & Ireland | EMEANorthern RegionUK & Ireland

EMEA | | | EMEA

I want User A to see Northern Region and all district below so in this case UK & Ireland

I want User B to all of EMEA data including all Regions and Districts

I want User C to only see data for the district of UK & Ireland

I want User D to see all Geos, Regions and Districts

Is this possible?

Thanks

Atish

[v-zhenbw-msft · Accepted solution]

Hi @akhatri ,

For Dynamic Row Level Security, maybe you can refer the following blogs.

https://www.blue-granite.com/blog/using-dynamic-row-level-security-with-organizational-hierarchies

https://radacad.com/dynamic-row-level-security-with-organizational-hierarchy-power-bi

https://radacad.com/dynamic-row-level-security-in-power-bi-with-organizational-hierarchy-and-multipl...

In addition, RLS has some limitations, please refer this document.

https://docs.microsoft.com/en-us/power-bi/admin/service-admin-rls#manage-security-on-your-model

BTW, RLS will not work if the users have edit access to the report. They will see all the data.

Best regards,

Community Support Team _ zhenbw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lbendlin]

Are you planning to create a role per key?  And do you have a table that maps email addresses to keys?

[akhatri]

No I was not planning on doing that, was looking to utilise the email address and userprincipal name method which an option to filter it to the users accesa level.

Yes i do have anothet table which just contains just a list of the variations of keys.

[lbendlin]

At the very minimum you need

- one role that contains all users (can use PDLs for membership management)

- a reference table with user email and key. This reference table must sit at the outer edge of the star schema, and must control all its children dimensions and the fact tables downstream via the key

- a DAX rule for the defined role and the reference table that matches the user email to USERPRINCIPALNAME()

But that's the bare minimum and does not give you much wiggling room.