MySQL 使用者密碼設定與權限管理完整指南

核心原則：密碼必須加密儲存

MySQL mysql.user 表的 authentication_string（5.7 前為 password）欄位儲存的是 雜湊值，而非明文。直接 INSERT ... VALUES('plain_pwd') 會導致驗證失敗，因為伺服器比對的是雜湊值。

四種設定密碼的標準方式

1. CREATE USER ... IDENTIFIED BY（建立同時設定，推薦）

CREATE USER 'app_user'@'%' IDENTIFIED BY 'strong_password';
-- 8.0+ 預設 caching_sha2_password 插件，相容性需求可指定：
CREATE USER 'legacy_user'@'%' IDENTIFIED WITH mysql_native_password BY 'pwd';

2. ALTER USER ... IDENTIFIED BY（修改既有用戶，推薦）

ALTER USER 'app_user'@'%' IDENTIFIED BY 'new_strong_password';
-- 指定插件：
ALTER USER 'legacy_user'@'%' IDENTIFIED WITH mysql_native_password BY 'new_pwd';

3. SET PASSWORD（舊版相容，5.7+ 仍支援）

SET PASSWORD FOR 'app_user'@'%' = 'new_password';
-- 或當前用戶：
SET PASSWORD = 'new_password';

4. GRANT ... IDENTIFIED BY（授權同時設定/修改密碼）

GRANT SELECT, INSERT ON db_name.* TO 'app_user'@'%' IDENTIFIED BY 'password';
-- 8.0+ 已移除 GRANT 建立用戶功能，需先 CREATE USER 再 GRANT

5. 命令列工具 mysqladmin（腳本自動化適用）

mysqladmin -u root -p'old_root_pwd' password 'new_root_pwd'
# 修改其他用戶：
mysqladmin -u root -p'root_pwd' -u 'app_user' password 'new_pwd'

權限管理最佳實踐

最小權限原則

-- 僅授予必要權限於特定庫/表
GRANT SELECT, INSERT, UPDATE ON `biz_db`.`orders` TO 'app_rw'@'10.0.1.%';
GRANT SELECT ON `biz_db`.* TO 'app_ro'@'10.0.1.%';
GRANT PROCESS, REPLICATION CLIENT ON *.* TO 'monitor'@'10.0.2.%';

撤銷權限

REVOKE INSERT ON `biz_db`.`orders` FROM 'app_rw'@'10.0.1.%';
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'old_user'@'%';

查看權限

SHOW GRANTS FOR 'app_user'@'%';
SHOW GRANTS;  -- 當前用戶

權限生效

FLUSH PRIVILEGES;
-- 或
mysqladmin -u root -p flush-privileges
mysqladmin -u root -p reload

注意： 8.0+ 授權語句隱含提交，通常無需手動 FLUSH PRIVILEGES，但手動執行無副作用。

常見坑點與解決

安全加固清單

1. 刪除匿名用戶：DELETE FROM mysql.user WHERE User='';
2. 禁止 root 遠端登入：UPDATE mysql.user SET Host='localhost' WHERE User='root'; 或 RENAME USER 'root'@'%' TO 'root'@'localhost';
3. 移除測試資料庫：DROP DATABASE test; DELETE FROM mysql.db WHERE Db='test' OR Db='test\\_%';
4. 執行 mysql_secure_installation 互動式完成上述動作。
5. 啟用密碼驗證插件：INSTALL PLUGIN validate_password SONAME 'validate_password.so'; 強制複雜度。
6. 定期輪換密碼、審計權限、啟用 SSL 連線（REQUIRE SSL）。

總結

MySQL 密碼管理核心在於：永遠使用內建函數/語句（CREATE/ALTER/SET PASSWORD/GRANT）讓伺服器自動雜湊，絕不手寫雜湊值。配合最小權限、Host 限制、定期輪換、審計日誌，構建纵深防禦體系。