5 Replies Latest reply on Aug 27, 2018 11:08 AM by Zach Leber

    work around for row level security AND still download workbooks?

    Sarah Delaney

      Bear in mind I'm an analyst, not a server admin when you read this...

       

      Overview:

      We are a central board with 34 separate colleges where each college can see  everyone else's summary data, but not individual student data.  Most of the data is only updated annually, but we do have some quarterly updates.There is a need to compare summary level such as a college’s graduation rates with another college’s rate.

       

      Current State

      I’ve been publishing to our Tableau server with the extract embedded and we’ve inserted the dashboards in website restricted to our colleges by IP address.  Colleges are actually gaining access by the guest account, but I’ve prevented web edit, save, save as and access to full data.  I have a lot of aliases in my workbooks which makes swapping data sources a pain unless you edit the xml.

       

      Business Requirements

      Colleges want to download my workbooks so they can build on what I have done. The colleges would also like access to their own student level data while still allowing summary level comparisons.  I don’t want to have to maintain multiple versions of the same workbook.

       

      Research

      I’ve viewed several security and permissions videos on the Tableau Admin Virtual User Group as well as

      Tableau Report Security: For your Eyes Only by Dr. Sinji Yang  and administering-tableau-server-10-with-real-time-scenarios and I don’t think it is possible to do this  - at least not in a single workbook because any row level security would shut off the ability to do a comparison across colleges. Plus allowing the workbook to be saved would allow editing of the user filter so an row level security could be bypassed.

       

      Rube Goldberg solution…

      Keep current workbooks just as they are

      Create a safe, dummy data set

      Copy each of my workbooks into a separate project

      Edit the XML to point the copy to the dummy data set

      Allow colleges to download the copy workbooks that point to the dummy data set

      Provide access to each college’s student level data (data source, SQL view, ????)

      Create documentation so that the colleges know how to edit the XML and point to their data

       

      Is there something better than this?

       

      Sarah

        • 1. Re: work around for row level security AND still download workbooks?
          Zach Leber

          Consider splitting the data sources out from the workbook. You could have a single published data source with a data source filter that uses either USERNAME() to check a user's Tableau login against a field in the database, or ISMEMBEROF() to check a user's Tableau login against a list of Tableau groups you maintain on server. Users could connect but not download or edit the data source. You'll have to stop using the guest account so that you can authenticate and authorize users by username.

           

          Restrict Data Access with User Filters and Row Level Security

           

          To allow comparison of summary data, you can create second published data source, that aggregates data by college, but doesn't have any row-level security, so anyone can use it.

           

          Then you can publish a generic workbook that connects to the published data. Users can download and modify your workbook but only connect to your carefully controlled data sources.

          • 2. Re: work around for row level security AND still download workbooks?
            Sarah Delaney

            Thank you so much for your suggestions. I have been using this article, Pixel Mixer: Row-Level Security On A Published Tableau Data Source as a temple for the 1st paragraph in your suggestion. I'm on a 2018.2 test server with a small dataset of a few colleges and I'm not filtering when I connect to the data source.

             

            Can you see if I have misinterpreted what I need to do? Or did something extra?

             

            Server

            • Create groups named "CollegeCode-CollegeName" for each college
            • Add users to each college's group

             

            Desktop

            • Create a new workbook "Step1" with a MS SQL Server datasource
            • Create calculated field called CollegeMemberCheck:

            //CollegeMemberCheck

            ( isMemberOf('300-CollegeA') and [College Code] = '300')

            or

            ( isMemberOf('230-CollegeB') and [College Code] = '230')

            or isMemberOf('Researchers')

            • Add CollegeMemberCheck = true as data source filter
            • Change from live to extract
            • Publish data source to server as Step1DataSourceWithFilter
              • set permissions so college groups have Data Source Connector role
              • set permissions so a group I'm a member of has Data Source Connector role
              • uncleck UpdateWorkbook to use the published data source

             

            Desktop

            • Create new workbook "Step2" and connect to Step1DataSourceWithFilter

            I can connect , but  when I "filter as" as user that should only see 1 college I am seeing all colleges.

            • 3. Re: work around for row level security AND still download workbooks?
              Zach Leber

              Hi Sarah, that looks really good, something small is probably wrong.

              - Is the test user in the Researchers group?

              - did CollegeMemberCheck=true get saved as a DS filter, not just a local filter?

               

              One caution is that DS filters are finicky and can have different impacts depending on how and when you add them, e.g.

              - if you add them using the filter card then convert then to apply to all worksheets they become a DS filter and then they might disappear from the filter card a little later! yes that happens, you have to look for them under DS filters

              - if you extract data after creating a DS filter the DS filter is applied by default which is not what you want if your DS filter is dynamic, e.g. based on username - you need to extract all data while maintaining the dynamic data source filter - I don't think this happened to you because you say you can see all data but a caution for the future

               

              One improvement for your scale-up could be to rename your Server groups 'College-NNN" (omit the actual names) so your CollegeMemberCheck can be simplified to:

                   isMemberOf('Researchers') OR isMemberOf('College-'+[College Code])

               

              Another option to consider is to allow Web Edit so users don't have to use Desktop to connect to your published data source and create and save new workbooks. You can save on Desktop licenses if you have Server licenses instead and for basic workbooks it may even be easier for your college users.

               

              The Filter as User test in Desktop is the right way to go, it's super handy.

               

              Let's figure this out.


              Regards,

              Zach

              • 4. Re: work around for row level security AND still download workbooks?
                Sarah Delaney

                I had a couple of my college users do some testing for me this morning and things worked differently than when I had been "filtering as". They were both locked down to their appropriate colleges when connecting to the data source!  

                 

                I had been able to see all colleges while I was "filtering as" connecting to the published data source in Desktop, but i was also logged into the server in a web browser with my site admin rights while I was testing.

                 

                I like the simplicity of   isMemberOf('Researchers') OR isMemberOf('College-'+[College Code]) .My oriiginal plan was to name the server groups as "College Abbreviation" +"college code" to make it easier on me to put users into one of 34 college groups without looking at a college code/title list. Am I wrong in thinking that my maintenance work is going to be in the group assignments and that once I create CollegeMemberCheck with the 34 college options it will be static and I'll just paste this in as I need it?

                • 5. Re: work around for row level security AND still download workbooks?
                  Zach Leber

                  Great to hear that your college users got the correct permissions though I don't understand how your Filter as User was getting overridden by your admin login, that seems like a Tableau glitch.

                   

                  If your college list is static using the names will work but there's just an increased chance of a typo in your calculated field. But I agree it will be easier to add new users to the correct college group if the groups are named rather than just numbered.