PostgreSQL只读角色权限及关联表查询方法求助

不同数据库系统的权限查询逻辑存在差异，以下是主流数据库的对应查询语句：

PostgreSQL #

查询readonly角色的所有权限 #

SELECT r.rolname, n.nspname AS schema_name, c.relname AS object_name, a.privilege_type
FROM pg_roles r
LEFT JOIN pg_auth_members m ON r.oid = m.member
LEFT JOIN pg_roles rm ON m.roleid = rm.oid
LEFT JOIN pg_class c ON c.relowner = rm.oid OR c.oid = a.tableoid
LEFT JOIN pg_namespace n ON c.relnamespace = n.oid
LEFT JOIN pg_privileges a ON a.grantee = r.rolname 
WHERE r.rolname = 'readonly'
AND a.privilege_type IS NOT NULL
ORDER BY schema_name, object_name;

查询readonly角色拥有SELECT权限的所有表 #

SELECT n.nspname AS schema_name, c.relname AS table_name
FROM pg_class c
JOIN pg_namespace n ON c.relnamespace = n.oid
JOIN pg_privileges p ON c.relname = p.tablename 
    AND n.nspname = p.schemaname
WHERE p.grantee = 'readonly' 
    AND p.privilege_type = 'SELECT'
    AND c.relkind = 'r' -- 仅筛选普通表，可按需移除该条件
ORDER BY schema_name, table_name;

MySQL #

查询readonly角色的所有权限 #

-- 表级权限
SELECT user, host, db AS schema_name, table_name, privilege_type
FROM information_schema.table_privileges
WHERE user = 'readonly'
UNION
-- 全局权限
SELECT user, host, NULL AS schema_name, NULL AS table_name, privilege_type
FROM information_schema.user_privileges
WHERE user = 'readonly';

查询readonly角色拥有SELECT权限的所有表 #

SELECT table_schema AS schema_name, table_name
FROM information_schema.table_privileges
WHERE grantee = '`readonly`@`%`' -- 替换为实际的host，比如`readonly`@`localhost`
    AND privilege_type = 'SELECT'
ORDER BY schema_name, table_name;

Oracle #

查询readonly角色的所有权限 #

-- 对象级权限
SELECT grantee, owner AS schema_name, table_name AS object_name, privilege
FROM dba_tab_privs
WHERE grantee = 'READONLY'
UNION
-- 系统级权限
SELECT grantee, NULL AS schema_name, NULL AS object_name, privilege
FROM dba_sys_privs
WHERE grantee = 'READONLY';

查询readonly角色拥有SELECT权限的所有表 #

SELECT owner AS schema_name, table_name
FROM dba_tab_privs
WHERE grantee = 'READONLY'
    AND privilege = 'SELECT'
    AND table_type = 'TABLE' -- 可选，仅筛选普通表
ORDER BY schema_name, table_name;

注：若当前用户无DBA权限，可将dba_tab_privs替换为all_tab_privs或user_tab_privs。

内容的提问来源于stack exchange，提问作者user2599851