【openGauss】openGauss查对象权限
openGauss查对象权限
drop view if exists gs_obj_privs;
create view gs_obj_privs as
WITH pg_rel_privs AS (
SELECT
xx.schemaname,
xx.relname,
xx.relkind,
xx.relacls[1] AS grantee,
xx.relacls[2] AS privilege,
xx.relacls[3] AS grantor
FROM ( SELECT
( SELECT n.nspname
FROM pg_namespace n
WHERE n.oid = tt.relnamespace) AS schemaname,
tt.relname,
tt.relkind,
regexp_split_to_array(unnest(tt.relacl)::character varying::text, '=|/'::text) AS relacls
FROM pg_class tt
WHERE tt.relacl IS NOT NULL) xx
)
SELECT
t.schemaname,
t.relname,
CASE
WHEN t.relkind = 'r'::"char" THEN 'table'::text
WHEN t.relkind = 'S'::"char" THEN 'sequence'::text
WHEN t.relkind = 'v'::"char" THEN 'view'::text
WHEN t.relkind = 'p'::"char" THEN 'patition table'::text
ELSE t.relkind::text
END AS type_,
t.grantee,
trim(case when instr(t.privilege,'a')>0 then 'insert,' end ||
case when instr(t.privilege,'r')>0 then 'select,' end ||
case when instr(t.privilege,'w')>0 then 'update,' end ||
case when instr(t.privilege,'d')>0 then 'delete,' end ||
case when instr(t.privilege,'D')>0 then 'truncate,' end ||
case when instr(t.privilege,'U')>0 then 'usage,' end ,',') as privilege,
t.grantor
FROM pg_rel_privs t
where t.grantor!= 'rdsAdmin'
and t.grantee!= t.grantor;