Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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

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)
    EmployeeIDDepartmentBusiness UnitTeam Supervisory LevelScore
    1TLTPORRExR10
    2AsAssZAssZ1Public20
    3FSHCDADB25
    4TLTPOTTExR15
    5TLTVVT16
  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.
    IDnameemailDepartmentBusiness UnitTeamSupervisory Level
    1Ian[email protected]TL   
    2Andreas[email protected] TPO  
    2Andreas[email protected] T  
    3Sara[email protected]  RR 
    4Peter[email protected]   ExR
    5Boss[email protected]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!

 

 

2 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable

      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