'username'@'host'(host 支援 % 通配、IP、主機名)。*.* → 庫級 db.* → 表級 db.table → 欄位級。mysql.user、db、tables_priv、columns_priv、procs_priv、proxies_priv。CREATE USER 'app_user'@'192.168.1.%' IDENTIFIED BY 'StrongPass123!';
-- 或指定認證插件(8.0 預設 caching_sha2_password)
CREATE USER 'legacy_app'@'%' IDENTIFIED WITH mysql_native_password BY 'LegacyPass456!';
INSERT INTO mysql.user (Host, User, authentication_string)
VALUES ('localhost', 'old_user', PASSWORD('pass'));
FLUSH PRIVILEGES;
| 權限 | 說明 |
|---|---|
| ALL PRIVILEGES | 除 GRANT OPTION 外所有權限 |
| SELECT, INSERT, UPDATE, DELETE | 基礎 DML |
| CREATE, DROP, ALTER, INDEX | DDL |
| EXECUTE | 執行存儲過程/函數 |
| RELOAD, SHUTDOWN, PROCESS | 管理權限 |
| REPLICATION SLAVE/CLIENT | 複寫相關 |
| GRANT OPTION | 允許將自身權限授予他人 |
-- 庫級唯讀
GRANT SELECT ON app_db.* TO 'readonly'@'192.168.1.%';
-- 表級讀寫
GRANT SELECT, INSERT, UPDATE, DELETE ON app_db.orders TO 'order_svc'@'10.0.0.%';
-- 欄位級敏感資料保護
GRANT SELECT (id, name, email) ON app_db.users TO 'api_user'@'%';
-- 開發帳號:特定庫全權限,含建表、索引、視圖
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALTER, INDEX, CREATE VIEW
ON dev_db.* TO 'dev'@'192.168.1.%';
-- 超級管理員(謹慎授予)
GRANT ALL PRIVILEGES ON *.* TO 'admin'@'192.168.1.100' WITH GRANT OPTION;
FLUSH PRIVILEGES;
ALTER USER 'user'@'host' IDENTIFIED BY 'NewPass123!';
-- 或
SET PASSWORD FOR 'user'@'host' = 'NewPass123!';
SET PASSWORD FOR 'user'@'host' = PASSWORD('NewPass123!');
-- 或
UPDATE mysql.user SET authentication_string=PASSWORD('NewPass123!') WHERE User='user' AND Host='host';
FLUSH PRIVILEGES;
mysqladmin -u user -p'oldpass' password 'newpass'
REVOKE INSERT, UPDATE ON app_db.* FROM 'readonly'@'192.168.1.%';
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'old_admin'@'%';
FLUSH PRIVILEGES;
⚠️ REVOKE 需精確匹配原 GRANT 的層級(庫/表/欄位),否則不生效。用 SHOW GRANTS FOR 'user'@'host'; 查看當前權限再精確撤銷。
DROP USER 'user'@'host';
-- 或舊版
DELETE FROM mysql.user WHERE User='user' AND Host='host';
FLUSH PRIVILEGES;
SHOW GRANTS FOR 'user'@'host';
SHOW GRANTS; -- 當前用戶
GRANT SELECT (id, name) ON db.users TO 'api'@'%'; 避免密碼雜湊、身份證等洩露。% 主機:生產環境僅授具體 IP/網段。SELECT User, Host FROM mysql.user WHERE Host='%'; 掃描通配主機帳號。validate_password 強制複雜度,定期 ALTER USER ... PASSWORD EXPIRE。-- 建立角色
CREATE ROLE 'app_readonly', 'app_readwrite';
-- 角色授權
GRANT SELECT ON app_db.* TO 'app_readonly';
GRANT SELECT, INSERT, UPDATE, DELETE ON app_db.* TO 'app_readwrite';
-- 用戶授予角色
CREATE USER 'svc_order'@'%' IDENTIFIED BY 'pass';
GRANT 'app_readwrite' TO 'svc_order'@'%';
-- 設定預設角色(登入自動生效)
SET DEFAULT ROLE 'app_readwrite' TO 'svc_order'@'%';
角色簡化大量用戶權限管理,變更權限只需改角色,所有關聯用戶自動生效。
MySQL 用戶管理核心:**CREATE USER 建立身份 → GRANT 最小權限 → 定期審計撤銷 → 角色簡化批量管理**。牢記 FLUSH PRIVILEGES 讓修改即時生效,生產環境嚴禁 root@%、嚴禁弱密碼、嚴禁過度授權。