MySQL 用戶賬號管理基礎：GRANT/REVOKE 權限體系完整指南

權限表結構

（host 表不受 GRANT/REVOKE 影響）

GRANT 語法詳解

GRANT privileges (columns) ON what TO user IDENTIFIED BY 'password' WITH GRANT OPTION

• privileges：ALTER, CREATE, DELETE, DROP, INDEX, INSERT, SELECT, UPDATE, FILE, PROCESS, RELOAD, SHUTDOWN, ALL, USAGE。
• columns：欄位清單（可選）。
• what：*.*、db.*、db.tbl。
• user：user@host，host 支援 %、%.domain、IP/網段（192.168.0.0/17）。
• WITH GRANT OPTION：授權遞授權限。

授權實例分類

1. 連線來源控制

GRANT ALL ON samp_db.* TO boris@localhost IDENTIFIED BY 'ruby';
GRANT ALL ON samp_db.* TO max@'%' IDENTIFIED BY 'diamond';
GRANT ALL ON samp_db.* TO mary@'%.snake.net' IDENTIFIED BY 'topaz';
GRANT ALL ON samp_db.* TO rex@'192.168.128.0/17' IDENTIFIED BY 'ice';

2. 權限層級

-- 超級用戶
GRANT ALL ON *.* TO ethel@localhost IDENTIFIED BY 'coffee' WITH GRANT OPTION;
-- 僅管理權限
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 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;

3. 臨時資料庫與匿名用戶

GRANT ALL ON tmp.* TO ''@'%.mars.net';

REVOKE 與徹底刪除用戶

REVOKE SELECT ON samp_db.* FROM bill@racer.snake.net;
DELETE FROM user WHERE User='bill' AND Host='racer.snake.net';
FLUSH PRIVILEGES;

常見陷阱：匿名用戶優先級

建立 fred@%.snake.net 後，本機連線被 ''@localhost 匿名帳號攔截（無密碼導致拒絕）。解法：DELETE FROM user WHERE User=''; FLUSH PRIVILEGES;

本地主機名指定技巧

若用伺服器主機名而非 localhost 授權，可能因名稱解析不一致（FQDN vs 短名）導致連線失敗。查看一般日誌中記錄的主機名格式，授權時以相同格式指定。