核心命令速查表
| 操作 | SQL 命令 | 適用版本 |
|---|
| 創建用戶 | CREATE USER 'user'@'host' IDENTIFIED BY 'pass'; | 5.7+ |
| 授權（全庫全表） | GRANT ALL PRIVILEGES ON *.* TO 'user'@'host'; | 所有 |
| 授權（指定庫） | GRANT ALL ON db.* TO 'user'@'host'; | 所有 |
| 授權（指定表） | GRANT SELECT, INSERT ON db.tbl TO 'user'@'host'; | 所有 |
| 修改密碼 (5.7+) | ALTER USER 'user'@'host' IDENTIFIED BY 'newpass'; | 5.7+ |
| 修改密碼 (5.6-) | SET PASSWORD FOR 'user'@'host' = PASSWORD('newpass'); | 5.6- |
| 刷新權限 | FLUSH PRIVILEGES; | 所有 |
| 刪除用戶 | DROP USER 'user'@'host'; | 5.7+ |
| 查看權限 | SHOW GRANTS FOR 'user'@'host'; | 所有 |
| 查看所有用戶 | SELECT User, Host FROM mysql.user; | 所有 |
實戰場景範例
場景一：開發環境只讀賬號
CREATE USER 'dev_readonly'@'%' IDENTIFIED BY 'strong_pass_123';
GRANT SELECT ON myapp.* TO 'dev_readonly'@'%';
FLUSH PRIVILEGES;
場景二：應用程式讀寫賬號（最小權限）
CREATE USER 'app_rw'@'10.0.1.%' IDENTIFIED BY 'complex_pass_456';
GRANT SELECT, INSERT, UPDATE, DELETE ON myapp.* TO 'app_rw'@'10.0.1.%';
-- 禁止 DDL、GRANT、FILE 等危險權限
FLUSH PRIVILEGES;
場景三：備份專用賬號
CREATE USER 'backup_user'@'10.0.2.10' IDENTIFIED BY 'backup_pass_789';
GRANT SELECT, LOCK TABLES, SHOW VIEW, EVENT, TRIGGER, RELOAD, PROCESS ON *.* TO 'backup_user'@'10.0.2.10';
FLUSH PRIVILEGES;
場景四：修改 root 密碼（已知舊密碼）
-- 5.7+
ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_root_pass';
-- 5.6
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('new_root_pass');
FLUSH PRIVILEGES;
忘記 root 密碼：緊急重置流程
Linux/macOS
# 1. 停止 MySQL
systemctl stop mysqld
# 2. 跳過授權表啟動
mysqld_safe --skip-grant-tables --skip-networking &
# 3. 無密碼登入
mysql -u root
# 4. 修改密碼
FLUSH PRIVILEGES; -- 關鍵：載入授權表到內存
ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_pass';
-- 或 5.6: UPDATE mysql.user SET authentication_string=PASSWORD('new_pass') WHERE User='root' AND Host='localhost';
# 5. 正常重啟
mysqladmin shutdown
systemctl start mysqld
Windows
REM 1. 停止服務
net stop mysql
REM 2. 跳過授權啟動
mysqld --skip-grant-tables --skip-networking
REM 3. 新開 CMD 窗口
mysql -u root
REM 4. 修改密碼
FLUSH PRIVILEGES;
ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_pass';
REM 5. 關閉跳過授權進程，正常啟動服務
net start mysql
權限系統深度理解
權限層級
- 全局：
GRANT ON *.*（管理類：SHUTDOWN、RELOAD、FILE、PROCESS、SUPER）
- 庫級：
GRANT ON db.*（CREATE、DROP、INDEX、ALTER、REFERENCES）
- 表級：
GRANT ON db.tbl（SELECT、INSERT、UPDATE、DELETE、TRIGGER）
- 列級：
GRANT SELECT (col1, col2) ON db.tbl
- 存儲程序級：
GRANT EXECUTE ON PROCEDURE db.proc
Host 匹配規則
'user'@'localhost'：僅本地 Unix socket/127.0.0.1'user'@'10.0.1.%'：IP 前綴匹配'user'@'%'：任意主機（含 localhost，但優先級較低）- 具體 Host 優先於通配符：
'root'@'localhost' 不受 'root'@'%' 影響
安全最佳實踐
- 禁用
root@%，僅保留 root@localhost / root@127.0.0.1
- 每個應用/服務獨立賬號，最小權限原則
- 密碼策略：
validate_password 插件強制長度/複雜度/過期
- 定期審計：
SELECT User, Host, plugin FROM mysql.user; 檢查無密碼/弱密碼賬號
- 生產環境啟用 SSL：
REQUIRE SSL 在 GRANT 中指定
- 審計日誌：MariaDB Audit Plugin / MySQL Enterprise Audit
常見錯誤與排查
| 錯誤 | 原因 | 解決 |
|---|
| Access denied for user 'x'@'%' | Host 不匹配/密碼錯誤/無權限 | 檢查 SELECT User, Host FROM mysql.user，確認 Host 模式 |
| GRANT 無效果 | 未執行 FLUSH PRIVILEGES | 5.7+ 通常自動生效，舊版本必須手動 FLUSH |
| 密碼修改不生效 | 用舊認證插件 | 5.7+ 用 ALTER USER，非 UPDATE mysql.user |
| 無法刪除用戶 | 有活躍連接 | KILL 相關連接後再 DROP USER |
