安全加固

CREATE USER 'app'@'10.%' IDENTIFIED BY 'strong-random-32-char';
GRANT SELECT, INSERT, UPDATE, DELETE ON app.* TO 'app'@'10.%';
-- 不给 DROP / ALTER / GRANT / SUPER

CREATE USER 'readonly'@'%' IDENTIFIED BY 'xxx';
GRANT SELECT ON app.* TO 'readonly'@'%';
-- 也可以加 SHOW VIEW

CREATE USER 'backup'@'localhost' IDENTIFIED BY 'xxx';
GRANT RELOAD, LOCK TABLES, PROCESS, REPLICATION CLIENT, SHOW VIEW, EVENT, TRIGGER ON *.* TO 'backup'@'localhost';
GRANT BACKUP_ADMIN ON *.* TO 'backup'@'localhost';

CREATE USER 'dba'@'10.0.0.5' IDENTIFIED BY 'xxx';
GRANT ALL PRIVILEGES ON *.* TO 'dba'@'10.0.0.5' WITH GRANT OPTION;

CREATE USER 'exporter'@'localhost' IDENTIFIED BY 'xxx';
GRANT PROCESS, REPLICATION CLIENT, SELECT ON performance_schema.* TO 'exporter'@'localhost';
GRANT SLAVE MONITOR ON *.* TO 'exporter'@'localhost';

CREATE USER 'ai_audit'@'%' IDENTIFIED BY 'xxx';
GRANT SELECT, INSERT ON audit.* TO 'ai_audit'@'%';

ALTER USER 'app'@'10.%' REQUIRE SSL;

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

SHOW STATUS LIKE 'Ssl_cipher';
-- 输出非空就是 TLS 连接

mysql -h host -u app -p --ssl --ssl-ca=/path/to/ca.pem

# 生成 CA
openssl genrsa 4096 > ca.key
openssl req -x509 -days 3650 -nodes -key ca.key -out ca.pem -subj "/CN=MariaDB CA"

# server cert
openssl req -newkey rsa:2048 -nodes -keyout server.key -out server.csr -subj "/CN=db.example.com"
openssl x509 -req -days 365 -in server.csr -CA ca.pem -CAkey ca.key -CAcreateserial -out server.pem

INSTALL SONAME 'server_audit';
SET GLOBAL server_audit_logging = ON;
SET GLOBAL server_audit_events = 'CONNECT,QUERY_DDL,QUERY_DML,QUERY_DCL';
SET GLOBAL server_audit_file_path = '/var/log/mariadb/audit.log';
SET GLOBAL server_audit_file_rotate_size = 100M;
SET GLOBAL server_audit_file_rotations = 100;

20260518 12:00:00,host,app,10.0.0.5,123,12345,QUERY,app,'SELECT * FROM users',0

SET GLOBAL general_log = ON;
SET GLOBAL general_log_file = '/var/log/mariadb/general.log';

slow_query_log = 1
slow_query_log_file = /var/log/mariadb/slow.log
long_query_time = 0.5
log_queries_not_using_indexes = 1

[mariadb]
plugin_load_add = file_key_management
file_key_management_filename = /etc/mysql/keyfile
file_key_management_encryption_algorithm = AES_CTR

innodb_encrypt_tables = ON
innodb_encrypt_log = ON
innodb_encryption_threads = 4

1;DEADBEEFDEADBEEFDEADBEEFDEADBEEFDEADBEEFDEADBEEFDEADBEEFDEADBEEF
2;BEEFDEADBEEFDEADBEEFDEADBEEFDEADBEEFDEADBEEFDEADBEEFDEADBEEFDEAD

INSERT INTO users (email, ssn_enc) VALUES (?, AES_ENCRYPT(?, @key));
SELECT AES_DECRYPT(ssn_enc, @key) FROM users;

CREATE SQL SECURITY DEFINER VIEW users_masked AS
SELECT
  id,
  CONCAT(LEFT(name, 1), '***') AS name,
  CONCAT(LEFT(email, 3), '***@', SUBSTRING_INDEX(email, '@', -1)) AS email,
  '***' AS phone
FROM users;

GRANT SELECT ON app.users_masked TO 'support'@'%';

[mariadb]
bind-address = 127.0.0.1     # 只本机访问
# 或
bind-address = 10.0.0.10     # 只内网某 IP

# iptables
iptables -A INPUT -p tcp --dport 3306 -s 10.0.0.0/8 -j ACCEPT
iptables -A INPUT -p tcp --dport 3306 -j DROP

# ufw
ufw allow from 10.0.0.0/8 to any port 3306

// ❌
db.query(`SELECT * FROM users WHERE email = '${email}'`);

// ✅
db.query('SELECT * FROM users WHERE email = ?', [email]);

INSTALL SONAME 'simple_password_check';
SET GLOBAL simple_password_check_minimal_length = 12;
SET GLOBAL simple_password_check_digits = 1;
SET GLOBAL simple_password_check_letters_same_case = 1;
SET GLOBAL simple_password_check_other_characters = 1;

-- 用户密码定期过期
ALTER USER 'app'@'%' PASSWORD EXPIRE INTERVAL 90 DAY;

-- 限连接尝试
INSTALL PLUGIN MAX_FAILED_LOGIN_ATTEMPTS SONAME 'auth_plugin_user_lock.so';
ALTER USER 'app'@'%' FAILED_LOGIN_ATTEMPTS 5 PASSWORD_LOCK_TIME 1;

chmod 700 /var/lib/mysql
chown -R mysql:mysql /var/lib/mysql

chmod 600 /etc/mysql/keyfile /etc/mysql/my.cnf

mariabackup --backup --target-dir=/backup -uroot | \
  openssl enc -aes-256-cbc -salt -k "$BACKUP_PASS" -out /backup/encrypted.bin