MySQL GRANT 語句新增用戶權限管理入門指南

兩種授權方式

1. GRANT 語句：語法簡潔、自動生效、不易出錯（推薦）
2. 直接操作授權表：需手動 FLUSH PRIVILEGES、易遺漏欄位

前置條件

• 本機以 root 連線
• root 擁有 INSERT（mysql.db）與 RELOAD 權限

GRANT 基礎範例

mysql> GRANT ALL PRIVILEGES ON *.* TO 'monty'@'localhost' IDENTIFIED BY 'something' WITH GRANT OPTION;
mysql> GRANT ALL PRIVILEGES ON *.* TO 'monty'@'%' IDENTIFIED BY 'something' WITH GRANT OPTION;
mysql> GRANT RELOAD, PROCESS ON *.* TO 'admin'@'localhost';
mysql> GRANT USAGE ON *.* TO 'dummy'@'localhost';

建立的三類用戶

關鍵：必須同時建立 user@localhost 和 user@%，否則匿名用戶優先匹配導致本地連線失效。

複雜授權場景

用戶 custom 從三主機連線，密碼 stupid，分庫分主機授權：

GRANT SELECT,INSERT,UPDATE,DELETE ON bankaccount.* TO 'custom'@'localhost' IDENTIFIED BY 'stupid';
GRANT SELECT,INSERT,UPDATE,DELETE ON expenses.* TO 'custom'@'whitehouse.gov' IDENTIFIED BY 'stupid';
GRANT SELECT,INSERT,UPDATE,DELETE ON customer.* TO 'custom'@'localhost' IDENTIFIED BY 'stupid';
GRANT SELECT,INSERT,UPDATE,DELETE ON customer.* TO 'custom'@'server.domain' IDENTIFIED BY 'stupid';
GRANT SELECT,INSERT,UPDATE,DELETE ON customer.* TO 'custom'@'whitehouse.gov' IDENTIFIED BY 'stupid';

直接操作授權表（參考）

INSERT INTO user VALUES('localhost','monty',PASSWORD('something'), 'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y');
INSERT INTO user VALUES('%','monty',PASSWORD('something'), 'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y');
INSERT INTO user SET Host='localhost', User='admin', Reload_priv='Y', Process_priv='Y';
INSERT INTO user (Host,User,Password) VALUES('localhost','dummy','');
FLUSH PRIVILEGES;

注意：MySQL 版本不同，user 表權限欄位數量不同。強烈建議使用 GRANT。

驗證與撤銷

# 查看權限
SHOW GRANTS FOR 'monty'@'localhost';

# 撤銷權限
REVOKE ALL PRIVILEGES ON *.* FROM 'monty'@'localhost';

# 刪除用戶
DROP USER 'monty'@'localhost';