MySQL安全管理 - 当发网

MySQL安全概述

MySQL安全管理涉及用户认证、权限控制、数据加密、网络安全等多个方面，是保护数据库系统安全的重要措施。一个完善的安全策略应该涵盖从用户身份验证到数据传输加密的各个环节。

安全管理的目标

防止未授权访问 - 确保只有授权用户能够访问数据库
保护数据机密性 - 防止敏感数据泄露
确保数据完整性 - 防止数据被非法篡改
实现访问审计 - 记录用户操作以便追踪和审计
保障系统可用性 - 防止服务中断或拒绝服务攻击

MySQL安全架构

MySQL的安全架构分为多个层次，从连接层到存储层都有相应的安全机制：

网络层安全 - 使用SSL/TLS加密通信
连接层安全 - 用户身份验证和连接限制
权限层安全 - 基于角色的权限控制
数据层安全 - 数据加密和完整性保护

用户管理

用户管理是MySQL安全的基础，包括创建、修改和删除用户账户，以及设置用户认证方式。

创建用户

                        SQL示例
                    

                        -- 创建用户并设置密码
CREATE USER 'username'@'host' IDENTIFIED BY 'password';

-- 示例
CREATE USER 'app_user'@'localhost' IDENTIFIED BY 'secure_password123';
CREATE USER 'readonly_user'@'192.168.1.%' IDENTIFIED BY 'readonly_pass';

-- 创建用户并指定认证插件
CREATE USER 'secure_user'@'%' 
IDENTIFIED WITH caching_sha2_password BY 'password';

-- 创建用户并设置密码过期
CREATE USER 'temp_user'@'localhost' IDENTIFIED BY 'temp123' PASSWORD EXPIRE;
                    

修改用户密码

                        SQL示例
                    

                        -- MySQL 5.7.6及以上版本
ALTER USER 'username'@'host' IDENTIFIED BY 'new_password';

-- 旧版本语法
SET PASSWORD FOR 'username'@'host' = PASSWORD('new_password');

-- 修改当前用户密码
ALTER USER USER() IDENTIFIED BY 'new_password';

-- 设置密码过期策略
ALTER USER 'user'@'host' PASSWORD EXPIRE INTERVAL 90 DAY;
ALTER USER 'user'@'host' PASSWORD EXPIRE NEVER;
                    

密码重试策略（MySQL 8.0.19+）

                        SQL示例
                    

                        -- 创建用户时设置密码重试策略
CREATE USER 'user'@'host' IDENTIFIED BY 'password'
FAILED_LOGIN_ATTEMPTS 5 PASSWORD_LOCK_TIME 1;

-- 修改现有用户的密码重试策略
ALTER USER 'user'@'host' FAILED_LOGIN_ATTEMPTS 3 PASSWORD_LOCK_TIME UNBOUNDED;

-- 解锁被锁定的账户
ALTER USER 'user'@'host' ACCOUNT UNLOCK;
                    

删除用户

                        SQL示例
                    
                        DROP USER 'username'@'host';

-- 示例
DROP USER 'old_user'@'localhost';

-- 删除多个用户
DROP USER 'user1'@'localhost', 'user2'@'%';

查看用户

                        SQL示例
                    

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

-- 查看用户权限
SHOW GRANTS FOR 'username'@'host';

-- 查看当前用户
SELECT CURRENT_USER();

-- 查看用户账户锁定状态
SELECT user, host, account_locked FROM mysql.user;
                    

用户账户锁定和解锁

                        SQL示例
                    
                        -- 锁定用户账户
ALTER USER 'username'@'host' ACCOUNT LOCK;

-- 解锁用户账户
ALTER USER 'username'@'host' ACCOUNT UNLOCK;

-- 查看账户锁定状态
SELECT user, host, account_locked FROM mysql.user;

双因素认证（MySQL 8.0.27+ 企业版）

                        SQL示例
                    

                        -- 为用户启用双因素认证
CREATE USER 'secure_user'@'localhost'
IDENTIFIED WITH authentication_ldap_sasl
BY 'password'
AND IDENTIFIED WITH authentication_fido
BY 'registration_credential';
                    

权限管理

MySQL使用基于权限的访问控制模型，可以为用户授予特定数据库、表、列或存储过程的访问权限。

授予权限

                        SQL示例
                    

                        -- 授予特定权限
GRANT privilege_type ON database.table TO 'username'@'host';

-- 示例
GRANT SELECT, INSERT, UPDATE ON mydb.* TO 'app_user'@'localhost';
GRANT ALL PRIVILEGES ON mydb.employees TO 'admin_user'@'localhost';

-- 授予所有数据库的权限
GRANT ALL PRIVILEGES ON *.* TO 'super_user'@'localhost';

-- 授予权限并允许用户授予权限给其他用户
GRANT SELECT ON mydb.* TO 'user'@'host' WITH GRANT OPTION;

-- 授予列级权限
GRANT SELECT (employee_id, name) ON mydb.employees TO 'readonly'@'localhost';
                    

常用权限类型

权限	描述	作用级别
SELECT	查询数据	表、列
INSERT	插入数据	表、列
UPDATE	更新数据	表、列
DELETE	删除数据	表
CREATE	创建数据库/表	数据库、表
DROP	删除数据库/表	数据库、表
ALTER	修改表结构	表
INDEX	创建/删除索引	表
CREATE VIEW	创建视图	数据库、表
SHOW VIEW	查看视图定义	数据库、表
CREATE ROUTINE	创建存储过程和函数	数据库
ALTER ROUTINE	修改存储过程和函数	数据库
EXECUTE	执行存储过程和函数	数据库
TRIGGER	创建/删除触发器	表
EVENT	创建/修改/删除事件	数据库
ALL PRIVILEGES	所有权限	所有级别

撤销权限

                        SQL示例
                    

                        -- 撤销特定权限
REVOKE privilege_type ON database.table FROM 'username'@'host';

-- 示例
REVOKE DELETE ON mydb.employees FROM 'app_user'@'localhost';
REVOKE ALL PRIVILEGES ON mydb.* FROM 'old_user'@'localhost';

-- 撤销所有权限
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'user'@'host';
                    

权限生效

                        SQL示例
                    
                        -- 刷新权限
FLUSH PRIVILEGES;

-- 注意：在MySQL 8.0+中，大多数权限更改会立即生效
-- 但某些情况下仍需要刷新权限

角色管理（MySQL 8.0+）

MySQL 8.0引入了角色功能，可以更方便地管理用户权限。角色是一组权限的集合，可以授予给多个用户。

创建角色

                        SQL示例
                    
                        -- 创建角色
CREATE ROLE 'read_only', 'read_write', 'app_developer';

-- 创建带密码的角色
CREATE ROLE 'manager' IDENTIFIED BY 'manager_pass';

为角色授予权限

                        SQL示例
                    

                        -- 为角色授予权限
GRANT SELECT ON mydb.* TO 'read_only';
GRANT SELECT, INSERT, UPDATE, DELETE ON mydb.* TO 'read_write';
GRANT ALL PRIVILEGES ON mydb.* TO 'app_developer';

-- 为角色授予角色
GRANT 'read_only' TO 'read_write';
                    

将角色授予用户

                        SQL示例
                    

                        -- 将角色授予用户
GRANT 'read_only' TO 'report_user'@'localhost';
GRANT 'read_write' TO 'app_user'@'localhost';

-- 激活角色
SET DEFAULT ROLE 'read_write' TO 'app_user'@'localhost';

-- 用户激活所有角色
SET DEFAULT ROLE ALL TO 'user'@'host';
                    

角色管理操作

                        SQL示例
                    

                        -- 查看所有角色
SELECT user, host FROM mysql.user WHERE account_locked = 'Y';

-- 查看角色权限
SHOW GRANTS FOR 'role_name';

-- 撤销角色
REVOKE 'read_only' FROM 'user'@'host';

-- 删除角色
DROP ROLE 'role_name';
                    

数据加密

MySQL提供了多种数据加密方式，包括字段级加密、表空间加密和连接加密。

字段级加密

                        SQL示例
                    

                        -- 使用AES_ENCRYPT加密数据
INSERT INTO users (username, password, ssn)
VALUES ('john_doe', AES_ENCRYPT('my_password', 'encryption_key'), 
       AES_ENCRYPT('123-45-6789', 'encryption_key'));

-- 使用AES_DECRYPT解密数据
SELECT username, AES_DECRYPT(ssn, 'encryption_key') AS decrypted_ssn
FROM users;

-- 使用SHA2进行密码哈希
INSERT INTO users (username, password_hash)
VALUES ('user1', SHA2('password123', 256));

-- 使用密钥环组件管理密钥（MySQL 8.0.19+）
CREATE TABLE encrypted_data (
    id INT,
    data VARBINARY(255),
    key_id INT
);
                    

透明数据加密（TDE）

                        SQL示例
                    

                        -- 启用InnoDB表空间加密（需要keyring插件）
-- 安装keyring_file插件
[mysqld]
early-plugin-load = keyring_file.so
keyring_file_data = /var/lib/mysql_keyring/keyring

-- 创建加密表
CREATE TABLE secure_data (
    id INT PRIMARY KEY,
    sensitive_info VARCHAR(255)
) ENCRYPTION='Y';

-- 查看表加密状态
SELECT table_schema, table_name, create_options 
FROM information_schema.tables 
WHERE create_options LIKE '%ENCRYPTION%';
                    

数据脱敏（MySQL 8.0+ 企业版）

                        SQL示例
                    
                        -- 安装数据脱敏组件
INSTALL COMPONENT 'file://component_masking_functions';

-- 使用脱敏函数
SELECT ssn, MASK_SSN(ssn) AS masked_ssn FROM users;

-- 自定义脱敏规则
SELECT email, MASK_INNER(email, 3, 4) AS masked_email FROM users;

SSL连接加密

                        SQL示例
                    

                        -- 要求用户使用SSL连接
CREATE USER 'secure_user'@'%' IDENTIFIED BY 'password' 
REQUIRE SSL;

-- 要求X509证书
CREATE USER 'secure_user'@'%' IDENTIFIED BY 'password' 
REQUIRE X509;

-- 要求特定证书
CREATE USER 'cert_user'@'%' IDENTIFIED BY 'password'
REQUIRE SUBJECT '/CN=client.example.com';

-- 查看SSL状态
SHOW VARIABLES LIKE 'have_ssl';

-- 查看当前连接是否使用SSL
SHOW STATUS LIKE 'Ssl_cipher';
                    

审计和监控

审计和监控是安全管理的重要组成部分，可以帮助发现异常行为和满足合规要求。

启用通用查询日志

                        SQL示例
                    

                        -- 在配置文件中设置
# my.cnf
[mysqld]
general_log = 1
general_log_file = /var/log/mysql/general.log
log_output = FILE

-- 动态启用通用查询日志
SET GLOBAL general_log = 'ON';
SET GLOBAL log_output = 'FILE';
                    

启用慢查询日志

                        SQL示例
                    

                        -- 在配置文件中设置
[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2
log_queries_not_using_indexes = 1

-- 动态启用慢查询日志
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 2;
                    

MySQL企业版审计插件

                        SQL示例
                    
                        -- 安装审计插件（企业版）
INSTALL PLUGIN audit_log SONAME 'audit_log.so';

-- 配置审计
SET GLOBAL audit_log_policy = 'ALL';

-- 查看审计日志状态
SHOW VARIABLES LIKE 'audit_log%';

二进制日志

                        SQL示例
                    

                        -- 启用二进制日志
[mysqld]
log_bin = /var/log/mysql/mysql-bin.log
expire_logs_days = 10
max_binlog_size = 100M
binlog_encryption = ON  -- MySQL 8.0.14+

-- 查看二进制日志
SHOW BINARY LOGS;
SHOW BINLOG EVENTS IN 'mysql-bin.000001';
                    

SQL注入防护

                        SQL示例
                    

                        -- 使用参数化查询（应用层）
-- 在MySQL中启用SQL过滤（企业版防火墙）
INSTALL PLUGIN mysql_firewall SONAME 'mysql_firewall.so';

-- 启用防火墙组
SET GLOBAL mysql_firewall_mode = 'RECORDING';
SET GLOBAL mysql_firewall_mode = 'PROTECTING';

-- 使用存储过程限制直接SQL访问
CREATE PROCEDURE GetUser(IN user_id INT)
BEGIN
    SELECT id, name, email FROM users WHERE id = user_id;
END;
                    

安全最佳实践

密码策略

                        SQL示例
                    

                        -- 设置密码策略（MySQL 5.7.6+）
SET GLOBAL validate_password.policy = MEDIUM;
SET GLOBAL validate_password.length = 8;
SET GLOBAL validate_password.number_count = 1;
SET GLOBAL validate_password.special_char_count = 1;

-- 查看密码策略
SHOW VARIABLES LIKE 'validate_password%';

-- 设置密码过期策略
ALTER USER 'user'@'host' PASSWORD EXPIRE INTERVAL 90 DAY;

-- 设置密码重用策略
SET PERSIST password_history = 5;
SET PERSIST password_reuse_interval = 365;
                    

最小权限原则

每个用户只授予完成工作所需的最小权限
应用程序使用专用数据库用户，避免使用root
定期审查和清理不必要的用户和权限
使用存储过程和视图限制对基础数据的直接访问
为不同应用程序使用不同的数据库账户

网络安全

限制数据库服务器的网络访问
使用防火墙限制连接来源IP
在生产环境中禁用远程root登录
考虑使用SSH隧道或VPN访问数据库
定期更新MySQL版本以修复安全漏洞
禁用不必要的网络协议和功能

资源组控制

                        SQL示例
                    

                        -- 创建资源组限制用户资源使用（MySQL 8.0+）
CREATE RESOURCE GROUP app_group
    TYPE = USER
    VCPU = 0-3
    THREAD_PRIORITY = 5;

-- 将用户会话分配到资源组
SET RESOURCE GROUP app_group FOR 'app_user'@'localhost';

-- 设置连接数限制
ALTER USER 'user'@'host' WITH MAX_USER_CONNECTIONS 10;
                    

定期安全审计

定期检查用户和权限
监控异常访问模式
及时安装安全补丁
定期备份和测试恢复流程
审查日志文件中的可疑活动
定期进行安全扫描和漏洞评估

安全提醒：数据库安全是一个持续的过程，需要定期审查和更新安全策略。永远不要在生产环境中使用默认密码或弱密码。定期进行安全审计和漏洞扫描，确保数据库环境的安全性。

安全相关系统变量

变量	说明	推荐值
max_connect_errors	最大连接错误数	100000
max_connections	最大连接数	根据应用调整
wait_timeout	连接空闲超时	600
interactive_timeout	交互连接超时	28800
local_infile	是否允许LOAD DATA LOCAL	OFF
autocommit	自动提交	OFF（生产环境建议）

安全工具和资源

MySQL Enterprise Security - 提供高级安全功能
MySQL Workbench - 提供用户和权限管理界面
Percona Toolkit - 包含安全审计工具
MySQL安全白皮书 - 提供详细的安全指南
CVE数据库 - 跟踪MySQL安全漏洞

下一步学习

掌握了安全管理知识后，您可以继续学习：

备份与恢复 - 学习数据备份和灾难恢复策略
MySQL参考手册 - 查看完整的MySQL语法参考