您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 資料庫相關知識

MySQL GRANT 與 REVOKE 權限管理完整語法與實戰指南

編輯：MySQL 資料庫相關知識

MySQL GRANT 與 REVOKE 權限管理完整語法與實戰指南

MySQL 權限系統採用分層架構：全域 → 資料庫 → 表 → 欄位 → 程序。本文詳細解析 GRANT 與 REVOKE 完整語法、權限層級、SSL/X509 安全連線要求、資源限制與實戰最佳實踐。

一、GRANT 完整語法結構

GRANT priv_type [(column_list)] [, priv_type [(column_list)]] ...
  ON [object_type] {tbl_name | * | *.* | db_name.*}
  TO user [IDENTIFIED BY [PASSWORD] 'password']
      [, user [IDENTIFIED BY [PASSWORD] 'password']] ...
  [REQUIRE
      NONE |
      [{SSL | X509}]
      [CIPHER 'cipher' [AND]]
      [ISSUER 'issuer' [AND]]
      [SUBJECT 'subject']]
  [WITH with_option [with_option] ...]

with_option:
  GRANT OPTION
  | MAX_QUERIES_PER_HOUR count
  | MAX_UPDATES_PER_HOUR count
  | MAX_CONNECTIONS_PER_HOUR count
  | MAX_USER_CONNECTIONS count

object_type: TABLE | FUNCTION | PROCEDURE

二、REVOKE 完整語法結構

REVOKE priv_type [(column_list)] [, priv_type [(column_list)]] ...
  ON [object_type] {tbl_name | * | *.* | db_name.*}
  FROM user [, user] ...

REVOKE ALL PRIVILEGES, GRANT OPTION FROM user [, user] ...

核心差異：TO → FROM，無 IDENTIFIED BY、WITH GRANT OPTION、REQUIRE 子句。

三、五大權限層級與儲存位置

層級	適用範圍	儲存表	典型語法
全域	伺服器所有資料庫	`mysql.user`	`GRANT ALL ON . TO 'dba'@'%';`
資料庫	指定資料庫所有物件	`mysql.db`, `mysql.host`	`GRANT ALL ON mydb.* TO 'dev'@'%';`
表	指定表所有欄位	`mysql.tables_priv`	`GRANT SELECT,INSERT ON mydb.t1 TO 'app'@'%';`
欄位	指定表單一欄位	`mysql.columns_priv`	`GRANT SELECT(id,name) ON mydb.t1 TO 'app'@'%';`
程序	預存程序/函數	`mysql.procs_priv`	`GRANT EXECUTE ON PROCEDURE mydb.p1 TO 'app'@'%';`

四、常用權限類型對照表

權限	允許操作	層級
ALL [PRIVILEGES]	除 GRANT OPTION 外所有權限	所有
SELECT	查詢資料	全域/庫/表/欄位
INSERT	插入資料	全域/庫/表/欄位
UPDATE	修改資料	全域/庫/表/欄位
DELETE	刪除資料	全域/庫/表
CREATE	建立資料庫/表/索引	全域/庫/表
DROP	刪除資料庫/表/視圖	全域/庫/表
ALTER	修改表結構	全域/庫/表
INDEX	建立/刪除索引	全域/庫/表
EXECUTE	執行預存程序	全域/庫/程序
CREATE ROUTINE	建立預存程序/函數	全域/庫
ALTER ROUTINE	修改/刪除預存程序	全域/庫/程序
CREATE VIEW	建立視圖	全域/庫
SHOW VIEW	查看視圖定義	全域/庫
CREATE USER	建立/刪除/重命名用戶	全域
FILE	`SELECT ... INTO OUTFILE`, `LOAD DATA INFILE`	僅全域
PROCESS	`SHOW FULL PROCESSLIST`	僅全域
RELOAD	`FLUSH` 操作	僅全域
REPLICATION SLAVE	從庫讀取 Binlog	僅全域
REPLICATION CLIENT	查詢主/從位址	僅全域
SUPER	`CHANGE MASTER`, `KILL`, `PURGE MASTER LOGS`, `SET GLOBAL`	僅全域
SHUTDOWN	`mysqladmin shutdown`	僅全域
GRANT OPTION	將自身權限授予他人	全域/庫/表
USAGE	無實際權限（建立用戶用）	全域

五、用戶帳號命名規則

格式：'user_name'@'host_name'
主機名支援萬用字元：'%'（任意主機）、'192.168.1.%'（C 類網段）、'%.example.com'（網域）
用戶名不支援萬用字元
含特殊字元（-、%、_）需用單引號包裹：'test-user'@'%.example.com'
資料庫/表/欄位/程序名含特殊字元用反引號：\`db-name\`.\`tbl_name\`
密碼用單引號：IDENTIFIED BY 'pass_word'
匿名用戶：''@'localhost'（生產環境建議刪除）

六、SSL/X509 安全連線要求（REQUIRE 子句）

模式	語法範例	說明
無要求	`GRANT ... TO 'u'@'%';`	允許非加密；客戶端有憑證可選擇加密
要求 SSL	`GRANT ... TO 'u'@'%' REQUIRE SSL;`	僅允許 SSL 加密連線
要求 X509	`GRANT ... TO 'u'@'%' REQUIRE X509;`	需有效憑證（CA 可驗證簽名），不檢查具體簽發者/主體
指定簽發者	`REQUIRE ISSUER '/C=CN/ST=.../CN=CA'`	憑證必須由指定 CA 簽發
指定主體	`REQUIRE SUBJECT '/C=CN/.../CN=client'`	憑證主體必須完全匹配
指定加密套件	`REQUIRE CIPHER 'EDH-RSA-DES-CBC3-SHA'`	強制使用特定加密演算法
組合	`REQUIRE SUBJECT '...' AND ISSUER '...' AND CIPHER '...'`	多條件同時滿足

七、資源限制（WITH 子句）

MAX_QUERIES_PER_HOUR N：每小時最大查詢數
MAX_UPDATES_PER_HOUR N：每小時最大更新數
MAX_CONNECTIONS_PER_HOUR N：每小時最大連線數
MAX_USER_CONNECTIONS N：同一用戶同時連線上限（0=受 max_user_connections 系統變數控制）
修改現有用戶資源限制不影響權限：GRANT USAGE ON *.* TO 'u'@'%' WITH MAX_QUERIES_PER_HOUR 1000;

八、實戰最佳實踐

最小權限原則：應用帳號僅授予必要庫/表的 SELECT/INSERT/UPDATE/DELETE，嚴禁 GRANT ALL ON *.*
區分環境帳號：開發/測試/生產使用不同帳號，生產帳號僅限 DBA 與自動化部署系統持有
避免匿名用戶：DELETE FROM mysql.user WHERE User=''; FLUSH PRIVILEGES;
本地帳號優先：應用與資料庫同機時，用 'app'@'localhost' 配合 Unix socket，效能更高且避免網路暴露
定期審計：SHOW GRANTS FOR 'user'@'host'; 檢查權限是否過度
撤銷權限後需重連：權限變更即時生效於新連線，現有連線需重連或執行 FLUSH PRIVILEGES
備份授權表：遷移升級前備份 mysql.user, mysql.db, mysql.tables_priv, mysql.columns_priv, mysql.procs_priv