Dynamic RLS with complex security module

[khalilg]

Hi,

I'm trying to create a Dynamic RLS for a complex security module,

here are the table structure and relationship:

Table 1. Users

Username	Company Code	Role Type	Access ID
A	A	S
B	A	A
C	A	R	77
D	B	B

Table 2. Roles

Username	Access Detail
A	12345
A	67891
C	77
D	97

Table 3. Data

ID	Date	Value	Company Code	Role S	Role B	Role R
1	01-01-2021	1	A	12345		77
2	01-01-2021	2	A	67891
3	01-05-2021	3	A			77
4	10-01-2021	4	B		97

As it's showing in the above Data Table, User "A" should be able to see the first 2 rows as Role S 12345 & 67891 are linked to user A in Roles Table and user A is in Company "A", User “C” should be able to see row 1 and row 3 as Role R is 77 and 77 is linked to user C and the user is in Company “A”, and finally User “D” should be able to see only row 4 as Role B is 97 and user Company is “B”.
User “B” should be able to see everything as Role Type “A” is Admin.
So, the relationship here is:

1. Table 1 (Username) -> Table 2 (Username)
2. Table 1 (Company Code) -> Table 3 (Company Code)
3. Table 1 (Role Type) -> Table 3 (Role S, Role B or Role R)
4. Table 2 (Access Detail) -> Table 3 Value of (Role S, B or R)

I’ve tried different type of DAX expression but with no luck so far, any ideas that might help in resolving this?
Thanks in advance,

[lbendlin]

@khalilg  Why do you have AccessID as part of the Users table?

It feels as if you need to have a concatenated list of Access Details in the Users table, and make them company specific to remove the dependency on Company Code.

Username	Company Code	Role Type	Access Codes
A	A	S	12345|67891
B	A	A
C	A	R	77
D	B	B	97

And then in the RLS rules you can check PATHCONTAINS()