權限系統架構：四張授權表

MySQL 權限存儲在 mysql 系統庫四張表中，層層遞進：

表名	作用域	關鍵列（大小寫敏感性）
user	全局（服務器級）	Host(Y), User(Y), Password(Y), 全局權限列(N)
db	數據庫級	Host(N), User(Y), Db(Y), 權限列(N)
tables_priv	表級	Host(N), User(Y), Db(Y), Table_name(Y), Column_name(N)
columns_priv	列級	Host(N), User(Y), Db(Y), Table_name(Y), Column_name(N)

匹配順序：user → db → tables_priv → columns_priv，首次匹配生效，後續不再檢查。

權限分類與適用範圍

1. 數據操作權限（DML/DDL）

權限	列名	作用域	說明
SELECT	Select_priv	表/列	查詢數據
INSERT	Insert_priv	表/列	插入數據
UPDATE	Update_priv	表/列	修改數據
DELETE	Delete_priv	表	刪除數據
INDEX	Index_priv	表	建/刪索引
ALTER	Alter_priv	表	修改表結構
CREATE	Create_priv	庫/表/索引	創建對象
DROP	Drop_priv	庫/表	刪除對象
REFERENCES	References_priv	庫/表	外鍵引用（現版本未啟用）

2. 管理權限（高危，謹慎授予）

權限	列名	作用域	風險說明
FILE	File_priv	服務器文件	讀寫服務器文件，極高危，可讀 /etc/passwd、寫 webshell
GRANT	Grant_priv	庫/表	授權給他人，含遞授權
PROCESS	Process_priv	服務器	查看/殺死所有線程（含他人）
RELOAD	Reload_priv	服務器	FLUSH 操作、刷新權限/日志
SHUTDOWN	Shutdown_priv	服務器	關閉服務器
SUPER	Super_priv	服務器	超級權限：殺線程、修改全局變量、CHANGE MASTER、PURGE BINLOG 等
REPLICATION SLAVE	Repl_slave_priv	服務器	從庫連接主庫讀 Binlog
REPLICATION CLIENT	Repl_client_priv	服務器	SHOW MASTER/SLAVE STATUS

GRANT 實戰範例

1. 創建用戶並授權（推薦 5.7+ 語法）

-- 僅創建用戶
CREATE USER 'app_user'@'10.0.1.%' IDENTIFIED BY 'StrongPass#123';

-- 授予特定庫所有表的 讀/寫/索引 權限
GRANT SELECT, INSERT, UPDATE, DELETE, INDEX 
ON `app_db`.* TO 'app_user'@'10.0.1.%';

-- 授予僅讀權限（只讀賬號）
GRANT SELECT ON `app_db`.* TO 'readonly'@'10.0.1.%';

-- 生效
FLUSH PRIVILEGES;

2. 通配符主機與密碼策略

-- 允許任意 IP（極不推薦生產使用）
GRANT ALL ON *.* TO 'root'@'%' IDENTIFIED BY 'ComplexPass!';

-- 僅允許特定網段
GRANT ALL ON *.* TO 'admin'@'192.168.10.%' IDENTIFIED BY 'ComplexPass!';

-- 密碼過期策略（5.7+）
ALTER USER 'app_user'@'10.0.1.%' PASSWORD EXPIRE INTERVAL 90 DAY;

3. 查看用戶權限

SHOW GRANTS FOR 'app_user'@'10.0.1.%';
-- 輸出示例：
-- GRANT USAGE ON *.* TO `app_user`@`10.0.1.%`
-- GRANT SELECT, INSERT, UPDATE, DELETE, INDEX ON `app_db`.* TO `app_user`@`10.0.1.%`

REVOKE 回收權限

-- 回收特定庫權限
REVOKE ALL ON `app_db`.* FROM 'app_user'@'10.0.1.%';

-- 回收全局權限
REVOKE RELOAD, PROCESS ON *.* FROM 'admin'@'%';

-- 徹底刪除用戶（5.7+）
DROP USER 'app_user'@'10.0.1.%';

注意：REVOKE ALL 僅回收權限，不刪除用戶記錄（殘留在 mysql.user）。

權限管理黃金法則

1. 最小權限原則：業務賬號僅授 SELECT/INSERT/UPDATE/DELETE，禁止 DDL/管理權限。
2. 網段限制：Host 欄位盡量具體到 /24 網段，禁用 %。
3. 分層賬號：管理員（SUPER）、開發（DDL+DML）、只讀（SELECT）、備份（SELECT+LOCK TABLES+REPLICATION CLIENT）、監控（PROCESS+REPLICATION CLIENT）。
4. 定期審計：SELECT User, Host FROM mysql.user WHERE User != 'mysql.sys'; 清理僵屍賬號。
5. 密碼複雜度：啟用 validate_password 插件，強制長度、混合字符、過期輪換。

常見錯誤與排查

• ERROR 1045 (28000): Access denied → 檢查 Host 匹配（IP vs 域名）、密碼、skip-name-resolve 影響。
• ERROR 1142 (42000): SELECT command denied → 權限不足，SHOW GRANTS 核對。
• ERROR 1227 (42000): Access denied; you need SUPER privilege → 操作需 SUPER，如 SET GLOBAL、KILL 他人線程。