Forum Discussion
RLS with org hierarchy
Hello
I need to develop a dynamic security set up for the data model being built and needed some help.
My model has a shipment table with managers responsible. These managers have superiors as SVP’s and their superiors as Presidents. Shipments data will have the Manager or SVP's tied to shipment lines. But will not have the President’s ID’s.
The ask is to have the report be viewed by only the Managers or superiors who are part of the hierarchy.
President P1 - Can view all the data under his line of business. There are 4 president's who can view all the data.
SVP1 - SHould view details of shipments of all managers under his Group.
Managers can view only the data they are tied to in the shipments.
I was looking at the below link , but in my scenario i could have multiple Level 1's, so the path gets distorted.
https://radacad.com/dynamic-row-level-security-with-organizational-hierarchy-power-bi
Attaching the sample data that i am working with.
I have this hierarchy in a table -USers_hier.
Can RLS be applied if the user is not part of the shipments data?
https://drive.google.com/file/d/14GfMN15z7irifqC9WqibZbySpWD-vGkd/view?usp=sharing
Anonymous , if their reportees is part of if they should get access to it.
I think the MBAS Gallary video they have even shown how to bypass it for a certain user. Saw it long back so not sure now. Even you can have a fact where you do no apply RLS and give access to the executive from that
5 Replies
- amitchandak
Super User
Anonymous , refer if these can help
RLS - Row Level security
https://community.powerbi.com/t5/MBAS-Gallery/Microsoft-Power-BI-Unleash-row-level-security-patterns-in-Power/td-p/712613
https://www.blue-granite.com/blog/using-dynamic-row-level-security-with-organizational-hierarchies
https://docs.microsoft.com/en-us/power-bi/service-admin-rls
https://blog.tallan.com/2018/04/10/row-level-security-in-power-bi-part-1-roles-and-users/Thanks amitchandak
These are good reads on RLS.
I wanted to clarify 1 point.
If there are users - Mainly executives who are not part of the FACT data and if they need access to all the reporting, what is the usual practice? Is it better to NOT to add them to any roles?
I liked the dynamic approach narrated by Reza here - https://radacad.com/dynamic-row-level-security-with-organizational-hierarchy-power-bi. But, it kind of breaks if there are multiple managers for a user.
- amitchandak
Anonymous , if their reportees is part of if they should get access to it.
I think the MBAS Gallary video they have even shown how to bypass it for a certain user. Saw it long back so not sure now. Even you can have a fact where you do no apply RLS and give access to the executive from that
v-xicaiCommunity Support
Hi Anonymous ,
You may set Row-Level Security (RLS) to manage roles, please see the link Power BI Desktop Dynamic security cheat sheet, which described the detailed steps. Maybe it doesn't work, there are some tips to let it work and test it efficiently.
Best Regards,
Amy
Community Support Team _ Amy
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi,
In this case you'll have to create 2 roles;
Role 1 - for managers/SVPs whose details are available in the shipment data. You can use pathcontains and username/userprincipalname to validate the user. Add managers and SVPs to this group in the RLS of dataset.
Role 2 - for Presidents whose details are unavilable in the shipment data. Just create a role without using security filter functions and add presidents to this group similarly.
This way Presidents have access to view all the data and managers/SVPs have access to view the data that they are entitled to.
