Dynamic RLS with Complex Hierarchy

[StephenK]

Hey all,

Looking for suggestions and feedback. I'm trying to set up my model in a way to allow me to apply RLS at three different levels dynamically for hundreds of users. I'm pretty close, but I feel like there are perhaps I'm missing some things as far as optimization. I have two attributes I need to build my rules around. The first is office location and the second is department. Here are my parameters I'm working around...

1) Some users need unrestricted access. (No problem there. I have one RLS role for privileged users.)

2) Some users need restricted access down to the office location level. In other words, User_A only gets to see Office_A, but all departments in Office_A are unrestricted. (No problem here. I have another RLS role for users restricted to Office level and a table in my model that includes office assignments.)

3) Some users need restricted access down to the department level. They may have access to multiple offices, but should only see specific departments in those offices. Example 1: User_A can see departments 1, 2 and 3 out of 10 for Office_A. User_B can see departments 1 and 2 for Office_A and Office_B. (I have a third RLS role for users restricted to the Department level within office(s) and a table in my model with department assignments--these have a unique identifier for every office).

Anyway,

My initial thinking was I would set up three RLS roles. 1) Privileged. 2) Office Level. 3) Department Level.

Then I would have two RLS tables in my model, one with Office assignments by User. Another with Department assignments by User. These would restrict the view of the fact table based on the UserPrincipalName. There would be three AD security groups where users would be assigned based on their intended viewing privileges and these would be mapped to each of the RLS roles.

I've tested everything and it seems to be working, but I want to make sure I'm not missing any best practices or steps to optimize  or if anyone has a better idea on howI could simplify this further...

My original ideal was to have just two RLS roles because then there would be less back and forth with IT--Restricted and Unrestricted. But, I couldn't think of a way to do that and handle the complexity of the department restrictions. I also need to make sure the roles don't conflict.

Any feedback on my though process? Here is a screenshot of my model:

[lbendlin]

Then you will need to change your data (or your data model)  to enforce a 1:M relationship option which then allows you to set the single filter direction.

[StephenK]

@lbendlin Understood! Thanks for the feedback. I've made changes as follows:

Note the settings on the Office Access to Offices relationship is still set to both. This is to ensure that Users with a null value in their office assignment (as indicated in red) have unrestricted viewing while those with an assignment have restricted viewing.

[lbendlin · Accepted solution]

It usually sounds good. What you should not do is allow filter propagation in RLS tables. That defeats the purpose of RLS.

[StephenK]

Hey @lbendlin,

Can you elaborate further on what you said regarding filter propagation into the RLS tables? 

If you're referring to the cross filter direction, PBI will only allow me to have the cross filter direction set to "Both".