Forum Discussion
RLS with Roleplaying
Greetings!
I´m struggling with setting up RLS for two different scenarios. First of I have two tables, UserTable and FactTable:
The tables can be connected by SupplierId or StoreId. I need it to be dynamic depending on who logs in. Which is normally achived by roleplaying dimensions (USERELATIONSHIP). Per my understanding it is not supported with RLS. Im looking for other alternatives.
Scenario 1 - [email protected] logs in
The report needs to be filtered on his associated StoreId,10.
Scenario 2 - [email protected] logs in
The report needs to be filtered on his associated SupplierId, 1. He should see all Stores with the same SupplierId. In this case, 10 and 20. (The "*" can be changed)
The benefit of a solution would be that we will not need to create up to 500 rows for each User, as some Suppliers are located in up to 500 Stores.
Any suggestions are welcome!
3 Replies
You could potentially do the following:
1. Create 2 different User security tables.
1.a. For users that have no filter on one of the tables, include a row with "All" for that user.
2. Create a bridge table between each security table and the fact table.
2.a. In the bridge table, "All" can be associated with all of the Stores / Suppliers.
3. Apply RLS to both User security tables.
Hope this helps,
Nathan
Thank you for your fast reply Anonymous !
Are you suggesting splitting the Users into two Security Groups?
Security Group1
[email protected]Security Group 2
Noone is suppose to see everything. Security Group 1(SG1) should see all stores under a certain Supplier.
SG2 should only see specifik Stores.