Forum Discussion

mark_carlisle's avatar
mark_carlisle
Advocate IV
6 years ago
Solved

Help required: Dynamic Row Level Security with conditions

I have the following example data and from this I would like to create dynamic row level security to simplify the process on managing permisson levels on reports and ensure people only see what they need to.

 

EmployeeNameEmployeeNumberEmployeeEmailAddressEmployeeUPNManagerEmployeeKeyIsManagerIsTrainingManagerIsCoachIsDeputyEmployeeKey
Mark1[email protected][email protected] 10001
Frank2[email protected][email protected]110002
Sophie3[email protected][email protected]111003
Ruby4[email protected][email protected]200104
Ben5[email protected][email protected]200015
John6[email protected][email protected]300006
Lisa7[email protected][email protected]300007
Paul8[email protected][email protected]111008
Rebecca9[email protected][email protected]110009
Lucy10[email protected][email protected]8000010
John-Paul11[email protected][email protected]8000011
Simon12[email protected][email protected]9000112
Alan13[email protected][email protected]9000013

 

Visual the org stucture looks like this;

 

 

Example

  • Mark, Frank, Sophie, Paul and Rebecca are all managers (IsManager = 1) so should be able to see all data in the report.
  • Ruby is on a development opportunity and is coaching (IsCoach = 1) the new starters that the training managers manage (IsTrainingManager = 1)
    • Because of this Ruby should be able to see her own data plus those of John, Lisa, Lucy, John-Paul as they are managed by Sophie and Paul.
  • Ben and Simon act as deputies (IsDeputy = 1) in their managers, Frank and Rebecca, absence.
    • Because of this Ben and Simon should be able to see the data of their team. For clarity;
      • Ben should be able to see his and Rubys figures
      • Simon should be able to see his and Alans figures

Essentialy its comes down to;

  • IsManager = 1 should have unrestricted access.
  • IsCoach = 1 should have access to only their data and that of the direct reports of the training managers, IsTrainingManager = 1.
  • IsDeputy = 1 should have access to only their data and that of there managers direct reports.

 

I've tried a few things but I'm having issues with the logic.