安全加固

MySQL 安全加固完全指南

安全是数据库运维的基础。本指南涵盖账户管理、权限控制、加密、审计日志和威胁防御策略。

1. 账户管理与认证

创建用户和密码策略：

基本用户创建:
CREATE USER 'app_user'@'%' IDENTIFIED BY 'strong_password';

-- MySQL 8.0+: 使用安全的哈希方式
CREATE USER 'app_user'@'%'
  IDENTIFIED WITH caching_sha2_password BY 'password';

强制密码策略:
SET GLOBAL validate_password.policy = 'STRONG';
SET GLOBAL validate_password.length = 12;

密码过期管理:
-- 180天过期
ALTER USER 'app_user'@'%' PASSWORD EXPIRE INTERVAL 180 DAY;

-- 强制下次登录时修改
ALTER USER 'app_user'@'%' PASSWORD EXPIRE;

失败登录限制:
SET GLOBAL connection_control_failed_connections_threshold = 3;
SET GLOBAL connection_control_min_connection_delay = 5000;  -- 5秒延迟

账户锁定:
-- 锁定账户（无法登录）
ALTER USER 'app_user'@'%' ACCOUNT LOCK;

-- 解锁账户
ALTER USER 'app_user'@'%' ACCOUNT UNLOCK;

用户查看和管理:

查看所有用户
SELECT user, host, authentication_string FROM mysql.user;

查看用户权限
SHOW GRANTS FOR 'app_user'@'%';

删除用户
DROP USER 'old_user'@'%';

权限管理（最小权限原则）:

应用程序用户：仅SELECT、INSERT、UPDATE、DELETE
CREATE USER 'app'@'10.0.1.%' IDENTIFIED BY 'password';
GRANT SELECT, INSERT, UPDATE, DELETE ON mydb.* TO 'app'@'10.0.1.%';

备份用户：仅SELECT
CREATE USER 'backup'@'localhost' IDENTIFIED BY 'password';
GRANT SELECT ON *.* TO 'backup'@'localhost';

分析用户：仅SELECT报表库
CREATE USER 'analyst'@'%' IDENTIFIED BY 'password';
GRANT SELECT ON analytics_db.* TO 'analyst'@'%';

DBA账户：完全权限（仅本地）
CREATE USER 'dba'@'localhost' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON *.* TO 'dba'@'localhost' WITH GRANT OPTION;

撤销权限:
REVOKE SUPER ON *.* FROM 'app_user'@'%';
REVOKE FILE ON *.* FROM 'app_user'@'%';

2. 网络安全与加密

网络隔离:

仅允许应用服务器连接：
CREATE USER 'app'@'10.0.1.50' IDENTIFIED BY 'password';

仅本地连接：
CREATE USER 'admin'@'localhost' IDENTIFIED BY 'password';

防火墙规则:
# 仅允许来自应用子网的连接
sudo iptables -A INPUT -p tcp --dport 3306 -s 10.0.1.0/24 -j ACCEPT
sudo iptables -A INPUT -p tcp --dport 3306 -j DROP

SSL/TLS加密:

要求SSL连接:
CREATE USER 'secure_user'@'%'
  IDENTIFIED BY 'password'
  REQUIRE SSL;

配置服务器SSL:
[mysqld]
ssl-ca = /etc/mysql/ca.pem
ssl-cert = /etc/mysql/server-cert.pem
ssl-key = /etc/mysql/server-key.pem
tls-version = 'TLSv1.2,TLSv1.3'

验证连接是否加密:
SHOW STATUS LIKE 'Ssl_cipher';
# 如果有值表示已加密

复制连接加密:
CHANGE REPLICATION SOURCE TO
  MASTER_HOST = 'master.example.com',
  MASTER_USER = 'replication',
  MASTER_PASSWORD = 'password',
  MASTER_SSL = 1,
  MASTER_SSL_CA = '/path/to/ca.pem';

3. 数据加密和审计日志

透明数据加密（TDE）:

为新表启用加密:
CREATE TABLE sensitive_data (
  id INT PRIMARY KEY,
  ssn VARCHAR(11)
) ENCRYPTION='Y';

检查加密状态:
SELECT TABLESPACE_NAME, ENCRYPTION
FROM INFORMATION_SCHEMA.INNODB_TABLESPACES;

审计日志（生产推荐）:

启用审计插件:
[mysqld]
plugin-load-add = audit_log.so
audit_log_events = CONNECT,QUERY_DDL,QUERY_DML

查看审计日志:
SHOW VARIABLES LIKE 'audit_log_file';

常见安全漏洞:

SQL注入：使用参数化查询
✅ PREPARE stmt FROM 'SELECT * FROM users WHERE id = ?';
❌ "SELECT * FROM users WHERE id = " + userId;

弱密码：强制复杂度 + 失败限制 + 定期更改

无限权限：移除不必要的SUPER、FILE权限

未加密连接：启用require_secure_transport = ON

匿名用户：删除所有匿名账户
DROP USER ''@'%';

安全配置文件:

[mysqld]
default-authentication-plugin = caching_sha2_password
require_secure_transport = ON
skip-name-resolve
bind-address = 10.0.1.10
ssl-ca = /etc/mysql/ca.pem
ssl-cert = /etc/mysql/server-cert.pem
ssl-key = /etc/mysql/server-key.pem
secure_file_priv = /tmp
audit_log_events = CONNECT,QUERY_DDL,QUERY_DML

生产前安全检查清单

账户安全:
[ ] 修改root默认密码
[ ] 删除匿名账户
[ ] 删除test数据库
[ ] 审计所有用户 (< 10个)
[ ] 启用密码过期 (90天)
[ ] 锁定服务账户
[ ] 启用SSL/TLS

权限管理:
[ ] 实施角色基访问控制
[ ] 最小权限原则 (无SUPER给应用)
[ ] 审计GRANT/REVOKE变更
[ ] 移除FILE权限

网络安全:
[ ] 防火墙限制3306端口
[ ] 启用SSL远程连接
[ ] 配置firewall-d或iptables
[ ] 本地连接用Unix socket

数据保护:
[ ] 启用TDE加密
[ ] 备份加密（GPG）
[ ] 3份备份副本（现场+异地）
[ ] 定期测试恢复

监控审计:
[ ] 启用审计日志
[ ] 监控失败登录
[ ] 每月审查审计日志
[ ] 保留审计日志6个月+

总结:
安全不是一次性工作，而是持续实践。实施深度防御：强认证、最小权限、网络隔离、加密、审计日志和持续监控。定期安全审计和事件响应演练确保MySQL系统安全。

5. 常见漏洞与防范

SQL注入:
✅ 参数化查询（预处理语句）：
PREPARE stmt FROM 'SELECT * FROM users WHERE id = ?';
EXECUTE stmt USING @user_id;

❌ 字符串拼接（存在漏洞）：
"SELECT * FROM users WHERE id = " + userId;

弱密码:
✅ 强制强密码策略
SET GLOBAL validate_password.policy = 'STRONG';
SET GLOBAL validate_password.length = 12;

✅ 登录失败限制
SET GLOBAL connection_control_failed_connections_threshold = 3;
SET GLOBAL connection_control_min_connection_delay = 5000;

不必要的权限:
❌ 所有用户拥有SUPER权限
❌ 用户拥有FILE权限
❌ 用户拥有GRANT权限

✅ 审计权限
SELECT user, host, Super_priv, File_priv, Grant_priv
FROM mysql.user WHERE Super_priv='Y';

✅ 删除不必要权限
REVOKE SUPER ON *.* FROM 'app_user'@'%';
REVOKE FILE ON *.* FROM 'app_user'@'%';

匿名用户:
SELECT user, host FROM mysql.user WHERE user = '';
DROP USER ''@'localhost';
DROP USER ''@'%';

root无密码:
ALTER USER 'root'@'localhost' IDENTIFIED BY 'secure_password';

未加密复制:
CHANGE REPLICATION SOURCE TO
  MASTER_HOST='master.example.com',
  MASTER_SSL=1,
  MASTER_SSL_CA='/path/to/ca.pem',
  MASTER_SSL_CERT='/path/to/client-cert.pem',
  MASTER_SSL_KEY='/path/to/client-key.pem';

6. 安全监控

失败登录监控:
SELECT COUNT(*) as failed_logins FROM mysql.general_log
WHERE SQLTEXT LIKE '%Access denied%'
AND event_time > DATE_SUB(NOW(), INTERVAL 1 HOUR);
-- 1小时内超过10次则告警

权限提升监控:
SELECT SQLTEXT, event_time FROM mysql.audit_log
WHERE SQLTEXT LIKE '%GRANT%' OR SQLTEXT LIKE '%ALTER USER%'
ORDER BY event_time DESC LIMIT 20;

文件操作监控（应为空）:
SELECT EVENT_TIME, USER_HOST, SQLTEXT
FROM mysql.audit_log
WHERE SQLTEXT LIKE '%INTO OUTFILE%' OR SQLTEXT LIKE '%LOAD%INFILE%';

总结

安全不是一次性工作，而是持续实践。实施深度防御：强认证、最小权限、网络隔离、加密、审计日志和持续监控。定期安全审计和事件响应演练确保MySQL系统免受不断演变的威胁。