2 Replies Latest reply on Oct 12, 2015 3:32 PM by Matt Coles

    Again Row Level Security

    Murali Govindu

      I originally had couple of columns (sample one below) to implement the RLS, here I we had an opportunity to work with self-joins and mitigated the issue by adding couple more (sample two below).  I hope now it is possible to get the Row Level Security done.  Can any through some light on this  (Assume we had AD group which is good with UI column) ?

       

      One

      UI                    Reports To

      SVenkata          Mgovindu    

      Lgovindu           Svenkata

      Mgovindu          none


      Here SVenkata is a Manager, Lgovindu is a Coordinator while Mgovindu is an Executive.


      Two (after self-joins made in the db view, etc..)

      UI                    Reports To     Mgr.               Exe.

      SVenkata          Mgovindu        Mgovindu        None

      Lgovindu           Svenkata         Svenkata        Mgovindu

      Mgovindu          none                none               None


      Question:

      there should be way out to implement the self joins kind in Tableau itself without going taking the route of db view joins i.e., a) blending (by copying the table once again as this is all by one data source) b) writing required custom-sql on the data source used and blending the data).


      Question2:

      how we can implement security through calculation fields where Manager can see their two team while Coordinator sees their own stuff.  Executive should be able to see all the Supervisors data.


      I can use something like username() = Mgr. then 1 elseif username() = Exe. then 1 etc.. and place it in the filters selecting 1.


      Attached, workbook for a quick resolution, pl. reach out to me for any quick clarifications...

      Thanks

      murali.govindu@gmail.com

       

      Message was edited by: Murali.Govindu@gmail.com

        • 1. Re: Again Row Level Security

          Hey Murali,

           

          I've moved this post to our Server Admin group where some of our users may have experience implementing row-level security.

           

          Thanks,

           

          -Diego

          • 2. Re: Again Row Level Security
            Matt Coles

            Yes, you can self-join in a standard Tableau connection the same table to itself over and over again, but only to a finite number of levels. If the number of levels you have is arbitrary, then it becomes a big pain--you'd need a recursive structure on the database side, I would think, or a custom mapping CSV file you blended in.

             

            One method might be with Groups. There's a function called ISMEMBEROF() that validates whether or not the user viewing the viz is a member of that group or not, and returns true if they are. So you might be able to use that for a structure like this:

             

            IF ISMEMBEROF('OpsExecutives') AND [Department] = 'Ops' THEN 1

            ELSEIF ISMEMBEROF('DevExecutives') AND [Department] = 'Dev' THEN 1

            ..

            END

             

            Trouble with that is, the function requires a static string as input--you can't make it dynamically validate groups that exist as values in your data.

             

            What I have done when working against a PostgreSQL was write a bit of customSQL where I have a list of users who have access to a given record (in my case, only 1 or 2), and pivot them so that the result is something like:

             

            UsernameLeader 1Leader 2
            userxleader x
            useryleader xleader y
            userzleader z

             

            ... where the two Leader columns are what I am pivoting. Then the calc is:

             

            IF USERNAME() = [Username] THEN 1 // user can see their own data

            ELSEIF USERNAME() = [Leader 1] THEN 1 // manager can see their direct reports' data

            ELSEIF USERNAME() = [Leader 2] THEN 1 // director can see indirect reports' data

            ELSE 0 // no data for you!

            END

             

            That solution can work, but the custom SQL can get tricky, and if your organization grows and you have to add a new level--ugh...that's a pain. Performance also might suffer at large volumes, but I haven't analyzed that specifically.

             

            Yet ANOTHER solution is more dynamic but can also increase confusion--join tables such that you duplicate the rows in your actual data, with each row of data also containing the specific username who has access to that row. In that case my previous example would look like:

             

            dataUserWhoCanAccess
            userx

            userx

            userxleaderx
            useryusery
            useryleaderx
            useryleadery
            userzuserz
            userzleaderz

             

            Then the calc is:

             

            USERNAME() = [UserWhoCanAccess]

             

            Much simpler on the permissions side, but it also can blow your data out to large multiples of its original size, which can be pretty ugly.