Dynamic Row-Level-Security not working in service

[freidav1984]

Hi All!

I have a report where I set up dynamic row level security.

I have a brand_user assignment table, which contains the following fields:

• id (counter)
• user name - name
• e-mail (equal to username())
• userid (equal to userprincipalname())
• brand

I also have a fact table, which contains plenty of transactional data. Also contains the brand in a column.

I created a many-to-many relationship via the brand fiel between the brand_user_addignment table and the fact table. The filter is a one-directional: brand assignment filters the fact table. 

I created a row level security on the brand assignment table for power bi desktop testing purposes (RLS_desktop): [email] = username() // this works fine in desktop.

But the final rls rule, which I created for BI service is not working, does not filter. (This is RLS_service - filters also the brand_assignment talbe): [userid] = userprincipalname().

I also created a measure, to show the userprincipalname() in the power bi service, and it shows the correct userid, for which I want to make the filtering. 

I also added the user to the rls in the power bi service at the dataset's security menu.

Do you have any idea what am I doing wrong?

I did researches, I found posts that the actual user name in the service might not match the userprincipalname? Or is there any mistake I have done?

Thx!

David

[GilbertQ]

Hi @freidav1984 

I would suggest following Kasper's cheat sheet for learning how to implement dynamic RLS to make sure that you follow the same pattern to get it working.

Here is the link: Power BI Desktop Dynamic security cheat sheet - Kasper On BI

[freidav1984]

Hi,

Thx for your answer, I recomposed the data model according to to above mentioned blog. Somehow, did not get any active RLS after I published the report to the power bi service, and I have still no clue, what could've gone wrong... I think I did not skip any minor issue, but who knows...

[GilbertQ]

Hi @JR-DCPS 

Double check if you have followed the steps as it does work.

[JR-DCPS · Accepted solution]

Did you remember to add users to the security group designations after publishing, and ensure that those users are viewers? If a user is designated higher than "Viewer" then RLS won't apply to them; and the users need to be placed in the security group for PBI Service to know which ruleset to apply to them.

[freidav1984]

Hi!

Thx a lot. The workspace viewer was the key element. 

The user got direct access and read permission in my situation. But it seems that rls works with workspace viewer permission. 

As I changed the user permission to workspace viewer, RLS worked.

This way, I face the following problem: This user should only see this particular report, and the user is not eligible to view other reports in this workspace. Any idea how to solve this issue in the same workspace, or do I need to create a separate workspace for him / her?

Thx a lot!