萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

MySQL 用戶創建與權限授權實戰:從入門到最小權限原則最佳實踐

用戶體系核心概念

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
RELOADFLUSH 操作
SHUTDOWN關閉服務器
SUPER超級權限(繞過 read_only、KILL 線程、修改全局變量)
特殊ALL PRIVILEGES除 GRANT OPTION 外所有權限
特殊GRANT OPTION將自己的權限授予他人

授權實戰示例

場景 1:應用讀寫賬號(單庫)

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 等管理權限

場景 2:只讀報表賬號

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;

場景 3:開發人員權限(多庫、無 DROP)

GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER, INDEX, REFERENCES
ON `dev_%`.* TO 'developer'@'10.0.3.%' IDENTIFIED BY 'Dev@2024';

場景 4:DBA 管理賬號

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';

最小權限原則落地清單

  1. ✅ 業務應用賬號:僅授單庫 DML + 必需 DDL,禁 SUPER、SHUTDOWN、FILE
  2. ✅ 只讀賬號:僅 SELECT,添加 MAX_QUERIES_PER_HOUR 限流
  3. ✅ 遷移/同步賬號:REPLICATION SLAVE + REPLICATION CLIENT + SELECT
  4. ✅ 監控賬號:PROCESS + REPLICATION CLIENT + SELECT ON performance_schema.*
  5. ✅ 定期審計:每季度導出 mysql.user、mysql.db、mysql.tables_priv 審計
  6. ✅ 密碼策略:validate_password 插件強制複雜度、過期輪換
  7. ✅ 主機限制:禁止 '%' 通配符,精確到應用服務器 IP 段

MySQL 8.0 新特性:角色 (Role) 管理

-- 創建角色
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';
copyright © 萬盛學電腦網 all rights reserved