14 Replies Latest reply on Dec 12, 2018 1:43 AM by Nutan Patel

    Implementing RLS using CROSS Database Join feature

    Nutan Patel

      Dear Experts,

       

      I would like to implement row level security using cross database join feature. I have two different data sources one is pulling data from OKTA using HTTP GET request and another is pulling data through SQL procedure. Below is the sample data for your reference. The goal is to show the related data to user means if Test user login then show him/her only Palo Alto location data and if TestAdmin user login then show them all locations data (Palo Alto, San Fransisco, New York & London). Please be noted that we can directly compare UserName == USERNAME() as its real login name.  Kindly do needful..

       

      OKTA data (coming through HTTP request)
      UserNameLocationIdAdditionalLocationId
      Test10
      TestAdmin118,9,10
      Report Data (coming through SQL procedure)
      YearMonthLocationIdLocationNameSalesAmount
      2018110Palo Alto10000
      2018110Palo Alto8000
      201818London7000
      201829New York6000
      201829New York5000
      2018211San Fransisco6000
      201839New York8000
      2018310Palo Alto4500
      2018410Palo Alto6000

       

      Thanks

      Nutan Patel

        • 1. Re: Implementing RLS using CROSS Database Join feature
          Ombir Rathee

          Not sure but try this. It should work.

           

          1. Create a Left join between two tables on Location Id. Data table as Left table and Security table as Right.

           

           

           

           

          2. Create a calculated field and add this to data source level and select TRUE.

           

          IF USERNAME()='TestAdmin' THEN

                  TRUE

          ELSE

              USERNAME()=[User Name]

          END

          • 2. Re: Implementing RLS using CROSS Database Join feature
            Ombir Rathee

            I checked it but this approach will duplicate the rows when there are multiple users. I think you've to go with ISMEMBEROF approach rather than cross database join.

            • 3. Re: Implementing RLS using CROSS Database Join feature
              Nutan Patel

              Yes, the approach you suggested won't work because this are just test users, we can not write hard coded statements.

              Can you please elaborate for ISMEMBEROF approach, please?

               

              Also, just to confirm... can we pass AdditionalLocationIds values as a parameter value of report data as is? using Parameter or something else?. If we can achieve this, it will also resolve my problem, because SQL procedure reads comma separated location ids and if we can pass comma separated location ids from security table to SQL procedure then my problem will be resolved automatically.

               

              Thanks

              Nutan Patel

              • 4. Re: Implementing RLS using CROSS Database Join feature
                Ombir Rathee

                Give this method a try. It doesn't duplicate the rows and working fine with one to many relationship.

                 

                1. Modify your security table and assign a Location Id that is not in the table like 99999 for admin user.

                 

                2. Left join both tables. Data as Left and Security as Right.

                 

                3. Create a join calculation for Left table

                IF USERNAME()='TestAdmin' THEN

                    9999

                ELSE

                    [Location Id]

                END

                4. Select Location Id field from Right table

                5. Add another Join Condition and Create join calculation for left table.

                 

                6. Select Username field from Right Table.

                7. Add filter at data source level on username field.

                 

                 

                For Ismemberof approach check this link:

                 

                https://tableauandbehold.com/2016/08/08/defusing-row-level-security-in-tableau-data-extracts-before-they-blow-up-part-1/

                • 5. Re: Implementing RLS using CROSS Database Join feature
                  Nutan Patel

                  Hi Ombir Rathee

                   

                  First of all thanks for your quick responses.

                  The approach you suggested looks good but it won't work because I can not modify my security table. Also I believe you are in the impression that only admin user will have access to all locations (AdditionalLocationId field values), but that's not true. Many users have multiple locations assigned to them (it can be set of all locations or subset of locations). This method will not work with that scenario as you have managed admin scenario hard coded.

                   

                  Hope I am clear this time.!

                   

                  Thanks

                  Nutan Patel

                  • 6. Re: Implementing RLS using CROSS Database Join feature
                    Nutan Patel

                    Also, Could you please let me know what does that last step do?

                    7. Add filter at data source level on username field.

                     

                    Thanks

                    Nutan Patel

                    • 7. Re: Implementing RLS using CROSS Database Join feature
                      Ombir Rathee

                      This approach requires security table to be modified. I've hardcoded only admin scenario ( person who will access to all locations). If someone has access to multiple locations but not all then there should be a separate row for each user and location rather than comma separated values.

                       

                      Another approach as I suggested earlier is by creating Groups on Tableau Server and then define the access in calculated field using ISMEMBEROF function. Check the link I shared earlier. This method is explained in detail.

                       

                      Row level security can also be implemented using Initial Sql feature in tableau but for that you need row level access details in the table itself.

                      • 8. Re: Implementing RLS using CROSS Database Join feature
                        Ombir Rathee

                        On Data Source Page, you'll find Edit option on upper right corner. You've to select the username field and add the condition as I mentioned above.

                         

                         

                        • 9. Re: Implementing RLS using CROSS Database Join feature
                          Nutan Patel

                          Ok thanks for the update Ombir Rathee

                           

                          As of now I can not modify the security table but one more question for you. Let's assume we are not using additionallocationid field then its possible. right?

                           

                          I was trying to implement what you suggested, but I noticed when we used security file along with MS SQL we can not straight forward use store procedure with CROSS DB join, correct? I see it was disabled. Thee I tried using calling parameterized SQL procedure from Custom SQL but getting below error.  Calling procedure like this...

                           

                          EXEC ReportData @yearIDs=<Parameters.YearIdsTest>,@affiliateIDs=<Parameters.AffiliateIdsTest>,@mergerStatus=<Parameters.Merge Status>,@demographicsID=<Parameters.demographicsIDs_151>,@userID=<Parameters.UserId>

                           

                           

                          Thanks

                          Nutan Patel

                          • 10. Re: Implementing RLS using CROSS Database Join feature
                            Ombir Rathee

                            You can't use the sp with cross database join.

                             

                            Custom SQL only allow to write the Select statement for SQL server. You've to use the initial SQL instead. You can find the option while connecting to SQL server.

                             

                            Check this Run Initial SQL - Tableau . But I don't think you'll be able to pass the user defined parameters with initial sql.

                            • 11. Re: Implementing RLS using CROSS Database Join feature
                              Nutan Patel

                              Hi Ombir Rathee

                               

                              Thanks for your help so far.!!!

                              Will data blending or dynamic parameter (not sure possible or not) will work?

                               

                              I read somewhere we can pass dynamically pass parameter values, if that is possible based on USERNAME we can read additional location ids (comma separated) and then assign them dynamically to locationids parameter which is already passing to SQL procedure. Will that works?

                               

                              Let me know if you know something about dynamically passing parameters values.

                               

                              Thanks

                              Nutan Patel

                              • 12. Re: Implementing RLS using CROSS Database Join feature
                                Ombir Rathee

                                In order to apply the Row level security using cross database join, you've to join the two tables on basis of primary key which is combination of Username and location ID in your case.

                                 

                                Additional Location ID column directly can't be used to join the two tables neither with blending nor with dynamic parameter.

                                 

                                One option I can think of is to use the data blending after transformation of your security table. For that, first you've to split the additional id column using comma as delimiter. Then pivot the split columns along with location id column to get the table ready for blending with data table. But as you're connected with web data, I don't think pivot option will be available. You can give a try.

                                 

                                If this doesn't works then you're left with two options.

                                 

                                Either you've to modify the security table so that there is separate row for each user and location id to which the user has access.

                                 

                                or

                                 

                                You need to create the different security groups on tableau server. For ex: Location1 group contains list of all users who can access Location_1, Location_1_2 group contains list of all users who can access Location 1 and 2 and so on. Then you can call these groups with the help of calculated field using ISMEMBEROF function. I again suggest you go though the link that I shared earlier.

                                 

                                There is another option available to implement user level security with the help of User Filter which is available in Server Menu but this is a manual activity.

                                 

                                 

                                 

                                For any other advance approach may be Jamieson Christian   Jonathan Drummey can assist.

                                • 13. Re: Implementing RLS using CROSS Database Join feature
                                  Nutan Patel

                                  I am not sure why this approach will give us duplicate records. Because there will be unique location ids associated with each username and only for logged in user, it will return TRUE rest will be false. so how duplicates will be generated. At a time only one user can login. If we change from LEFT JOIN to INNER JOIN. This approach should work

                                   

                                  Regards

                                  Nutan Patel

                                  • 14. Re: Implementing RLS using CROSS Database Join feature
                                    Nutan Patel

                                    I think I will go with this approach ...

                                     

                                    "Either you've to modify the security table so that there is separate row for each user and location id to which the user has access."

                                     

                                    I modified my security table and now I have data as below..

                                     

                                    UserName                LocationId

                                    user1                         10

                                    user1                         11

                                    user1                         12

                                    user2                         10

                                     

                                    Now using INNER JOIN between Security table and Report procedure based on LocationId & your USERNAME condition. Hope it will work

                                     

                                    Thanks

                                    Nutan Patel