11 Replies Latest reply on Nov 12, 2019 12:01 PM by Donna Phillips

    Tableau server 2019.X permissions export

    Julien Hauswirth

      Dear all,

       

      I'm currently struggling to find a way to have all the permissions by user / view for our Tableau Server Instance. We are running on 2019.2 and it seems that all the workbook I was able to find until now doesn't take into consideration the modifications made since the latest updated.

       

      Basically I'm trying to figure out the correct SQL query to get from the Postgres DB the export of all permissions given to all user. We need this for audit purpose in my company.

       

      I've seen that there is an old post, recently commented though (Using Postgres To Examine Permissions ) that is now outdated, so I don't know if it's the correct way to proceed but I'm opening a new one.

       

      If anybody has a query or even better a dashboard it would be of great help.

       

      Thanks in advance

       

      Julien

        • 2. Re: Tableau server 2019.X permissions export
          Julien Hauswirth

          Hello Micheal,

           

          Thanks for the link, it's not exactly what I'm looking for but it's definitely something useful to have.

           

          Unfortunately I'm really looking for a query to gather all the permissions across site / project / workbooks and views.

           

          The answer I'm trying to answer is : Who has access to what? I know it seems quite easy but I didn't achieve to find something accurate for the moment...

           

          If you could add a "TS Permissions" in your package that would be awesome!

          • 3. Re: Tableau server 2019.X permissions export
            Michael Gillespie

            So, credit where credit is due.  Those belong to Matt Coles, who has been maintaining them for a long while!  I'm just a consumer, like you.

             

            I am no expert on the mysteries of the repository, but perhaps Matt can offer some advice.

            • 4. Re: Tableau server 2019.X permissions export
              Matt Coles

              (thanks Michael Gillespie!)

               

              Hi Julien, we don't have anything for permissions in the TS data sources just yet, but this will mark around the eleven thousandth time it's been requested, so we might have to start building it.

               

              Short answer in the meantime is that the permissions are stored in next_gen_permissions, and could reference either groups of individuals, or actual users. It contains all grants or denies on both group and user levels, which could apply to projects, workbooks, views, data sources, or flows. You will not likely be able to obtain information on all individual users' permissions to every object without generating a query that takes hours, and returns millions and millions of rows. But you could get something that provided high level information on the groups and users' permissions to a given piece of content.

              • 5. Re: Tableau server 2019.X permissions export
                Gerardo Varela

                Hi All,

                There used to be a workbook in the blog Tableau Love that had what we needed.  It's been removed, sadly.

                 

                https://tableaulove.com/dumping-tableau-server-permissions-the-story-can/

                 

                I believe this is the custom SQL that the workbook contained.  (If I find something useful I store it.  Sometimes I don't remember exactly where I get things from.) 

                 

                 

                SELECT * 
                FROM   (SELECT N.authorizable_type, 
                               N.authorizable_id, 
                               N.grantee_id, 
                               N.grantee_type, 
                               N.capability_id, 
                               N.permission, 
                               U.friendly_name AS grantee_name, 
                               P.NAME          AS authorizable_name, 
                               P.NAME          AS project, 
                               '.'             AS workbook, 
                               '.'             AS VIEW 
                        FROM   next_gen_permissions N 
                               LEFT JOIN _users U 
                                      ON N.grantee_id = U.id 
                               LEFT JOIN projects P 
                                      ON N.authorizable_id = P.id 
                        WHERE  N.grantee_type = 'User' 
                               AND N.authorizable_type = 'Project' 
                        UNION 
                        SELECT N.authorizable_type, 
                               N.authorizable_id, 
                               N.grantee_id, 
                               N.grantee_type, 
                               N.capability_id, 
                               N.permission, 
                               G.NAME AS grantee_name, 
                               P.NAME AS authorizable_name, 
                               P.NAME AS project, 
                               '.'    AS workbook, 
                               '.'    AS VIEW 
                        FROM   next_gen_permissions N 
                               LEFT JOIN groups G 
                                      ON N.grantee_id = G.id 
                               LEFT JOIN projects P 
                                      ON N.authorizable_id = P.id 
                        WHERE  N.grantee_type = 'Group' 
                               AND N.authorizable_type = 'Project' 
                        UNION 
                        SELECT N.authorizable_type, 
                               N.authorizable_id, 
                               N.grantee_id, 
                               N.grantee_type, 
                               N.capability_id, 
                               N.permission, 
                               G.NAME AS grantee_name, 
                               W.NAME AS authorizable_name, 
                               P.NAME AS project, 
                               W.NAME AS workbook, 
                               '.'    AS VIEW 
                        FROM   next_gen_permissions N 
                               LEFT JOIN groups G 
                                      ON N.grantee_id = G.id 
                               LEFT JOIN workbooks W 
                                      ON N.authorizable_id = W.id 
                               LEFT JOIN projects P 
                                      ON W.project_id = P.id 
                        WHERE  N.grantee_type = 'Group' 
                               AND N.authorizable_type = 'Workbook' 
                        UNION 
                        SELECT N.authorizable_type, 
                               N.authorizable_id, 
                               N.grantee_id, 
                               N.grantee_type, 
                               N.capability_id, 
                               N.permission, 
                               U.friendly_name AS grantee_name, 
                               W.NAME          AS authorizable_name, 
                               P.NAME          AS project, 
                               W.NAME          AS workbook, 
                               '.'             AS VIEW 
                        FROM   next_gen_permissions N 
                               LEFT JOIN _users U 
                                      ON N.grantee_id = U.id 
                               LEFT JOIN workbooks W 
                                      ON N.authorizable_id = W.id 
                               LEFT JOIN projects P 
                                      ON W.project_id = P.id 
                        WHERE  N.grantee_type = 'User' 
                               AND N.authorizable_type = 'Workbook' 
                        UNION 
                        SELECT N.authorizable_type, 
                               N.authorizable_id, 
                               N.grantee_id, 
                               N.grantee_type, 
                               N.capability_id, 
                               N.permission, 
                               G.NAME AS grantee_name, 
                               V.NAME AS authorizable_name, 
                               P.NAME AS project, 
                               W.NAME AS workbook, 
                               V.NAME AS VIEW 
                        FROM   next_gen_permissions N 
                               LEFT JOIN groups G 
                                      ON N.grantee_id = G.id 
                               LEFT JOIN views V 
                                      ON N.authorizable_id = V.id 
                               LEFT JOIN workbooks W 
                                      ON V.workbook_id = W.id 
                               LEFT JOIN projects P 
                                      ON W.project_id = P.id 
                        WHERE  N.grantee_type = 'Group' 
                               AND N.authorizable_type = 'View' 
                        UNION 
                        SELECT N.authorizable_type, 
                               N.authorizable_id, 
                               N.grantee_id, 
                               N.grantee_type, 
                               N.capability_id, 
                               N.permission, 
                               U.friendly_name AS grantee_name, 
                               V.NAME          AS authorizable_name, 
                               P.NAME          AS project, 
                               W.NAME          AS workbook, 
                               V.NAME          AS VIEW 
                        FROM   next_gen_permissions N 
                               LEFT JOIN _users U 
                                      ON N.grantee_id = U.id 
                               LEFT JOIN views V 
                                      ON N.authorizable_id = V.id 
                               LEFT JOIN workbooks W 
                                      ON V.workbook_id = W.id 
                               LEFT JOIN projects P 
                                      ON W.project_id = P.id 
                        WHERE  N.grantee_type = 'User' 
                               AND N.authorizable_type = 'Workbook') X 
                       LEFT JOIN capabilities C 
                              ON X.capability_id = C.id 
                

                 

                Please be careful and heed the warnings Matt mentioned as well Darth Flashy Pants and do not run this on your production server.  You'll need to adjust the query to include the new permissions that tableau has added since the blog post above.  Hopefully, that is enough to get you started.

                 

                Regards,

                Gerardo

                 

                P.S.  You may want to contact Darth Flashy Pants to see if he has a copy of the original workbook lying around. 

                 

                https://russellchristopher.me/

                 

                 

                 

                 

                 

                 

                SELECT *
                FROM   (SELECT N.authorizable_type,
                               N.authorizable_id,
                               N.grantee_id,
                               N.grantee_type,
                               N.capability_id,
                               N.permission,
                               U.friendly_name AS grantee_name,
                               P.NAME          AS authorizable_name,
                               P.NAME          AS project,
                               '.'             AS workbook,
                               '.'             AS VIEW
                        FROM   next_gen_permissions N
                               LEFT JOIN _users U
                                      ON N.grantee_id = U.id
                               LEFT JOIN projects P
                                      ON N.authorizable_id = P.id
                        WHERE  N.grantee_type = 'User'
                               AND N.authorizable_type = 'Project'
                        UNION
                        SELECT N.authorizable_type,
                               N.authorizable_id,
                               N.grantee_id,
                               N.grantee_type,
                               N.capability_id,
                               N.permission,
                               G.NAME AS grantee_name,
                               P.NAME AS authorizable_name,
                               P.NAME AS project,
                               '.'    AS workbook,
                               '.'    AS VIEW
                        FROM   next_gen_permissions N
                               LEFT JOIN groups G
                                      ON N.grantee_id = G.id
                               LEFT JOIN projects P
                                      ON N.authorizable_id = P.id
                        WHERE  N.grantee_type = 'Group'
                               AND N.authorizable_type = 'Project'
                        UNION
                        SELECT N.authorizable_type,
                               N.authorizable_id,
                               N.grantee_id,
                               N.grantee_type,
                               N.capability_id,
                               N.permission,
                               G.NAME AS grantee_name,
                               W.NAME AS authorizable_name,
                               P.NAME AS project,
                               W.NAME AS workbook,
                               '.'    AS VIEW
                        FROM   next_gen_permissions N
                               LEFT JOIN groups G
                                      ON N.grantee_id = G.id
                               LEFT JOIN workbooks W
                                      ON N.authorizable_id = W.id
                               LEFT JOIN projects P
                                      ON W.project_id = P.id
                        WHERE  N.grantee_type = 'Group'
                               AND N.authorizable_type = 'Workbook'
                        UNION
                        SELECT N.authorizable_type,
                               N.authorizable_id,
                               N.grantee_id,
                               N.grantee_type,
                               N.capability_id,
                               N.permission,
                               U.friendly_name AS grantee_name,
                               W.NAME          AS authorizable_name,
                               P.NAME          AS project,
                               W.NAME          AS workbook,
                               '.'             AS VIEW
                        FROM   next_gen_permissions N
                               LEFT JOIN _users U
                                      ON N.grantee_id = U.id
                               LEFT JOIN workbooks W
                                      ON N.authorizable_id = W.id
                               LEFT JOIN projects P
                                      ON W.project_id = P.id
                        WHERE  N.grantee_type = 'User'
                               AND N.authorizable_type = 'Workbook'
                        UNION
                        SELECT N.authorizable_type,
                               N.authorizable_id,
                               N.grantee_id,
                               N.grantee_type,
                               N.capability_id,
                               N.permission,
                               G.NAME AS grantee_name,
                               V.NAME AS authorizable_name,
                               P.NAME AS project,
                               W.NAME AS workbook,
                               V.NAME AS VIEW
                        FROM   next_gen_permissions N
                               LEFT JOIN groups G
                                      ON N.grantee_id = G.id
                               LEFT JOIN views V
                                      ON N.authorizable_id = V.id
                               LEFT JOIN workbooks W
                                      ON V.workbook_id = W.id
                               LEFT JOIN projects P
                                      ON W.project_id = P.id
                        WHERE  N.grantee_type = 'Group'
                               AND N.authorizable_type = 'View'
                        UNION
                        SELECT N.authorizable_type,
                               N.authorizable_id,
                               N.grantee_id,
                               N.grantee_type,
                               N.capability_id,
                               N.permission,
                               U.friendly_name AS grantee_name,
                               V.NAME          AS authorizable_name,
                               P.NAME          AS project,
                               W.NAME          AS workbook,
                               V.NAME          AS VIEW
                        FROM   next_gen_permissions N
                               LEFT JOIN _users U
                                      ON N.grantee_id = U.id
                               LEFT JOIN views V
                                      ON N.authorizable_id = V.id
                               LEFT JOIN workbooks W
                                      ON V.workbook_id = W.id
                               LEFT JOIN projects P
                                      ON W.project_id = P.id
                        WHERE  N.grantee_type = 'User'
                               AND N.authorizable_type = 'Workbook') X
                       LEFT JOIN capabilities C
                              ON X.capability_id = C.id

                 

                3 of 3 people found this helpful
                • 6. Re: Tableau server 2019.X permissions export
                  Julien Hauswirth

                  Thanks a lot Gerardo,

                   

                  this will definitely help me to start figuring out something. The granularity isn't enough for our need though, I really need to have a per user permission even though the result will be huge in terms of rows.

                   

                  Anyway thanks again for the query. I'll keep the post open (but mark your post as useful) until maybe someone (or even I) comes with something more detailed.

                  • 7. Re: Tableau server 2019.X permissions export
                    Donna Phillips

                    Thank you for that - very helpful!

                    For my purposes I eliminated the capability-level detail which reduced rows. I hope that data element isn't necessary to do the next thing I hope to accomplish:

                    Using the query you provided, I am trying to add the group>user relationship as I want to audit who has direct permissions rather than permissions as result of group membership. I haven't managed it so far - my join from group_users to either system_users or users and to groups is failing spectacularly. If  you (or anybody) has any insight into how to make this work, please share. If I manage it eventually, I will add it here.

                    • 8. Re: Tableau server 2019.X permissions export
                      Gerardo Varela

                      Hi Donna,

                          I'm not completely following, I'm blaming the time change, but I think what you are looking for is the query above with:

                       

                      Grantee_type = User and Workbook <> NULL

                       

                      That should give you who has explicit permissions to the workbook,  I think (I'm working off memory).  

                       

                      Regards,

                      Gerardo

                      • 9. Re: Tableau server 2019.X permissions export
                        Angie Greenhaw

                        Thank you, Gerardo! I used this today and it worked perfectly. Between the .twb from the related post and swapping in your updated SQL from this post, it was exactly what I was looking for. You rock and thanks for your contributions!

                         

                        Angie Greenhaw

                        1 of 1 people found this helpful
                        • 10. Re: Tableau server 2019.X permissions export
                          Gerardo Varela

                          Thank you for your kind words!

                           

                          Regards,

                          Gerardo

                          • 11. Re: Tableau server 2019.X permissions export
                            Donna Phillips

                            Thank you for your response. Your query combined with the related post that Angie Greenhaw mentioned have answered my question! I really appreciate it!