RLS security

[eirika]

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.

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 🙂

[SamWiseOwl · Accepted solution]

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

Did the above download work for you? @eirika 

[eirika]

hi,

Yes this works, thanks a lot 🙂

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

[SamWiseOwl]

Should do, let us know how you get on!

[SamWiseOwl]

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)

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

[eirika]

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.

[SamWiseOwl]

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

[eirika]

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

[SamWiseOwl]

Hi @eirika 

How will the filtering work on publish?

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

[eirika]

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]

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())