MySQL 安全审计

参考文章:

• 等保测评2.0：MySQL安全审计
• MySQL 数据库安全之审计
• MySQL会话控制限制登录次数

MySQL 安全审计

数据库审计主要用于监视并记录对数据库服务器的各类操作行为，并记入审计日志或数据库中以便日后进行跟踪、查询、分析，以实现对用户操作的监控和审计。审计是一项非常重要的工作，也是企业数据安全体系的重要组成部分。

MySQL企业版自带审计功能，但是需要付费。MySQL社区版没有审计功能，基于成本的考虑，很多用户采用社区版MySQL作为业务系统数据库。

以下是等保评测 2.0 中关于MySQL 安全审计的评测项:

a. 应启用安全审计功能，审计覆盖到每个用户，对重要的用户行为和重要安全事件进行审计；
b 审计记录应包括事件的日期和时间、用户、事件类型、事件是否成功及其他与审计相关的信息；
c. 应对审计记录进行保护，定期备份，避免受到未预期的删除、修改或覆盖等；
d. 应对审计进程进行保护，防止未经授权的中断。

自带的审计功能

在 MySQL 中自带了审计功能 —— general_log，它会记录所有关于 mysql 的 sql 语句（所以会给服务器和数据库带来很大的资源占用）。不过从评测要求来说，如果开启了 general_log，那么是符合评测项 a 的。

1. 可以使用以下命令查询 general_log 是否开启

   MySQL [(none)]> SHOW GLOBAL VARIABLES LIKE '%general%';
   +------------------+-------------------------+
   | Variable_name    | Value                   |
   +------------------+-------------------------+
   | general_log      | OFF                     |
   | general_log_file | /data/mysql/mysql-8.log |
   +------------------+-------------------------+
   2 rows in set (0.01 sec)

   • general_log: 是否开启审计日志，value 是布尔值，所以可以设置为 ON（或者1）以及 OFF（或者0）
   • general_log_file: 审计日志文件名称，如果

2. MySQL 5.1.6版开始，可以将日志存储在表当中，这个由 log_output 参数进行控制，值为 file，则代表存储在文件中，为 table，则代表存储在gengera_log 表中。

   MySQL [(none)]> SHOW VARIABLES LIKE '%log_output%';
   +---------------+-------+
   | Variable_name | Value |
   +---------------+-------+
   | log_output    | FILE  |
   +---------------+-------+
   1 row in set (0.01 sec)

3. 可以设置 global 变量开启 general_log，也可以在配置文件 my.cnf 中设置，区别在于设置 global 变量，则数据库重启后设置就失效了。

   [mysqld]
   general_log = on                                    # on为开启；off为关闭
   general_log_file = /data/mysql/generalLog.log          # 审计信息存储位置
   log_timestamps = SYSTEM                             # 设置日志文件的输出时间为地方时

   • 设置 global 变量临时开启 general_log 的 sql 语句如下：

   set global general_log = on;
   set global general_log_file = '/data/mysql/generalLog.log';
   set global log_timestamps = SYSTEM;

4. 随便输入几条 sql 语句，查看审计日志内容，如下

   MySQL [(none)]> use mysql;
   MySQL [mysql]> select user,host from user;

   • 审计日志内容如下:

   # taif /data/mysql/generalLog.log 
   /usr/sbin/mysqld, Version: 8.0.24 (MySQL Community Server - GPL). started with:
   Tcp port: 3306  Unix socket: /var/lib/mysql/mysql.sock
   Time                 Id Command    Argument
   2021-11-16T10:28:37.180633+08:00            8 Query     SELECT DATABASE()
   2021-11-16T10:28:37.181395+08:00            8 Init DB   mysql
   2021-11-16T10:28:47.262609+08:00            8 Query     select user,host from user

使用 Binlog + init_connect 方式

开启 general_log 只要用户执行了操作，无论对错，MySQL 都会记录日志，这样的话日志量会非常庞大，对数据库效率有影响。所以我们一般不建议开启开功能，个别情况下可能会临时的开一段时间以供排查故障等使用。

BinLog 是 MySQL 操作时留下的日志，BinLog 一方面可以用在数据库的恢复与主从复制上，另外一方面可以用来做数据库的审计。
由于 BinLog 日志里面无法查询是谁在哪个时间段登录的等信息，缺少审计必要的信息。在 MySQL 中，每个连接都会先执行 init_connect 进行连接的初始化，我们可以在这里获取用户的登录名称和 thread ID 值。然后配合 BinLog，就可以追踪到每个操作语句的操作时间，操作人等信息,再加上 BinLog 日志信息实现审计。

注意: 采用这种方式进行审计，由于 init-connect 只会在连接时执行，不会对数据库产生大的性能影响，但是 init-connect 不会记录拥有 root 权限的用户记录.

配置 binlog 以及 auditdb 数据库

1. 创建审计使用的 数据库和表

   MySQL [(none)]> create database auditdb character set utf8mb4 collate utf8mb4_general_ci;
   Query OK, 1 row affected (0.00 sec)
   
   MySQL [(none)]> use auditdb;
   Database changed
   
   MySQL [(none)]> create table accesslog (
       id int primary key auto_increment,
       connectionid int,
       connectionuser varchar(50),
       logintime datetime
       );
   Query OK, 0 rows affected (0.10 sec)

2. 创建具有操作 auditdb 数据权限的用户(可选，不创建的话可以使用 root 账号操作)

   MySQL [(none)]> grant all privileges on auditdb.* to 'admin' identified by 'Admin_Audit123';
   Query OK, 0 rows affected, 1 warning (0.00 sec)
   
   MySQL [(none)]> flush privileges;
   Query OK, 0 rows affected (0.01 sec)

3. 如果已经有用户，需要对现有用户添加操作auditdb的权限

   MySQL [(none)]> insert into mysql.db (Host, Db, User, Insert_priv) values ('localhost', 'auditdb', '', 'Y');
   Query OK, 1 row affected (0.00 sec)
   
   -- 查看权限
   MySQL [(none)]> select * from mysql.db \G
   *************************** 1. row ***************************
                   Host: localhost
                     Db: performance_schema
                   User: mysql.session
             Select_priv: Y
             Insert_priv: N
             Update_priv: N
             Delete_priv: N
             Create_priv: N
               Drop_priv: N
             Grant_priv: N
         References_priv: N
             Index_priv: N
             Alter_priv: N
   Create_tmp_table_priv: N
       Lock_tables_priv: N
       Create_view_priv: N
         Show_view_priv: N
     Create_routine_priv: N
     Alter_routine_priv: N
           Execute_priv: N
             Event_priv: N
           Trigger_priv: N
   
   -- ... 省略 N 行 ...
   *************************** 6. row ***************************
                   Host: %
                     Db: auditdb
                   User: admin_audit
             Select_priv: Y
             Insert_priv: Y
             Update_priv: Y
             Delete_priv: Y
             Create_priv: Y
               Drop_priv: Y
             Grant_priv: N
         References_priv: Y
             Index_priv: Y
             Alter_priv: Y
   Create_tmp_table_priv: Y
       Lock_tables_priv: Y
       Create_view_priv: Y
         Show_view_priv: Y
     Create_routine_priv: Y
     Alter_routine_priv: Y
           Execute_priv: Y
             Event_priv: Y
           Trigger_priv: Y
   6 rows in set (0.01 sec)

4. 配置 my.cnf 配置文件，设置 init_connect 参数，在 [mysqld] 部分添加以下内容:

   [mysqld]
   log_bin = mysql-bin      # 开启 binlog
   binlog_format = mixed
   expire_logs_days = 7
   init_connect = 'insert into auditdb.accesslog(connectionid, connectionuser, logintime) values(connection_id(), user(), now());'

5. 重启 MySQL 服务

   systemctl restart mysqld

验证审计

1. 创建测试用数据库，普通用户 wms，并授权

   MySQL [(none)]> create database test;
   
   MySQL [test]> create user 'wms'@'%' identified by '123456';
   MySQL [(none)]> grant all privileges on test.* to 'wms'@'%';

2. 以普通用户 wms 登录数据库，执行一些操作

   create table table1(id int primary key auto_increment, name varchar(50));
   insert into table1(name) value('wanwu');
   insert into table1(name) value('lonely');

3. 查看当前使用的 binlog 文件

   MySQL [(none)]> show master status;
   +------------------+----------+--------------+------------------+-------------------+
   | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
   +------------------+----------+--------------+------------------+-------------------+
   | mysql-bin.000015 |     3620 |              |                  |                   |
   +------------------+----------+--------------+------------------+-------------------+
   1 row in set (0.00 sec)

4. 查看当前使用的 binlog 文件 mysql-bin.000015，找到相应操作语句对应的 Thread ID

   # mysqlbinlog /data/mysql/mysql-bin.000015 |less
   # at 2161
   # at 2193
   #211117 10:14:48 server id 1  end_log_pos 2193 CRC32 0x16bde8ea         Intvar
   SET INSERT_ID=1/*!*/;
   #211117 10:14:48 server id 1  end_log_pos 2306 CRC32 0xe8b5c045         Query   thread_id=6     exec_time=0     error_code=0
   use `test`/*!*/;
   SET TIMESTAMP=1637115288/*!*/;
   insert into table1(name) value('wanwu')
   /*!*/;

   注意查看 binlog 中的 thread_id=6，这个 id 对应 auditdb.accesslog 表中的 connectionid。

5. 查看审计表中的数据

   MySQL [(none)]> select * from auditdb.accesslog;
   +----+--------------+-------------------+---------------------+
   | id | connectionid | connectionuser    | logintime           |
   +----+--------------+-------------------+---------------------+
   |  1 |            6 | wms@localhost     | 2021-11-17 10:10:33 |
   |  2 |            9 | wms@localhost     | 2021-11-17 10:24:09 |
   |  3 |           11 | wms@192.168.55.14 | 2021-11-17 11:02:17 |
   +----+--------------+-------------------+---------------------+

   通过上面的信息可以得知该语句是由 wms 用户在本地执行的操作。

使用审计插件

除了商业版的审计插件外，常见的还有三类审计插件 Percona Audit Log Plugin、MariaDB Audit Plugin、McAfee MySQL Audit Plugin。这几个插件功能上大同小异，只是展示的内容和格式略有不同。

MySQL 登录限制

一般我们开发环境的 MySQL 是没有配置登录保护的，但仅限于开发环境，正式环境是不允许无限制登录，存在很大的风险。
MySQL 5.7 以后提供了 Connection-Control 插件用来控制客户端在登录操作连续失败一定次数后的响应的延迟。该插件可有效的防止客户端暴力登录的风险(攻击)。该插件包含以下两个组件：

• connection_control：控制失败次数以及延迟时间
• connection_control_failed_login_attempts: 将登录失败的操作记录至 information_schema 表

插件库文件基本名称是 connection_control。文件名后缀因平台而异（例如，.so 用于 Unix 和类 Unix 系统，.dll 用于 Windows）。

安装 connection_control

1. 查看插件状态，MySQL 数据库默认是不加载 connection_control 插件的，如下

   MySQL [(none)]> SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS  WHERE PLUGIN_NAME LIKE 'connection%';
   Empty set (0.00 sec)

2. 安装 connection_control 有两种方法:

   • 方法1: 在服务器 my.cnf 配置文件中，添加需要加载的插件，根据需要调整平台的 .so 后缀：

   [mysqld]
   plugin-load-add=connection_control.so

   如果要在数据库启动时激活 connection_control 插件，并设置不允许在运行时卸载该插件，可以添加以下配置

   connection-control=FORCE_PLUS_PERMANENT
   connection-control-failed-login-attempts=FORCE_PLUS_PERMANENT

   修改 my.cnf 后，重启服务器使新设置生效。

   • 方法2: 在运行时加载插件，使用以下 SQL 语句，根据需要调整平台的 .so 后缀：

   MySQL [(none)]> INSTALL PLUGIN CONNECTION_CONTROL SONAME 'connection_control.so';
   Query OK, 0 rows affected (0.04 sec)
   
   MySQL [(none)]> INSTALL PLUGIN CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS SONAME 'connection_control.so';
   Query OK, 0 rows affected (0.00 sec)

   如果插件无法初始化，请检查服务器错误日志。

3. 重新查看插件安装状态

   MySQL [(none)]> SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS  WHERE PLUGIN_NAME LIKE 'connection%';
   +------------------------------------------+---------------+
   | PLUGIN_NAME                              | PLUGIN_STATUS |
   +------------------------------------------+---------------+
   | CONNECTION_CONTROL                       | ACTIVE        |
   | CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS | ACTIVE        |
   +------------------------------------------+---------------+

4. 配置 connection_control 插件，以下是关于 connection_control 的配置参数说明

   • connection_control_failed_connections_threshold: 允许帐户的连续登录失败次数。要禁用失败连接计数，将该参数设置为 0;
   • connection_control_min_connection_delay: 高于阈值的连接失败的最小延迟 (以毫秒为单位);
   • connection_control_max_connection_delay: 超过阈值的连接失败的最大延迟 (以毫秒为单位);

   通过 SQL 语句配置参数，如下

   SET GLOBAL connection_control_failed_connections_threshold = 4;
   SET GLOBAL connection_control_min_connection_delay = 1500;

验证 connection_control 插件

1. 使用 wms 普通用户连续输错4次密码，检查连接状态是否会被阻塞

   [patrol@glpi ~]$ mysql -uwms  -h192.168.55.23 -p
   Enter password: 
   ERROR 1045 (28000): Access denied for user 'wms'@'192.168.55.14' (using password: YES)
   [patrol@glpi ~]$ mysql -uwms  -h192.168.55.23 -p
   Enter password: 
   ERROR 1045 (28000): Access denied for user 'wms'@'192.168.55.14' (using password: YES)
   [patrol@glpi ~]$ mysql -uwms  -h192.168.55.23 -pdqwasd
   mysql: [Warning] Using a password on the command line interface can be insecure.
   ERROR 1045 (28000): Access denied for user 'wms'@'192.168.55.14' (using password: YES)
   [patrol@glpi ~]$ mysql -uwms  -h192.168.55.23 -pdqwasdasda
   mysql: [Warning] Using a password on the command line interface can be insecure.
   ERROR 1045 (28000): Access denied for user 'wms'@'192.168.55.14' (using password: YES)
   [patrol@glpi ~]$ mysql -uwms  -h192.168.55.23 -p
   Enter password: 

   第 5 次开始连接被阻塞，时长1分钟左右；

2. 使用 root 账号查看失败计数

   MySQL [information_schema]> select * from CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS;
   +-----------+-----------------+
   | USERHOST  | FAILED_ATTEMPTS |
   +-----------+-----------------+
   | 'wms'@'%' |               5 |
   +-----------+-----------------+
   1 row in set (0.00 sec)

   注意: 一旦用户成功登录，则关于该用户的计数会清零.

限制IP地址登录数据库

1. 方法一: 通过 iptables 限制来源网址或者网段访问 3306 端口

   iptables -A INPUT -p tcp -m state --state NEW -m tcp -s 192.168.55.0/24 --dport 3306 -j ACCEPT 

2. 方法二: 通过 MySQL 用户管理

   create user 'wms'@'192.168.%.%' identified by 'xxxxxxx';