MySQL 使用者帳號、密碼與權限的三元組綁定機制：避開「建立用戶無法登入」陷阱

MySQL 使用者帳號、密碼與權限的三元組綁定機制：避開「建立用戶無法登入」陷阱

MySQL 使用者身份由 User + Host + Password + Privileges 四元組唯一確定，而非單純的用戶名。本文揭示分步建立用戶再授權導致無法登入的根因，並給出正確姿勢。

錯誤示範

-- 步驟 1：建立用戶（指定密碼）
CREATE USER 'app'@'localhost' IDENTIFIED BY 'secret123';

-- 步驟 2：授權（未指定密碼）
GRANT ALL ON `mydb`.* TO 'app'@'localhost';

-- 結果：用 app/secret123 登入失敗！
-- ERROR 1045 (28000): Access denied for user 'app'@'localhost'

根因分析

MySQL mysql.user 表主鍵為 (Host, User)，但**認證資訊（Password）與權限是分開儲存**的：

• CREATE USER 在 mysql.user 插入一行，寫入 User、Host、authentication_string（密碼雜湊）。
• GRANT ... TO 'user'@'host' **若不帶 IDENTIFIED BY**，會在權限表 (mysql.db, mysql.tables_priv 等) 寫入權限記錄，**但不更新 mysql.user 的密碼欄位**。
• 若該用戶在 mysql.user 已存在，**再次 GRANT 不帶密碼不會報錯，也不會更新密碼**，導致權限表有記錄但認證表密碼為舊值或空，登入失敗。

正確姿勢：一步到位

-- 建議：GRANT 同時建立用戶、設密碼、授權
GRANT ALL ON `mydb`.* TO 'app'@'localhost' IDENTIFIED BY 'secret123';
-- MySQL 5.7+ 等價於：
-- CREATE USER 'app'@'localhost' IDENTIFIED BY 'secret123';
-- GRANT ALL ON `mydb`.* TO 'app'@'localhost';
-- FLUSH PRIVILEGES;

-- 8.0+ 語法分離（推薦）
CREATE USER 'app'@'localhost' IDENTIFIED BY 'secret123';
GRANT ALL ON `mydb`.* TO 'app'@'localhost';

驗證用戶實際狀態

-- 查看認證資訊
SELECT `User`, `Host`, `plugin`, `authentication_string` 
FROM `mysql`.`user` WHERE `User` = 'app';

-- 查看權限
SHOW GRANTS FOR 'app'@'localhost';

-- 實際登入測試
mysql -u app -p'secret123' -h 127.0.0.1 -P 3306

Host 匹配陷阱補充

💡 **最佳實踐**：為同一應用建立三組帳號 'app'@'localhost'、'app'@'127.0.0.1'、'app'@'%'（或僅必要網段），密碼一致，權限一致，覆蓋所有連線方式。

常見誤區對照