MySQL 5.7 安全特性深度解析：賬號管理、密碼策略、SSL、SQL Mode

MySQL 5.7 安全特性深度解析：賬號管理、密碼策略、SSL、SQL Mode

MySQL 5.7 在安全體系上進行了重大重構：賬號創建與授權分離、密碼過期/鎖定策略、代理用戶、默認 SSL 加密、嚴格 SQL Mode、移除測試庫、初始化隨機密碼。本文逐項解析配置方法、實戰場景與避坑指南。

一、賬號管理重構：創建與授權分離

1.1 棄用 GRANT 創建用戶

# 5.7 前 (棄用，報 Warning)
GRANT ALL ON *.* TO 'dxy'@'localhost' IDENTIFIED BY '123456';
# Warning: Using GRANT for creating new user is deprecated...

1.2 標準兩步走：CREATE USER + GRANT

-- 步驟1: 創建用戶 (認證插件、密碼策略、鎖定、過期均在這裡控制)
CREATE USER 'dxy'@'localhost' IDENTIFIED BY '123456';
-- 等同於指定默認插件
CREATE USER 'dxy'@'localhost' IDENTIFIED WITH 'mysql_native_password' BY '123456';

-- 使用哈希值創建 (避免明文傳輸)
CREATE USER 'dxy'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9';

-- 步驟2: 授權
GRANT SELECT, INSERT, UPDATE, DELETE ON dba_test.* TO 'dxy'@'localhost';

1.3 管理員賬號權限全集

-- 查看默認 root 權限
SHOW GRANTS FOR 'root'@'localhost';
-- 輸出兩條:
-- GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION
-- GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION

-- 創建新管理員
CREATE USER 'dba'@'127.0.0.1' IDENTIFIED BY '123456';
GRANT ALL PRIVILEGES ON *.* TO 'dba'@'127.0.0.1' WITH GRANT OPTION;
GRANT PROXY ON ''@'' TO 'dba'@'127.0.0.1' WITH GRANT OPTION;  -- 代理用戶授權需此權限

1.4 常用查看命令

-- 查看權限
SHOW GRANTS FOR 'dxy'@'localhost';

-- 查看創建語句 (含哈希密碼、過期策略、鎖定狀態)
SHOW CREATE USER 'dxy'@'localhost';
-- CREATE USER 'dxy'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*HASH' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK

二、密碼過期策略：強制定期輪換

適用場景：合規要求、定期輪換、洩露風險降低、程序強制斷開重連獲取新密碼。

2.1 策略選項

2.2 實戰操作

-- 鎖定賬號 (拒絕所有連接)
ALTER USER 'dxy'@'localhost' ACCOUNT LOCK;
-- 登錄報錯: ERROR 3118 (HY000): Access denied for user 'dxy'@'localhost'. Account is locked.

-- 解鎖恢復
ALTER USER 'dxy'@'localhost' ACCOUNT UNLOCK;

四、代理用戶：權限委派與審計溯源

場景：連接池用戶代理業務用戶、審計需區分真實操作者、多租戶權限復用。

4.1 前置條件

-- 1. 原始賬號 (擁有實際權限)
CREATE USER 'dxy'@'127.0.0.1' IDENTIFIED BY '123456';
GRANT ALL ON test.* TO 'dxy'@'127.0.0.1';

-- 2. 代理賬號 (應用連接池使用)
CREATE USER 'dxy_proxy'@'127.0.0.1' IDENTIFIED BY '123456';

-- 3. 授予代理權限
GRANT PROXY ON 'dxy'@'127.0.0.1' TO 'dxy_proxy'@'127.0.0.1';

-- 4. 驗證
-- 代理賬號登錄後:
SELECT USER(), CURRENT_USER();
-- USER() = 'dxy_proxy@127.0.0.1' (連接用戶)
-- CURRENT_USER() = 'dxy@127.0.0.1' (生效權限用戶)

SHOW GRANTS;  -- 顯示 dxy 的權限
-- 實際操作權限 = dxy 的權限

五、其他賬號限制選項

# 一鍵生成 SSL/RSA 文件 (數據目錄下生成 *.pem)
mysql_ssl_rsa_setup

# 生成文件:
# ca.pem, ca-key.pem          -- CA 證書/私鑰
# server-cert.pem, server-key.pem  -- 服務端證書/私鑰
# client-cert.pem, client-key.pem  -- 客戶端證書/私鑰
# private_key.pem, public_key.pem  -- RSA 密鑰對 (用於 caching_sha2_password)

# 默認連接即使用 SSL 加密
mysql -u dba -p -h 127.0.0.1
\s
-- SSL: Cipher in use is DHE-RSA-AES256-SHA

# 強制 SSL 登錄 (創建用戶時 REQUIRE SSL)
CREATE USER 'ssl_user'@'%' IDENTIFIED BY 'pwd' REQUIRE SSL;

性能提示： SSL 加密傳輸約帶來 20-25% CPU 開銷，內網高並發場景權衡啟用。

6.3 初始化方式變更：mysqld --initialize

SELECT @@sql_mode;
-- ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,
-- ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

7.2 核心模式解讀與影響

7.3 主從/遷移一致性要求

• 主從雙端 SQL Mode 必須一致，否則復制中斷 (如主庫允許零日期、從庫嚴格模式報錯)
• 數據遷移前對齊 SQL Mode，或使用兼容模式導入
• 建議：開發規範使用標準 SQL，不依賴寬鬆模式行為

八、總結：5.7 安全清單