MySQL 用戶管理與授權機制深度解析：核心原理、匹配優先級、三大授權表結構與安全加固

1. 授權機制核心作用

基本功能：授予特定主機用戶對特定資料庫的 SELECT/INSERT/UPDATE/DELETE 權限。額外功能：控制匿名存取、特定函數使用（如 LOAD DATA INFILE）。MySQL 用戶名與系統用戶名無關，標準做法用 --user 選項指定。

2. 授權運作核心：主機+用戶=唯一標識

主機 1 的 lee 與主機 2 的 lee 是不同使用者，權限可異。核心問題：授予「從某主機登入的某用戶」對「某資料庫」的權限。可用 mysqlaccess 測試。所有授權資訊存於 mysql 資料庫的 user、host、db 三表。

3. 匹配搜尋機制與優先級（關鍵）

通配符 % 表示任意，空值亦同義。搜尋順序：user → db → host。

• 先檢索 user 表，找到第一匹配記錄 → 稱 Priv
• 再搜 db 表獲取授權
• 若 db 表 Host 為空，且 Priv 記錄主機包含在 host 表 Host 中，則可將 host 表中為 Y 的權限加給該用戶
• 若 db 表 Host 非空，則不影響該用戶/主機授權

各表記錄優先級排序規則

1. user 表： 先 Host 後 User。規則：無通配符 → 有通配符 → 空值。同一 Host 內再按 User 同規則排序。
2. db 表： 按 Host 欄位：無通配符 → 有通配符 → 空值。
3. host 表： 按 Host 欄位：無通配符 → 有通配符 → 空值。

匹配順序範例

+-----------+---------+
| Host      | User    |
+-----------+---------+
| %         | root    |
| %         | jeffrey |
| localhost | root    |
| localhost |         |
+-----------+---------+
搜尋順序：
localhost/root
localhost/any
any/jeffrey
any/root

localhost 的 jeffrey 連線，授權依 localhost/any 行而非 any/jeffrey。配置不當極易導致無法正常使用。

4. 實戰：添加用戶 custom

需求：用戶 custom 從 localhost、server.domain、whitehouse.gov 連線，密碼 stupid。bankaccount 僅允許 localhost 存取；customers 允許三主機存取。

INSERT INTO user (host,user,password) VALUES ('localhost','custom',PASSWORD('stupid'));
INSERT INTO user (host,user,password) VALUES ('server.domain','custom',PASSWORD('stupid'));
INSERT INTO user (host,user,password) VALUES ('whitehouse.gov','custom',PASSWORD('stupid'));
INSERT INTO db (host,db,user,Select_priv,Insert_priv,Update_priv,Delete_priv,Create_priv,Drop_priv)
VALUES ('localhost','bankaccount','custom','Y','Y','Y','Y','Y','Y');
INSERT INTO db (host,db,user,Select_priv,Insert_priv,Update_priv,Delete_priv,Create_priv,Drop_priv)
VALUES ('%','customers','custom','Y','Y','Y','Y','Y','Y');

5. 三大授權表結構

user 表

欄位：Host char(60) PRI, User char(16) PRI, Password char(16), Select_priv enum('N','Y') 預設 N, Insert_priv, Update_priv, Delete_priv, Create_priv, Drop_priv, Reload_priv, Shutdown_priv, Process_priv, File_priv

db 表

欄位：Host char(60) PRI, Db char(64) PRI, User char(16) PRI, Select_priv...Drop_priv (同 user 表前 8 權限)

host 表

僅在 db 表項目 Host 為空時使用。欄位：Host, Db, Select_priv...Drop_priv。

三表均支援通配符。

6. Access Denied 常見原因

1. 未執行 mysql_install_db 建立授權表（測試：mysql -u root 應無錯誤）
2. 初次使用應 mysql -u root mysql 或以 root 身份操作
3. 修改授權後未 mysqladmin reload 更新
4. 測試時建議 --skip-grant-tables 啟動
5. 未用 PASSWORD('pwd') 加密密碼；-ppassword 無空格

7. 強化 MySQL 安全性

1. 每個用戶設密碼，否則可用 mysql --user other_user db 繞過驗證
2. 勿用 root 啟動服務，可建專用用戶（MySQL 用戶≠系統用戶）
3. 嚴禁授予 Process_priv、File_priv 等高危權限
4. 不信任 DNS 時用 IP 替代主機名，謹慎用通配主機名
5. 啟動選項：--secure (檢查 IP 一致性)、--skip-grant-tables (慎用)、--skip-name-resolve (授權表只能用 IP/localhost)、--skip-networking (禁用 TCP/IP，僅 Unix Socket，強烈推薦)