核心操作速查表
| 操作 | SQL (8.0+ 推薦語法) | 舊版兼容語法 |
|---|
| 創建用戶 | CREATE USER 'user'@'host' IDENTIFIED BY 'pass'; | GRANT USAGE ON *.* TO 'user'@'host' IDENTIFIED BY 'pass'; |
| 授權 (庫級) | GRANT SELECT,INSERT,UPDATE,DELETE ON db.* TO 'user'@'host'; | 同 |
| 授權 (表級) | GRANT SELECT ON db.tbl TO 'user'@'host'; | 同 |
| 授權 (列級) | GRANT SELECT (col1,col2) ON db.tbl TO 'user'@'host'; | 同 |
| 刷新權限 | FLUSH PRIVILEGES; | 同 |
| 查看權限 | SHOW GRANTS FOR 'user'@'host'; | 同 |
| 撤銷權限 | REVOKE INSERT ON db.* FROM 'user'@'host'; | 同 |
| 刪除用戶 | DROP USER 'user'@'host'; | DELETE FROM mysql.user WHERE User='user' AND Host='host'; FLUSH PRIVILEGES; |
| 修改密碼 (自身) | SET PASSWORD = 'newpass'; | SET PASSWORD FOR 'user'@'host' = PASSWORD('newpass'); |
| 修改密碼 (管理員) | ALTER USER 'user'@'host' IDENTIFIED BY 'newpass'; | UPDATE mysql.user SET authentication_string=PASSWORD('newpass') WHERE User='user' AND Host='host'; FLUSH PRIVILEGES; |
| 重命名用戶 | RENAME USER 'old'@'host' TO 'new'@'host'; | 不支持 |
Host 模式精講
| Host 值 | 含義 | 典型場景 |
|---|
localhost | Unix Socket 本地連接 | 本機腳本、管理任務 |
127.0.0.1 | TCP/IP 回環 | 本機應用走 TCP |
::1 | IPv6 回環 | IPv6 環境 |
192.168.1.100 | 精確 IP | 固定 IP 應用服務器 |
192.168.1.% | 網段通配符 | 動態擴縮容應用集群 |
% | 任意主機 | **僅測試/開發**，生產禁用 |
app.example.com | 域名 (需 DNS 解析、skip_name_resolve=OFF) | 雲環境彈性 IP |
最小權限模型實戰 (RBAC 思想)
-- 1. 唯讀角色 (報表、BI、只讀 API)
CREATE ROLE 'role_readonly';
GRANT SELECT ON prod_db.* TO 'role_readonly';
GRANT SELECT ON reporting_db.* TO 'role_readonly';
-- 2. 開發角色 (無 DDL 權限)
CREATE ROLE 'role_dev';
GRANT SELECT, INSERT, UPDATE, DELETE ON dev_db.* TO 'role_dev';
GRANT CREATE TEMPORARY TABLES ON dev_db.* TO 'role_dev';
-- 3. 運維角色 (含 DDL、無 DROP DATABASE)
CREATE ROLE 'role_ops';
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER, INDEX, DROP ON prod_db.* TO 'role_ops';
GRANT SHOW VIEW, EVENT, TRIGGER ON prod_db.* TO 'role_ops';
-- 4. 用戶綁定角色
CREATE USER 'bi_user'@'10.0.1.%' IDENTIFIED BY '...';
GRANT 'role_readonly' TO 'bi_user'@'10.0.1.%';
SET DEFAULT ROLE 'role_readonly' TO 'bi_user'@'10.0.1.%'; -- 8.0 自動激活
-- 5. 應用賬號 (極度最小化)
CREATE USER 'order_svc'@'10.0.2.%' IDENTIFIED BY '...';
GRANT INSERT, SELECT ON prod_db.orders TO 'order_svc'@'10.0.2.%';
GRANT SELECT ON prod_db.customers TO 'order_svc'@'10.0.2.%';
GRANT EXECUTE ON PROCEDURE prod_db.sp_create_order TO 'order_svc'@'10.0.2.%';
密碼安全策略 (validate_password 插件)
-- 安裝插件 (8.0 內建)
INSTALL PLUGIN validate_password SONAME 'validate_password.so';
-- 配置 (持久化寫入 my.cnf)
[mysqld]
validate_password.length = 12
validate_password.mixed_case_count = 1
validate_password.number_count = 1
validate_password.special_char_count = 1
validate_password.policy = MEDIUM -- LOW/MEDIUM/STRONG
validate_password.dictionary_file = '/usr/share/dict/words' -- 禁用弱密碼字典
-- 動態調整
SET GLOBAL validate_password.length = 16;
審計與巡檢清單 (月度執行)
- 通配符 Host 掃描：
SELECT User, Host FROM mysql.user WHERE Host = '%';
- 弱密碼/空密碼檢測：
SELECT User, Host, plugin, authentication_string
FROM mysql.user
WHERE authentication_string = '' OR LENGTH(authentication_string) < 20;
- 匿名用戶清理：
DROP USER ''@'localhost', ''@'%';
- 過期未登錄用戶 (8.0+
last_login 欄位)：
SELECT User, Host FROM mysql.user WHERE last_login < DATE_SUB(NOW(), INTERVAL 90 DAY);
- 權限過度授予檢查：
SELECT User, Host, Grantee, Privilege_Type, Is_Grantable
FROM information_schema.USER_PRIVILEGES
WHERE Privilege_Type IN ('FILE','PROCESS','SUPER','SHUTDOWN','RELOAD','CREATE USER')
AND Grantee NOT IN ('root@localhost','admin@10.0.0.%');
常見坑位
GRANT ALL PRIVILEGES ON *.* TO 'user'@'%' 含 GRANT OPTION，該用戶可授權他人，**極度危險**。FLUSH PRIVILEGES 僅在直接操作 mysql.* 系統表後需要；GRANT/REVOKE/CREATE USER 自動生效。- 8.0
caching_sha2_password 默認插件，舊版驅動 (JDBC <8.0.13, PHP mysqlnd <7.4) 連接失敗，需 ALTER USER ... IDENTIFIED WITH mysql_native_password BY 'pass'; 或升級驅動。
REVOKE 不會級聯撤銷被授權者再授權的權限，需手動遞歸清理。
將用戶管理納入 **IaC (Terraform/Ansible) 代碼化**，所有賬號變更走 PR 流程，實現權限狀態「版本可控、審計可追、回滾可及」。
