MySQL 採用「用戶@主機」二元組 + 五層權限體系 (全局/庫/表/列/程序)。本文系統整理 GRANT/REVOKE 核心語法、分層授權實戰模板、權限審計查詢、密碼策略、常見誤區與安全基線配置，助您建立最小權限、可審計、合規的數據庫訪問控制體系。
| 層級 | 授權表 | 範圍 | 典型權限 |
|---|---|---|---|
| 全局 | mysql.user | *.* | ALL PRIVILEGES, SUPER, RELOAD, PROCESS, REPLICATION SLAVE, CREATE USER |
| 庫級 | mysql.db | db.* | CREATE, ALTER, DROP, SELECT, INSERT, UPDATE, DELETE, INDEX, EXECUTE, CREATE VIEW |
| 表級 | mysql.tables_priv | db.tbl | SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALTER, INDEX, TRIGGER |
| 列級 | mysql.columns_priv | db.tbl(col) | SELECT, INSERT, UPDATE (指定列) |
| 程序級 | mysql.procs_priv | db.proc/func | EXECUTE, ALTER ROUTINE |
生效規則：權限檢查順序 全局 → 庫 → 表 → 列，任一層允許即通過。GRANT 即時生效 (內存權限緩存)，REVOKE 需重新連接生效。
GRANT priv_type [(column_list)] ON object_type object_name TO 'user'@'host' [IDENTIFIED BY 'password'] [WITH GRANT OPTION] [REQUIRE ssl_options];CREATE USER 'dba'@'localhost' IDENTIFIED BY 'DbaStrongPass123!';
GRANT ALL PRIVILEGES ON *.* TO 'dba'@'localhost' WITH GRANT OPTION;CREATE USER 'app_rw'@'192.168.10.%' IDENTIFIED BY 'AppRwPass123!';
GRANT SELECT, INSERT, UPDATE, DELETE ON biz_db.* TO 'app_rw'@'192.168.10.%';CREATE USER 'readonly'@'10.0.1.%' IDENTIFIED BY 'ReadOnlyPass123!';
GRANT SELECT ON reporting_db.* TO 'readonly'@'10.0.1.%';CREATE USER 'dev'@'192.168.20.%' IDENTIFIED BY 'DevPass123!';
GRANT CREATE, ALTER, DROP, INDEX, CREATE VIEW, SHOW VIEW,
CREATE ROUTINE, ALTER ROUTINE, EXECUTE
ON dev_db.* TO 'dev'@'192.168.20.%';-- 複製
CREATE USER 'repl'@'192.168.1.101' IDENTIFIED BY 'ReplPass123!';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.1.101';
-- 備份 (mysqldump --single-transaction)
CREATE USER 'backup'@'192.168.1.10' IDENTIFIED BY 'BackupPass123!';
GRANT SELECT, RELOAD, PROCESS, SHOW VIEW, EVENT, TRIGGER, LOCK TABLES ON *.* TO 'backup'@'192.168.1.10';
-- 監控 (Prometheus mysqld_exporter)
CREATE USER 'exporter'@'192.168.1.%' IDENTIFIED BY 'ExporterPass123!' WITH MAX_USER_CONNECTIONS 3;
GRANT PROCESS, REPLICATION CLIENT, SELECT ON performance_schema.* TO 'exporter'@'192.168.1.%';GRANT SELECT (id, username, email, created_at) ON users TO 'api_public'@'%';
GRANT UPDATE (email, profile_json) ON users TO 'api_public'@'%';
-- 不授權 password_hash, salt, id_card, phone 等敏感列-- 密碼複雜度 (validate_password 插件)
INSTALL PLUGIN validate_password SONAME 'validate_password.so';
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;
SET GLOBAL validate_password.policy = 'STRONG';
-- 密碼過期/歷史/鎖定
ALTER USER 'app_rw'@'192.168.10.%' PASSWORD EXPIRE INTERVAL 90 DAY;
ALTER USER 'app_rw'@'192.168.10.%' PASSWORD HISTORY 5;
ALTER USER 'app_rw'@'192.168.10.%' FAILED_LOGIN_ATTEMPTS 5 PASSWORD_LOCK_TIME 2;
-- 強制 SSL
GRANT SELECT ON biz_db.* TO 'app_rw'@'192.168.10.%' REQUIRE SSL;
ALTER USER 'app_rw'@'192.168.10.%' REQUIRE SSL;-- 1. 所有用戶全局權限概覽
SELECT User, Host,
Select_priv, Insert_priv, Update_priv, Delete_priv,
Create_priv, Drop_priv, Grant_priv, Super_priv,
Create_user_priv, Repl_slave_priv, Repl_client_priv
FROM mysql.user ORDER BY User, Host;
-- 2. 庫級權限矩陣
SELECT User, Host, Db,
Select_priv, Insert_priv, Update_priv, Delete_priv,
Create_priv, Drop_priv, Index_priv, Alter_priv
FROM mysql.db ORDER BY Db, User, Host;
-- 3. 表/列/程序權限
SELECT * FROM mysql.tables_priv;
SELECT * FROM mysql.columns_priv;
SELECT * FROM mysql.procs_priv;
-- 4. 危險權限掃描 (超級/文件/關閉/創建用戶)
SELECT User, Host FROM mysql.user
WHERE Super_priv='Y' OR File_priv='Y' OR Shutdown_priv='Y' OR Create_user_priv='Y';
-- 5. 通配符主機掃描 (風險)
SELECT User, Host FROM mysql.user WHERE Host='%';-- 回收具體權限
REVOKE INSERT, UPDATE ON biz_db.* FROM 'app_rw'@'192.168.10.%';
-- 回收所有權限 (含 GRANT OPTION)
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'old_dba'@'localhost';
-- 徹底刪除用戶 (8.0 自動清理所有授權表)
DROP USER 'old_dba'@'localhost';| 誤區 | 後果 | 正確做法 |
|---|---|---|
| GRANT ALL ON *.* TO 'app'@'%' | 應用擁有關閉實例、創建用戶、讀取所有庫權限 | 最小權限：僅授業務庫 CRUD，綁定具體 IP 段 |
| 密碼寫在應用配置明文 | 代碼洩露 = 數據庫失陷 | 配置中心加密、Vault/Secrets Manager、IRSA/IAM 角色 |
| root 賬號日常使用 | 誤操作風險極高、審計不可追溯 | root 僅緊急運維，禁用遠程，auth_socket 本地免密 |
| REVOKE 後不重連 | 舊連接仍保留舊權限 | 應用層連接池重建或殺掉舊連接 |
| 匿名用戶 ''@'%' 保留 | 任意連接無密碼嘗試 | 安裝後立即 DELETE FROM mysql.user WHERE User=''; FLUSH PRIVILEGES; |
skip-name-resolve 禁用 DNS 解析，授權表僅用 IP。local_infile=OFF 禁用 LOAD DATA LOCAL (應用層控制)。secure_file_priv=/data/mysql/secure 限制 SELECT ... INTO OUTFILE 目錄。audit_log 記錄所有連接/查詢/DDL/DML/錯誤。pt-table-checksum 校驗主從一致性，pt-query-digest 分析慢查詢。mysqldump | gpg -e，異地存儲，定期恢復演練。權限管理不是一次性配置，而是持續的「授權→審計→回收→審計」閉環。將上述模板、審計查詢、基線清單固化為 Ansible Playbook / Terraform Module / CI Pipeline，實現基礎設施即代碼，才能在規模擴大時保持合規與安全。
