Forum Discussion
RLS does not get applied
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;
Edit: this was the correct approach; it solved my issue
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;
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;
Edit: this was the correct approach; it solved my issue