cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Christofer Regular Visitor
Regular Visitor

RLS with Roleplaying

Greetings!

I´m struggling with setting up RLS for two different scenarios. First of I have two tables, UserTable and FactTable:

UserTable.PNGFactTable.PNG

 

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.

Relation.PNG

 

Scenario 1 - MrX@email.com logs in 
The report needs to be filtered on his associated StoreId,10.

 

Scenario 2 - MrA@email.com 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 3
natelpeterson New Contributor
New Contributor

Re: RLS with Roleplaying

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

Christofer Regular Visitor
Regular Visitor

Re: RLS with Roleplaying

Thank you for your fast reply @natelpeterson !

 

Are you suggesting splitting the Users into two Security Groups?

 

Security Group1
MrA@email.com

 

Security Group 2

MrX@email.com

MrY@email.com

MrZ@email.com

 

Noone is suppose to see everything. Security Group 1(SG1) should see all stores under a certain Supplier.

SG2 should only see specifik Stores.

natelpeterson New Contributor
New Contributor

Re: RLS with Roleplaying

@Christofer  - I was thinking of a single security group, with every user in both tables, but I like your idea better. Then you can eliminate the suggested bridge tables and only apply the RLS on the relevant User Security table for each security group.

Cheers!

Nathan