1 Reply Latest reply on Jul 14, 2016 12:45 PM by David Mannering

    Implementing Row Level Security - synchronizing across multiple published data sources

    aaron.walker.3

      Hi all,

       

      I'm part of an IT team working on a Tableau implementation. We're creating data sources to be consumed by business users for reporting, so we need to restrict access prior to publishing those data sources.

       

      I'm working on creating a row level security implementation, and I'm at an impasse. I've searched the forums for a similar problem, and I think this might be unique.

      Our goal is to create a single place where row level security logic resides--this could be a database table, a Tableau data extract, a PL/SQL script, or anything else. We just want Tableau to read from a central location when displaying rows from published extracts to users.

       

      We need to limit row access based on somewhat complex logic. For instance, users may be able to see certain business units' sales from one region but not from another. There are a dozen various fields upon which we might restrict access. To explain, here's the logic represented in a calculated field. Note that "1" indicates permissions, and "0" indicates no permissions:

       

      if USERNAME() = 'user1'

        then  if

                 ([BUSINESS_UNIT] = 'BEVERAGES' and [CUSTOMER_KEY] = '123' and [REGION] = 'NORTH_AMERICA')

              or ([BUSINESS_UNIT] = 'FOOD'      and                            [REGION] = 'EUROPE')

              then 1

              Else 0 end

      Elseif USERNAME() = 'user2'

         then if

                 ([BUSINESS_UNIT] = 'BEVERAGES' and [CUSTOMER_KEY] = '123' and [REGION] = 'NORTH_AMERICA')

              or ([BUSINESS_UNIT] = 'BEVERAGES'                            and [REGION] = 'EUROPE')

              or ([BUSINESS_UNIT] = 'FOOD'      and [CUSTOMER_KEY] = '234' and [REGION] = 'EUROPE')

              then 1

              Else 0 end

      Else 0 END

       

      Right now, we have a working implementation of this logic for a single published data source:

      1. Connect to database and build a Data Source
      2. Create a Calculated field (see above) by referencing fields from that data source
      3. Add a Data Source Filter that filters out rows unless Calculated field = 1
      4. Publish data source

       

      This works great for a single data source, but it will be limiting if we move it to production. When we have 10 or 15 published sources, and we have to update permissions (which happens very frequently), we would have to open each data source, update the calculated field logic, and republish the data source. I'm hoping there is a better way to pass this logic into a calculated field. Or, if there's a better option to provide this type of row level security, I'd be happy to pursue that as well.

       

      Has anyone run into this, or does anyone have a great idea? I'm open to options and it's worth it to my team to put plenty of effort into automating row level security. Thank you all!

        • 1. Re: Implementing Row Level Security - synchronizing across multiple published data sources
          David Mannering

          I can think of one way of doing it, but it may not handle all of the complexity of your situation.  You could create a separate file (Excel or a table in the same database format as your other data) in which you create a column for each of the items your security requires and a row for each logical instance.  For example to encode your example above:

           

               

          User_ValeBus_Unit_ValueCust_Key_ValueRegion_ValueAccess
          User1BEVERAGES123NORTH AMERICAY
          User1FOOD(ANY)EUROPEY
          User2BEVERAGES123NORTH AMERICAY
          User2BEVERAGES(ANY)EUROPEY
          User2FOOD123EUROPEY

           

          Then you either join (hurray for version 10!) this or blend it using the four fields as links with your data sources.

           

          Then you create a generic calculation in each data source that looks something like this:

           

          if (USERNAME() = [User_Value] AND( [BUSINESS_UNIT] = [Bus_Unit_Value] OR [Bus_Unit_value = '(ANY)')

             AND [CUSTOMER_KEY] = [Cust_Key_Value]  OR [Cust_Key_value] = '(ANY)')

             AND [REGION] = [Region_Value] OR [Region_Value] = '(ANY)' then 1 else 0 end

           

          (The Access column is not really needed)

           

          I'm not sure how well this would work with blending, but if you only had a maximum of three (let's say) clauses per user, you could combine them in one row [Bus_Unit_Value_1],[Bus_Unit_Value_2] etc.) which would give you one record per user, which would work much better.

          1 of 1 people found this helpful