Forum Discussion

Bare's avatar
Bare
Frequent Visitor
7 years ago

Multilevel RLS with userprincipalname

Hi All,

 

Can anyone help with pointing me in the right direction towards extending RLS beyond what I'm allowed to view?

 

Scenario:

So, I have the following structure:

 

organizational structure

Now I was able to set up RLS for a user to view hers and immediate direct employee reporting to her (for eample Emp B can view Emp B, Emp C & Emp D). I achieved this with an OR statement using 'userprincipalname()' in the 'Manage Roles' section (employee ID = userprincipalname() || manager ID = userprincipalname() or the employee dimension table. Works fine.

 

Challenge:

My challenge is, still using Emp B example, I want to be able to view next levels as well. That is, to be able to view results of Emp E, Emp F & Emp G via drill through if I so wish (?).

 

I guess the question is, is this even possible with plain vanilla RLS? In the employee dimension table I have created columns using the PATH functionalities believing therein may be the answer but I'm stuck.

 

 

In a nutshell, as an employee being able to view data at my level & further down the hierarchy chain from my level. Is there another way other than RLS?

 

Can you help?

Many thanks for your time

 

Regards,

Bare

 

PS I couldn't attach a sample file. Happy to do so if I figure out how :smileyembarrassed:

 

 

 

RLS
Row Level Security

5 Replies

  • avanderschilden's avatar
    avanderschilden
    Resolver I
    7 years ago

    Hello,

     

    You need a table with unique employees and their email/login. This will be the starting point of the RLS filter;

     

    Emp1 | [email protected]

    Emp2 | [email protected] 
    Emp3 | [email protected]
    Emp4 | [email protected]

     

    The second table you need is a table with employees and their children, or parents and all employees they have access too;

     

    Emp1 | Emp1

    Emp1 | Emp2

    Emp1 | Emp3

    Emp2 | Emp3
    Emp2 | Emp4

     

    Now, Emp1 will filter the second table and only filter out Emp1, Emp2 and Emp3. No access to Emp4.

     

    The last table is your transaction table with sales or whatever, and this will be connected to table 2 based on employee;

     

    2019-01-01 | Emp2 | 50 Sales

    2019-02-02 | Emp4 | 100 Sales

     

    Now, Emp1, will only see a total number of 50 sales, since there is no access to the second row of the transaction table.

     

    Hope this helps to model your data in the right way.

     

    Regards,

    Adrian

     

    Emp1 | 

    • Bare's avatar
      Bare
      Frequent Visitor
      7 years ago

      Thanks for your quick response avanderschilden 

       

      This is exactly where I got stuck: Emp 1 in your use can see Emps 2 & 3.

       

      But because Emp 4 reports to Emp 2 who in turn reports to Emp 1, I want Emp 1 to be able to see figures for Emp 4 as well. Kind of like a waterfall RLS from the top node to the most bottom node in that chain for Emp 1

       

      Regards,

      Bare

      • avanderschilden's avatar
        avanderschilden
        Resolver I
        7 years ago
        So why dont you just add a row with;

        Emp1 | Emp4

        In that case you have what you need right?

        You just have to store your table with parents and childs different, without level. Just 2 columns with a parents and child.