10 Replies Latest reply on Jun 5, 2015 5:03 AM by Jeff Strauss

    Row level security

    Bhuwan Vikram Singh

      Hi Guys ,

      Suppose we have 1000 or 10000, or 10000   managers and we have to apply row level security for

      dashboard then it would be very hectic job right , we will have to create 1000 users in server and

      then will have to apply from desktop , I mean if we have so many mangers /users and we have to

      apply row level security then how will we do that ?

       

      If we have small size of users e.g  5 then creating user level filter is simple .

       

      Suggest me guys , what would be alternate ?

       

       

       

      Regards ,

      Bhuwan

        • 1. Re: Row level security
          Mark Fraser

          Hi Bhuwan

           

          I'm moving your question to Server Administration as I think you'll get more views/ help there.

           

          Cheers

          Mark

          • 2. Re: Row level security
            Jaideep Rokade

            Hello,

             

            You have to make data accordingly.

            Each row will have user field and in this field all the user's name who have access to that row level data.

            eg Region     State     City          QTY       Amt          User Name

                 West         MH     Mumbai     30          12000          ABC,XYZ.....

             

            now In tableau when you create a view/report apply user security here

            In the Calculated Field dialog box, do the following tasks:

            1. In the Name text box, type User is a manager.
            2. In the Formula text box, type the formula below, and click OK.

            USERNAME()= [Manager]

             

            NEXT

             

            Refer the link below

            http://kb.tableau.com/articles/knowledgebase/row-level-security-and-user-filters

             

            Continue with point no 10.

             

            Hope this work.

             

            Regards

            Jaideep

            • 3. Re: Row level security
              Bhuwan Vikram Singh

              Hi Jaideep ,

               

              I am aware about this concept, suppose if you have 1000 users & need to

              apply row level security , then it would be tedious job right?

               

              So is there any other method ?

               

               

               

               

              Regards ,

               

              Bhuwan

               

              On Mon, Jun 1, 2015 at 5:22 PM, Jaideep Rokade <

              • 4. Re: Row level security
                Jeff Strauss

                You can apply row level security by dynamically blending a core published datasource (e.g. performance data) with a security based datasource (e.g. company user filter).  We've had success with this, but you should be careful to do this with diligence to ensure optimal performance and proper functioning of security.

                 

                1. The core datasource (primary) has company_id along with all the dimensions / metrics that need to be sliced / diced.  It's updated once a day.

                 

                2. The security datasource (secondary) has company_id and is blended to #1.  It's updated whenever our system of record triggers a refresh of this data.

                 

                3. Inside the secondary datasource, each row has company_id, username, and a calculated field (IIF(CONTAINS(LOWER([Username]), LOWER(username())),1,0))

                 

                4. Within each report, there is a blend between #1 and #2.  And then there is a filter on the calculated field looking for true.

                 

                Let me know if this is clear and if it helps.

                1 of 1 people found this helpful
                • 5. Re: Row level security
                  Bhuwan Vikram Singh

                  Hi Jeffrey ,

                  Thank you , I have doubt  about this (" (IIF(CONTAINS(LOWER([Username]), LOWER(username())),1,0)) "),

                  you mean this calculation we need to do in database itself or in tableau ?

                   

                  Please elaborate about this .

                   

                   

                   

                  Regards ,

                  Bhuwan

                  • 6. Re: Row level security
                    Jeff Strauss

                    Here you go:

                     

                    The LOWER([Username] is the field in the secondary data source.  (i.e. jstrauss).  The LOWER(username()) uses the built-in Tableau functionality to capture the authenticated logged in userid (i.e. corp\jstrauss).

                     

                    Comparing the 2 strings permits filtering down to the company id's that the id is associated with by only taking the fields that are marked as "1".  So if I am logged in with jstrauss, then I only get 1Toyota and 2Ford with the above calc filter. And then these are joined to the main data source for filtering.

                     

                    Example:

                    Username     company_id         

                    jstrauss        1Toyota

                    msmith         2Ford

                    jstrauss        2Ford

                    sjones          3Nissan

                    twilson         4BMW

                    bobama        5Mercedes

                    1 of 1 people found this helpful
                    • 7. Re: Row level security
                      Bhuwan Vikram Singh

                      Hi Jeffrey ,

                      Very informative , so this calculation  (" (IIF(CONTAINS(LOWER([Username]) will have to right in tableau , can you please explain why do we need two times Username in calculation (" (IIF(CONTAINS(LOWER([Username]), LOWER(username())),1,0)) "),?



                      Thank You !!

                      • 8. Re: Row level security
                        Jeff Strauss

                        it's just the way that we set it up, it could perhaps be clearer.  Pay attention to case.

                         

                        the all lowercase username() is the buiilt-in function to capture the authenticated logged in userid (i.e. corp\jstrauss).

                         

                        the one with the upper "U" ([Username]) is the field in the secondary data source.  (i.e. jstrauss), it could be named whatever you want it to be in the secondary source.

                        • 9. Re: Row level security
                          Bhuwan Vikram Singh

                          Thank you !!

                           

                          On Thu, Jun 4, 2015 at 4:42 PM, Jeffrey Strauss <

                          • 10. Re: Row level security
                            Jeff Strauss

                            is it answered?