Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
jdobrzen
Advocate II
Advocate II

RLS cumulative security

Are Power BI security roles cumulative in the same way that Analysis Services Tabular is?

 

In the Permissions section of the arctile below it states:

 

"When a user is a member of multiple roles, the permissions defined for each role are cumulative. For example, if a user is a member of a role with the Read permission, and also a member of a role with None permission, that user will have Read permissions."

 

https://docs.microsoft.com/en-us/sql/analysis-services/tabular-models/roles-ssas-tabular

 

The reason I ask is because I have two RLS roles on a single Power BI dataset, and there are a few users that belong to both AD groups.  When I test with one of those users I get the following error message.  The error message makes me assume that the set of users needs to be mutally exclusive between the AD groups attached to the roles.  I believe this is not the case in Analysis Services Tabular.

 

PowerBIForumSecurityRoleQuestion.PNG

 

 

 

1 ACCEPTED SOLUTION

So I did some research to see if Analysis Services (AS) would respond the same way, and the short answer is it does.

 

I created a model with two different RLS "enabled" security roles, and assigned the same user to both.  I also tried this with the two roles assigned to different RLS tables securing two different dimensional tables. The results were the same.

 

databaseroles.PNG

 

TOP5 Role.PNGNEXT5 Role.PNG

 

When I access the model through Excel with this user, I receive the following error.  While not as descriptive as the error message in Power BI, I assume it is because of the same reason.

 

ExcelError.PNG

 

I experimented a bit further and found that the user can belong two seperate security roles, but not more than one of those roles can be RLS "enabled".  When I remove the RLS logic from just one of the two roles, I am then able to see all of the top 10 customer IDs that are defined in the RLS database table.  So it sticks to the fact that role based security is cumulative. One role has read access to the entire model, while the other only has access to the top 5 customers. This will result in being able to see all customers contained in the underlying RLS database table.

 

When running the same experiment against Power BI, it responded the same way.  When I have two roles that are both RLS "enabled" I get the original error message.  When I remove the RLS logic from one of the security roles, the security becomes cumulative and the user is then able to see all entries in the underlying RLS table.

 

So I guess the solution is to design around this behavior and avoid applying multiple RLS "enabled" security roles to a single model.  When I refactor the design to include just one security role with the following logic instead, I still get the desired result of having either or logic applied and allowing only project managers to see their projects, practice leads to see their practice's projects, and project manager/practice leads to see both.

 

refactoredRLSlogic.PNG

 

Please let me know if you see a hole in this logic, if I missed something, or you have anything else to add.

 

 

 

 

View solution in original post

7 REPLIES 7
Anonymous
Not applicable

to extend this thread:

What happens, if you have RLS Role(s) defined and a Users IS NOT member of any role?
In SSAS if a user is in NO Role he has NO Access to the Cube?

But i'm afraid in PowerBI this user would have FULL access to all data? (because no rls-filters are applied!?)

Does anyone tested that secnario already? I will try to reproduce...

@Anonymous If I understand you correctly, it would be a gaping security hole if all you had to do to circumvent a security role would be to just not be a member of it. 

 

Let's say you have a report built on top of a Power BI data model that has a RLS security role defined.  Also, the report is part of a published app and the user has access to the published app, but is not a member of the RLS security role.  When the user opens the report from the published app, they will be presented with a report that is filled with "X's".  This is because they have security to access the report, but not the underlying data.

 

The exception to this is if you're working inside of the app workspace as an admin.  A workspace admin does not have to be a member of the RLS security role, in order to see all of the underlying data.

Hi there, the above would only apply if the user is a member of the App Workspace.

If the user is a consumer of an App, or has a Dashboard or Report shared they will not be able to see anything.




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

GilbertQ
Super User
Super User

Hi there, do the users belong to one role with two AD Groups?




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

@GilbertQ For testing, my user currently belongs to one AD group that is applied to two roles.  In production I will have a user in two different AD groups that are applied to two different roles.

It would appear to me that it looks like having to roles with 2 users in 2 AD groups, when it comes to filter them correctly it cannot filter it to get the required result. And that is why you are getting the error?

I have used Dynamic Row Level Security where the user is in one role, but can still see multiple artifacts?




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

So I did some research to see if Analysis Services (AS) would respond the same way, and the short answer is it does.

 

I created a model with two different RLS "enabled" security roles, and assigned the same user to both.  I also tried this with the two roles assigned to different RLS tables securing two different dimensional tables. The results were the same.

 

databaseroles.PNG

 

TOP5 Role.PNGNEXT5 Role.PNG

 

When I access the model through Excel with this user, I receive the following error.  While not as descriptive as the error message in Power BI, I assume it is because of the same reason.

 

ExcelError.PNG

 

I experimented a bit further and found that the user can belong two seperate security roles, but not more than one of those roles can be RLS "enabled".  When I remove the RLS logic from just one of the two roles, I am then able to see all of the top 10 customer IDs that are defined in the RLS database table.  So it sticks to the fact that role based security is cumulative. One role has read access to the entire model, while the other only has access to the top 5 customers. This will result in being able to see all customers contained in the underlying RLS database table.

 

When running the same experiment against Power BI, it responded the same way.  When I have two roles that are both RLS "enabled" I get the original error message.  When I remove the RLS logic from one of the security roles, the security becomes cumulative and the user is then able to see all entries in the underlying RLS table.

 

So I guess the solution is to design around this behavior and avoid applying multiple RLS "enabled" security roles to a single model.  When I refactor the design to include just one security role with the following logic instead, I still get the desired result of having either or logic applied and allowing only project managers to see their projects, practice leads to see their practice's projects, and project manager/practice leads to see both.

 

refactoredRLSlogic.PNG

 

Please let me know if you see a hole in this logic, if I missed something, or you have anything else to add.

 

 

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors