MySQL 8.0中SHOW GRANTS通配符使用及用户权限表创建方法

MySQL 8.0 原生不支持在SHOW GRANTS语句中直接用通配符匹配查询所有用户权限，直接执行SHOW GRANTS FOR '%'@'%';只会查询账号名和主机名完全为通配符字面量的单个账号权限，不会遍历所有账号返回结果，无法实现查询全量用户权限的目的。

你之前导出所有非系统账号对应SHOW GRANTS语句的逻辑是可行的，按以下步骤操作即可生成包含「用户名、用户权限」两列的存储表：

• 第一步：先创建权限存储表，执行如下建表语句：

CREATE TABLE IF NOT EXISTS user_permissions (
    username VARCHAR(255) NOT NULL COMMENT '账号标识，格式为 用户名@主机名',
    permission_text TEXT NOT NULL COMMENT '账号对应的权限配置'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

• 第二步：优先推荐跳过中间导出文件，直接通过存储过程一次性完成全量权限采集写入，操作更简便：

DELIMITER //
CREATE PROCEDURE collect_all_user_grants()
BEGIN
    DECLARE done INT DEFAULT 0;
    DECLARE cur_user VARCHAR(128);
    DECLARE cur_host VARCHAR(128);
    DECLARE user_cur CURSOR FOR 
        SELECT user, host FROM mysql.user 
        WHERE user NOT IN ('mysql.infoschema','mysql.session','mysql.sys');
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

    TRUNCATE TABLE user_permissions;

    OPEN user_cur;
    read_loop: LOOP
        FETCH user_cur INTO cur_user, cur_host;
        IF done = 1 THEN
            LEAVE read_loop;
        END IF;
        SET @grant_sql = CONCAT(
            "INSERT INTO user_permissions(username, permission_text) ",
            "SELECT CONCAT('",cur_user,"'@'",cur_host,"'), GROUP_CONCAT(`Grants for ",QUOTE(CONCAT(cur_user,'@',cur_host)),"` SEPARATOR '\n') ",
            "FROM (SHOW GRANTS FOR '",cur_user,"'@'",cur_host,"') AS t"
        );
        PREPARE stmt FROM @grant_sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
    END LOOP;
    CLOSE user_cur;
END //
DELIMITER ;

-- 执行存储过程完成数据采集
CALL collect_all_user_grants();
-- 采集完成后可删除存储过程
DROP PROCEDURE IF EXISTS collect_all_user_grants;

• 如果你要复用之前导出到/var/lib/mysql-files/show_grants.txt的文件，可以在服务器命令行执行sed命令，把导出的SHOW GRANTS语句批量转换为插入语句：

sed "s/SHOW GRANTS FOR '\(.*\)'@'\(.*\)';/INSERT INTO user_permissions(username, permission_text) SELECT CONCAT('\1'@'\2'), GROUP_CONCAT(`Grants for '\1@\2'` SEPARATOR '\\n') FROM (SHOW GRANTS FOR '\1'@'\2') AS t;/g" /var/lib/mysql-files/show_grants.txt > /var/lib/mysql-files/insert_grants.sql

转换完成后登录MySQL，执行source /var/lib/mysql-files/insert_grants.sql;即可把所有权限数据写入存储表，执行完成后可以用SELECT * FROM user_permissions;验证结果。

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