MySQL GRANT 授權與 REVOKE 回收權限完整指南

MySQL 權限系統以 GRANT 授權、REVOKE 回收為核心。本文系統整理授權語法、權限層級、密碼管理、WITH GRANT OPTION 傳遞權限、撤銷權限與徹底刪除使用者的完整操作指南。

一、GRANT 授權語法總覽

GRANT privileges ON db.tbl TO 'user'@'host' [IDENTIFIED BY 'pass'] [WITH GRANT OPTION] [resource_limits];

1. 常用權限關鍵字

2. 授權範例

-- 全庫全表唯讀
GRANT SELECT ON *.* TO 'readonly'@'%' IDENTIFIED BY 'pass';

-- 特定庫讀寫
GRANT SELECT, INSERT, UPDATE, DELETE ON mydb.* TO 'app'@'10.0.0.%' IDENTIFIED BY 'pass';

-- 全權限且可再授權
GRANT ALL PRIVILEGES ON *.* TO 'admin'@'%' IDENTIFIED BY 'pass' WITH GRANT OPTION;

-- 僅管理權限（無數據權限）
GRANT RELOAD, PROCESS ON *.* TO 'monitor'@'localhost';

二、密碼管理

1. 設定/修改密碼

-- 指定用戶（管理員）
SET PASSWORD FOR 'user'@'host' = PASSWORD('newpass');

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

-- GRANT 方式（不改權限只改密碼）
GRANT USAGE ON *.* TO 'user'@'host' IDENTIFIED BY 'newpass';

2. MySQL 5.7+ 推薦：ALTER USER

ALTER USER 'user'@'host' IDENTIFIED BY 'newpass';

三、WITH GRANT OPTION：權限傳遞

擁有 WITH GRANT OPTION 的用戶，可將自己擁有的權限授予他人。極度謹慎授予，建議僅限 DBA 帳號。

四、REVOKE 回收權限

REVOKE privileges ON db.tbl FROM 'user'@'host';

關鍵差異

• REVOKE 僅移除權限，**不刪除使用者帳號**（mysql.user 記錄保留）。
• 使用者仍可連線，但無任何操作權限。

範例

REVOKE INSERT, UPDATE ON mydb.* FROM 'app'@'%';
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'admin'@'%';

五、徹底刪除使用者

MySQL 5.7+（推薦）

DROP USER 'user'@'host';

舊版本手動清理

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

六、權限生效機制

• GRANT/REVOKE：即時生效，自動 FLUSH PRIVILEGES。
• 直接操作 mysql.user/db/tables_priv/columns_priv/procs_priv 表：需手動 FLUSH PRIVILEGES。
• 已建立的連線：**不受影響**，需重連或執行 FLUSH PRIVILEGES 後新連線生效。

七、最佳實踐清單

1. 最小權限原則：業務帳號僅授 SELECT/INSERT/UPDATE/DELETE。
2. Host 限制具體 IP 段：'app'@'10.0.1.%' 優於 'app'@'%'。
3. 定期審計：SELECT User, Host FROM mysql.user WHERE User NOT IN ('root','mysql.sys','mysql.session');
4. 禁用 root 遠程：DELETE FROM mysql.user WHERE User='root' AND Host='%';
5. 啟用 validate_password 強制密碼複雜度。

權限管理是資料庫安全的核心，建立標準化授權/回收/刪除 SOP，並納入變更管理流程，才能守住資料最後一道防線。