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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
eirika
Frequent Visitor

RLS security

I have 3 tables:

  1. access - this table holds userid and access_id. each user has one row for each access_id he has access to. access_id is the foreign key for the tables t1 and t2
  2. t1 - this table holds objects that the user can have access to
  3. t2 - this table holds objects that the user can have access to

I need to have the security filter on the userid field in the access table. This way, when a user logs in this will filter only the rows in the access table for that user, and the rows in tables t1 and t2 will only show the rows that the user has access to.

 

the t1 and t2 tables will have unique values in the access_id field.

the access table will have duplicate values in the access_id. But when we filter on a user the values in access_id is unique.

 

when creating the relationship i powerbi, he want's to create a one-to-many relationship from t1-to-access. In the filter direction field it's only possible to select single or both. I can't find any way to say that the filter direction should go from access-to-t1.

 

The model looks like below. This seems like a simple thing, but for some reason it's impossible to filter from many-to-one side.

 

2024-08-09_11-33-14.png

One workaround is to select both in cross-filter direction and apply security filter in both directions. But this doesn't work when you have multiple tables connected to the access table.

 

Second workaround is to create the relationships as many-to-many. Then you can choose to filter from access-to-t1. But not sure if this will filter correctly when adding more tables.

 

Thanks for any suggestions 🙂

1 ACCEPTED SOLUTION
SamWiseOwl
Community Champion
Community Champion

@eirika 

Great I've added some fake email addresses and used UserPrincipalName to apply the filter:
testrls - sam.pbix

 

You can impersonate an email by entering it into Other user and ticking the role you want to test:

SamWiseOwl_0-1723216378783.png

 

SamWiseOwl_1-1723216453384.png

 


If you are happy with this answer please mark as a solution for others to find !

Kudos are always appreciated! Check out our free Power BI video courses.

View solution in original post

11 REPLIES 11
SamWiseOwl
Community Champion
Community Champion

@eirika 

Great I've added some fake email addresses and used UserPrincipalName to apply the filter:
testrls - sam.pbix

 

You can impersonate an email by entering it into Other user and ticking the role you want to test:

SamWiseOwl_0-1723216378783.png

 

SamWiseOwl_1-1723216453384.png

 


If you are happy with this answer please mark as a solution for others to find !

Kudos are always appreciated! Check out our free Power BI video courses.

Did the above download work for you? @eirika 


If you are happy with this answer please mark as a solution for others to find !

Kudos are always appreciated! Check out our free Power BI video courses.

hi,

Yes this works, thanks a lot 🙂

will this be usable on large datasets with million of records in the tables?

Should do, let us know how you get on!


If you are happy with this answer please mark as a solution for others to find !

Kudos are always appreciated! Check out our free Power BI video courses.

SamWiseOwl
Community Champion
Community Champion

Hi @eirika 

Under the RLS for t2 could you add a filter to AccessId=

AccessID IN Values(access[AccessID)

Same with t1

AccessID IN Values(access[AccessID)

SamWiseOwl_0-1723199529303.png

 

 

This should directly apply filters onto those tables based on the list of ID the person has


If you are happy with this answer please mark as a solution for others to find !

Kudos are always appreciated! Check out our free Power BI video courses.

Hi,

Thanks for your response.

Sorry to say that this didn't work. It doesn't filter out anything, so the result is the same as without the new filters.

 

1.png
 

Hi @eirika  Can you share a sample file? It works on my test file but the stucture could be different.


If you are happy with this answer please mark as a solution for others to find !

Kudos are always appreciated! Check out our free Power BI video courses.

hi,

thanks for your help

 

didn't find a way to include it here so uploaded a test file to
https://1drv.ms/u/s!Av4RVTN1GLuWgutkQzhr1KeJEP7bVQ?e=2MOgoq

Hi @eirika 

How will the filtering work on publish?

Are you going to add emails into the Access table next to their UserId?


If you are happy with this answer please mark as a solution for others to find !

Kudos are always appreciated! Check out our free Power BI video courses.

No, I'm using the embedded solution.

So when the code call the EffectiveIdentity method from the application to open the PowerBI report we pass the role "User" and the value of "userId" for the logged in user. This give us possibility to use the username() function in RLS to get the userId value.

 

So the filter on the useraccess table in my example is hard coded to 1, this number will be replaced with username() on deploy.

 

the access table is only for limiting the rows the user has access to in t1 and t2. There will bo no extra columns.

eirika
Frequent Visitor

 

I did get it to work now by using the filter below on t1 and t2 (replaces username() with hard coded value 1). But is this a nice and fast solution?

 

t1[access_id] IN CALCULATETABLE(DISTINCT(access[access_id]), access[userid] = USERNAME())

 

 

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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