RLS - Assign 2 security roles to a user

Question

My simplified data model is like below - "Prod level 1" and "GEO" are distinct valued tables from my two dimension tables - Prod mapping and GeoCountries - so I can create many to many relationship between my security tables and the 2 dimension tables.

Now I want a user to be limited to APAC region, and Prod A only. I added this user in both security tables - secure by prod, and secure by GEO, and assigned both sucrity roles to him. When I test it in PBI desktop (image 2) , the security doesnt do anything. If I only select 1 role to test, I can see this user is limited to see APAC only but for all products,, or Prod A only but for all GEOs.

Is there a way I can make both security roles to work on a user?

Thanks a lot,

NM

anonymous · Answer

RLS is an additive security system.&nbsp; Whatever you secure by, they will have an all of those allows added together.
&nbsp;
The next part is that your filtering has to work.&nbsp; Without RLS, can you set up 2 filters on a test report page and get the filtering to work there as well?

anonymous · Answer

Wow, yeah your data model is certainly the problem.&nbsp; You need to start again and redesign this.&nbsp; You are trying to relate data that cannot be related based on how it is presented.&nbsp; I would question if you have created unncessary tables that should be combined to create a streamlined model.
&nbsp;
I also should point out that it looks like you might be putting in too many 2 way relationships.&nbsp; They do some great things, but they allow you to make errors that come back to bite you once you expand your model.

nanma94 · Answer

Thank you Ross. If I understand your reply correctly, I created a visual table to show the data flow from secure by GEO -> GEO -> GeoCountry.

And you are absolutely correct it throws error saying cant determine the relationship between 2 or more fields. 2 tables connect - either Secure by GEO/GEO, or GEO/GeoCountry, but all 3 tables dont connect and throw error. What could be the reason? My GEO table is just a summarize function all GeoCountry[GEO] column. and the relationship is bidirectional.

Regards,

NM

anonymous · Answer

Take a snip of the table relationship from inside Power BI. I'm expecting you are trying to link data through a Many to one to Many relationship in a way that doesn't make sense.&nbsp; Bi-directional can give you a false sense of security.

nanma94 · Answer

Anonymous&nbsp;Ross I dont think I Understand. Can you give me more hint:)&nbsp;Thanks much.&nbsp;NM

Forum Discussion

RLS - Assign 2 security roles to a user

7 Replies

Related content

Role Level Security

Assigning users row level security roles

How to migrate RLS roles assignment between datasets

Security roles

Workspace Role