背景
由于MySQL社区版没有自带的审计功能或插件，对于等级保护当中对数据库管理的要求的就存在一定的不满足情况的，抛开条条框框不说数据库的日志是值得研究的，通过收集数据库的日志到企业SOC平台便于安全事件的溯源与故障分析，配合目前的UEBA技术能够轻松发现很多恶意事件。
数据库审计主要用于监视并记录对数据库服务器的各类操作行为，并记入审计日志或数据库中以便日后进行跟踪、查询、分析，以实现对用户操作的监控和审计。审计是一项非常重要的工作，也是企业数据安全体系的重要组成部分。
自建MySQL数据库场景
方法一：开启General日志
默认情况下，MySQL不开启General log; 开启General log后，MySQL将所有到达MySQL Server的SQL语句记录下来。
开启General日志方法
查看General Log开启情况：执行SQL命令show variables like ‘%general_log%’ ;可以看到默认general_log是OFF的：
mysql> show variables like '%general_log%';
+------------------+---------------------------+
| Variable_name | Value |
+------------------+---------------------------+
| general_log | OFF |
| general_log_file | /var/lib/mysql/node02.log |
+------------------+---------------------------+
2 rows in set (0.01 sec)开启General Log: 执行SQL命令set global general_log=on; 而后查看general_log是否打开了
mysql> set global general_log=on;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like '%general_log%';
+------------------+---------------------------+
| Variable_name | Value |
+------------------+---------------------------+
| general_log | ON |
| general_log_file | /var/lib/mysql/node02.log |
+------------------+---------------------------+
2 rows in set (0.01 sec)验证：general_log打开后，所有SQL的访问都会记录在general_log_file指向的日志文件。我们执行几个SQL语句测试
2021-10-29T03:11:05.078633Z 4 Query show tables
2021-10-29T03:11:10.177062Z 4 Query show variables like '%general_log%'
2021-10-29T03:11:14.561076Z 4 Quit开启General Log只要用户执行了操作，无论对错，MySQL就会记录日志，这样的话日志量会非常庞大，对数据库效率有影响。所以我们一般不建议开启开功能，个别情况下可能会临时的开一段时间以供排查故障等使用。
方法二：开启Binlog+Init_connect
BinLog是MySQL操作时留下的日志，BinLog一方面可以用在数据库的恢复与主从复制上，另外一方面可以用来做数据库的审计。
由于BinLog日志里面无法查询是谁在哪个时间段登录的等信息，缺少审计必要的信息。在MySQL中，每个连接都会先执行init_connect进行连接的初始化，我们可以在这里获取用户的登录名称和thread ID值。然后配合BinLog，就可以追踪到每个操作语句的操作时间，操作人等信息,再加上BinLog的日志信息实现审计。
配置和验证过程如下
创建审计用的数据库和表
mysql> create database auditdb;
Query OK, 1 row affected (0.00 sec)
mysql> use auditdb;
Database changed
mysql> create table accesslog(
-> id int primary key auto_increment,
-> connectionid int,
-> connectionuser varchar(30),
-> logintime datetime
-> );
Query OK, 0 rows affected (0.03 sec)
mysql>创建具有操作auditdb数据权限的用户
mysql> grant all privileges on auditdb.* to 'user1' identified by 'user1';
Query OK, 0 rows affected, 1 warning (0.00 sec)如果已经有用户，需要对现有用户添加操作auditdb的权限
mysql> insert into mysql.db (Host,Db,User,Insert_priv) values ('%','auditdb','user1','Y');
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
设置init_connect,并重启MySQL数据库在初始化参数文件[mysql]部分添加如下内容
[mysqld]
log-bin=mysql-bin
server-id=1
init_connect='insert into auditdb.accesslog(connectionid, connectionuser,logintime) values(connection_id(),user(),now());' #设置初始>>化连接参数 重启数据库生效
[root@node02 mysql]# systemctl restart mysqld
[root@node02 mysql]# systemctl status mysqld
● mysqld.service - MySQL Server
Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
Active: active (running) since 五 2021-10-29 13:52:43 CST; 1min 7s ago
Docs: man:mysqld(8)
http://dev.mysql.com/doc/refman/en/using-systemd.html
Process: 21276 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid $MYSQLD_OPTS (code=exited, status=0/SUCCESS)
Process: 21258 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
Main PID: 21279 (mysqld)
CGroup: /system.slice/mysqld.service
└─21279 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid
10月 29 13:52:43 node02 systemd[1]: Starting MySQL Server...
10月 29 13:52:43 node02 systemd[1]: Started MySQL Server.用user1用户登录，执行一些写入和删除的操作
[root@node02 mysql]# mysql -u user1 -puser1
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.35-log MySQL Community Server (GPL)
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| auditdb |
+--------------------+
2 rows in set (0.00 sec)
mysql> use auditdb;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> create table test2(id int);
Query OK, 0 rows affected (0.02 sec)
mysql> insert into test2 values(1);
Query OK, 1 row affected (0.00 sec)
mysql> insert into test2 values(2);
Query OK, 1 row affected (0.00 sec)
mysql> delete from test2;
Query OK, 2 rows affected (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)用mysqlbinlog工具查看BinLog，根据delete操作找到相应的ThreadId,而后在前面创建的审计日志表auditdb.accesslog里面根据ThreadID找到用户登录信息
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 | 2010 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)查看当前binlog mysqlbin.000002的内容，并找到delete操作对应的ThreadID
[root@node02 mysql]# mysqlbinlog mysql-bin.000002|egrep -iB 10 delete
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 1510
#211029 14:09:38 server id 1 end_log_pos 1585 CRC32 0x383011df Query thread_id=3 exec_time=0 error_code=0
SET TIMESTAMP=1635487778/*!*/;
BEGIN
/*!*/;
# at 1585
#211029 14:09:38 server id 1 end_log_pos 1636 CRC32 0x2eaeea4d Table_map: `auditdb`.`test2` mapped to number 109
# at 1636
#211029 14:09:38 server id 1 end_log_pos 1681 CRC32 0x766c0e0f Delete_rows: table id 109 flags: STMT_END_F从Binlog中可以得知删除Test2表对应的threadid是3，根据ThreadID在审计表里查看用户的登录信息，可以得知这个删除操作是user1用户在本机执行的操作。
mysql> select * from auditdb.accesslog;
+----+--------------+-----------------+---------------------+
| id | connectionid | connectionuser | logintime |
+----+--------------+-----------------+---------------------+
| 1 | 2 | user1@localhost | 2021-10-29 14:05:57 |
| 2 | 3 | user1@localhost | 2021-10-29 14:07:59 |
| 3 | 4 | user1@localhost | 2021-10-29 14:10:34 |
+----+--------------+-----------------+---------------------+
3 rows in set (0.00 sec)说明：采用这种方式进行审计，由于init-connect只会在连接时执行，不会对数据库产生大的性能影响，但是init-connect不会记录拥有root权限的用户记录.
方法三：使用审计插件
客户需要查询谁修改、插入、删除的操作记录，通常在没有开启审计功能的话，
可以利用binlog解析数据获取，但是比较麻烦，今天给大家介绍一个mysql审计插件。除了商业版的审计插件外，常见的还有三类审计插件Percona Audit Log Plugin、MariaDB Audit Plugin、McAfee MySQL Audit Plugin。这几个插件功能上大同小异，只是展示的内容和格式略有不同。
我们以MariaDB Audit Plugin为例，给大家介绍如何采用插件的方式实现对Mysql数据的审计。
下载与安装
下载MariaDB Audit Plugin插件
wget https://github.com/mcafee/mysql-audit/releases/download/v1.1.9/audit-plugin-mysql-5.7-1.1.9-974-linux-x86_64.zip
unzip audit-plugin-mysql-5.7-1.1.9-974-linux-x86_64.zip
[root@node02 ~]# cd audit-plugin-mysql-5.7-1.1.9-974/lib/
[root@node02 lib]# ls
libaudit_plugin.so
[root@node02 lib]# chmod +x libaudit_plugin.so
检查是否有plugin,首先查看plugin目录
mysql> show variables like 'plugin_dir';
+---------------+--------------------------+
| Variable_name | Value |
+---------------+--------------------------+
| plugin_dir | /usr/lib64/mysql/plugin/ |
+---------------+--------------------------+
1 row in set (0.01 sec)把server_audit.so复制到对应的mysql插件库中 /usr/lib64/mysql/plugin/
cp -rp libaudit_plugin.so /usr/lib64/mysql/plugin/
执行install安装
install plugin audit soname 'libaudit_plugin.so';报错信息如下：
ERROR 1123 (HY000): Can’t initialize function ‘audit’; Plugin initialization function failed.解决办法：
yum install gcc gcc-c++ autoconf automake zlib zlib-devel openssl openssl-devel pcre pcre-devel texinfo
wget http://ftp.gnu.org/gnu/gdb/gdb-8.1.1.tar.gz
tar xvf gdb-8.1.1.tar.gz
cd gdb-8.1.1/
./configure
make
make install
gdb -v
cd audit-plugin-mysql-5.7-1.1.9-974/utils
chmod +x offset-extract.sh
[root@node02 utils]# ./offset-extract.sh /usr/sbin/mysqld
//offsets for: /usr/sbin/mysqld (5.7.35)
{"5.7.35","90184c67ebbac99d3bd2641e28b1a302", 7832, 7880, 3640, 4800, 456, 360, 0, 32, 64, 160, 544, 7996, 4368, 3648, 3656, 3660, 6080, 2072, 8, 7064, 7104, 7088, 13480, 148, 672, 0},
编辑/etc/my.cnf
plugin-load=AUDIT=libaudit_plugin.so
audit_offsets = 7832, 7880, 3640, 4800, 456, 360, 0, 32, 64, 160, 544, 7996, 4368, 3648, 3656, 3660, 6080, 2072, 8, 7064, 7104, 7088, 13480, 148, 672, 0
audit_json_file = on
audit_record_cmds = 'insert,delete,update,create,drop,alter,grant,truncate'
audit_json_log_file = /var/lib/mysql/mysql-audit.json
重启mysql
[root@node02 utils]# systemctl restart mysqld进入mysql查询插件列表
mysql> show plugins;
+----------------------------+----------+--------------------+----------------------+---------+
| Name | Status | Type | Library | License |
+----------------------------+----------+--------------------+----------------------+---------+
| binlog | ACTIVE | STORAGE ENGINE | NULL | GPL |
| mysql_native_password | ACTIVE | AUTHENTICATION | NULL | GPL |
| sha256_password | ACTIVE | AUTHENTICATION | NULL | GPL |
| CSV | ACTIVE | STORAGE ENGINE | NULL | GPL |
| MEMORY | ACTIVE | STORAGE ENGINE | NULL | GPL |
| InnoDB | ACTIVE | STORAGE ENGINE | NULL | GPL |
| INNODB_TRX | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_LOCKS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_LOCK_WAITS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMP | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMP_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMPMEM | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMPMEM_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMP_PER_INDEX | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMP_PER_INDEX_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_BUFFER_PAGE | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_BUFFER_PAGE_LRU | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_BUFFER_POOL_STATS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_TEMP_TABLE_INFO | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_METRICS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_DEFAULT_STOPWORD | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_DELETED | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_BEING_DELETED | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_CONFIG | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_INDEX_CACHE | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_INDEX_TABLE | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_TABLES | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_TABLESTATS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_INDEXES | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_COLUMNS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_FIELDS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_FOREIGN | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_FOREIGN_COLS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_TABLESPACES | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_DATAFILES | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_VIRTUAL | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| MyISAM | ACTIVE | STORAGE ENGINE | NULL | GPL |
| MRG_MYISAM | ACTIVE | STORAGE ENGINE | NULL | GPL |
| PERFORMANCE_SCHEMA | ACTIVE | STORAGE ENGINE | NULL | GPL |
| ARCHIVE | ACTIVE | STORAGE ENGINE | NULL | GPL |
| BLACKHOLE | ACTIVE | STORAGE ENGINE | NULL | GPL |
| FEDERATED | DISABLED | STORAGE ENGINE | NULL | GPL |
| partition | ACTIVE | STORAGE ENGINE | NULL | GPL |
| ngram | ACTIVE | FTPARSER | NULL | GPL |
| AUDIT | ACTIVE | AUDIT | libaudit_plugin.so | GPL |
| validate_password | ACTIVE | VALIDATE PASSWORD | validate_password.so | GPL |
+----------------------------+----------+--------------------+----------------------+---------+
46 rows in set (0.00 sec)查询audit版本
mysql> show global status like 'AUDIT_version';
+---------------+-----------+
| Variable_name | Value |
+---------------+-----------+
| Audit_version | 1.1.9-974 |
+---------------+-----------+
1 row in set (0.00 sec)查询日志存放位置
[root@node02 mysql]# ls mysql-audit.json
mysql-audit.json
[root@node02 mysql]# pwd
/var/lib/mysql
[root@node02 mysql]# cat mysql-audit.json
{"msg-type":"header","date":"1635500386917","audit-version":"1.1.9-974","audit-protocol-version":"1.0","hostname":"node02","mysql-version":"5.7.35-log","mysql-program":"/usr/sbin/mysqld","mysql-socket":"/var/lib/mysql/mysql.sock","mysql-port":"0","server_pid":"28362"}
{"msg-type":"header","date":"1635500481116","audit-version":"1.1.9-974","audit-protocol-version":"1.0","hostname":"node02","mysql-version":"5.7.35-log","mysql-program":"/usr/sbin/mysqld","mysql-socket":"/var/lib/mysql/mysql.sock","mysql-port":"0","server_pid":"28431"}
{"msg-type":"header","date":"1635500543634","audit-version":"1.1.9-974","audit-protocol-version":"1.0","hostname":"node02","mysql-version":"5.7.35-log","mysql-program":"/usr/sbin/mysqld","mysql-socket":"/var/lib/mysql/mysql.sock","mysql-port":"0","server_pid":"28507"}
{"msg-type":"header","date":"1635500802089","audit-version":"1.1.9-974","audit-protocol-version":"1.0","hostname":"node02","mysql-version":"5.7.35-log","mysql-program":"/usr/sbin/mysqld","mysql-socket":"/var/lib/mysql/mysql.sock","mysql-port":"3306","server_pid":"28507"}
{"msg-type":"activity","date":"1635502123110","thread-id":"3239","query-id":"13","user":"user1","priv_user":"user1","ip":"","host":"localhost","_os":"Linux","_client_name":"libmysql","_pid":"1737","_client_version":"5.7.35","_platform":"x86_64","program_name":"mysql","pid":"1737","os_user":"root","appname":"mysql","rows":"1","status":"0","cmd":"insert","objects":[{"db":"auditdb","name":"accesslog","obj_type":"TABLE"}],"query":"insert into auditdb.accesslog(connectionid, connectionuser,logintime) values(connection_id(),user(),now())"}如无特殊说明，文章均为本站原创，转载请注明出处
- 转载请注明来源：MySQL5.7开启审计功能
- 本文永久链接地址：https://www.xionghaier.cn/archives/1324.html
该文章由 John 发布
