RLS - Grant specific users access to all agent data at a specific office location

[WinterMist]

Hello Community,

Please see the following PBIX for reference.

https://drive.google.com/drive/folders/13HqZmd_S7YEcTLWUr2txNN-7ysRug3ZX?usp=drive_link

   2023.08.04 User Security.pbix

Key Points:

1) One layer of RLS already exists & is working fine.  No problem.

   - On 'D Agent', the DAX filter expression is: [Agent Email] = USERPRINCIPALNAME()

   - When any agent views the report, RLS correctly filters to only show data pertaining to that specific agent.

2) Business requires that a second & separate layer of RLS be implemented.  This is what I'm not sure how to do.

   - When any user in 'User Security'[User Email] (i.e. USERPRINCIPALNAME() ) views the report,

        RLS should filter to only show data specific to values in 'User Security'[Office ID].

   - NOTE: In most cases, a user is NOT an agent, but it does happen occasionally.

   - NOTE: Users are not unique in 'User Security'.  The same user can appear multiple times in the table, each 

        time linked to a different Office ID.

EXAMPLE:

1) Without any filters applied, you can see the following 3 visuals.  (2 slicers & 1 table)

2) The 'User Security' table shows that User1 is linked to 2 different offices (Office IDs: 3 & 2)

As a result, when user1@test.com views the report, RLS must filter to show only the data pertaining to these 2 Office IDs:

- Office ID 3 = Detroit, MI

- Office ID 2 = Sacramento, CA

(Of course, all data for any other Office ID should be hidden from this user.)

'User Security'

      

'D Office'

The desired result should only show what is selected below.

Anything not selected should not even be visible to User 1.

Here are the remaining model items:

'Sales'

'D Agent'

Regards,

Nathan

[parry2k]

@WinterMist read more about multiple roles here Row-level security (RLS) guidance in Power BI Desktop - Power BI | Microsoft Learn

In nutshell, this is what is says: When a report user is assigned to multiple roles, RLS filters become additive. It means report users can see table rows that represent the union of those filters. What's more, in some scenarios it's not possible to guarantee that a report user doesn't see rows in a table.

👉 Learn Power BI and Fabric - subscribe to our YT channel - @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤️

[WinterMist]

@parry2k 

Not sure if you saw my previous post.

Can you advise on the following q's?

1) When 2 different RLS rules are active for a user, does RLS automatically apply them both?

2) If there is a conflict, is there a way to specify that only 1 RLS rule should be applied & not the other(s)?

Thanks again for your help!

Nathan

[WinterMist]

@parry2k 

Thanks very much for your help on this.

This makes sense so far.

However, in the case where the user is an agent, then both RLS rules would conflict with each other.

1) 'D Agent' Email = USERPRINCIPALNAME()

   - Only display data specific to the agent logged in (data for 1 single agent)

2) 'User Security' Email = USERPRINCIPALNAME()

   - Only display data specific to the agency(-ies) designated for the logged in user in 'User Security'.

      (data for many agents should appear)

How do I specify that this new RLS rule #2 should take priority over rule #1 in case of a conflict where the user is an agent?

Regards,

Nathan

P.S.  Update! 

1) So I am still interested to know how to have 1 RLS rule override the other if there is a conflict, because I'm sure this will come up.

2) However, I just realized this is not the requirement.  If there is a "conflict", we need to keep both rules active.  So if an agent logs in that exists as both an Agent in 'D Agent' and a User in 'User Security', that agent/user should see both his/her own data (RLS Rule #1) PLUS the data for all agents belonging to the specified Office IDs (RLS Rule #2).

Hopefully this makes sense.

[parry2k · Accepted solution]

@WinterMist it should be straightforward, set relationship as shown below:

Cross filter direction: both, apply security filter: on

Add another role on User Access table, I called it user access 

output:

👉 Learn Power BI and Fabric - subscribe to our YT channel - @PowerBIHowTo