MySQL用戶權限管理權威指南：GRANT/REVOKE深度解析與最小權限模型實踐

權限系統架構：四層權限表決策鏈

MySQL權限檢查順序：user(全局) → db(庫級) → tables_priv(表級) → columns_priv(列級)，首個匹配生效，host表不受GRANT/REVOKE影響（需直接DML維護）。

GRANT 語法全解析

GRANT priv_type [(col_list)]
    ON object_type priv_level
    TO user_specification [auth_option]
    [REQUIRE tls_option]
    [WITH grant_option | resource_option];

權限分類與業務映射

權限作用範圍（ON子句）精準控制

GRANT SELECT ON *.* TO 'monitor'@'%';           -- 全局只讀
GRANT SELECT,INSERT ON `biz_db`.* TO 'app_rw'@'10.0.%';  -- 庫級讀寫
GRANT UPDATE(col1,col2) ON `biz_db`.`tbl` TO 'etl'@'10.0.0.5'; -- 列級寫入
GRANT ALL ON `biz_db`.`tbl` TO 'dev'@'localhost';          -- 表級全權

帳號識別：用戶@主機 雙主鍵模型

• 'user'@'%'：任意主機（通配符%匹配任意長度，_匹配單字符）
• 'user'@'10.0.%'：IP段限制
• 'user'@'host.domain'：主機名（依賴DNS反解，建議優先用IP）
• 同用戶名不同Host視為不同帳號，認證時按Host精確度排序匹配

USAGE權限：帳號屬性管理的瑞士軍刀

-- 修改密碼（不影響權限）
GRANT USAGE ON *.* TO 'app'@'%' IDENTIFIED BY 'NewPass2024!';

-- 強制SSL
GRANT USAGE ON *.* TO 'app'@'%' REQUIRE SSL;

-- 限制資源（每小時）
GRANT USAGE ON *.* TO 'app'@'%' 
    WITH MAX_QUERIES_PER_HOUR 10000 
         MAX_UPDATES_PER_HOUR 2000 
         MAX_CONNECTIONS_PER_HOUR 500
         MAX_USER_CONNECTIONS 50;  -- 並發連接數

-- 清零計數器
FLUSH USER_RESOURCES;

REVOKE 精準收回與帳號註銷

-- 收回特定權限
REVOKE INSERT ON `biz_db`.* FROM 'app'@'%';

-- 收回所有權限（保留帳號）
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'app'@'%';

-- 徹底刪除帳號（需DROP權限或直接操作mysql.user表）
DROP USER 'app'@'%';
-- 或
DELETE FROM mysql.user WHERE User='app' AND Host='%';
FLUSH PRIVILEGES;

注意：REVOKE不移除REQUIRE SSL/資源限制，需用GRANT USAGE ... REQUIRE NONE / WITH MAX_... 0覆蓋清除。

最小權限模型實踐範本

應用讀寫賬號

CREATE USER 'app_rw'@'10.0.1.%' IDENTIFIED BY 'StrongPass!';
GRANT SELECT, INSERT, UPDATE, DELETE ON `biz_db`.* TO 'app_rw'@'10.0.1.%';
GRANT EXECUTE ON `biz_db`.* TO 'app_rw'@'10.0.1.%';  -- 若用存儲過程

只讀報表賬號

CREATE USER 'report_ro'@'10.0.2.%' IDENTIFIED BY 'StrongPass!';
GRANT SELECT ON `biz_db`.* TO 'report_ro'@'10.0.2.%'
    WITH MAX_QUERIES_PER_HOUR 5000 MAX_CONNECTIONS_PER_HOUR 20;

備份賬號

CREATE USER 'backup'@'10.0.3.10' IDENTIFIED BY 'StrongPass!';
GRANT SELECT, RELOAD, LOCK TABLES, SHOW VIEW, EVENT, TRIGGER 
    ON *.* TO 'backup'@'10.0.3.10'
    WITH MAX_CONNECTIONS_PER_HOUR 5;  -- 單連接備份

復制賬號

CREATE USER 'repl'@'10.0.4.%' IDENTIFIED BY 'StrongPass!';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'10.0.4.%';

權限審計與合規檢查

-- 發現無Host限制的高危帳號
SELECT User, Host FROM mysql.user WHERE Host='%' AND User NOT IN ('repl','monitor');

-- 發現擁有FILE/SHUTDOWN/SUPER的非DBA帳號
SELECT User, Host FROM mysql.user 
WHERE File_priv='Y' OR Shutdown_priv='Y' OR Super_priv='Y'
AND User NOT IN ('root','backup','repl');

-- 定期導出權限矩陣
SELECT * FROM information_schema.USER_PRIVILEGES;
SELECT * FROM information_schema.SCHEMA_PRIVILEGES;