5 Replies Latest reply on Mar 14, 2016 7:26 AM by Toby Erkson

    Query which objects a user and/or group has connector/viewer/interactor privileges to?

    Darin Coulter



      I like the latest permissions interface on the Server front end - it's really nice to pull up a single workbook and view the permissions.  Same for a project, etc.  Really nice.


      However - I would like to be able to audit workbook viewer/interactor/download/etc permissions from the Postgres database using the readonly user.


      I haven't been able to identify a query or the proper objects that might allow me to end up with a view that shows every project, data source or workbook that a user and/or group can access using the Postgres repository.


      There's a lot of responsibility placed on workbook owners/publishers to designate proper projects, AG groups or users with viewer/interactor rights to their objects.  I'm just looking for a way to audit that from the perspective of the group instead of the perspective of a single project/datasource/workbook.  I want to see everything that my most restrictive AG group can access.


      Is this do-able from the Postgres, workgroup database using the readonly user? 


      This is the closest I was able to come on my own at a project level to give a directional idea of what I'm trying to do.




              , c.display_name

              , c.display_order

              , p."name" as project_name

              , p.owner_id as proj_owner_id

              , CASE

                      WHEN pt.grantee_type = 'User' THEN su."name"

                      WHEN pt.grantee_type = 'Group' THEN g."name"

                END AS user_name

              , CASE

                      WHEN pt.grantee_type = 'User' THEN su.id

                      WHEN pt.grantee_type = 'Group' THEN g.id

                END AS user_id

              , CASE

                      WHEN pt.grantee_type = 'User' THEN su.email

                      ELSE NULL

                END as user_email

              , CASE

                      WHEN pt.grantee_type = 'User' THEN su.friendly_name

                      ELSE NULL

                END as user_friendly_name

      from permissions_templates as pt

              inner join capabilities as c

      on pt.capability_id = c.id

              inner join projects as p

      on pt.project_id = p.id

              left outer join system_users as su

      on pt.grantee_id = su.id

              left outer join groups as g

      on pt.grantee_id = g.id

      order by pt.project_id, pt.template_type, user_name




      There's no reason for All Users to have view/export access to a workbook in a "Pending Code Review" project because that's obviously not for global consumption.  I would like to be able to see which workbooks this involved.