审计高风险账户：空密码、'%' 主机、SUPER、mysql_native_password

命令

SELECT user, host, plugin,
       CONCAT_WS(', ',
         IF(authentication_string = '' AND account_locked = 'N', 'EMPTY PASSWORD', NULL),
         IF(host = '%', 'ANY HOST', NULL),
         IF(plugin = 'mysql_native_password', 'NATIVE AUTH', NULL),
         IF(Super_priv = 'Y', 'SUPER', NULL),
         IF(Grant_priv = 'Y', 'GRANT OPTION', NULL),
         IF(File_priv = 'Y', 'FILE', NULL),
         IF(ssl_type = '', 'NO TLS REQUIRED', NULL),
         IF(password_expired = 'Y', 'EXPIRED', NULL),
         IF(account_locked = 'Y', 'LOCKED', NULL)) AS findings,
       password_last_changed
FROM mysql.user
WHERE user NOT IN ('mysql.sys', 'mysql.session', 'mysql.infoschema')
ORDER BY (Super_priv = 'Y') DESC, user;

-- Accounts holding powerful dynamic privileges
SELECT GRANTEE, PRIVILEGE_TYPE FROM information_schema.USER_PRIVILEGES
WHERE PRIVILEGE_TYPE IN ('SUPER', 'SYSTEM_VARIABLES_ADMIN', 'CONNECTION_ADMIN', 'BINLOG_ADMIN', 'REPLICATION_SLAVE_ADMIN')
ORDER BY GRANTEE;

作用

按账户在一行中列出安全发现：无密码、可从任意位置连接、遗留认证插件、SUPER / GRANT / FILE 权限以及未要求 TLS；第二个查询显示危险的动态权限。

适用场景

定期安全审计、新接手服务器时以及升级到 8.4 之前。

注意

该查询直接读取 mysql.user，需要对其有 SELECT 权限。角色账户已锁定且无密码，因此只对未锁定账户报告 EMPTY PASSWORD。Super_priv 是静态 SUPER 权限；由于 8.0 将其拆分为动态权限，请同时检查第二个查询。标记为 NATIVE AUTH 的账户须在 8.4 之前迁移到 caching_sha2_password：ALTER USER 'u'@'h' IDENTIFIED WITH caching_sha2_password BY '...'；旧客户端/连接器可能不支持 caching_sha2_password，请先测试。