How to list granted access in greenplum database.

Products

• VMware Tanzu Greenplum

Issue/Introduction

If there is a request for listing all granted access in certain database, we can use below method to generate the list.

Resolution

For granted role to another role:

select r.rolname,  r2.rolname "group role" from pg_roles r, pg_roles r2 , pg_auth_members g where r.oid=g.member and g.roleid=r2.oid;

For object level granted access to another role. There are different view for different category:

select relname from pg_class where relnamespace=11724 and relname like 'role_%';
       relname
---------------------
 role_column_grants
 role_routine_grants
 role_table_grants
 role_usage_grants

role_column_grants
The view role_column_grants identifies all privileges granted on columns where the grantor or grantee is a currently enabled role. Further information can be found under column_privileges. The only effective difference between this view and column_privileges is that this view omits columns that have been made accessible to the current user by way of a grant to PUBLIC.

role_routine_grants
The view role_routine_grants identifies all privileges granted on functions where the grantor or grantee is a currently enabled role. Further information can be found under routine_privileges. The only effective difference between this view and routine_privileges is that this view omits functions that have been made accessible to the current user by way of a grant to PUBLIC.


role_table_grants
The view role_table_grants identifies all privileges granted on tables or views where the grantor or grantee is a currently enabled role. Further information can be found under table_privileges. The only effective difference between this view and table_privileges is that this view omits tables that have been made accessible to the current user by way of a grant to PUBLIC.


role_usage_grants
The view role_usage_grants identifies USAGE privileges granted on various kinds of objects where the grantor or grantee is a currently enabled role. Further information can be found under usage_privileges. The only effective difference between this view and usage_privileges is that this view omits objects that have been made accessible to the current user by way of a grant to PUBLIC.

One example of grant select access on sales table to admin role:

gpadmin=#  grant select on sales to admin;
 
gpadmin=# select * from information_schema.role_table_grants where table_name ='sales' and grantee <> 'gpadmin';
 grantor | grantee | table_catalog | table_schema | table_name | privilege_type | is_grantable | with_hierarchy
---------+---------+---------------+--------------+------------+----------------+--------------+----------------
 gpadmin | admin   | gpadmin       | public       | sales      | SELECT         | NO           | NO
(1 row)