MySQL 使用者帳號與權限管理核心知識：GRANT/REVOKE 實戰詳解

MySQL 3.22.11 引入 GRANT/REVOKE 簡化權限管理，取代直接操作授權表。本文系統說明權限架構、建立帳號與授權語法、主機名匹配陷阱、權限回收與帳號刪除、常見權限難題解決方案。

一、權限系統架構

GRANT/REVOKE 作用於四張授權表：

第五張表 host 不受 GRANT/REVOKE 影響。

二、GRANT 語法完整解析

GRANT privileges [(columns)]
ON what
TO user [IDENTIFIED BY "password"]
[WITH GRANT OPTION];

1. 權限說明符

• 資料庫/資料表/欄位： SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER
• 全域管理： FILE, PROCESS, RELOAD, SHUTDOWN（保守授予）
• 特殊： ALL（全部）、USAGE（僅登入）

2. 作用範圍 what

• *.*：所有資料庫、所有資料表
• db_name.*：特定資料庫所有表
• db_name.tbl_name：特定表
• db_name.tbl_name(col1,col2)：特定欄位（需搭配欄位權限）

3. 用戶格式 user

username@host，host 支援萬用字元 %：

• user@localhost：僅本機
• user@%.domain.com：特定網域所有主機
• user@192.168.1.%：IP 範圍
• user@192.168.1.0/255.255.255.0：CIDR 表示法（MySQL 3.23+）

省略 @host 等同 @%（最寬鬆、最不安全）。

4. 密碼與 WITH GRANT OPTION

• IDENTIFIED BY "pass"：指定密碼（明文傳入，MySQL 自動加密存儲）。新用戶省略則無密碼（不安全）。
• WITH GRANT OPTION：允許受授者將自己擁有的權限轉授他人（謹慎使用，易造成權限蔓延）。

三、實戰授權範例

-- 超級管理員（僅本機）
GRANT ALL ON *.* TO 'ethel'@'localhost' IDENTIFIED BY 'coffee' WITH GRANT OPTION;

-- 僅執行 FLUSH 的管理帳號
GRANT RELOAD ON *.* TO 'flush'@'localhost' IDENTIFIED BY 'flushpass';

-- 資料庫擁有者
GRANT ALL ON samp_db.* TO 'bill'@'racer.snake.net' IDENTIFIED BY 'rock';

-- 唯讀用戶
GRANT SELECT ON menagerie.* TO 'ro_user'@'%' IDENTIFIED BY 'dirt';

-- 細粒度：特定表特定欄位更新
GRANT SELECT ON samp_db.member TO 'assistant'@'localhost' IDENTIFIED BY 'pass';
GRANT UPDATE (expiration) ON samp_db.member TO 'assistant'@'localhost';
GRANT UPDATE (street,city,state,zip) ON samp_db.member TO 'assistant'@'localhost';

-- 匿名用戶使用臨時資料庫
GRANT ALL ON tmp.* TO ''@'%.mars.net';

四、REVOKE 與帳號刪除

REVOKE privileges [(columns)] ON what FROM user;

• user 必須與原 GRANT 完全一致。
• privileges 可部分回收。
• REVOKE 僅移除權限，**不刪除用戶記錄**；用戶仍可登入（無權限）。

徹底刪除用戶：

DELETE FROM mysql.user WHERE User='baduser' AND Host='%.host.net';
FLUSH PRIVILEGES;

五、權限難題：匿名用戶優先級陷阱

現象

建立 GRANT ALL ON samp_db.* TO 'fred'@'%.snake.net' IDENTIFIED BY 'cocoa';，fred 可從網域任何主機連線，**唯獨資料庫伺服器本機連線失敗**（Access denied）。

原因

預設安裝存在匿名用戶 ''@'localhost'、''@'real_hostname'。MySQL 驗證時依**主機名具體度**排序：

1. localhost（最具體）
2. real_hostname
3. %.snake.net
4. %（最不具體）

本機連線匹配到匿名用戶（無密碼），導致密碼錯誤或權限不足。

解決方案

DELETE FROM mysql.user WHERE User='';
FLUSH PRIVILEGES;

移除匿名用戶後，fred 即可從本機正常連線。

六、主機名解析匹配問題

• 授權表主機名需與客戶端連線時解析的主機名**完全一致**（限定/非限定域名）。
• 驗證方法：mysql -h hostname 連線後查看錯誤日誌中的主機名記錄，依該格式建立帳號。
• 本機連線建議同時建立 user@localhost 與 user@real_hostname 兩組。

七、被鎖在外面？Root 密碼重置速查

參考《MySQL Root 密碼遺忘？三步驟快速重置存取權限》一文。

結語

MySQL 權限模型雖舊但極具彈性。掌握 GRANT/REVOKE 語法、理解主機名匹配優先級、移除預設匿名用戶、實施最小權限原則，是資料庫安全的基石。建議搭配 mysql_setpermission 腳本簡化日常操作，並定期審計 mysql.user、db 等授權表。