RLS upside-down

[Maria_Maria]

Hi!

I need a way to allow only one user out of many to see all the data, and all other users to see the restricted data. 

I can't do a regular group as there are many users and they are added every day, so I basically need an opposite to a regular RLS: all users except one have limited access. 

Appreciate your help!

[Anonymous]

The simple answer is you create 2 roles

One with your RLS and that is given to everyone

then the second as an empty one (As in create it and do nothing), this second one is what you assign to the user that need everything.

Done 🙂

That simple

[v-xiaoyan-msft]

Hi @Maria_Maria ,

Please correct me if I have misunderstood.
You are now expecting one person in this organization to see all the content you want him to see all the time, while other users can only see limited content.
As a simple example, let's say you want to complete a manager-level RLS where your manager role users can see all the data all the time, and the people managed by the manager can only see the parts you want them to see, even if they are new to the organization and follow this rule.
If this is what you want, you can refer to this article below to set up an RLS.

Dynamic Row Level Security with Manager Level Access in Power BI 

Hope it helps,

Community Support Team _ Caitlyn

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[d_gosbell · Accepted solution]

Assuming you have an existing RLS role that is working the way you want and all you need to do is to give one of your users access to all the data. Then all you would need to do is to create a second role in your model with no restrictions or filters then add the user to this role. The permissions in roles are unioned together so this should grant unlimited access even if the user is also in the RLS role.

[Greg_Deckler]

@Maria_Maria Just create your RLS role for your one/few users. The other users will not have RLS applied if they are not assigned to an RLS role. You can prove this to yourself using the View as | Other user

[Maria_Maria]

hi @Greg_Deckler ,

This way 1 user will see the data based on his/her role, but the others will see all the data. What I need is exactly the opposite.

[Greg_Deckler]

@Maria_Maria Would need to understand your data to be more specific. But, in theory, create an Enter Data query with your users' UPN's that need to see all the data. Create an RLS rule like counting the rows in that table that match the UPN. If > 0 then return "true" to see all data, otherwise implement your RLS restriction. It's really kind of impossible to be more specific without knowing your data model and what you are trying to restrict.