MySQL 使用者管理與授權機制深度解析

授權機制核心概念

MySQL 採用 「主機 + 使用者」 雙重識別：user@host 視為唯一主體。同名使用者從不同主機連線，權限完全獨立。授權資訊儲於 mysql 系統資料庫四張核心表：

host 表維護「安全主機清單」，不受 GRANT/REVOKE 直接影響。

權限檢查順序與邏輯

1. 檢查 user 表：找到第一筆符合 Host、User 的記錄 → 取得 全域權限基礎集 (Priv)。
2. 檢查 db 表：找到符合 Host、Db、User 的記錄 → 取得 資料庫層級權限。
3. 合併邏輯： 若 db 表中 Host 為空，且 user 表的 Host 存在於 host 表中，則可獲得 host 表中為 'Y' 的權限。
4. 再檢查 tables_priv、columns_priv 疊加細粒度權限。

記錄匹配優先權 (關鍵！)

非自然順序，而是依 具體 > 通配 > 空值 排序：

• user 表：先比 Host (無通配 > 有通配 > 空)，再比 User (同規則)。
• db、host 表：依 Host 欄位同規則排序。

範例：localhost/root 優先於 %/root 優先於 localhost/ (匿名)。

GRANT 指令完整語法

GRANT privileges [(columns)] ON what TO user@host IDENTIFIED BY 'password' [WITH GRANT OPTION]

權限分類

關鍵子句

• IDENTIFIED BY 'pwd'：GRANT 會自動加密，勿自行用 PASSWORD()。
• WITH GRANT OPTION：允許該使用者將自己擁有的權限授予他人。
• columns：指定欄位級權限，如 GRANT UPDATE (col1, col2) ON db.tbl TO ...。

實用範例

-- 超級管理員 (僅本機)
GRANT ALL ON *.* TO 'admin'@'localhost' IDENTIFIED BY 'secret' WITH GRANT OPTION;

-- 開發人員 (特定庫，全權限)
GRANT ALL ON dev_db.* TO 'dev'@'192.168.1.%' IDENTIFIED BY 'devpass';

-- 唯讀帳號 (任何主機)
GRANT SELECT ON prod_db.* TO 'readonly'@'%' IDENTIFIED BY 'ropass';

-- 欄位級權限 (薪資欄位僅 HR 可改)
GRANT UPDATE (salary) ON hr.employees TO 'hr_user'@'localhost' IDENTIFIED BY 'hrpass';

-- 僅授管理權限 (清理日誌腳本用)
GRANT RELOAD ON *.* TO 'flusher'@'localhost' IDENTIFIED BY 'flushpass';

撤銷權限與刪除帳號

REVOKE ALL ON *.* FROM 'user'@'host';
DELETE FROM mysql.user WHERE User='user' AND Host='host';
FLUSH PRIVILEGES;

重要提醒

1. 修改授權表後 必須 執行 FLUSH PRIVILEGES 或 mysqladmin reload 生效。
2. 密碼在 user 表中以 PASSWORD() 加密儲存，明文無效。
3. 使用者名、密碼、資料庫名、表名 區分大小寫；主機名、欄位名 不區分。
4. 建立帳號時盡可能限制 Host 範圍，避免使用 %。