MySQL 帳號權限管理精要：GRANT/REVOKE 實務操作與最佳實踐

核心架構：四大授權表

GRANT/REVOKE 是操作授權表的前端介面，影響四張系統表：

host 表不受 GRANT/REVOKE 影響。直接操作授權表雖可行，但建議透過指令管理，並閱讀官方「安全性指南」理解底層機制。

GRANT 語法完整解析

GRANT 權限 [(欄位)] ON 物件 TO '使用者'@'主機' IDENTIFIED BY '密碼' [WITH GRANT OPTION]

1. 權限類型 (privileges)

2. 權限作用範圍 (ON 子句)

• ON *.*：全域 (所有庫、所有表) — 管理權限只能用此範圍。
• ON db_name.*：資料庫層級 (該庫所有表)。
• ON db_name.tbl_name：資料表層級。
• ON db_name.tbl_name (col1, col2)：欄位層級 (需在權限後加欄位清單)。

3. 使用者識別

格式：'user_name'@'host_spec'。MySQL 以「使用者+來源主機」唯一識別帳號，允許同名不同來源帳號共存。

• 具體主機：'boris'@'localhost'、'fred'@'res.mars.com'
• 萬用字元 %：'max'@'%' (任意主機，等同 'max') — 最不安全。
• 網域萬用字元：'mary'@'%.snake.net'
• IP 位址/網段：'boris'@'192.168.128.3'、'fred'@'192.168.128.%'、'rex'@'192.168.128.0/17' (CIDR 表示法，MySQL 3.23+)。
• 含特殊字元需雙引號：GRANT ... TO "my friend"@'boa.snake.net'

4. 密碼與 WITH GRANT OPTION

• IDENTIFIED BY 'plain_pwd'：明文密碼，MySQL 自動加密儲存 (勿自行用 PASSWORD())。
• 新帳號省略 = 無密碼 (極度不安全)。
• 舊帳號省略 = 保留原密碼。
• WITH GRANT OPTION：允許該帳號將自己擁有的權限再授予他人 (遞授權)。

實務授權策略範例

超級管理員 (僅本機)

GRANT ALL ON *.* TO 'ethel'@'localhost' IDENTIFIED BY 'coffee' WITH GRANT OPTION;

特定管理功能帳號 (如清除日誌)

GRANT RELOAD ON *.* TO 'flush_user'@'localhost' IDENTIFIED BY 'flushpass';

資料庫擁有者 (完整控制特定庫)

GRANT ALL ON samp_db.* TO 'bill'@'racer.snake.net' IDENTIFIED BY 'rock';

唯讀帳號

GRANT SELECT ON samp_db.* TO 'ro_user'@'%' IDENTIFIED BY 'rock';

自訂權限組合 (可讀寫、不可建刪結構)

GRANT SELECT, INSERT, UPDATE, DELETE ON samp_db.* TO 'app_user'@'snake.net' IDENTIFIED BY 'rock';

欄位級權限 (如僅允許更新 expiration 欄位)

GRANT SELECT ON samp_db.member TO 'bill'@'localhost' IDENTIFIED BY 'rock';
GRANT UPDATE (expiration) ON samp_db.member TO 'bill'@'localhost'; -- 免再輸密碼

多欄位授權

GRANT UPDATE (street, city, state, zip) ON samp_db.member TO 'assistant'@'localhost';

臨時表資料庫 (隔離 CREATE 權限)

CREATE DATABASE tmp;
GRANT ALL ON tmp.* TO ''@'mars.net'; -- 匿名帳號，任何使用者皆可用 tmp.tbl_name

資料庫管理員 (可遞授權)

GRANT ALL ON sales.* TO 'alicia'@'%.big.corp.com' IDENTIFIED BY 'applejuice' WITH GRANT OPTION;

撤銷權限與刪除帳號 (REVOKE / DELETE)

REVOKE 權限 [(欄位)] ON 物件 FROM '使用者'@'主機';

• 語法類似 GRANT，但 TO 改 FROM，無 IDENTIFIED BY 與 WITH GRANT OPTION。
• REVOKE 僅移除權限，user 表帳號紀錄保留 (仍可連線但無權操作)。
• 徹底刪除帳號： 必須直接刪除 user 表記錄並重載權限：

DELETE FROM mysql.user WHERE User='user_name' AND Host='host_name';
FLUSH PRIVILEGES;

(GRANT/REVOKE 會自動重載，手動修改授權表需手動 FLUSH PRIVILEGES)。

輔助工具

MySQL 附贈 Perl 腳本 mysqlaccess、mysql_setpermission (需 DBI) 提供互動式帳號管理介面，可作為 GRANT 指令替代方案。