MySQL 用戶管理全生命周期：創建、授權、認證、審計

MySQL 用戶管理全生命周期：創建、授權、認證、審計

「人人用 root」是最大安全隱患。本文構建 MySQL 用戶管理標準化體系，覆蓋創建、分級授權、密碼策略、認證插件、定期審計。

核心原則

1. 最小權限：按業務角色授權，拒絕 GRANT ALL ON *.*
2. 賬號隔離：應用、運維、開發、備份、監控分賬號
3. 主機限定：user@'10.0.1.%' 優於 user@'%'
4. 強認證：強密碼 + 8.0 默認 caching_sha2_password
5. 定期輪換：密碼 90 天輪換，離職即撤銷

用戶創建與認證（MySQL 8.0 语法）

-- 1. 創建應用讀寫賬號（指定認證插件兼容舊驅動）
CREATE USER 'app_rw'@'10.0.1.%' IDENTIFIED WITH mysql_native_password BY 'AppRwPassw0rd!';

-- 2. 創建只讀賬號
CREATE USER 'app_ro'@'10.0.1.%' IDENTIFIED BY 'AppRoPassw0rd!';  -- 8.0 默認 caching_sha2_password

-- 3. 創建備份賬號
CREATE USER 'backup'@'10.0.1.50' IDENTIFIED BY 'BackupPassw0rd!';

-- 4. 創建監控賬號
CREATE USER 'monitor'@'10.0.1.100' IDENTIFIED BY 'MonitorPassw0rd!' REQUIRE SSL;

分級授權模板

必須執行：FLUSH PRIVILEGES;

密碼管理

-- 修改密碼（三種等價方式）
ALTER USER 'app_rw'@'10.0.1.%' IDENTIFIED BY 'NewPassw0rd!';
SET PASSWORD FOR 'app_rw'@'10.0.1.%' = 'NewPassw0rd!';
-- 5.7 兼容
UPDATE mysql.user SET authentication_string = PASSWORD('NewPassw0rd!') WHERE user='app_rw' AND host='10.0.1.%'; FLUSH PRIVILEGES;

-- 密碼過期策略（8.0）
ALTER USER 'app_rw'@'10.0.1.%' PASSWORD EXPIRE INTERVAL 90 DAY;
ALTER USER 'app_rw'@'10.0.1.%' FAILED_LOGIN_ATTEMPTS 5 PASSWORD_LOCK_TIME 2;

權限查看與審計

-- 查看指定用戶權限
SHOW GRANTS FOR 'app_rw'@'10.0.1.%';

-- 查看所有用戶（核心字段）
SELECT user, host, authentication_string, plugin, password_expired, password_last_changed, account_locked
FROM mysql.user
ORDER BY user, host;

-- 審計腳本：檢查高風險配置
SELECT user, host FROM mysql.user WHERE user = 'root' AND host != 'localhost';
SELECT user, host FROM mysql.user WHERE authentication_string = '' OR authentication_string IS NULL;
SELECT user, host FROM mysql.user WHERE host = '%' AND user NOT IN ('monitor');

常見操作速查

8.0 認證插件兼容性處理

• 新應用：使用默認 caching_sha2_password，升級驅動（JDBC 8.0+、Connector/NET 8.0+、PHP mysqlnd 7.4+）
• 舊應用：創建用戶指定 mysql_native_password
• 遷移期：ALTER USER 'legacy'@'%' IDENTIFIED WITH mysql_native_password BY 'pwd';

自動化審計腳本（每日跑批）

#!/bin/bash
# audit_mysql_users.sh
mysql -h localhost -u auditor -p'AuditPass!' -e "
SELECT 'ROOT_REMOTE' AS check_type, user, host FROM mysql.user WHERE user='root' AND host!='localhost'
UNION ALL
SELECT 'EMPTY_PASS', user, host FROM mysql.user WHERE authentication_string='' OR authentication_string IS NULL
UNION ALL
SELECT 'WILDCARD_HOST', user, host FROM mysql.user WHERE host='%' AND user NOT IN ('monitor')
UNION ALL
SELECT 'NO_EXPIRE', user, host FROM mysql.user WHERE password_expired='N' AND user NOT IN ('mysql.sys','mysql.session');
" | mail -s "MySQL User Audit $(date +%F)" dba@company.com

將用戶管理納入基線檢查，從源頭消除「弱口令、過度授權、僵屍賬號」隱患。