RLS with related conditions on multiple tables

[Anonymous]

Hello everybody,

my issue is about creating roles in RLS with conditions based on 2 different tables. These conditions need to be linked and they can't stand divided. It's really important to me to solve it, i really need a solution.

I know RLS and I know it works with AND conditions when creating filters on different tables, but I need some more because conditions on one of my tables is based on fields from the other one.

Let's have some sample data about shipments:

Table A

KeyA	Sender	Receiver	Value
X1	A	B	10
X2	A	C	20
X3	B	A	30
X4	C	B	40

Table B

KeyB	KeyA	Sender Permission	Receiver Permission
Y1	X1	1	0
Y2	X2	0	1
Y3	X3	1	1
Y4	X4	1	0

In this case, I want person A to see the 3 shipments he is part of in Table A (X1, X2, X3) but he can see the records in Table B just if he has the rights to do it based on the role in the shipment (sender or receiver). For example, he can see Y1 because he is the sender of the shipment and sender permission is 1 for Y1, but he can't see Y2 because here sender permission is 0, while he can see again Y3 because he is the receiver and receiver permission in 1 there.

So when creating role for person A in RLS, it would be:

In Table A

Sender = "A" || Receiver = "A"

In Table B something like:

(Sender = "A" && Sender Permission = 1) || (Receiver = "A" && Receiver Permission = 1)

But for condition on Table B I need to rely on field from Table A, which I think is not allowed. I can't avoid to relate to Table A when writing conditions on Table B!

How can I fix it?

It's extremely important to me, so much really.

Please help me, and thanks in advance! 

[Anonymous · Accepted solution]

Hi @Anonymous,

You can try to use the following expression to create a role on  tableA to filter records based on two table conditions:

VAR CurrUser = "A"
VAR rList =
    CALCULATETABLE (
        VALUES ( TA[KeyA] ),
        FILTER ( ALLSELECTED ( TA ), [Receiver] = CurrUser )
    )
VAR sList =
    CALCULATETABLE (
        VALUES ( TA[KeyA] ),
        FILTER ( ALLSELECTED ( TA ), [Sender] = CurrUser )
    )
VAR tblist =
    CALCULATETABLE (
        VALUES ( TB[KeyA] ),
        FILTER (
            ALLSELECTED ( TB ),
            OR (
                [KeyA] IN rList
                    && [Receiver Permission] <> 0,
                [KeyA] IN sList
                    && [Sender Permission] <> 0
            )
        )
    )
RETURN
    [KeyA] IN tblist

Regards,

Xiaoxin Sheng

[Anonymous · Accepted solution]

Hi @Anonymous,

You can try to use the following expression to create a role on  tableA to filter records based on two table conditions:

VAR CurrUser = "A"
VAR rList =
    CALCULATETABLE (
        VALUES ( TA[KeyA] ),
        FILTER ( ALLSELECTED ( TA ), [Receiver] = CurrUser )
    )
VAR sList =
    CALCULATETABLE (
        VALUES ( TA[KeyA] ),
        FILTER ( ALLSELECTED ( TA ), [Sender] = CurrUser )
    )
VAR tblist =
    CALCULATETABLE (
        VALUES ( TB[KeyA] ),
        FILTER (
            ALLSELECTED ( TB ),
            OR (
                [KeyA] IN rList
                    && [Receiver Permission] <> 0,
                [KeyA] IN sList
                    && [Sender Permission] <> 0
            )
        )
    )
RETURN
    [KeyA] IN tblist

Regards,

Xiaoxin Sheng

[Anonymous · Accepted solution]

Thanks @Anonymous for your hint,

unfortunately I need to create it for many roles and not just for 1 or 2. If I took what you mean, your idea is good but I'd need to create a new table (I think) for each role and it's impossible to me.

For @lbendlin, thanks again, your answer is good and it would have worked through calculated columns, the bad is my data model is not so easy: I have an Intermediate table between the 2 because they would have been in a M-N relationship while using Intermediate I get 2 easier relationship which are 1-M and 1-N. So I can't use calculated columns, even if your hint is good, my bad not to represent my model correctly.

In the end I added the columns I needed in Table B and edited my ETL function which inserts data in my tables so that I get a copy of those columns where I need them. It's not fair, but it worked.

Thank you again guys!

[lbendlin]

You are not showing your data model.

Let's assume that there is a 1:M relationship from table A to table B.  The either RLS should "just work"  or you can create a calculated column in table B that pulls in a related column in table A (via RELATED( ) ).  That will then allow you to apply the RLS rule in table B.