授權表生效機制:四大場景
| 場景 | 觸發條件 | 生效時機 | 操作建議 |
| 服務啟動 | mysqld 啟動 | 讀取授權表至記憶體,立即生效 | 確保授權表完整、權限正確 |
| 動態修改 | GRANT/REVOKE/SET PASSWORD | **即時生效**,伺服器主動偵測 | 首選方式,無需手動刷新 |
| 手動修改表 | INSERT/UPDATE/DELETE 授權表 | **不生效**,直到 FLUSH PRIVILEGES 或重啟 | 避免直接操作;必須操作時緊接 FLUSH PRIVILEGES |
| 現有連線影響 | 授權表變更後 | 表/欄權限:下次請求;庫權限:下次 USE db;全域/密碼:下次重連 | 關鍵權限變更建議強制斷開相關連線 (KILL) |
初始化安全基線 (全新安裝必做)
- 執行
mysql_secure_installation (5.7+ 內建腳本),自動完成:
- 設定 root 密碼
- 移除匿名帳號
- 禁止 root 遠端登入
- 移除 test 資料庫及其權限
- 重新載入授權表
- 手動驗證清單:
-- 1. 確認無匿名帳號
SELECT User, Host FROM mysql.user WHERE User='';
-- 2. 確認 root 僅限本地
SELECT User, Host FROM mysql.user WHERE User='root';
-- 3. 確認 test 資料庫權限已清理
SELECT * FROM mysql.db WHERE Db LIKE 'test%';
-- 4. 確認 root 密碼已設定 (authentication_string 非空)
SELECT User, Host, plugin, authentication_string FROM mysql.user WHERE User='root';
核心授權原則 (鐵律)
- 授權表修改權僅屬 root (或等效管理帳號):
GRANT OPTION、INSERT/UPDATE/DELETE ON mysql.* 嚴禁授予業務帳號。
- 最小權限原則:按業務角色建立專用帳號,僅授必要庫/表/欄位權限。
- 主機名精確授權:避免
%、192.168.%,使用具體 IP/CIDR;不信任 DNS 時直接用 IP。
- 強制 SSL 連線:
CREATE USER ... REQUIRE SSL / ALTER USER ... REQUIRE SSL。
- 密碼策略強制:啟用
validate_password 元件,長度≥12、混合字元、過期輪換、歷史記憶。
- 定期審計:巡檢
mysql.user、mysql.db、information_schema.USER_PRIVILEGES、APPLICABLE_ROLES (8.0+)。
常見誤區與修正
| 誤區 | 風險 | 正確做法 |
開發帳號授予 GRANT ALL ON *.* | 可刪除生產資料、讀取所有庫、授權他人 | 僅授開發庫 CREATE, DROP, ALTER, INDEX, SELECT, INSERT, UPDATE, DELETE, EXECUTE |
備份帳號授予 FILE 權限 | 可讀寫伺服器任意檔案 | 使用 mysqldump --single-transaction 或 xtrabackup,無需 FILE |
| 應用帳號使用 root | 應用漏洞 = 伺服器淪陷 | 每個應用獨立帳號,最小權限 |
| 密碼寫在配置檔/程式碼明文 | 原始碼洩漏 = 資料庫洩漏 | 使用 Vault/環境變數/加密配置中心 |
| 從不輪換密碼、不清理離職帳號 | 殭屍帳號成攻擊跳板 | 自動化輪換、離職即撤銷、定期 DROP USER |
MySQL 8.0 動態權限體系 (替代 SUPER)
8.0 將 SUPER 拆分為 12+ 細粒度動態權限,授權更精準:
SYSTEM_VARIABLES_ADMIN、CONNECTION_ADMIN、BINLOG_ADMIN、ROLE_ADMIN、SET_USER_ID、ENCRYPTION_KEY_ADMIN、FIREWALL_ADMIN、RESOURCE_GROUP_ADMIN、SERVICE_CONNECTION_ADMIN、SESSION_VARIABLES_ADMIN、BACKUP_ADMIN、PERSIST_RO_VARIABLES_ADMIN。
- 授權範例:
GRANT BACKUP_ADMIN, SYSTEM_VARIABLES_ADMIN ON *.* TO 'backup_user'@'10.0.1.%';
授權變更應急回滾
-- 1. 記錄變更前狀態
SELECT * FROM mysql.user INTO OUTFILE '/backup/user_before.csv';
SELECT * FROM mysql.db INTO OUTFILE '/backup/db_before.csv';
-- 2. 變更後發現異常,快速回滾
-- 停止服務,恢復備份檔案 (或從備份實例複製 mysql 資料庫目錄)
-- 修正權限擁有者,啟動服務
-- 或使用二進位日誌時間點恢復 (PITR) 至變更前