如何在MySQL中编写查询获取指定schema下用户无SELECT权限的表

实现逻辑 #

MySQL没有内置has_table_privilege()函数，权限信息分别存储在mysql.user（全局权限）、mysql.db（库级权限）、information_schema.table_privileges（表级权限）三张系统表中，我们结合三张表联合判断当前用户拥有SELECT权限的表，再取差集即可得到无权限的表列表（MySQL中schema等价于database）。

完整查询语句 #

将语句中的<目标库名>替换为你要查询的schema名称即可：

SELECT t.table_name
FROM information_schema.tables t
WHERE t.table_schema = '<目标库名>'
  AND t.table_type = 'BASE TABLE' -- 仅查询普通表，需要包含视图可删除该行
  AND NOT EXISTS (
    SELECT 1
    FROM (
        -- 匹配有SELECT权限的三种场景
        -- 1. 全局SELECT权限
        SELECT 1 AS has_priv
        FROM mysql.user u
        WHERE CONCAT(u.User, '@', u.Host) = CURRENT_USER()
          AND u.Select_priv = 'Y'
        UNION ALL
        -- 2. 目标库级SELECT权限
        SELECT 1 AS has_priv
        FROM mysql.db d
        WHERE CONCAT(d.User, '@', d.Host) = CURRENT_USER()
          AND d.Db = '<目标库名>'
          AND d.Select_priv = 'Y'
        UNION ALL
        -- 3. 单表级SELECT权限
        SELECT 1 AS has_priv
        FROM information_schema.table_privileges tp
        WHERE REPLACE(tp.grantee, '''', '') = CURRENT_USER()
          AND tp.table_schema = '<目标库名>'
          AND tp.table_name = t.table_name
          AND tp.privilege_type = 'SELECT'
    ) priv_check
    WHERE priv_check.has_priv = 1
);

简化版本（无mysql系统库访问权限时使用） #

如果当前账号没有读取mysql系统库的权限，可以使用仅判断表级权限的简化版本，覆盖大部分日常使用场景：

SELECT table_name
FROM information_schema.tables
WHERE table_schema = '<目标库名>'
  AND table_type = 'BASE TABLE'
  AND table_name NOT IN (
    SELECT DISTINCT table_name
    FROM information_schema.table_privileges
    WHERE REPLACE(grantee, '''', '') = CURRENT_USER()
      AND table_schema = '<目标库名>'
      AND privilege_type = 'SELECT'
);

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