MySQL 權限系統深度解析：五張授權表、驗證流程與最佳實踐

MySQL 權限系統基於 mysql 系統庫中的五張授權表，實現連接驗證與請求授權的兩階段控制。理解其結構與邏輯，是解決「連不上、查不了、改不了」權限疑難雜癥的鑰匙。

一、五張授權表結構速覽

二、驗證與授權兩階段流程

1. 階段一：連接驗證（僅查 user 表）
   • 匹配 Host、User、Password（或插件驗證）
   • 通過 → 獲得 user 表中全局權限作為「基線權限」
   • 失敗 → Access denied
2. 階段二：請求授權（按順序查 db → host → tables_priv → columns_priv）
   • 每張表按「最具體匹配優先」原則（Host 具體 > 通配符 %，User 具體 > 空）
   • 權限 = 基線權限 OR 各層級授權權限（聯集）
   • 任一層級滿足 → 允許；全不滿足 → 拒絕

三、關鍵安全原則

1. 全局權限極度收緊

user 表授予的權限作用於 所有庫所有表，含 mysql 系統庫。除 root 外，**嚴禁** 在 user 表授予任何 Y。新建用戶默認全 N，登錄後「什麼也幹不了」，極其安全。

2. 密碼存儲與修改

Password / authentication_string 存儲的是 PASSWORD() / 插件加密後的雜湊值，**無逆運算**。手工修改必須使用加密函數：

-- 5.7 前
UPDATE user SET Password=PASSWORD('newpass') WHERE User='app' AND Host='%';

-- 5.7+ / 8.0
ALTER USER 'app'@'%' IDENTIFIED BY 'newpass';  -- 推薦，自動處理插件與刷新

3. Host 欄位精確控制

• root@localhost 僅允許本地 Unix Socket 連接
• root@192.168.1.% 僅允許特定網段
• 嚴禁 root@%（任意主機）

4. 手工修改授權表後必須刷新

FLUSH PRIVILEGES;

GRANT/REVOKE 會自動刷新，無需手動執行。

四、常用授權操作模板

創建應用賬號（推薦 GRANT 語法）

CREATE USER 'app_user'@'10.0.0.%' IDENTIFIED BY 'StrongPass123!';
GRANT SELECT, INSERT, UPDATE, DELETE, EXECUTE ON `biz_db`.* TO 'app_user'@'10.0.0.%';
-- 不授 GRANT OPTION、DROP、ALTER、CREATE、FILE、SHUTDOWN、PROCESS

只讀報表賬號

CREATE USER 'ro_report'@'10.0.1.%' IDENTIFIED BY 'ReadOnlyPass!';
GRANT SELECT ON `biz_db`.* TO 'ro_report'@'10.0.1.%';
GRANT SELECT ON `mysql`.`help_*` TO 'ro_report'@'10.0.1.%';  -- 如需查幫助

撤銷權限並刪除用戶

REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'old_user'@'%';
DROP USER 'old_user'@'%';

五、排查「連不上」的標準清單

1. 確認用戶名/密碼正確（含大小寫）
2. 確認客戶端 IP 匹配 user.Host（通配符 %、具體 IP、CIDR 192.168.1.0/255.255.255.0）
3. 確認 user.Password / authentication_string 非空、插件匹配（caching_sha2_password vs mysql_native_password）
4. 確認 FLUSH PRIVILEGES 已執行（手工改表後）
5. 確認網絡通達：telnet host 3306、防火牆、bind-address、skip-networking
6. 查看錯誤日誌：tail -f /var/log/mysql/error.log

權限系統看似複雜，實則邏輯嚴密。掌握「兩階段、五張表、聯集授權、最小權限」核心模型，即可從容應對所有權限場景。