3 Replies Latest reply on Jun 12, 2017 6:53 PM by Wilson Po

    Multi Level User Security Challenge (workbook Version 10.1.7)

    v subash

      Hi All,

       

       

      I am stuck up with multi-level user access (row level security) challenge. Please find the scenario below(Using Superstore data), which I am trying to implement same way with my data,

       

      I have Superstore data and one user mapping data. Which has  3 level access( Segment, category, Sub-category). Each user has different level access( please look out the below table). and I need to restrict the data based on below mapping.

       

       

       

      Level1

      Level2

      Level3

       

      UserName

      Segment

      Category

      Sub-Category

      Scenario1

       

      User1

      Consumer

      All

      All

      Scenario2

       

      User1

      Corporate

      Furniture

      All

       

      User2

      Consumer

      Office Supplies

      All

      Scenario3

       

      User2

      Consumer

      Furniture

      Bookcases

       

      User3

      Corporate

      Office Supplies

      Appliances

       

      User3

      Corporate

      Office Supplies

      Art

      Scenario4

       

      User4

      All

      All

      All

       

      * (Here my reference fields are Segment,Category & Sub-Category. and i don't have UserName in actual data. So i can't join the user mapping table here.)

       

      • User1 has access for seeing all consumer segment data (Scenario 1)  along with User1 has access for seeing all sub-category under furniture category only((Scenario 2)

       

      • User2 has access for seeing all sub-category under office supplies(Scenario 2)  along with seeing only bookcases sub category under furniture category only (Scenario 3).

       

      • User3 has access for seeing Applicances & Art subcategory under office Supplies (Scenario 3)

       

      • User 4 has access for seeing all Segment.

       

       

      Here I am looking for dymanic field which automatically restrict the data based on mapping. And I don’t want to apply any Mannual user filter.

      Because my data have n number of user with n number of level. So Please help me to over come this challange with dynamic solution.

      And I tried with below blogs, but nothing workout with my requirement. (Because same user has different level access)

       

          1. www.tableaulearners.com/2017/row-level-security-tableau

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

       

      So please help me to overcome from this challenge

       

       

      Note: I have attached the workbook with user mapping source in attachment.please find it

        • 1. Re: Multi Level User Security Challenge (workbook Version 10.1.7)
          Wilson Po

          This is a complex mapping issue and it does depend on what we want for a solution - if the user permissions are fixed and finite based on just the summary permission table you have above, you can get away with some hardcoding into a calculation:

             IF USERNAME()="User 1" THEN

                    IF [Segment]="Consumer" OR ([Segment]="Corporate" AND [Category] = "Furniture") THEN 1 ELSE 0 END

              ELSEIF USERNAME()="User 2" THEN

                    IF  ([Segment]="Consumer" AND [Category] = "Office Supplies") OR  ([Segment]="Consumer" AND [Category] = "Furniture" AND [Sub-Category]="Bookcase")  THEN 1 ELSE 0 END

              ELSEIF USERNAME()="User 3" THEN

                    IF ([Segment]="Corporate" AND [Category] = "Office Supplies" AND [Sub-Category]="Appliance")  OR  ([Segment]="Corporate" AND [Category] = "Office Supplies" AND [Sub-Category]="Art") THEN 1 ELSE 0 END

              ELSEIF USERNAME()="User 4" THEN

                    1

               END

          Apply this as a filter and set the filter value as 1.  As you can see we are translating the table into a calculation;  it works but it gets tedious.  While this avoids building an entitlement table that may duplicate the data size, it comes with the limitations of not working with a predefined entitlement: namely that the permissions is hardcoded into the workbook, and that suggests that updates need to be made directly in the workbook and it cannot be easily replicated to all other workbooks. 

           

          Instead I would still suggest the entitlement table route, but with a restructuring to the "Detailed User Mapping" to avoid data duplication.  I've attached an amended example to this mapping in the attached excel file.  In this case [Segment],[Category],[Sub-Category] still form a unique identifier so there will be no duplication of data.  While this structure can get wide depending on the complexity of the mapping, it does simplify the logic required to implement this inside of Tableau.  Simply join the table in based on [Segment],[Category],[Sub-Category], and implement the following user filter:

             USERNAME()=[Tier 1] OR

             USERNAME()=[Tier 2] OR

             USERNAME()=[Tier 3] OR

             USERNAME()=[Tier 4]

           

          This structure profiles each of the columns for match and validates combination that fit for the data. 

           

          Lastly, you can also implement the security directly on the data source and maintain a live connection.  Tableau supports a lot of data security functionality back to the DB including impersonation, initial SQL context setting and Kerberos delegation to manage user filtering on the data source: Data Security

           

          Hope this helps

          • 2. Re: Multi Level User Security Challenge (workbook Version 10.1.7)
            v subash

            Hi Wilson,

             

            Thanks for the Solution which you given.

            the 1st one, yes i need to workout more on duplicating user records with n conditions. So couldn't prefer that one.

            but 2nd one, there i have to add n Tier columns based on no. of users in mapping table. in my case, i have around 500+ users and also, my data comes from OData source. so joining is not possible.

            the real scenario is, i have 11 data source which including 2 Odata, 8 Excel data & one user mapping excel data source ( all have those 3 common fields - seg, cat., sub-cat). So with this much data if i create n Tier columns then performance hitting to the core.

            • 3. Re: Multi Level User Security Challenge (workbook Version 10.1.7)
              Wilson Po

              Hi V,

              Given the scope of data sources you are wrangling and the complex security model that you want to map out and manage, my blunt opinion is that you may need to consider investing into a data mart to help the data strategy achieve these security goals.  Odata and Excel really don't provide much tools in the scope of managing security mapping;  while Tableau does have user filtering functionality, it is typically managed on a workbook or data connection level rather than something more consistent or permissive for complex data security mapping.  I think a lot of our deployments find solutions in implementing a simple staging layer to help overcome the gaps in functionality in data source or the controls provided with Tableau.  Given the details I understand so far, this might be necessarily to help with the complexity in user mapping and may take some strain off from Tableau with having to work across multiple data sources.  Either way this should open options if processes can help simply stage the data together and support more complex security mechanisms;  something as simple as MS SQL Server may permit data security solutions such as impersonation or leveraging Redshift my allow for us to set session context with initial SQL.