changing cardinality in RLS makes RLS only work if used as filter, why?

Question

I have a fact table, with no security; all employees should see all the rows... But, but, just on one specific page in my report, I want the employees to see only their own data.

So I decided to have a model with the next tables:

Fact <- Employee <- BridgeEmployeeRole

My goal was to add RLS in BridgeEmployeeRole , having a filter Username = USERNAME() ( I thought that as long as BridgeEmployeeRole wasn't used in the report the filter wouldn't be triggered).

But I quickly realized that I was wrong, the filter cascaded to the Fact and messed up all my other pages.

BridgeEmployeeRole is just a copy of Employee with fewer columns. This is the relationship:

The RLS worked, but it filtered all the way to the fact, so I couldnt achieve my result.

Then... I was just playing around and I made it like this:

and this, somehow, achieved my desired result!!!!! (That is, the RLS doesnt work unless you add this filter):

(so I added the filter in the page I want to limit the view, and it works with unrestricted rows in other pages).

So, basically, It works but I dont know why... Why is that changing cardinality makes the RLS only work if the table is selected as filter in the pane...

Answer 1

Hi @ovonemty

First of all, I want to congratulate you on achieving the results you want.

Row-Level Security enables you to use group membership or execution context to control access to rows in a database table.

For your question, I guess that when the cardinality is 1:1, the system can automatically filter out the BridgeEmployeeRole corresponding to Employee Current. When the cardinality is modified to 1 to many, due to the existence of IsEmployee in the BridgeEmployeeRole table, I guess that there are employees and non-employees in the BridgeEmployeeRole table, and the employees are displayed as 1. At this time, the system cannot automatically identify which BridgeEmployeeRole is an employee and which is a non-employee corresponding to Employee Current, so it is necessary to manually filter out the employees in the BridgeEmployeeRole table.

Best regards,
Percy Tang

----------

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

The 1:* cardinality unlocked the RLS checkbox for the relationship, and you have it unchecked, so RLS-based filtering is not automatically propagated to the one side. It still applies to the M side, limiting the rows, and when you add a regular filter, you are getting the combined (RLS+regular) effect from the M side table in your output.
Looking at your original requirements, see whether you can achieve them without RLS and the other table altogether, by simply filtering Employee on that particular page.