MySQL 建立使用者、授權、密碼管理與權限回收完整指南

MySQL 建立使用者、授權、密碼管理與權限回收完整指南

本文系統整理 MySQL 使用者帳號全生命週期管理指令，包含建立、授權、密碼變更、權限回收、刪除帳號，並附上完整權限項目對照表，適合 DBA 速查與自動化腳本參考。

一、建立使用者

CREATE USER 'username'@'host' IDENTIFIED BY 'password';

• username：使用者名稱
• host：允許連線來源。localhost（本機）、192.168.1.101（特定 IP）、%（任意遠端）
• password：密碼，可為空（不建議）

範例

-- 本機帳號
CREATE USER 'dog'@'localhost' IDENTIFIED BY '123456';
-- 特定 IP
CREATE USER 'pig'@'192.168.1.101' IDENTIFIED BY '123456';
-- 任意遠端
CREATE USER 'pig'@'%' IDENTIFIED BY '123456';
-- 無密碼（不推薦）
CREATE USER 'pig'@'%' IDENTIFIED BY '';
CREATE USER 'pig'@'%';

二、授權

GRANT privileges ON databasename.tablename TO 'username'@'host';

• privileges：SELECT, INSERT, UPDATE, ALL 等
• databasename.tablename：*.* 代表所有庫所有表

範例

GRANT SELECT, INSERT ON test.user TO 'pig'@'%';
GRANT ALL ON *.* TO 'pig'@'%';

授予授權權限

GRANT privileges ON databasename.tablename TO 'username'@'host' WITH GRANT OPTION;

三、設定/變更密碼

SET PASSWORD FOR 'username'@'host' = PASSWORD('newpassword');
-- 當前登入使用者
SET PASSWORD = PASSWORD('newpassword');

範例

SET PASSWORD FOR 'pig'@'%' = PASSWORD('123456');

四、回收權限

REVOKE privilege ON databasename.tablename FROM 'username'@'host';

範例

REVOKE SELECT ON *.* FROM 'pig'@'%';

注意：授權粒度與回收粒度必須一致。若授權為 GRANT SELECT ON test.user，則 REVOKE SELECT ON *.* 無法移除該權限；需精確指定 REVOKE SELECT ON test.user。可用 SHOW GRANTS FOR 'pig'@'%'; 查看實際權限。

五、刪除使用者

DROP USER 'username'@'host';

六、MySQL 權限項目完整對照表

最佳實踐建議

• 遵循最小權限原則，業務帳號僅授予必要庫/表的 SELECT/INSERT/UPDATE/DELETE。
• 讀寫分離帳號分開（r_ 前綴唯讀、w_ 前綴讀寫）。
• 定期審計 mysql.user 與 information_schema.user_privileges，清理僵屍帳號。
• 生產環境禁用無密碼帳號、禁用 % 萬用字元帳號（改用具體 IP 或 VPN 網段）。