PostgreSQL-视图-02-查询对象权限视图-dba_tab_privs_postgrepsql 授权视图权限

PostgreSQL-视图-02-查询对象权限视图-dba_tab_privs

-- PostgreSQL查询对象权限视图

create view public.dba_tab_privs as
WITH x AS (
  select (select u.usename
            from pg_user u
           where (u.usesysid = tt.relowner)
         ) AS relowner,
         (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)
)
,pg_rel_privs as (
  select x.relowner,
         x.schemaname,
         x.relname,
         x.relkind,
         x.relacls[1] AS grantee,
         regexp_split_to_table(x.relacls[2], ''::text) AS privilege,
         x.relacls[3] AS grantor
    from x
)
select t.relowner AS owner_,
       t.schemaname AS schema_name,
       t.relname AS table_name,
       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 'partition table'::text
            ELSE (t.relkind::text)
       END AS type_,
       t.grantee,
       CASE WHEN (t.privilege = 'r'::text) THEN 'select'::text
            WHEN (t.privilege = 'a'::text) THEN 'insert'::text
            WHEN (t.privilege = 'd'::text) THEN 'delete'::text
            WHEN (t.privilege = 'w'::text) THEN 'update'::text
            WHEN (t.privilege = 'D'::text) THEN 'truncate'::text
            WHEN (t.privilege = 'X'::text) THEN 'execute'::text
            ELSE t.privilege
       END AS privilege,
       t.grantor
  from pg_rel_privs t
 where t.relowner <> t.grantee
;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50