Forum Discussion
Dynamic RLS with complex security module
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:
- Table 1 (Username) -> Table 2 (Username)
- Table 1 (Company Code) -> Table 3 (Company Code)
- Table 1 (Role Type) -> Table 3 (Role S, Role B or Role R)
- 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,
1 Reply
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()
