Dynamic Row Level Security data setup and questions

[lmhinson]

Hi all.

I'm building a report with multiple levels of managers (rep, district manager, regional managers, sales director) needing access to only their data. Sales director would see whole group, regional managers would only see results from their DMs and reps, DMs would only see their reps.

Assuming we structure our data roughly like this below - with the org structure essentially set up in the data, and with the entire report sliceable by these roles, will this work for dynamic RLS without having to set up paths or do anything else complicated?

So, can I set up a Sales Director role with "Sales Director email = USERPRINCIPALNAME()", and then another role for RDs as "Regional Director email = USERPRINCIPALNAME()" from a different column in the same table, and should that work when users log in? Or can you create one role (permissions) with "Sales Director email = USERPRINCIPALNAME()" + "Regional Director email = USERPRINCIPALNAME()" , etc?

Important note: I tested this, and when I "view as" a certain email address in Desktop, it filters as if should. But when I actually published to the service and sent it to someone in the data list, they saw the entire report and not just their data. Note I did add that one test email address into the security settings in Power BI service. 

Can anyone help me sort through this? Thank you!!

[otravers]

I would target AAD security groups rather than individual users, it will be easier to maintain. See the RLS link in my signature.

[shadow9600]

In order for RLS to work right in the service, you need to first add the users to the report dataset security, then  publish the report into an app (making sure the users have permission to the app), AND ensure the users are NOT members of the workspace the report is in. When a user is a member of the worksapce all RLS is bypassed for that user.

[lmhinson]

@shadow9600 Thanks - I did discover that was why my internal test wasn't working. Didn't realize you have to apply permissions in two places. 

[freginier]

import your data + user data 

create one RLS  'user date' [email] = USERPRINCIPALNAME()

link your data + user data and then your data will be filtered 

______________________________________________________

If you found this post helpful, please give Kudos C

[lmhinson]

@freginier In this scenario, you are saying that all user data would have to be in one dimension table, and all email addresses would have to be in the same column within that table (as opposed to split how I have it now, with org structure built out through different columns)?

How would you handle org structure in that case?

Thanks!

[freginier]

Yes you need to add file with all users something like : email , Role (director, manager, etc.)

Maybe I can help you anymore if you send your power bi without sensitive data