MySQL 賬號授權管理詳解：GRANT/REVOKE 實戰與權限表結構

權限表架構

GRANT/REVOKE 影響四張授權表：

（host 表不受 GRANT/REVOKE 影響）

GRANT 語法

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

• privileges：SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALTER, INDEX, FILE, PROCESS, RELOAD, SHUTDOWN, ALL, USAGE 等。
• columns：可選，指定欄位級權限（以逗號分隔）。
• what：*.*（全域）、db.*（資料庫）、db.tbl（資料表）。
• user：user@host，host 可用 %、%.domain.com、IP/網段（192.168.128.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 privileges (columns) ON what FROM user;
-- 僅移除權限，用戶記錄保留
DELETE FROM user WHERE User='user_name' AND Host='host_name';
FLUSH PRIVILEGES;

經典陷阱：匿名用戶優先級導致本機連線失敗

新增 fred@%.snake.net 後，本機連線被 ''@localhost 匿名用戶攔截（無密碼匹配失敗）。解法：DELETE FROM user WHERE User=''; FLUSH PRIVILEGES;