MySQL 建立用戶與授權實戰：從 localhost 限制到 % 通配符開放

問題現場

應用嘗試用 mysql -h 127.0.0.1 -u app_user -p 連線失敗：

ERROR 1045: Access denied for user 'app_user'@'127.0.0.1'

但 mysql -h localhost -u app_user -p 卻成功。查 mysql.user 發現：

+-------------+-----------+
| Host        | User      |
+-------------+-----------+
| localhost   | app_user  |
| localhost   | root      |
+-------------+-----------+

原因： MySQL 將 localhost（Unix socket）與 127.0.0.1（TCP/IP）視為不同 Host，權限獨立。

解決方案：授權給 % 或具體 IP

-- 登入 root
mysql -u root -p

-- 授權給所有 IP（開發/測試環境）
GRANT ALL PRIVILEGES ON *.* TO 'app_user'@'%' IDENTIFIED BY 'StrongPass123!' WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'RootPass123!' WITH GRANT OPTION;

-- 生產環境建議：僅授權應用伺服器 IP
GRANT SELECT, INSERT, UPDATE, DELETE ON mydb.* TO 'app_user'@'192.168.1.10' IDENTIFIED BY 'StrongPass123!';

FLUSH PRIVILEGES;

驗證授權結果

SELECT Host, User FROM mysql.user WHERE User IN ('app_user', 'root');
+------+-----------+
| Host | User      |
+------+-----------+
| %    | app_user  |
| %    | root      |
| localhost | app_user |
| localhost | root      |
+------+-----------+

測試連線

# 現在 127.0.0.1 也能連了
mysql -h 127.0.0.1 -u app_user -p
mysql -h 192.168.1.10 -u app_user -p   # 遠程測試

舊版客戶端相容性問題

若使用舊版工具（MySQL-Front、舊 JDBC）報錯：

1251 - Client does not support authentication protocol requested by server

解法：將用戶改用 mysql_native_password 插件：

ALTER USER 'app_user'@'%' IDENTIFIED WITH mysql_native_password BY 'StrongPass123!';
FLUSH PRIVILEGES;

或升級客戶端驅動（JDBC 8.0+、Connector/Python 8.0+、MySQL Workbench 8.0+）。

關鍵知識點總結

1. Host 匹配優先序： 具體 IP > 域名 > % > localhost。同用戶多 Host 記錄共存，MySQL 按最精確匹配生效。
2. FLUSH PRIVILEGES 必執行： 直接操作 grant tables 才需手動刷新；GRANT/CREATE USER 會自動刷新。
3. WITH GRANT OPTION： 允許該用戶將自己的權限再授予他人，生產環境慎用。
4. 密碼策略： 8.0 預設 validate_password 元件強制複雜度，若需簡單密碼可 UNINSTALL COMPONENT 'file://component_validate_password';（不建議）。

總結

MySQL 權限的「Host」欄位是連線來源的精確匹配鍵。localhost ≠ 127.0.0.1 是新手最常踩的坑。解決之道：根據部署架構，給應用用戶授權 %（全網）或具體應用伺服器 IP，並執行 FLUSH PRIVILEGES。生產環境務必配合防火牆白名單、強密碼、SSL 加密，實現纵深防禦。