Dynamic RLS based on two columns

[Anonymous]

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

Entity	Sector	Email
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
	SALES	sectorleader@contoso.eu

[v-cazheng-msft]

Hi, @Anonymous 

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.

[lbendlin]

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

[v-cazheng-msft]

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. 

Best Regards

Caiyun Zheng

[Anonymous]

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]

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]

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.

[Anonymous]

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. 

[Rigensis · Accepted solution]

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

and 'No RLS' role like this:

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