如何查询指定角色在全表中缺失的权限 以STUDENT_DBA的SELECT权限为例

MySQL 5.7/8.0 实现 #

查询逻辑：拉取目标schema下所有业务表，左关联权限表匹配STUDENT_DBA角色的SELECT权限，无匹配记录即为未授权表。

SELECT 
    t.table_schema,
    t.table_name
FROM 
    information_schema.tables t
LEFT JOIN 
    information_schema.table_privileges p 
    ON t.table_schema = p.table_schema 
    AND t.table_name = p.table_name 
    AND p.grantee = '\'STUDENT_DBA\'@\'%\'' 
    AND p.privilege_type = 'SELECT'
WHERE 
    t.table_schema = 'student_db' -- 替换为实际学生数据库名
    AND t.table_type = 'BASE TABLE'
    AND p.privilege_type IS NULL;

注意事项：

• 替换student_db为你实际使用的学生数据库schema名称
• 若STUDENT_DBA角色限制了登录主机，将\'STUDENT_DBA\'@\'%\''中的%替换为实际的主机地址

PostgreSQL 实现 #

查询逻辑：调用PG内置权限校验函数直接判断角色对表的SELECT权限，过滤出无权限的表。

SELECT 
    schemaname,
    tablename
FROM 
    pg_tables
WHERE 
    schemaname = 'student_schema' -- 替换为实际学生业务schema名
    AND NOT has_table_privilege('STUDENT_DBA', schemaname || '.' || tablename, 'SELECT');

注意事项：

• 自动排除系统表、视图等非业务表，结果仅包含用户创建的业务表
• 支持继承角色权限的场景，比手动关联系统权限表结果更准确

所有语句均为单条查询，可直接在对应数据库的查询客户端中执行

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