MySQL 用戶管理實戰：建立、授權、密碼變更、刪除完整指令集
MySQL 權限系統基於「用戶@主機」模型。本文整理生產環境常用用戶管理操作，含 8.0 新語法與舊版兼容寫法。
一、查看現有用戶
SELECT Host, User, authentication_string FROM mysql.user;
-- 8.0 欄位為 authentication_string，5.7 為 Password
二、建立用戶（兩種方式）
方式一：CREATE USER + GRANT（推薦，8.0 標準）
-- 建立用戶
CREATE USER 'app_user'@'%' IDENTIFIED BY 'StrongPass123!';
CREATE USER 'app_user'@'localhost' IDENTIFIED BY 'StrongPass123!';
CREATE USER 'app_user'@'192.168.1.%' IDENTIFIED BY 'StrongPass123!';
-- 授權
GRANT SELECT, INSERT, UPDATE, DELETE ON app_db.* TO 'app_user'@'%';
GRANT ALL PRIVILEGES ON app_db.* TO 'app_user'@'localhost';
FLUSH PRIVILEGES;
方式二：GRANT 隱式建立（5.7 及舊版，8.0 需開啟 NO_AUTO_CREATE_USER 關閉才可用）
GRANT ALL PRIVILEGES ON ec.* TO 'cockpit'@'%' IDENTIFIED BY '123456';
FLUSH PRIVILEGES;
三、權限授權細粒度控制
| 權限 | 說明 | 典型角色 |
|---|
| ALL PRIVILEGES | 該庫所有權限 | DBA、擁有者 |
| SELECT | 查詢 | 唯讀帳號、報表 |
| INSERT/UPDATE/DELETE | 增刪改 | 應用帳號 |
| CREATE/DROP/ALTER | DDL 操作 | 開發、遷移工具 |
| INDEX | 建立/刪除索引 | DBA、優化工具 |
| EXECUTE | 執行存儲過程/函數 | 應用帳號 |
| REPLICATION SLAVE | 主從複寫 | 複寫專用帳號 |
| SUPER | 超級權限（KILL、CHANGE MASTER 等） | 僅 DBA |
主機通配符說明
'user'@'%'：任意主機（含遠程），不含 localhost'user'@'localhost'：本機 Unix Socket 連線'user'@'127.0.0.1'：本機 TCP 連線'user'@'192.168.1.%'：特定網段
⚠️ %' 不匹配 localhost，需單獨建立 'user'@'localhost' 否則本機 Socket 連線會被匿名用戶攔截導致拒絕存取。
四、修改密碼
8.0+ 標準語法
ALTER USER 'root'@'localhost' IDENTIFIED BY 'NewStrongPass123!';
-- 或
SET PASSWORD FOR 'root'@'localhost' = 'NewStrongPass123!';
5.7 相容語法
UPDATE mysql.user SET authentication_string=PASSWORD('NewPass') WHERE User='root' AND Host='localhost';
FLUSH PRIVILEGES;
密碼過期策略（8.0+）
ALTER USER 'app_user'@'%' PASSWORD EXPIRE INTERVAL 90 DAY;
ALTER USER 'app_user'@'%' PASSWORD HISTORY 5; -- 禁止重用近 5 次密碼
五、刪除用戶
標準方式（推薦）
DROP USER 'test'@'localhost';
DROP USER 'test'@'%';
手動清理（不推薦，易殘留權限表記錄）
DELETE FROM mysql.user WHERE User='test' AND Host='localhost';
DELETE FROM mysql.db WHERE User='test';
FLUSH PRIVILEGES;
六、常見問題排查
| 現象 | 原因 | 解決 |
|---|
| 本機 root 免密登入 | auth_socket 外掛 / 匿名用戶 | 設定密碼、刪除匿名用戶 DELETE FROM mysql.user WHERE User=''; |
| 遠程連線拒絕 | 無 user@% / 防火牆 / bind-address | CREATE USER 'u'@'%' ...; GRANT ...; firewall-cmd --add-port=3306/tcp
| 權限不生效 | 未執行 FLUSH PRIVILEGES / 快取 | FLUSH PRIVILEGES; 或重新連線 |
| 主機名解析慢 | skip-name-resolve 未開啟 | skip-name-resolve=1 在 my.cnf |
七、安全基線清單
- 刪除匿名用戶：
DELETE FROM mysql.user WHERE User='';
- 禁止 root 遠程登入：
DELETE FROM mysql.user WHERE User='root' AND Host!='localhost';
- 移除 test 資料庫：
DROP DATABASE test; DELETE FROM mysql.db WHERE Db='test' OR Db='test\\_%';
- 強制密碼複雜度：安裝
validate_password 元件
- 定期審計權限：
SHOW GRANTS FOR 'user'@'host';
