MySQL 權限系統深度解析：user、db、tables_priv 三層防線

權限系統架構概覽

MySQL 權限儲存於 mysql 系統資料庫的五張表，形成「全域 → 資料庫 → 資料表 → 欄位」四層遞進檢查機制：

第一關：user 表 - 登入與全域權限

核心欄位

• Host：允許連線的主機（%、localhost、IP、主機名）
• User：使用者名稱
• authentication_string / Password：加密密碼（PASSWORD() 或 caching_sha2_password）
• Select_priv、Insert_priv...Grant_priv：全域權限旗標 (Y/N)

關鍵原則

1. 最小權限：除 root 外，user 表權限全設 N，僅允許登入
2. 密碼加密：手動修改密碼必須用 PASSWORD() 或 SET PASSWORD，不可存明文
3. Host 限制：root 僅允許 localhost，禁止 %
4. 備用超管：建立同權限帳號（如 admin@localhost），密碼離線保管，防 root 遺忘

建立帳號正確姿勢

-- MySQL 5.7+
CREATE USER 'bill'@'%' IDENTIFIED BY '12345';
-- 或 8.0+
CREATE USER 'bill'@'%' IDENTIFIED WITH caching_sha2_password BY '12345';

-- 授權走 GRANT，不直接改 user 表
GRANT SELECT, INSERT, UPDATE ON mydb.* TO 'bill'@'%';

第二關：db 表 - 資料庫級權限

運作機制

查詢時：先檢查 user 表全域權限 → 再檢查 db 表資料庫權限 → 最後檢查 tables_priv。

GRANT/REVOKE 實戰

-- 授予 mydb 所有權限（含 GRANT OPTION）
GRANT ALL PRIVILEGES ON mydb.* TO 'bill'@'%';

-- 僅授予讀寫
GRANT SELECT, INSERT, UPDATE, DELETE ON mydb.* TO 'bill'@'%';

-- 回收權限
REVOKE INSERT, UPDATE ON mydb.* FROM 'bill'@'%';

-- 查看權限
SHOW GRANTS FOR 'bill'@'%';

• GRANT 立即生效，無需 FLUSH PRIVILEGES
• 擁有 GRANT OPTION 者只能授予自己擁有的權限
• 建議不給 GRANT OPTION 給應用帳號

第三關：tables_priv / columns_priv - 細粒度控制

tables_priv 關鍵欄位

• User、Db、Table_name：三元組定位
• Table_priv：SET 型別，含 Select, Insert, Update, Delete, Alter, Create, Drop, Grant, Index, References

columns_priv 關鍵欄位

• 額外含 Column_name，Column_priv 為 Select, Insert, Update, References

實戰：敏感欄位只讀

-- 僅允許讀取 employees 表的 name、dept 欄位
GRANT SELECT (name, dept) ON hr.employees TO 'report_user'@'%';

Host 欄位的雙重作用

• 區分同名不同源使用者：'bill'@'10.0.1.%' vs 'bill'@'%'
• 同一使用者不同來源不同權限：內網給讀寫，外網僅給讀

GRANT SELECT, INSERT ON mydb.* TO 'app'@'10.0.%';
GRANT SELECT ON mydb.* TO 'app'@'%';

FLUSH PRIVILEGES;

-- 列出所有帳號權限
SELECT User, Host, 
    CONCAT_WS(', ', 
        IF(Select_priv='Y','SELECT',''),
        IF(Insert_priv='Y','INSERT',''),
        IF(Grant_priv='Y','GRANT','')
    ) AS global_privs
FROM mysql.user
WHERE User != '';