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.
, p."name" as project_name
, p.owner_id as proj_owner_id
WHEN pt.grantee_type = 'User' THEN su."name"
WHEN pt.grantee_type = 'Group' THEN g."name"
END AS user_name
WHEN pt.grantee_type = 'User' THEN su.id
WHEN pt.grantee_type = 'Group' THEN g.id
END AS user_id
WHEN pt.grantee_type = 'User' THEN su.email
END as user_email
WHEN pt.grantee_type = 'User' THEN su.friendly_name
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.