MySQL 用戶授權、密碼管理與權限回收完整指南

MySQL 用戶授權、密碼管理與權限回收完整指南

MySQL 權限系統基於 Grant 表，支援細粒度的庫、表、欄位級授權。本文整理常用授權、改密、撤銷、刪除用戶的標準語法與最佳實踐。

一、創建用戶並授權

1. 標準授權語法

GRANT privileges ON db_name.tbl_name TO 'user'@'host' [IDENTIFIED BY 'pass'] [WITH GRANT OPTION];

• privileges：SELECT, INSERT, UPDATE, DELETE 等，或 ALL PRIVILEGES。
• db_name.tbl_name：*.* 表示所有庫所有表。
• WITH GRANT OPTION：允許該用戶再授權給他人。

2. 典型範例

-- 超級管理員（僅本機）
GRANT ALL PRIVILEGES ON *.* TO 'monty'@'localhost' IDENTIFIED BY 'secret' WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON *.* TO 'monty'@'%' IDENTIFIED BY 'secret' WITH GRANT OPTION;

-- 僅 RELOAD、PROCESS 權限
GRANT RELOAD, PROCESS ON *.* TO 'admin'@'localhost';

-- 僅登入權限（USAGE = 無實質權限）
GRANT USAGE ON *.* TO 'dummy'@'localhost';

二、修改密碼

-- 指定用戶
SET PASSWORD FOR 'pig'@'%' = PASSWORD('123456');

-- 當前登入用戶
SET PASSWORD = PASSWORD('newpass');

MySQL 5.7.6+ 建議使用 ALTER USER 'user'@'host' IDENTIFIED BY 'newpass';

三、撤銷權限

REVOKE privileges ON db_name.tbl_name FROM 'user'@'host';

• 權限部分不需與原 GRANT 完全一致，可僅撤銷部分權限。
• REVOKE 不會刪除用戶帳號，mysql.user 記錄保留，用戶仍可連線（但無任何權限）。

四、徹底刪除用戶

DELETE FROM mysql.user WHERE User='user_name' AND Host='host_name';
FLUSH PRIVILEGES;

或 MySQL 5.7.6+：DROP USER 'user'@'host';

五、權限生效機制

• GRANT/REVOKE 執行後自動 FLUSH PRIVILEGES。
• 直接操作 mysql.user/db/tables_priv/columns_priv/procs_priv 表後，必須手動執行 FLUSH PRIVILEGES。

六、常見誤區與建議

1. 避免使用 '%' 主機通配：生產環境限制具體 IP 或子網，減少暴露面。
2. 最小權限原則：業務帳號僅授予必要的 DML 權限，DDL、管理權限隔離給運維帳號。
3. 定期審計：SELECT User, Host, authentication_string FROM mysql.user; 檢查空密碼、過期帳號。
4. 密碼策略：啟用 validate_password 插件強制複雜度、過期輪換。