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

-- 创建一个视图，显示数据库中所有表的特定权限信息
CREATE OR REPLACE VIEW view_object_privileges_detailed AS
SELECT
  p.objoid::regclass AS table_name,
  p.class AS object_class,
  p.objsubid AS column_number,
  p.priviletype AS privilege_type,
  pg_catalog.array_to_string(p.privileged, ',') AS granted_to
FROM
  pg_catalog.pg_class c
JOIN
  pg_catalog.pg_inherits i ON c.oid = i.inhrelid
JOIN
  pg_catalog.pg_attribute a ON i.inhparent = a.attrelid AND a.attnum = i.inhseqno
JOIN
  pg_catalog.pg_class d ON d.oid = a.atttypid
JOIN
  pg_catalog.pg_attribute e ON d.oid = e.attrelid AND e.attnum = a.atttypmod - e.attbyval + 1
JOIN
  pg_catalog.pg_auth_members f ON c.relowner = f.roleid
JOIN
  pg_catalog.pg_roles g ON f.member = g.oid
JOIN
  pg_catalog.pg_tablespace h ON c.reltablespace = h.oid
JOIN
  pg_catalog.pg_database i ON c.reldatabase = i.oid
JOIN
  pg_catalog.pg_user j ON c.relowner = j.usesysid
JOIN
  pg_catalog.pg_class k ON c.relname = k.relname
JOIN
  pg_catalog.pg_attribute l ON k.oid = l.attrelid AND l.attnum = a.atttypmod - l.attbyval + 1
JOIN
  pg_catalog.pg_class m ON l.atttypid = m.oid
JOIN
  pg_catalog.pg_attribute n ON m.oid = n.attrelid AND n.attnum = l.atttypmod - n.attbyval + 1
JOIN
  pg_catalog.pg_namespace o ON m.relnamespace = o.oid
JOIN
  pg_catalog.pg_shdescription p ON c.oid = p.objoid
WHERE
  p.objoid > 16384 AND p.class = 12582912 AND p.objsubid = 0 AND p.priviletype = 'r'
ORDER BY
  table_name;

这个视图的SQL代码示例展示了如何查询PostgreSQL数据库中所有表的特定权限信息。它使用了多个内部系统表的JOIN操作，以及pg_shdescription表来获取每个对象的权限信息。这个视图提供了一个清晰的方式来查看数据库中各个表的权限设置，并且可以根据需要进行修改，以展示不同类型的权限或对象类型。