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
Deepesh_V95
Frequent Visitor

How to apply dynamic Row level Security based on Username and location using Lookup

Hello,

 

This is related to Row-level security. Here is a dummy table on which I want to implement this RLS.

 

UserLocationlogin
BobAbob@g.com
RayAray@g.com
ChenAChen@g.com
BobBbob@g.com
RayCray@g.com
ChenAChen@g.com

 

The RLS formula I am using right now is - 

[location] = LOOKUPVALUE([location],[login],USERPRINCIPALNAME()) 

 

My end requirement is to make this RLS work on the case where if a user logs in he should see the data if the location he is in and also should see other users who are in the same location. 

But lookup is throwing an error 'A table of multiple values was supplied where a single value was expected'

Also, a single user can have access to multiple location and a single location could have multiple users. 

 

Please help me on how I can achive this level of Row-level security using either Lookup or using any other possible way.

 

Thanks a lot!

Deepesh

2 REPLIES 2
Salvador
Responsive Resident
Responsive Resident

Hello Deepesh,

 

You should use a table with all your users, must be single values, that connects with your database using bidirectional and security filters :D.

 

Regards,

Salva.

Well, I have tried that but by this way RLS will only filter based on Username and not on location. End result should be that if a user logs in, he should see all other users too who are in same location.

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.