MySQL資料庫安全加固實戰：8步鎖死你嘅Database Server

# Ubuntu/Debian 安裝
sudo apt update && sudo apt install mysql-server -y

# 即刻執行安全安裝精靈
sudo mysql_secure_installation

-- 檢查插件狀態
SHOW VARIABLES LIKE 'validate_password%';

-- 安裝插件（如果未啟用）
INSTALL PLUGIN validate_password SONAME 'validate_password.so';

-- 設定嚴格密碼政策
SET GLOBAL validate_password.policy = STRONG;
SET GLOBAL validate_password.length = 14;
SET GLOBAL validate_password.mixed_case_count = 1;
SET GLOBAL validate_password.number_count = 1;
SET GLOBAL validate_password.special_char_count = 1;

-- 建立專用 application user（只限特定 database）
CREATE USER 'app_user'@'192.168.1.%' IDENTIFIED BY 'StrongP@ssw0rd2026!';
GRANT SELECT, INSERT, UPDATE, DELETE ON myapp_db.* TO 'app_user'@'192.168.1.%';

-- 建立 read-only reporting user
CREATE USER 'report_user'@'192.168.1.%' IDENTIFIED BY 'An0therStr0ng!P@ss';
GRANT SELECT ON myapp_db.* TO 'report_user'@'192.168.1.%';

-- 建立 backup user（只需要 SELECT + LOCK TABLES）
CREATE USER 'backup_user'@'localhost' IDENTIFIED BY 'B@ckupStr0ngP@ss!';
GRANT SELECT, LOCK TABLES ON *.* TO 'backup_user'@'localhost';

FLUSH PRIVILEGES;

# 生成 self-signed cert（production 用正式 CA）
sudo mkdir /etc/mysql/ssl
cd /etc/mysql/ssl
sudo openssl genrsa 2048 > ca-key.pem
sudo openssl req -new -x509 -nodes -days 3650 -key ca-key.pem -out ca-cert.pem
sudo openssl req -newkey rsa:2048 -nodes -keyout server-key.pem -out server-req.pem
sudo openssl rsa -in server-key.pem -out server-key.pem
sudo openssl x509 -req -in server-req.pem -days 3650 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 -out server-cert.pem

[mysqld]
ssl_ca = /etc/mysql/ssl/ca-cert.pem
ssl_cert = /etc/mysql/ssl/server-cert.pem
ssl_key = /etc/mysql/ssl/server-key.pem
require_secure_transport = ON

sudo systemctl restart mysql

# 驗證 SSL 已啟用
mysql -u root -p -e "SHOW VARIABLES LIKE '%ssl%';"

-- 啟用 general query log（debug 用，production 小心 performance）
SET GLOBAL general_log = 'ON';
SET GLOBAL general_log_file = '/var/log/mysql/general.log';

-- 啟用 slow query log（偵測異常 query）
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 2;
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';

-- 啟用 error log 詳細模式
SET GLOBAL log_error_verbosity = 3;

# 下載 Percona Audit Plugin
wget https://downloads.percona.com/downloads/Percona-Audit-Log-Plugin/...
# 安裝後喺 my.cnf 加入
# plugin-load-add=audit_log.so
# audit_log_format=JSON
# audit_log_policy=ALL

# 1. Bind 去 localhost（如果 app 同 db 同一部機）
# 喺 mysqld.cnf: bind-address = 127.0.0.1

# 2. 用 firewall 限制來源 IP
sudo ufw allow from 192.168.1.0/24 to any port 3306
sudo ufw deny 3306  # block 所有其他來源

# 3. 考慮改 default port（security by obscurity，輔助性質）
# port = 3307

# mysqldump with SSL
mysqldump --ssl-mode=VERIFY_IDENTITY \
  --ssl-ca=/etc/mysql/ssl/ca-cert.pem \
  -u backup_user -p \
  --all-databases --single-transaction --routines --triggers \
  | gzip | openssl enc -aes-256-cbc -salt -out backup_$(date +%Y%m%d).sql.gz.enc

# Check current version
mysql --version

# Ubuntu/Debian 更新
sudo apt update && sudo apt upgrade mysql-server -y

# 用 Lynis 做 security audit
sudo lynis audit system --tests-from-group databases

# 用 nmap 掃自己 database port
nmap -sV -p 3306 localhost