RLS does not get applied

[MamaHani2]

hello

we have a tabular model deployed to powerbi service workspace; 

users can then connect to this model and build their reports;

they have been granted viewer access on the workspace with the model, plus build access directly on the model;

i have set up a table with access, where per user and fact table there is a list of companies to which he/she should have access;

i created a role in the model via tabular editor, for the particular table

original filter was like this:

VAR CurrentUserCompanies =
SELECTCOLUMNS (
FILTER (
SecurityMatrix,
SecurityMatrix[user_email] = USERPRINCIPALNAME() && SecurityMatrix[fact_table] = "Sales Orders"),
"@Name", SecurityMatrix[D_Company_SID]
)
RETURN
'Sales Orders'[D_Company_SID] IN CurrentUserCompanies

the model was deployed and an entra group giving viewer /build access to the model has been added as the member of this role;

however when i test this via test role as and give  a particular user for which i did the set up, nothing gets filters; he gets to see full list of all companies;

what am i missing?

in the original set up i did not have a relationships between secutiryMatrix and Sales orders, it did not work then; now i have this relationship, with both directions checked, and its also not working;

after some searching i found this proposal but it does not apply any filtering neither

'Sales Invoices'[D_Company_SID]
IN CALCULATETABLE (
VALUES ( 'SecurityMatrix'[D_Company_SID] ),
FILTER (
ALL ( 'SecurityMatrix' ),
('SecurityMatrix'[User email] = USERNAME ()
|| 'SecurityMatrix'[User email] = USERPRINCIPALNAME ()
) && 'SecurityMatrix'[fact_table] = "Sales Invoices"
)
)

thanks a lot

rgds

[SaiTejaTalasila]

Hi @MamaHani2 ,

Row-Level Security (RLS) in Power BI only works for users with Viewer permissions

Please refer this article-

https://learn.microsoft.com/en-us/fabric/security/service-admin-row-level-security

I hope it will be helpful.

Thanks,

Sai Teja 

[v-jialongy-msft]

Hi @MamaHani2 

First you can test in Power BI Desktop

Use View as Role to simulate the RLS role:

1. Select the role you created.
2. Manually enter the email address of a user in the 'SecurityMatrix'.

Verify whether filtering is applied as expected. If there is a problem, check the logic of your RLS expression.
Row-level security (RLS) with Power BI - Microsoft Fabric | Microsoft Learn

If it behaves fine in PowerBI Desktop, then you need to go to PowerBI Service to check that the assigned mailbox is exactly the same as the mailbox that the user logged in to PowerBI Service. If a user is added to an RLS role through an Entra group, ensure that the user is not only in the group, but also gets the correct access through the group assignment.

Best Regards,

Jayleny

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[MamaHani2]

hi!

thank you very much for your input;

i dont have the possibility to test this security in powerbi desktop becasue the rls is set up in the semantic model which is published to powerbi service workspace; so i can only connect in live connection to the model; and the option to view as is greyed out;

however i have some new development in this issue:

after small modification the filter looks like this:

'Sales Orders'[D_Company_SID]
IN CALCULATETABLE (
VALUES ('SecurityMatrix'[D_Company_SID]),
FILTER (
ALL ('SecurityMatrix'),
'SecurityMatrix'[user_email] = USERPRINCIPALNAME() && 'SecurityMatrix'[fact_table] = "Sales Orders"
)
)

when i go to the published model i can see under security the roles that were created;

i go 'test as role' on the particular role, and the test report from the same workspace opens, and it does filter correctly the list;  even for me, even though im admin on all the workspaces; however i asked one user to test this today and she still could see full list of values; she is member of X entra group, and X has been given viewer rights on the workspace with semantic model plus build rights on the model itself; so she is not admin or member or contributor; just a viewer with build right; when i test the role view as and select her email adress, i also get to see full list; im not sure what im missing where; 

[MamaHani2]

i have booked quite some progress;

i noticed that within 1 role the full context have to be included;

i have 1 role per fact table, as expectation is to limit the access to companies depending on the fact table ; i created therefore 2 roles, and put filters inside: 1 role -> table1 filter; 2 role - > table2 filter;

how Powerbi read this: 1 role -> table1 filter, table2 give full result; 2 role -> table2 filter, table1 give full result; as roles add to each other, it kept giving me full results; i put now the filters into 1 role, and first tests are very promising; all data got filtered on both tables; lets see where this gets me;