【openGauss】openGauss查对象权限

---

openGauss查对象权限

drop view if exists gs_obj_privs;
create view gs_obj_privs as 
WITH pg_rel_privs AS (
         SELECT /*xx.relowner,*/
            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;