安全管理

用户管理

创建用户

CREATE USER 'username'@'host' IDENTIFIED BY 'password';
CREATE USER 'admin'@'localhost' IDENTIFIED BY 'Admin@123';
CREATE USER 'app'@'%' IDENTIFIED BY 'App@123';

查看用户

SELECT user, host FROM mysql.user;

修改密码

ALTER USER 'username'@'host' IDENTIFIED BY 'new_password';
SET PASSWORD FOR 'username'@'host' = PASSWORD('new_password');

删除用户

DROP USER 'username'@'host';

用户锁定/解锁

ALTER USER 'username'@'host' ACCOUNT LOCK;
ALTER USER 'username'@'host' ACCOUNT UNLOCK;

权限管理

授予权限

GRANT ALL PRIVILEGES ON *.* TO 'admin'@'localhost';
GRANT SELECT, INSERT, UPDATE ON mydb.* TO 'app'@'%';
GRANT SELECT ON mydb.users TO 'readonly'@'%';
GRANT ALL PRIVILEGES ON mydb.* TO 'app'@'%' WITH GRANT OPTION;

查看权限

SHOW GRANTS FOR 'username'@'host';
SHOW GRANTS FOR CURRENT_USER();

撤销权限

REVOKE ALL PRIVILEGES ON *.* FROM 'admin'@'localhost';
REVOKE INSERT, UPDATE ON mydb.* FROM 'app'@'%';

刷新权限

FLUSH PRIVILEGES;

权限类型

权限	说明
ALL	所有权限
SELECT	查询权限
INSERT	插入权限
UPDATE	更新权限
DELETE	删除权限
CREATE	创建权限
DROP	删除权限
ALTER	修改权限
INDEX	索引权限
EXECUTE	执行存储过程权限
GRANT OPTION	授权权限

密码策略

查看密码策略

SHOW VARIABLES LIKE 'validate_password%';

设置密码策略

[mysqld]
validate_password_policy=MEDIUM
validate_password_length=8
validate_password_mixed_case_count=1
validate_password_number_count=1
validate_password_special_char_count=1

密码策略级别

级别	说明
LOW	只检查长度
MEDIUM	检查长度、数字、大小写、特殊字符
STRONG	额外检查字典文件

连接安全

SSL 连接

查看 SSL 配置

SHOW VARIABLES LIKE '%ssl%';

创建 SSL 证书

mysql_ssl_rsa_setup --datadir=/var/lib/mysql

强制 SSL 连接

CREATE USER 'secure_user'@'%' IDENTIFIED BY 'password' REQUIRE SSL;
ALTER USER 'secure_user'@'%' REQUIRE SSL;

SSL 连接

mysql -u secure_user -p --ssl-mode=REQUIRED

主机名验证

CREATE USER 'user'@'%.example.com' IDENTIFIED BY 'password';

审计日志

开启审计日志

[mysqld]
plugin-load=audit_log.so
audit_log_format=JSON
audit_log_policy=ALL

查看审计日志

SHOW VARIABLES LIKE 'audit_log%';

审计日志过滤

INSTALL PLUGIN audit_log SONAME 'audit_log.so';
SELECT audit_log_filter_set_filter('log_all', '{"filter": {"log": true}}');
SELECT audit_log_filter_set_user('%', 'log_all');

数据加密

透明数据加密 (TDE)

INSTALL PLUGIN keyring_file SONAME 'keyring_file.so';

CREATE TABLE encrypted_table (
    id INT PRIMARY KEY,
    data VARCHAR(100)
) ENCRYPTION='Y';

列加密

CREATE TABLE sensitive_data (
    id INT PRIMARY KEY,
    credit_card VARBINARY(255)
);

INSERT INTO sensitive_data VALUES (1, AES_ENCRYPT('1234567890123456', 'secret_key'));

SELECT AES_DECRYPT(credit_card, 'secret_key') FROM sensitive_data;

加密函数

SELECT MD5('password');
SELECT SHA1('password');
SELECT SHA2('password', 256);
SELECT AES_ENCRYPT('data', 'key');
SELECT AES_DECRYPT(encrypted_data, 'key');

SQL 注入防护

使用参数化查询

cursor.execute("SELECT * FROM users WHERE username = %s", (username,))

使用预处理语句

PREPARE stmt FROM 'SELECT * FROM users WHERE username = ?';
SET @username = '张三';
EXECUTE stmt USING @username;
DEALLOCATE PREPARE stmt;

输入验证

SELECT * FROM users WHERE id = CAST(? AS UNSIGNED);

安全配置

基本安全配置

[mysqld]
skip-name-resolve
local_infile=OFF
secure_file_priv=/var/lib/mysql-files

禁用危险功能

[mysqld]
skip-show-database
skip-grant-tables

网络安全

[mysqld]
bind-address=127.0.0.1
port=3306

安全检查清单

1. 用户权限

• [ ] 删除匿名用户
• [ ] 删除测试数据库
• [ ] 限制 root 远程登录
• [ ] 使用最小权限原则

2. 密码安全

• [ ] 设置强密码策略
• [ ] 定期更换密码
• [ ] 禁用空密码用户

3. 网络安全

• [ ] 使用防火墙限制访问
• [ ] 启用 SSL 连接
• [ ] 限制绑定地址

4. 数据安全

• [ ] 定期备份
• [ ] 加密敏感数据
• [ ] 启用审计日志

5. 系统安全

• [ ] 更新 MySQL 版本
• [ ] 限制文件访问权限
• [ ] 监控异常活动

安全脚本

DELETE FROM mysql.user WHERE User='';
DELETE FROM mysql.user WHERE User='root' AND Host NOT IN ('localhost', '127.0.0.1', '::1');
DROP DATABASE IF EXISTS test;
DELETE FROM mysql.db WHERE Db='test' OR Db='test\\_%';
FLUSH PRIVILEGES;

下一步学习

• 主从复制 - 掌握主从复制