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

MySQL 用戶權限管理全攻略：GRANT/REVOKE 實戰、最小權限原則與安全加固清單

編輯：MySQL 資料庫相關知識

權限體系架構

MySQL 權限分層：

全局級： *.* — 管理權限 (SHUTDOWN, SUPER, FILE, REPLICATION 等)
庫級： db.* — 所有表對象權限
表級： db.tbl — 特定表權限
列級： db.tbl(col1,col2) — 特定列權限
程序級： PROCEDURE/FUNCTION db.proc_name — 存儲過程/函數執行權限

GRANT 語法全解析


GRANT priv_type [(column_list)] 
  ON {tbl_name | * | *.* | db_name.*}
  TO user_specification [, user_specification] ...
  [REQUIRE {NONE | ssl_option ...}]
  [WITH with_option ...]

user_specification:
  user [IDENTIFIED BY 'password']  -- 5.7+ 推薦分開 CREATE USER

with_option:
  GRANT OPTION          -- 允許將權限再授予他人
  MAX_QUERIES_PER_HOUR count
  MAX_UPDATES_PER_HOUR count
  MAX_CONNECTIONS_PER_HOUR count
  MAX_USER_CONNECTIONS count

實戰授權模板 (按角色分層)

1. 唯讀賬號 (報表/分析/監控)


CREATE USER 'readonly'@'%' IDENTIFIED BY 'ReadOnly#2024!';
GRANT SELECT ON `app_db`.* TO 'readonly'@'%';
-- 限制資源防止失控查詢拖垮主庫
GRANT USAGE ON *.* TO 'readonly'@'%' 
  WITH MAX_QUERIES_PER_HOUR 10000 
       MAX_CONNECTIONS_PER_HOUR 100 
       MAX_USER_CONNECTIONS 10;
FLUSH PRIVILEGES;

2. 應用讀寫賬號 (業務服務)


CREATE USER 'app_rw'@'10.0.%.%' IDENTIFIED BY 'AppRW#2024!@#';
GRANT SELECT, INSERT, UPDATE, DELETE ON `app_db`.* TO 'app_rw'@'10.0.%.%';
-- 禁止 DDL、TRUNCATE、DROP、ALTER、CREATE、INDEX、LOCK TABLES
FLUSH PRIVILEGES;

3. 開發/測試賬號 (含 DDL、索引、視圖、存儲過程)


CREATE USER 'dev'@'10.0.%.%' IDENTIFIED BY 'DevPass#2024!';
GRANT SELECT, INSERT, UPDATE, DELETE, 
      CREATE, ALTER, DROP, INDEX, 
      CREATE VIEW, SHOW VIEW,
      CREATE ROUTINE, ALTER ROUTINE, EXECUTE,
      CREATE TEMPORARY TABLES,
      LOCK TABLES, REFERENCES
  ON `app_db`.* TO 'dev'@'10.0.%.%';
FLUSH PRIVILEGES;

4. DBA 管理賬號 (分級)


-- 庫級 DBA
CREATE USER 'dba_app'@'10.0.0.10' IDENTIFIED BY 'DBA_App#2024!';
GRANT ALL PRIVILEGES ON `app_db`.* TO 'dba_app'@'10.0.0.10';

-- 實例級超級管理員 (極少量人持有)
CREATE USER 'root_dba'@'10.0.0.10' IDENTIFIED BY 'Root_DBA#2024!@#$';
GRANT ALL PRIVILEGES ON *.* TO 'root_dba'@'10.0.0.10' WITH GRANT OPTION;
-- 關鍵管理權限：SHUTDOWN, SUPER, REPLICATION SLAVE/CLIENT, FILE, PROCESS, RELOAD
FLUSH PRIVILEGES;

5. 複製/備份/監控專用賬號


-- 複製
CREATE USER 'repl'@'10.0.%.%' IDENTIFIED BY 'ReplPass#2024!';
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'repl'@'10.0.%.%';

-- 備份 (mysqldump/xtrabackup)
CREATE USER 'backup'@'10.0.%.%' IDENTIFIED BY 'BackupPass#2024!';
GRANT SELECT, RELOAD, LOCK TABLES, SHOW VIEW, EVENT, TRIGGER, PROCESS ON *.* TO 'backup'@'10.0.%.%';

-- 監控 (Prometheus exporter)
CREATE USER 'monitor'@'10.0.%.%' IDENTIFIED BY 'MonitorPass#2024!';
GRANT SELECT ON `mysql`.* TO 'monitor'@'10.0.%.%';
GRANT SELECT ON `performance_schema`.* TO 'monitor'@'10.0.%.%';
GRANT SELECT ON `information_schema`.* TO 'monitor'@'10.0.%.%';
GRANT PROCESS, REPLICATION CLIENT ON *.* TO 'monitor'@'10.0.%.%';

列級權限精細控制


-- 僅允許讀取非敏感列
GRANT SELECT (id, username, email, created_at) ON `app_db`.`users` TO 'api_public'@'%';

-- 僅允許更新狀態列
GRANT UPDATE (status) ON `app_db`.`orders` TO 'order_worker'@'%';

權限回收與刪除


-- 回收特定權限
REVOKE INSERT, UPDATE ON `app_db`.* FROM 'old_dev'@'%';

-- 回收所有權限 (保留用戶)
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'old_dba'@'%';

-- 徹底刪除用戶
DROP USER 'expired_user'@'%';
FLUSH PRIVILEGES;

權限查看與審計


-- 當前用戶
SHOW GRANTS;

-- 指定用戶
SHOW GRANTS FOR 'app_rw'@'10.0.%.%';

-- 系統表查詢 (信息 schema)
SELECT * FROM information_schema.USER_PRIVILEGES WHERE GRANTEE LIKE '%app_rw%';
SELECT * FROM information_schema.SCHEMA_PRIVILEGES WHERE GRANTEE LIKE '%app_rw%';
SELECT * FROM information_schema.TABLE_PRIVILEGES WHERE GRANTEE LIKE '%app_rw%';
SELECT * FROM information_schema.COLUMN_PRIVILEGES WHERE GRANTEE LIKE '%app_rw%';

密碼管理與輪換


-- 修改密碼 (5.7+)
ALTER USER 'app_rw'@'10.0.%.%' IDENTIFIED BY 'NewAppRW#2024!@#';

-- 密碼過期策略 (5.7+)
ALTER USER 'app_rw'@'10.0.%.%' PASSWORD EXPIRE INTERVAL 90 DAY;

-- 密碼歷史/重用限制
ALTER USER 'app_rw'@'10.0.%.%' PASSWORD HISTORY 5;
ALTER USER 'app_rw'@'10.0.%.%' PASSWORD REUSE INTERVAL 365 DAY;

-- 強制密碼複雜度 (validate_password 組件)
INSTALL COMPONENT 'file://component_validate_password';
SET GLOBAL validate_password.length=12;
SET GLOBAL validate_password.mixed_case_count=1;
SET GLOBAL validate_password.number_count=1;
SET GLOBAL validate_password.special_char_count=1;

主機名/通配符最佳實踐

模式	含義	建議場景
`'user'@'%'`	任意主機	極度不推薦生產，僅內網測試
`'user'@'10.0.%.%'`	IP 前綴匹配	應用服務器網段、K8s Pod CIDR
`'user'@'app-server-%'`	主機名模糊匹配	需 DNS 解析穩定，慎用
`'user'@'localhost'`	本地 Unix Socket	本機管理腳本、備份任務
`'user'@'127.0.0.1'`	本地 TCP	應用連接池走 TCP 時

安全加固清單 (上線前必檢)

刪除匿名用戶： DROP USER ''@'%'; DROP USER ''@'localhost';
移除 root 遠程登錄： DELETE FROM mysql.user WHERE User='root' AND Host NOT IN ('localhost','127.0.0.1','::1'); FLUSH PRIVILEGES;
刪除 test 庫及權限： DROP DATABASE test; DELETE FROM mysql.db WHERE Db='test' OR Db='test\\_%';
運行安全腳本： mysql_secure_installation (交互式完成以上所有)
啟用 SSL/TLS： REQUIRE SSL 在 GRANT 中，強制加密傳輸
審計日志： 安裝 audit_log 插件，記錄所有連接/查詢/DDL/DML
定期審計： 月度導出 SHOW GRANTS FOR ... 對比基線，檢測權限蔓延
最小權限原則： 無 SELECT 不給 SELECT，無 DDL 不給 DDL，IP 白名單最小化

避坑指南

GRANT 隱式創建用戶已移除 (5.7+)： 必須先 CREATE USER 再 GRANT
權限生效需重新連接： 修改後現有會話不受影響，應用需重啟或重連
FLUSH PRIVILEGES 只在直接操作 mysql 系統表時需要，正常 GRANT/REVOKE 自動生效
GRANT OPTION 慎給： 導致權限傳遞失控，僅超級管理員持有
通配符 % 匹配不包括 localhost (Unix Socket)： 需額外授權 'user'@'localhost'
列級權限與 SELECT * 衝突： 有列級權限的用戶執行 SELECT * 會報錯，需指定列名

總結

MySQL 權限管理的核心是**「最小權限、網絡隔離、定期審計、自動化輪換」**。從 CREATE USER 到分層 GRANT，再到 validate_password、audit_log、PASSWORD EXPIRE 構建纵深防御。記住：**數據庫賬號即攻擊面，每一個多餘的權限都是潛在漏洞**。建立「申請→審批→授權→審計→回收」全生命周期流程，配合 Infrastructure as Code (Terraform/Ansible) 管理權限即代碼，才是現代雲原生環境下的最佳實踐。