Forum Discussion
Dynamic RLS and all access
Hi Community!
I'm new on this forum, prior i have found all my answers here but today i want to ask my first question. Please forgive me any newbie errors.
I have created dynamic rls for my report and it is working for the users that are listed in the roles that are restricted (userprincipalname). Now my question is i have some users that should have access to 100% of the data. So in the rls roles currently i have only listed the people with restricted access, not the people with 100% access as those don't have a relationship to the data. So i figured if i just share the report with them they should have access however when they open the report it says "The report can't be viewed because the underlying dataset uses row-level security (RLS)".
How can i share with someone to have access to all?
Or do i need to create a new role for those members?
Let me know if you need additional information in order to understand my request.
I think the short answer is, yes, you have to add a "No RLS" security group to put them in. Make sure that anyone you have in "No RLS" is not also in any other RLS group.
There is a longer answer if your scenario is:
- you are adding all users (via distribution list email, security group, etc.) to a single dynamic RLS group that leverages USERNAME/USERPRINCIPALNAME against a Users table
AND - your Users table only includes people with some kind of restriction, but people who are in the RLS group but not in the Users table should see all rows
In this case, you can create a measure that returns TRUE if the current user is NOT in the Users table, and then just add that to your RLS conditions with an OR.
Example:
Say you have a simple dynamic RLS model like so:
With RLS set up like this:
Then you can create the following measure:
Super User Check = CALCULATE( ISEMPTY( Users ), TREATAS( { USERPRINCIPALNAME() }, Users[User] ), REMOVEFILTERS( Users ) )And update your RLS accordingly:
Remember, everyone still needs to be added to Dyn RLS in your dataset security settings on the service.
- you are adding all users (via distribution list email, security group, etc.) to a single dynamic RLS group that leverages USERNAME/USERPRINCIPALNAME against a Users table
3 Replies
I think the short answer is, yes, you have to add a "No RLS" security group to put them in. Make sure that anyone you have in "No RLS" is not also in any other RLS group.
There is a longer answer if your scenario is:
- you are adding all users (via distribution list email, security group, etc.) to a single dynamic RLS group that leverages USERNAME/USERPRINCIPALNAME against a Users table
AND - your Users table only includes people with some kind of restriction, but people who are in the RLS group but not in the Users table should see all rows
In this case, you can create a measure that returns TRUE if the current user is NOT in the Users table, and then just add that to your RLS conditions with an OR.
Example:
Say you have a simple dynamic RLS model like so:
With RLS set up like this:
Then you can create the following measure:
Super User Check = CALCULATE( ISEMPTY( Users ), TREATAS( { USERPRINCIPALNAME() }, Users[User] ), REMOVEFILTERS( Users ) )And update your RLS accordingly:
Remember, everyone still needs to be added to Dyn RLS in your dataset security settings on the service.
- you are adding all users (via distribution list email, security group, etc.) to a single dynamic RLS group that leverages USERNAME/USERPRINCIPALNAME against a Users table
I realize it in one report that I use only the userprincipalname to indentifty the person and than create a own role-access table in the report itself. Example:
If you have 3 departments X,Y,Z , I but the filter criteria (X,Y,Z) in this table.USER FilterDepartment 1 X 2 X 2 Z 3 Y I use this table as a lookup table in my dax expression to filter the different other tables in the report.
