Solved: Dynamic RLS and all access

Anonymous · ‎03-17-2023

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.

MarkLaf · ‎03-17-2023

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.

View solution in original post

MarkLaf · ‎03-17-2023

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.

Anonymous · ‎03-17-2023

Thank you!

The simple answer worked for me! I added a group called "All access" that doesn't have a relationship. Users i put in there showed all data.

Appreciate the help!

andhiii079845 · ‎03-17-2023

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.