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

MySQL 5 權限管理實戰：GRANT/REVOKE 語法全解與最小權限授權模板

編輯：MySQL 資料庫相關知識

MySQL 權限系統基於「最小權限原則」，透過 GRANT 授予、REVOKE 撤銷權限。本文系統整理五大角色權限模板（唯讀、開發、DBA、超級管理員、應用帳號）、多層級授權語法、查看與撤銷操作、生產環境最佳實踐，幫助 DBA 快速建立合規的權限體系。

GRANT 基本語法


GRANT priv_type [(columns)] ON object TO user [IDENTIFIED BY 'pass'] [WITH GRANT OPTION] [resource_options];

priv_type：SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALTER, INDEX, EXECUTE 等
object：*.* (全域), db.* (庫), db.tbl (表), db.tbl(col) (欄位)
user：'user'@'host' 格式，% 代表任意主機

五大角色權限模板

1. 唯讀帳號（報表、備份、只讀 API）


GRANT SELECT ON prod_db.* TO 'readonly'@'10.0.1.%' IDENTIFIED BY 'ro_pass';
-- 僅允許內網報表伺服器 IP 段連線

2. 開發人員（結構變更 + DML，無 DROP DATABASE/用戶管理）


GRANT SELECT, INSERT, UPDATE, DELETE,
      CREATE, DROP, ALTER, INDEX,
      CREATE VIEW, SHOW VIEW,
      CREATE ROUTINE, ALTER ROUTINE, EXECUTE,
      CREATE TEMPORARY TABLES,
      LOCK TABLES
ON dev_db.* TO 'dev'@'192.168.1.%' IDENTIFIED BY 'dev_pass';

-- 參考權限（外鍵需求）
GRANT REFERENCES ON dev_db.* TO 'dev'@'192.168.1.%';

3. 應用帳號（僅業務必要 DML，無 DDL）


GRANT SELECT, INSERT, UPDATE, DELETE, EXECUTE
ON app_db.* TO 'app_user'@'10.0.2.%' IDENTIFIED BY 'app_strong_pass'
WITH MAX_QUERIES_PER_HOUR 5000
     MAX_UPDATES_PER_HOUR 1000
     MAX_CONNECTIONS_PER_HOUR 200
     MAX_USER_CONNECTIONS 50;

4. 庫級 DBA（管理特定庫，無跨庫權限）


GRANT ALL PRIVILEGES ON prod_db.* TO 'dba_prod'@'10.0.0.10' IDENTIFIED BY 'dba_pass';
-- 可建表、改結構、管理權限、檢視進程，但無法 SHUTDOWN/FILE/REPLICATION

5. 超級管理員（全域所有權限，極少數人持有）


GRANT ALL ON *.* TO 'root'@'localhost' IDENTIFIED BY 'super_strong_pass' WITH GRANT OPTION;
-- 僅限 localhost，禁止遠程 root

權限作用層級對照表

層級	ON 子句	儲存表	典型場景
全域	`ON .`	`mysql.user`	超管、備份帳號、監控帳號
資料庫	`ON db.*`	`mysql.db`, `host`	應用帳號、開發、庫級 DBA
資料表	`ON db.tbl`	`mysql.tables_priv`	敏感表隔離、跨部門共享
欄位	`ON db.tbl (col1,col2)`	`mysql.columns_priv`	薪資、身分證等敏感欄位
程序/函數	`ON PROCEDURE db.proc`	`mysql.procs_priv`	API 僅執行特定預存程序

查看權限


-- 當前用戶
SHOW GRANTS;

-- 指定用戶
SHOW GRANTS FOR 'app_user'@'10.0.2.%';

撤銷權限 (REVOKE)


-- 撤銷特定權限
REVOKE INSERT, UPDATE ON app_db.* FROM 'app_user'@'10.0.2.%';

-- 撤銷所有權限（不刪除帳號）
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'dev'@'192.168.1.%';

-- 徹底刪除帳號
DROP USER 'old_user'@'%';
-- 或
DELETE FROM mysql.user WHERE User='old_user' AND Host='%';
FLUSH PRIVILEGES;

生產環境最佳實踐

禁止遠程 root：DELETE FROM mysql.user WHERE User='root' AND Host='%';
專用帳號專用權限：備份用備份帳號、監控用監控帳號、應用用應用帳號
IP 白名單：Host 欄位限制為具體 IP/網段，避免 %
強密碼策略：啟用 validate_password 插件，強制複雜度與輪換
資源配額：應用帳號加 MAX_* 限制，防止失控查詢拖垮實例
SSL 強制：GRANT ... REQUIRE SSL; 加密傳輸
定期審計：pt-show-grants 匯出權限快照，納入版本控制
權限變更流程：工單申請 → 審批 → 變更 → 驗證 → 記錄

常見誤區

誤區	正確做法
`GRANT ALL ON .` 給開發	按環境分級：開發庫給 DDL+DML，生產庫僅給 DML
帳號共用	每人/每服務獨立帳號，便於審計與撤銷
密碼明文寫在腳本/代碼	使用 Vault/Secrets Manager，或 `mysql_config_editor` 加密存儲
`REVOKE` 後不重連	權限快取需新連線生效，或執行 `FLUSH PRIVILEGES`