MySQL 用戶由 用戶名@主機 唯一標識,同名不同主機視為不同用戶。權限分為全局級、庫級、表級、列級四個層級。
-- MySQL 5.7/8.0 標準語法
CREATE USER 'app_user'@'192.168.1.%' IDENTIFIED BY 'StrongPass@123';
CREATE USER 'readonly'@'%' IDENTIFIED BY 'ReadOnly@456' REQUIRE SSL; -- 強制 SSL
-- 指定認證插件 (MySQL 8.0 默認 caching_sha2_password)
CREATE USER 'legacy_app'@'%' IDENTIFIED WITH mysql_native_password BY 'LegacyPass@789';
-- 所有用戶
SELECT User, Host, plugin FROM mysql.user;
-- 特定用戶權限
SHOW GRANTS FOR 'app_user'@'192.168.1.%';
-- 當前用戶權限
SHOW GRANTS;
-- 8.0 推薦
ALTER USER 'app_user'@'192.168.1.%' IDENTIFIED BY 'NewPass@123';
-- 5.7 兼容
SET PASSWORD FOR 'app_user'@'192.168.1.%' = 'NewPass@123';
-- 當前用戶自改
SET PASSWORD = 'NewPass@123';
DROP USER 'app_user'@'192.168.1.%';
DROP USER 'readonly'@'%';
| 類別 | 權限 | 說明 |
|---|---|---|
| 數據操作 (DML) | SELECT | 查詢數據 |
| INSERT | 插入數據 | |
| UPDATE | 更新數據 | |
| DELETE | 刪除數據 | |
| 結構操作 (DDL) | CREATE | 創建表/索引/視圖 |
| DROP | 刪除表/視圖 | |
| ALTER | 修改表結構 | |
| INDEX | 創建/刪除索引 | |
| REFERENCES | 外鍵引用 | |
| 管理權限 | PROCESS | 查看進程 SHOW PROCESSLIST |
| RELOAD | FLUSH 操作 | |
| SHUTDOWN | 關閉服務器 | |
| SUPER | 超級權限(繞過 read_only、KILL 線程、修改全局變量) | |
| 特殊 | ALL PRIVILEGES | 除 GRANT OPTION 外所有權限 |
| 特殊 | GRANT OPTION | 將自己的權限授予他人 |
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALTER, INDEX
ON `shop_db`.* TO 'shop_app'@'10.0.1.%' IDENTIFIED BY 'ShopApp@2024';
-- 等效於 (MySQL 8.0)
GRANT ALL ON `shop_db`.* TO 'shop_app'@'10.0.1.%' IDENTIFIED BY 'ShopApp@2024';
-- 注意:庫級 ALL 不包含 GRANT OPTION、SUPER 等管理權限
GRANT SELECT ON `shop_db`.* TO 'report_user'@'10.0.2.%' IDENTIFIED BY 'Report@2024';
-- 生產環境建議添加資源限制
GRANT SELECT ON `shop_db`.* TO 'report_user'@'10.0.2.%'
WITH MAX_QUERIES_PER_HOUR 1000 MAX_CONNECTIONS_PER_HOUR 50;
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER, INDEX, REFERENCES
ON `dev_%`.* TO 'developer'@'10.0.3.%' IDENTIFIED BY 'Dev@2024';
GRANT ALL ON *.* TO 'dba_admin'@'10.0.0.1' IDENTIFIED BY 'DbaAdmin@2024' WITH GRANT OPTION;
-- 僅允許跳板機 IP 訪問,必須配合 MFA
-- 回收特定權限
REVOKE DROP ON `shop_db`.* FROM 'shop_app'@'10.0.1.%';
-- 回收所有權限(保留用戶)
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'shop_app'@'10.0.1.%';
-- 審計:查找擁有危險權限的用戶
SELECT User, Host FROM mysql.user WHERE Super_priv = 'Y';
SELECT User, Host, Db FROM mysql.db WHERE Drop_priv = 'Y' OR Alter_priv = 'Y';
MAX_QUERIES_PER_HOUR 限流mysql.user、mysql.db、mysql.tables_priv 審計validate_password 插件強制複雜度、過期輪換-- 創建角色
CREATE ROLE 'app_readonly', 'app_readwrite', 'db_admin';
-- 角色授權
GRANT SELECT ON `shop_db`.* TO 'app_readonly';
GRANT SELECT, INSERT, UPDATE, DELETE ON `shop_db`.* TO 'app_readwrite';
GRANT ALL ON *.* TO 'db_admin' WITH GRANT OPTION;
-- 用戶授予角色
GRANT 'app_readwrite' TO 'shop_app'@'10.0.1.%';
GRANT 'app_readonly' TO 'report_user'@'10.0.2.%';
-- 設置默認角色 (登錄自動生效)
SET DEFAULT ROLE 'app_readwrite' TO 'shop_app'@'10.0.1.%';
-- 會話中切換角色
SET ROLE 'app_readonly';