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.
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?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.