MySQL GRANT 權限授予實戰指南：從入門到進階
基於 MySQL 5.0+ 實測，系統整理 GRANT 各層級授權語法、典型角色配置、跨權限風險與最佳實踐。
一、快速授權範本（按角色）
1. 唯讀帳號（報表、分析師）
GRANT SELECT ON `testdb`.* TO 'readonly'@'%' IDENTIFIED BY 'pass';
FLUSH PRIVILEGES;
2. 應用寫入帳號（CRUD）
GRANT SELECT, INSERT, UPDATE, DELETE ON `testdb`.* TO 'app_user'@'%' IDENTIFIED BY 'pass';
FLUSH PRIVILEGES;
3. 開發人員（DDL + DML）
GRANT CREATE, ALTER, DROP, INDEX, CREATE TEMPORARY TABLES,
CREATE VIEW, SHOW VIEW,
CREATE ROUTINE, ALTER ROUTINE, EXECUTE,
SELECT, INSERT, UPDATE, DELETE, REFERENCES
ON `testdb`.* TO 'dev'@'192.168.0.%' IDENTIFIED BY 'pass';
FLUSH PRIVILEGES;
4. 單庫 DBA
GRANT ALL PRIVILEGES ON `testdb`.* TO 'dba'@'localhost' IDENTIFIED BY 'pass';
FLUSH PRIVILEGES;
5. 超級 DBA（全權限）
GRANT ALL ON *.* TO 'root'@'localhost' IDENTIFIED BY 'pass' WITH GRANT OPTION;
FLUSH PRIVILEGES;
二、五大權限層級授權語法
| 層級 | 語法範例 | 說明 |
|---|
| 全域 | GRANT SELECT ON *.* TO 'u'@'%'; | 所有資料庫所有表 |
| 資料庫 | GRANT SELECT ON `testdb`.* TO 'u'@'%'; | 指定庫所有表 |
| 表 | GRANT SELECT, INSERT ON `testdb`.`orders` TO 'u'@'%'; | 指定表所有欄位 |
| 欄位 | GRANT SELECT(`id`, `name`, `email`) ON `testdb`.`users` TO 'u'@'%'; | 指定表指定欄位 |
| 程序 | GRANT EXECUTE ON PROCEDURE `testdb`.`proc1` TO 'u'@'%';
GRANT EXECUTE ON FUNCTION `testdb`.`func1` TO 'u'@'%'; | 指定預存程序/函數 |
三、查看權限
-- 當前用戶
SHOW GRANTS;
-- 指定用戶
SHOW GRANTS FOR 'dba'@'localhost';
四、撤銷權限 (REVOKE)
-- 撤銷特定權限
REVOKE INSERT ON `testdb`.* FROM 'app_user'@'%';
-- 撤銷所有權限（含 GRANT OPTION）
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'dba'@'localhost';
-- 注意：REVOKE 不刪除用戶帳號，需額外執行
DROP USER 'user'@'host';
-- 或
DELETE FROM mysql.user WHERE User='user' AND Host='host';
FLUSH PRIVILEGES;
五、關鍵注意事項
- 權限生效需重連：GRANT/REVOKE 後，該用戶必須重新連線才生效。
- WITH GRANT OPTION 慎用：允許受授權者將權限轉授他人。
GRANT SELECT ON db.* TO 'u'@'%' WITH GRANT OPTION; 實務中權限應由 DBA 統一管理。
- 通配符資料庫名轉義：資料庫名含
_ 或 % 時需轉義：GRANT ... ON \`foo\_bar\`.* TO ...
- 用戶名/主機名引號規則：含特殊字元（-、%、_）需單引號：
'test-user'@'%.example.com'
- 匿名用戶風險：
''@'localhost' 會攔截本地具名用戶登入。建議刪除：DELETE FROM mysql.user WHERE User=''; FLUSH PRIVILEGES;
- 資源限制：
GRANT USAGE ON *.* TO 'u'@'%'
WITH MAX_QUERIES_PER_HOUR 1000
MAX_UPDATES_PER_HOUR 500
MAX_CONNECTIONS_PER_HOUR 100
MAX_USER_CONNECTIONS 10;
- SSL/X509 強制加密：
GRANT ALL ON `testdb`.* TO 'secure_user'@'%'
IDENTIFIED BY 'pass'
REQUIRE SSL; -- 或 X509, ISSUER, SUBJECT, CIPHER 組合
- 升級授權表：舊版升級後需執行
mysql_upgrade 才能使用新權限類型（CREATE ROUTINE 等）。
六、權限儲存表對應
| 層級 | 儲存表 |
|---|
| 全域 | mysql.user |
| 資料庫 | mysql.db, mysql.host |
| 表 | mysql.tables_priv |
| 欄位 | mysql.columns_priv |
| 程序 | mysql.procs_priv |
七、實戰檢查清單
- [ ] 應用帳號是否只有必要庫/表的 CRUD 權限？
- [ ] 是否禁用匿名用戶？
- [ ] 本地帳號是否優先用
localhost（Unix socket）？
- [ ] 是否定期
SHOW GRANTS 審計？
- [ ] 生產環境是否啟用 SSL 強制加密？
- [ ] 備份是否包含授權表？
