Forum Discussion
RLS cumulative security
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.
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.
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.
Please let me know if you see a hole in this logic, if I missed something, or you have anything else to add.
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.
