Mysql 数据库安全之审计 - BinLog+ init_connect

前言

数据库审计主要用于监视并记录对数据库的各类操作，并记入审计日志或数据库中以便日后进行跟踪、查询、分析，以实现对用户操作的监控和审计。审计是一项非常重要的工作，也是企业数据安全体系的重要组成部分，虽然Mysql企业版自带审计功能，但是需要付费，而Mysql社区版没有审计功能，本文主要介绍简单易用+免费审计方法 Binlog + init_connect

Binlog 是指打开mysql的binlog功能，通过分析binlog可以帮助我们在需要时查询数据库做了哪些操作，但是只通过binlog没有办法发现是哪个用户，哪个客户端连过来进行操作的，只有thread_id这个信息。
Init_connect是指登录数据库以后，自动执行指定的命令，我们可以把用户的登录threadid, 用户名，时间等信息通过init_connect 记录到数据库中，从而达到用户登录信息的审计

通过这两个功能，就能够查询到某一个具体的命令是通过哪个客户端连接进来进行操作的。

优点：
通过简单的设置就可以对数据库进行审计，同时binlog功能是很多生产服务器都已经开启的功能，如果binlog已开启，数据库可以不需要重启就可以设置。 init_connect 只会在连接时执行，不会对数据库产生大的性能影响
缺点：
1. binlog日志通常都会设置expire_logs_days，超过这个时间的binlog会被删除，无法查询太长时间的信息，除非手动备份binlog文件
2. init_connect 不会记录拥有root权限的用户记录，只能对普通用户的操作进行审计

配置和验证过程

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

mysql> create database auditdb;
mysql> use auditdb;
mysql> create table accesslog(
       id int primary key auto_increment,
       connectid int,
       connectuser varchar(30),
       logintime datetime);

2. 为用户赋予权限

由于数据库连接后，都需要往auditdb.accesslog表插入数据，因此需要为所有的用户赋予写auditdb.accesslog的权限

select concat("grant insert on auditdb.accesslog to '",user,"'@'",host,"';") from mysql.user; #拼结授权语句

执行生成的语句，然后执行flush privileges命令

注意，以后每添加一个用户都必须授权此表的插入权限，要不会连接不上。

3. 设置init_connect   
该参数可动态调整，同时在my.cnf中设置，重启后仍然生效

mysql> set global init_connect='insert into auditdb.accesslog(connectid, connectuser, logintime) values(connection_id(), user(), now());';
# vi /etc/my.cnf
init_connect='insert into auditdb.accesslog(connectid, connectuser, logintime) values(connection_id(), user(), now());'

4. 开启binlog功能

开启binlog功能需要重启数据库才能生效，如果数据库已开启binlog，可忽略本步骤，如果未设置，对于生产系统，需要在可变更时重启数据库
如果是第一次设置log_bin参数，建议设置expire_logs_days参数，设置binlog保留时长的参数，以免文件过大导致把服务器空间占满

# vi /etc/my.cnf
log_bin = /data/mysqllog/binlog/mysql_bin
# systemctl stop mysqld
# systemctl start mysqld

5. 审计测试及验证

测试一：以root用户进行登录，执行操作后，查询accesslog表，没有记录，说明该方式不会记录root用户的操作

$ mysql -uroot  -10.10.10.1 -p

mysql> use testdb
mysql> insert into t4 values(20);
Query OK, 1 row affected (0.00 sec)
mysql> select user();
+----------------+
| user()         |
+----------------+
| root@172.16.79.1 |
+----------------+
1 row in set (0.00 sec)

mysql> select * from auditdb.accesslog; 
Empty set (0.00 sec)

测试二: 以普通用户登录

# mysql -h10.10.10.1 -utestusr -p

mysql> use testdb;
mysql> insert into t4 values (10);
Query OK, 1 row affected (0.01 sec)
mysql> drop table t4;       ==> 执行drop表操作
Query OK, 0 rows affected (0.00 sec)

如果想查询谁执行了delete table t4 这条命令，可以先查询binlog文件
# ls -l mysql_bin.*

-rw-rw---- 1 mysql mysql  483 Dec 31 14:11 mysql_bin.000200
-rw-rw---- 1 mysql mysql 2717 Apr 30 13:28 mysql_bin.000261
-rw-rw---- 1 mysql mysql  174 Apr 30 13:28 mysql_bin.000262
-rw-rw---- 1 mysql mysql 2193 Apr 30 16:00 mysql_bin.000263
-rw-rw---- 1 mysql mysql 7800 Dec 16 10:44 mysql_bin.index

如果知道相对准确的删除时间，则可以根据时间点找到对应的binlog文件
如果知道大概的删除时间点，比如大概在哪几天，则需要使用以下命令分析多个binlog文件

1) 对一个或者多个binlog文件进行格式化输出
mysqlbinlog -v mysql_bin.000262 > bin262.out
mysqlbinlog -v mysql_bin.000263 > bin263.out
......
2) 模糊查找，知道找到匹配的记录

# cat bin262.out |grep -i drop
# cat bin263.out |grep -i drop
DROP TABLE `t4` /* generated by server */
3) 进一步查看对应的文件,查看详细的记录信息
# vi bin263.out

# at 2074
#210430 16:00:28 server id 523306  end_log_pos 2193 CRC32 0xaafe21a0    Query   thread_id=116   exec_time=0     error_code=0
use `testdb`/*!*/;
SET TIMESTAMP=1619769628/*!*/;
DROP TABLE `t4` /* generated by server */
/*!*/;

分析：可以看到DROP TABLE t4命令是由thread_id=116的用户执行的

4）查看登录日志

# mysql -uroot -p
mysql> select * from accesslog;
+----+-----------+-----------------------+---------------------+
| id | connectid | connectuser           | logintime           |
+----+-----------+-----------------------+---------------------+
|  1 |       116 | testusr@172.16.79.231 | 2021-04-30 15:57:32 |
+----+-----------+-----------------------+---------------------+
1 row in set (0.00 sec)

可以看到thread_id为116 的用户为testusr，在172.16.79.231机器上操作删除的，完成了对数据的简单审计