Dynamic Row-level security with multiple cost centers, roles and tables

[Anonymous]

Hi All, 

As the title suggest, I'm looking for a way to implement dynamic row-level security automatically within a cost center hierarchy with, at the lowest level, multiple parents.  I've already read several partial solutions, but, I'm not able to figure this out totally.

• Radacad explains perfectly how you can setup RLS within a org hierarchy using Path function. 
• His second and third articles even explains how you can go about solving this if users have several positions. (removed links due to deleting of previous post due to ban?!)
• This post explains how you can create an hierarchy where there are multiple parents.
• This post takes another approach, which seems much simpler, but I can't get it to work either.

So why didn't i implement this yet, you ask? Well, I just can work out how I can put all these together... I'm really scratching the edges of my understanding of relational databases, many to many relationships and DAX. So your help in putting this all together is much appreciated. 

My situation is as follows:

1. I have 4 levels within my organization: Departments, Business units, Teams and Supervisory level. 
2. Supervisory level can be under several Teams (multiple parents, see ID 1 & 4)
   

   EmployeeID	Department	Business Unit	Team	Supervisory Level	Score
   1	TL	TPO	RR	ExR	10
   2	As	AssZ	AssZ1	Public	20
   3	FS	HC	DA	DB	25
   4	TL	TPO	TT	ExR	15
   5	TL	T	V	VT	16

3. I already have static RLS in place in the first three levels (up to Business Unit), now the client wants to implement it also for the lower two levels, which would introduce over 500 extra roles, which is obviously not an ideal situation. 
4. I want to be able to load a table which determines the rights see table of each specific person.
   

   ID	name	email	Department	Business Unit	Team	Supervisory Level
   1	Ian	Ian@example.com	TL
   2	Andreas	andreas@example.com		TPO
   2	Andreas	andreas@example.com		T
   3	Sara	Sara@example.com			RR
   4	Peter	Peter@example.com				ExR
   5	Boss	Boss@example.com	All, including below

5. Each person could have potentially several rights on the same level (see Andreas in table above).
6. Each person should should have rights to see data of determined level, and everything below that. So a board member with rights to all departments should everything, somebody with supervisory level rights should only see that specific SL.
7. Supervisory level makes this complicated (see point 2), since a supervisory level could span multiple teams, and if somebody is a manager of a SL, I want that person to see the whole of his/her SL, spanning multiple teams.

So yeah, In my (probably very noobish view), this is pretty complicated, especially since it combines implementing dynamic row-level security with multiple positions, multiple parents and multiple tables (see screenshot below of real data model). Has anybody figured this out ever?   Thanks so much in advance!

 

[Anonymous]

HI @Anonymous,

I'd like to suggest you take a look at following blog if it suitable for your scenario:

Dynamic Row Level Security (Rls) and Company Hierarchies 

Regards,

Xiaoxin Sheng

[Anonymous]

Hi @Anonymous , 

Thanks for the reply. The link provided unfortunately doesn't help me, since it does not cover multiple parents of the same children (see my second point). 

Best, Strike88