RLS not working, still showing values from hidden rows

[jdusek92]

Hello, I am current working on a RLS solution to my data model.

The report is on People Analytics data, so it contains infromation about employees, like function, age, hire date and bunch other attributes on per employee/per month granularity.

now I want to add info about employees ethnicity (1 row per employee) and I want to limit access to this data by using RLS.

I was able to create a dummy sample report that demonstrates the problem:

https://drive.google.com/file/d/1RoQro0KhMrCyVDGGK5xGprKOqZRL_2qB/view?usp=sharing

The Limited view RLS role has a simple rule, for Ethnicity table = False(). This works correct, it hides all the rows from Ethnicity Table, but because the security filter propagates on the relation ship, it also hides all the rows from the Employee Data, which is not what I want, I still want Limited view Users to use the report and see information from Employee data table. So that is why I inactivated the relationship.

Now I for measures to calculate correctly, I have to use USERELATIONSHIP, to make details by Ethnicity available for ADMIN users

Sum FTE = CALCULATE(SUM('Employee Data'[FTE]),USERELATIONSHIP('Employee Data'[ID],Ethnicity[ID]))

this works fine and as expected for Admin role:

but there is an unexpected behavior when I view as limited Role:

Additionaly I discovered this "security issue" - the filter pane filter does not show any values for filtering, but if I change the filter type to advanced, I can manually type in the value and the filter works:

I would like to ask, how is it possible that with RLS applied and the Ethnicity table having NO ROWS available for this role, it still uses the Ethnicity values in the matrix.

and is the "advanced filter" issue a bug or expected behaviour?

Any other Ideas how to achive having a limited RLS role, with no access to selected attributres, but still has access to the main fact table?

Thanks

Jakub

[lbendlin]

inactive relationship = ineffective RLS.  Your USERELATIONSHIP measure is only used in the visualization, not in the backend data. Users will still see everything from all the other tables.

Rethink/rework your data model.

[jdusek92]

RLS is properly filtering the Ethnicity table - in data view correctly no rows are visible in this table, and also correctly all rows are visible in the Employee Data table

[Anonymous]

Hi, @jdusek92 

Have you solved your problem? If so, can you share your solution here and mark the correct answer as a standard answer to help other members find it faster? Thank you very much for your kind cooperation!

Best Regards

Yongkang Hua

[jdusek92]

Hello @Anonymous 
 I tried to use OLS via tabular editor and it seems to work for my scenario, but these questions from my post is still unanswered and I would like to know the explanation in order to fully understand why RLS behaves in the way I described:

• how is it possible that with RLS applied and the Ethnicity table having NO ROWS available for this role, it still uses the Ethnicity values in the matrix.
• and is the "advanced filter" issue a bug or expected behaviour?

[jdusek92]

Hello, any ideas?