MySQL 使用者帳號與權限管理權威指南：GRANT/REVOKE 深度解析與實戰範例

權限系統架構

GRANT/REVOKE 操作四張授權表：user（全域/連線）、db（資料庫級）、tables_priv（資料表級）、columns_priv（欄位級）。另有 host 表不受 GRANT 影響。

GRANT 語法結構

GRANT privileges (columns)
ON what
TO user IDENTIFIED BY "password"
WITH GRANT OPTION

權限分類完整表

實戰授權範例集

1. 限制連線來源

• 單一主機：GRANT ALL ON db.* TO 'user'@'localhost' IDENTIFIED BY 'pwd';
• 任意主機（不建議）：GRANT ALL ON db.* TO 'user'@'%' IDENTIFIED BY 'pwd';
• 網域通配：GRANT ALL ON db.* TO 'user'@'%.example.com' IDENTIFIED BY 'pwd';
• IP 段：GRANT ALL ON db.* TO 'user'@'192.168.1.%' IDENTIFIED BY 'pwd';
• CIDR：GRANT ALL ON db.* TO 'user'@'192.168.1.0/255.255.255.0' IDENTIFIED BY 'pwd';

2. 建立超級管理員

GRANT ALL ON *.* TO 'admin'@'localhost' IDENTIFIED BY 'strong_pwd' WITH GRANT OPTION;

3. 僅授予管理權限（如日誌清理腳本帳號）

GRANT RELOAD ON *.* TO 'flush_user'@'localhost' IDENTIFIED BY 'pwd';

4. 資料庫級唯讀帳號

GRANT SELECT ON db_name.* TO 'readonly'@'%' IDENTIFIED BY 'pwd';

5. 精確到欄位的更新權限

GRANT SELECT ON db.member TO 'user'@'localhost' IDENTIFIED BY 'pwd';
GRANT UPDATE (expiration) ON db.member TO 'user'@'localhost';

多欄位：GRANT UPDATE (street,city,zip) ON db.member TO 'user'@'localhost';

6. 臨時資料庫隔離技巧

GRANT ALL ON tmp.* TO ''@'internal.net';

允許內網任何用戶使用 tmp 庫建臨時表，不影響核心業務權限。

REVOKE 撤銷權限

REVOKE UPDATE ON db.member FROM 'user'@'localhost';
REVOKE ALL ON *.* FROM 'user'@'%';

刪除使用者

DROP USER 'user'@'host';  -- MySQL 5.0.2+
-- 或
DELETE FROM mysql.user WHERE User='user' AND Host='host';
FLUSH PRIVILEGES;

輔助工具

• mysqlaccess：命令列權限檢查工具
• mysql_setpermission：Perl 互動式授權腳本（需 DBI）

最佳實踐原則

1. 最小權限：僅授予業務必要權限
2. 分離帳號：應用、備份、監控、管理分離
3. 限制主機：避免 @'%'，優先 @'localhost' 或具體 IP
4. 定期稽核：SHOW GRANTS FOR 'user'@'host';
5. 密碼策略：強制複雜度、定期輪換、禁止預設帳號