MySQL 權限系統深度解析：Host 匹配、權限刷新與常見坑

問題背景

MySQL 被 Oracle 收購後權限機制更精細。安裝後發現：root 僅能本地登入，遠程連線失敗；修改 host='%' 後遠程可連，但 localhost / 127.0.0.1 卻無法登入。根因在於 mysql.user 表中同一用戶存在多條 Host 不同的記錄，權限各不相同。

權限表結構核心欄位

mysql> DESC mysql.user;
+------------------------+-----------------------------------+------+-----+---------+-------+
| Field                  | Type                              | Null | Key | Default | Extra |
+------------------------+-----------------------------------+------+-----+---------+-------+
| Host                   | char(60)                          | NO   | PRI |         |       |
| User                   | char(32)                          | NO   | PRI |         |       |
| Password               | char(41)                          | NO   |     |         |       |
| Select_priv, Insert_priv... | enum('N','Y')                | NO   |     | N       |       |
| ... (其他權限欄位)               |                                 |      |     |         |       |
+------------------------+-----------------------------------+------+-----+---------+-------+

Host 匹配優先級規則

1. 具體 IP > 通配符 % > localhost > 127.0.0.1 (按字串排序，具體優先)。
2. 連線時 MySQL 依序匹配 Host，第一條匹配生效，不再繼續。
3. localhost 走 Unix Socket，127.0.0.1 走 TCP，屬不同 Host 記錄。

常見場景與解決

場景一：允許 root 遠程連線

-- 查看現有 root 記錄
SELECT Host, User, Password FROM mysql.user WHERE User='root';
+-----------+------+-------------------------------------------+
| Host      | User | Password                                  |
+-----------+------+-------------------------------------------+
| localhost | root | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B |
| 127.0.0.1 | root |                                           |
| %         | root | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B |
+-----------+------+-------------------------------------------+

-- 方案：統一授權所有 Host (生產環境建議限制具體 IP)
UPDATE mysql.user SET 
  Select_priv='Y', Insert_priv='Y', Update_priv='Y', Delete_priv='Y',
  Create_priv='Y', Drop_priv='Y', Reload_priv='Y', Shutdown_priv='Y',
  Process_priv='Y', File_priv='Y', Grant_priv='Y', References_priv='Y',
  Index_priv='Y', Alter_priv='Y', Show_db_priv='Y', Super_priv='Y',
  Create_tmp_table_priv='Y', Lock_tables_priv='Y', Execute_priv='Y',
  Repl_slave_priv='Y', Repl_client_priv='Y', Create_view_priv='Y',
  Show_view_priv='Y', Create_routine_priv='Y', Alter_routine_priv='Y',
  Create_user_priv='Y', Event_priv='Y', Trigger_priv='Y',
  Create_tablespace_priv='Y',
  Password=PASSWORD('StrongPass123!')
WHERE User='root';

FLUSH PRIVILEGES;

場景二：匿名用戶干擾登入

-- 刪除匿名用戶 (User='')
DELETE FROM mysql.user WHERE User='';
FLUSH PRIVILEGES;

場景三：密碼過期/插件不匹配 (MySQL 5.7+)

-- 修改認證插件為 mysql_native_password
ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'NewPass123!';
-- 或 8.0+
ALTER USER 'root'@'%' IDENTIFIED BY 'NewPass123!';

FLUSH PRIVILEGES;

權限生效機制

• FLUSH PRIVILEGES;：將 grant tables 內存快取刷新，使修改立即生效。
• 修改 my.cnf 的 skip-grant-tables 可跳過權限檢查 (緊急重置密碼用)，重啟後生效。

最佳實踐建議

1. 最小權限原則： 應用程式使用專用帳號，僅授予必要庫/表的 SELECT/INSERT/UPDATE/DELETE。
2. 避免使用 %： 生產環境限制具體應用伺服器 IP。
3. 定期審計： SELECT User, Host FROM mysql.user; 檢查異常帳號。
4. 使用角色 (8.0+)： CREATE ROLE 'app_read'; GRANT SELECT ON db.* TO 'app_read'; GRANT 'app_read' TO 'app_user'@'%';
5. 密碼策略： 啟用 validate_password 元件，強制複雜度、過期輪換。