Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

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

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

@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

 

 

Anonymous
Not applicable

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
Super User

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
Super User

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
Not applicable

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. 

 

 

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

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

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.