cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JeanWang
Helper V
Helper V

Dynamic RLS based on two columns

I'm wondering if it's possible to create a user access table like this. So I have a fact sheet (employee data), I need to give HRBPs access to data based on their roles. Most HRBPs are responsible for single entities, so I list their entity in column "Entity", some HRBPs are responsible for the whole sales sector, that means they should have access to all SALES entities. 

 

When I try to link this table to another table in data modeling, I get an error message as below. 

 

How can I solve the problem?

 

User access

Column 'Company/Entity' in Table 'user access' contains blank values and this is not allowed for columns on the one side of a many-to-one relationship or for columns that are used as the primary key of a table."

User Access Table

EntitySectorEmail
A user1@contoso.eu
B user2@contoso.eu
C user3@contoso.eu
D user4@contoso.eu
E user1@contoso.eu
F user1@contoso.eu
G user1@contoso.eu
H user1@contoso.eu
I user1@contoso.eu
J user1@contoso.eu
 SALESsectorleader@contoso.eu

 

1 ACCEPTED SOLUTION

@JeanWang You could create two RLS roles
RLS which would be used for all people with single entity access

and 'No RLS' role like this:

Rigensis_0-1613493285827.png

When you publish the report, put all users responsible for the whole sales sector under this 'No RLS' role, and they will be able to see all the data, as effectively for them the RLS is not applied as the restriction of 1=1 is always true, thus will be seeing all the rows.

 

Hope this helps

 

View solution in original post

8 REPLIES 8
v-cazheng-msft
Community Support
Community Support

Hi, @JeanWang 

If you want super users access data of the entire dataset and not restricted by RLS, you can give them Build permission. And apply RLS to those users who can only access single entity. For more details, you can refer Ways to give Build permission.

 

Best Regards,

Caiyun Zheng

 

Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

@v-cazheng-msft i think that is incorrect. Build permissions do not override RLS.  To override RLS you need to have contributor or better membership in the underlying workspace. And you want to think twice before you do that for regular (management) users.

Hi, @lbendlin 

If a user has contributor or better membership in the worksapce, actually he will has build permission to the datasets of the entire workspace. And then RLS will not restrict his acess to the data in the dataset. RLS will not take effect to him. 

 

v-cazheng-msft_0-1613446565074.png

 

Best Regards

Caiyun Zheng

 

 

Thanks, however, i don't want to add the user to the member group of the space because i'm not sure if that will give the user access to the whole sharepoint site behind the group. There are lots of sensitive info in the sharepoint site which the user should not have access to. 

 

 

lbendlin
Super User III
Super User III

actually no, it won't necessarily slow it down. Being verbose at a lower  level is often better than having to apply complex rules.

 

Test it out and compare the performance for both approaches.

lbendlin
Super User III
Super User III

RLS rules apply to the entire table (the "R" in RLS) as opposed to CLS rules that Power BI doesn't support yet.

You can make the rule as complex as you want, even including related tables. Don't go overboard though - RLS is costly from a performance perspective.

You are right that RLS seems to slow down the reports significantly. In addition to creating a customized user acess based on a table. How can I give some super users access to the whole dataset? I don't want to include those super users to the access table because that will add many lines to the table and slow down the filtering speed. 

 

 

@JeanWang You could create two RLS roles
RLS which would be used for all people with single entity access

and 'No RLS' role like this:

Rigensis_0-1613493285827.png

When you publish the report, put all users responsible for the whole sales sector under this 'No RLS' role, and they will be able to see all the data, as effectively for them the RLS is not applied as the restriction of 1=1 is always true, thus will be seeing all the rows.

 

Hope this helps

 

View solution in original post

Helpful resources

Announcements
User Groups Public Preview

Join us for our User Group Public Preview!

Power BI User Groups are coming! Make sure you’re among the first to know when user groups go live for public preview.

March Update

Check it Out!

Click here to read more about the March 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.