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
SteveCarter1
Advocate II
Advocate II

RLS works but only if filter condition is true else user sees everything

Hi

Unsure how else to describe it in the title.

 

I'm creating a test report to play with RLS so I have a single flat SQL query pulling back a table of data that includes an e-mail address.

 

I have setup RLS using userprinciplename() as the filter for a 'user' role, which is basically:
[user email] = userprinciplename()

 

The [user email] is a configured field in the SQL database.

 

I have a basic page that includes a table that shows database row id, date and e-mail fields and a card that shows my userprinciplename() measure.

 

In both Desktop and Service that works great where the userprinciplename matches an email in the sql column table, but if a user views the report and they do not have any matching email, then they see everything in the report, like an 'admin' rls role would.

 

For example in Desktop I select View As / Other user and type in dummy data like qwerty@nowhere.com which doesn't exist in my sql data, then I see all sql data, but if I type in a real e-mail address of someone else, then the table does filter down to only that.

 

How do I restrict the view so if the email does not match upn then they should just see a blank report?

 

It almost seems I have to make sure there is data in the report specific to a user before I can allow them to view the report. The problem here is I need to use dynamic security via groups because we have too many users to deal with it manually.

 

Help appreciated.

1 ACCEPTED SOLUTION

Hi @SteveCarter1,

If you test your RLS with dummy email, you need to select two options: Other User and YourRole. Something like this:

Migasuke_0-1676284474890.png

'
Generally speaking - the set up you have should work just fine. If you still encounter some problems, try to create DIM table with RLS, Basically another table, which is connected via emails and filters your fact table.

 



If my answer was helpful please give me a Kudos or even accept as a Solution.

Let's connect on LinkedIn!

View solution in original post

3 REPLIES 3
SteveCarter1
Advocate II
Advocate II

In Desktop if I choose View as roles of 'None' I see everything.

If I chosoe role 'Other User' and upt in a dummy email address I see everything

If I choose role 'User' it filters the list to rows with my e-mail address.

 

What I want to acheive is no results if no match so I don't understand what I'm doing wrong where role 'none' or 'other user' matches everything?

Hi @SteveCarter1,

If you test your RLS with dummy email, you need to select two options: Other User and YourRole. Something like this:

Migasuke_0-1676284474890.png

'
Generally speaking - the set up you have should work just fine. If you still encounter some problems, try to create DIM table with RLS, Basically another table, which is connected via emails and filters your fact table.

 



If my answer was helpful please give me a Kudos or even accept as a Solution.

Let's connect on LinkedIn!

Thanks.  Needed time to do some testing with real 'friendly' end users and looks like it does work as expected, and thank you for the guide on selecting both options when testing it in desktop for myself.

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.