MySQL 資料庫防破解指南：從傳輸加密到權限最小化的多層防禦體系

資料庫是核心資產集中地，面對掃描器、暴力破解、SQL 注入、內部洩露等多重威脅，單一防護措施遠遠不夠。本文基於 MySQL 官方安全建議，構建「傳輸加密、認證強化、權限最小化、審計追蹤、參數硬化」五層防禦體系，提供可直接落地的配置清單與腳本。

第一層：傳輸通道加密 — 讓竊聽者看到亂碼

SSL/TLS 配置 (MySQL 5.7+ 原生支援)

[mysqld]
require_secure_transport = ON          # 強制所有連線必須加密
ssl_ca = /etc/mysql/ssl/ca.pem
ssl_cert = /etc/mysql/ssl/server-cert.pem
ssl_key = /etc/mysql/ssl/server-key.pem

客戶端驗證

# 憑證驗證模式
mysql -h db.example.com -u app_user -p --ssl-mode=VERIFY_CA --ssl-ca=/path/ca.pem
mysql -h db.example.com -u app_user -p --ssl-mode=VERIFY_IDENTITY --ssl-ca=/path/ca.pem

SSH 隧道替代方案 (舊版/無 SSL 環境)

# 應用伺服器建立隧道
ssh -L 13306:localhost:3306 -N -f db_user@db_server
# 應用連線 127.0.0.1:13306 實際經加密通道到達資料庫

第二層：認證與授權鐵律 — 堵住暴力破解與提權路徑

1. 全帳號強密碼 + 定期輪換

[mysqld]
validate_password_policy = STRONG
validate_password_length = 16
validate_password_mixed_case_count = 1
validate_password_number_count = 1
validate_password_special_char_count = 1

2. 移除匿名帳號與遠端 root

DELETE FROM mysql.user WHERE User = '';
DELETE FROM mysql.user WHERE User = 'root' AND Host != 'localhost';
FLUSH PRIVILEGES;

3. 應用帳號最小權限 + IP 白名單

CREATE USER 'app_rw'@'10.0.1.%' IDENTIFIED BY 'StrongPass@2024';
GRANT SELECT, INSERT, UPDATE, DELETE ON `biz_db`.* TO 'app_rw'@'10.0.1.%';
-- 不授予：DROP, ALTER, GRANT, FILE, SUPER, PROCESS, SHUTDOWN, CREATE USER

4. 限制單帳號最大連線數

GRANT USAGE ON *.* TO 'app_rw'@'10.0.1.%' WITH MAX_USER_CONNECTIONS 50;
-- 全域備用
SET GLOBAL max_user_connections = 200;

5. 資源限制擴充

GRANT USAGE ON *.* TO 'app_rw'@'10.0.1.%' 
    WITH MAX_QUERIES_PER_HOUR 50000
         MAX_UPDATES_PER_HOUR 20000
         MAX_CONNECTIONS_PER_HOUR 1000;

第三層：SQL 注入與輸入驗證 — 應用層第一道防線

參數化查詢 — 唯一根治

-- ❌ 字串拼接 (極度危險)
"SELECT * FROM users WHERE name = '" + input_name + "'"

-- ✅ 預處理語句 (所有主流驅動支援)
PreparedStatement ps = conn.prepareStatement("SELECT * FROM users WHERE name = ?");
ps.setString(1, input_name);

輸入驗證三重門

1. 前端：格式/長度/字元集攔截 (用戶體驗優化，非安全邊界)
2. 後端業務層：白名單驗證、型別轉換、長度截斷、XSS 過濾
3. 資料庫層：欄位型別約束 (VARCHAR(32))、CHECK 約束 (8.0.16+)、觸發器攔截

第四層：危險權限隔離 — 即使被駭也限制損害範圍

第五層：系統參數硬化與審計 — 減少攻擊面、留存證據

關鍵安全參數

[mysqld]
# 網路
skip_name_resolve = 1              # 禁用 DNS 反查，加速連線、避免 DNS 劫持
bind_address = 10.0.0.10           # 僅綁定內網 IP，不監聽 0.0.0.0

# 檔案存取
local_infile = 0                   # 禁用 LOAD DATA LOCAL (客戶端任意讀檔)
secure_file_priv = /var/lib/mysql-files/  # 限制 LOAD DATA/SELECT INTO OUTFILE 目錄

# 資訊洩露
skip_show_database = 1             # 非 SUPER 權限隱藏 SHOW DATABASES

# 符號連結
skip_symbolic_links = ON           # 禁用符號連結繞過目錄權限

# 審計 (需安裝 audit_log 外掛)
plugin_load_add = audit_log.so
audit_log_policy = ALL
audit_log_format = JSON
audit_log_rotate_on_size = 100M
audit_log_rotations = 10

tcpdump 實戰審計：發現明文傳輸/異常查詢

# 捕獲 3306 流量提取可讀字串
tcpdump -i eth0 -nn -s 0 -A 'port 3306' | strings | grep -i -E 'select|insert|update|delete|password|passwd'

# 完整封包存檔離線分析
tcpdump -i eth0 -w /tmp/mysql_audit_$(date +%F).pcap port 3306
# Wireshark 開啟 → Follow TCP Stream → 還原 SQL 全貌

緊急應急：Root 密碼遺失/洩露重置流程

# 1. 停止服務
systemctl stop mysqld

# 2. 跳過授權表 + 禁用網路 (防入侵)
mysqld_safe --skip-grant-tables --skip-networking &

# 3. 無密碼登入重設
mysql -u root mysql
UPDATE user SET authentication_string = PASSWORD('NewStrongPass@2024') WHERE User = 'root';
FLUSH PRIVILEGES;

# 4. 清理匿名/遠端 root
DELETE FROM mysql.user WHERE User = '';
DELETE FROM mysql.user WHERE User = 'root' AND Host != 'localhost';
FLUSH PRIVILEGES;

# 5. 正常重啟
pkill mysqld_safe
systemctl start mysqld

# 6. 驗證
mysql -u root -p'NewStrongPass@2024' -e "SELECT USER();"

安全基線自動化檢查腳本

#!/bin/bash
# mysql_security_audit.sh
echo "=== MySQL Security Baseline Check $(date) ==="
mysql -u root -p"$ROOT_PASS" -e "
-- 1. 匿名帳號
SELECT 'Anonymous Users' AS check_item, COUNT(*) AS count FROM mysql.user WHERE User='';
-- 2. 遠端 root
SELECT 'Remote Root' AS check_item, COUNT(*) AS count FROM mysql.user WHERE User='root' AND Host!='localhost';
-- 3. 弱密碼 (簡易檢查: 密碼長度<12 或 無特殊字元)
SELECT 'Weak Password Hash' AS check_item, User, Host FROM mysql.user WHERE LENGTH(Password) < 41;
-- 4. 危險權限持有者
SELECT 'FILE Priv' AS check_item, User, Host FROM mysql.user WHERE File_priv='Y';
SELECT 'SUPER Priv' AS check_item, User, Host FROM mysql.user WHERE Super_priv='Y';
SELECT 'GRANT Priv' AS check_item, User, Host FROM mysql.user WHERE Grant_priv='Y';
-- 5. SSL 狀態
SHOW VARIABLES LIKE 'have_ssl';
SHOW VARIABLES LIKE 'require_secure_transport';
"
echo "=== Check Complete ==="

總結

MySQL 防破解非單點防護，而是**傳輸加密阻斷竊聽、強認證阻擋暴力破解、最小權限限制橫向移動、參數化查詢根治注入、參數硬化收斂攻擊面、審計日誌留存事後追溯**六位一體。建議將上述配置納入基礎設施即代碼，新庫上線必跑基線腳本，季度滾動審計，構建持續安全運營閉環。