SQL to list Grants and Privileges in Greenplum

The gadget spec URL could not be found
The gadget spec URL could not be found

select obj_name,

obj_type,

grantee,

grantor,

case when grantee is null then null else case when acc like '%r%' then true else false end end select_priv,

case when grantee is null then null else case when acc like '%a%' then true else false end end insert_priv,

case when grantee is null then null else case when acc like '%w%' then true else false end end update_priv,

case when grantee is null then null else case when acc like '%d%' then true else false end end delete_priv,

case when grantee is null then null else case when acc like '%x%' then true else false end end reference_priv,

case when grantee is null then null else case when acc like '%t%' then true else false end end trigger_priv,

case when grantee is null then null else case when acc like '%X%' then true else false end end execute_priv

from

(

select obj_name,

obj_type,

split_part(priv,'=',1) grantee,

split_part(split_part(priv,'=',2), '/',2) grantor,

split_part(split_part(priv,'=',2), '/',1) acc

from

(

select n.nspname || '.' || c.relname obj_name,

case c.relkind

when 'r' then case when relstorage = 'x' then 'external table' else 'table' end

when 'v' then 'view'

when 'S' then 'sequence'

end obj_type,

priv

from pg_catalog.pg_class c

left join (select oid, regexp_split_to_table(array_to_string(relacl, ','), ',') priv from pg_class) privs on privs.oid = c.oid

left join pg_catalog.pg_namespace n on n.oid = c.relnamespace

where c.relkind in ('r', 'v', 'S')

and n.nspname NOT IN ('gp_toolkit', 'information_schema', 'pg_catalog','pg_aoseg','pg_toast')

) a

) b

;

The gadget spec URL could not be found
The gadget spec URL could not be found
Comments