MySQL配置数据库审计

1 背景

项目接近尾声，验收是项目收尾的一个必不可少的环境，验收需要用到两个重要的凭证，国家信息安全等级保护三级认证（以下简称“等保”）和密码应用安全性评估（以下简称“密评”），其中等保里面对数据库进行了核查工作，针对数据库提到了两块内容，1、当前版本5.7.31存在漏洞，需要升级至5.7.34及以上版本，升级步骤参考《项目进行时-安全整改-docker中的mysql升级》2、数据库审计功能开具，本篇以此展开。

2 环境

3 准备工作

工欲善其事必先利其器，针对这个审计功能，开始是从官网上下载当前5.7最新的版本5.7.40的版本，安装完毕后，审计插件尝试安了3个不同版本，都没有安装成功，然后各种尝试。各种撞墙。mysql官网下载最新版本，mariadb数据库下载最新版本，然后从mariadb目录中提取audit_service.so插件，各种安装各种失败。撞了三天墙，终于有点眉目了。逐项抛开云雾，终见明了。
1、安装mysql
安装mysql不是本篇重点，在本篇介绍，如需了解参考《Linux操作系统安装MySQL（rpm安装）》
2、下载审计插件

由于使用的是社区版MySql，没有内置审计，使用的McAfee的插件
MySQL5.7.39 审计日志插件安装mcafee日志插件
下面利用第三方开源审计插件 libaudit_plugin.so 在 MySQL 5.7.39上完成审计工作。

审计插件包下载

在线下载

wget https://github.com/mcafee-enterprise/mysql-audit/releases/download/v1.1.12/audit-plugin-mysql-5.7-1.1.12-999-linux-x86_64.zip

4 安装审计

4.1 解压审计包

将下载的审计包上传至指定目录

unzip audit-plugin-mysql-5.7-1.1.12-999-linux-x86_64.zip

4.2 查看数据库插件目录

登录mysql数据库，查询MySQL插件目录

mysql> SHOW GLOBAL VARIABLES LIKE '%plugin_dir%';
+---------------+--------------------------+
| Variable_name | Value                    |
+---------------+--------------------------+
| plugin_dir    | /usr/lib64/mysql/plugin/ |
+---------------+--------------------------+
1 row in set (0.00 sec)

4.3 安装数据库审计插件

4.3.1 复制到插件目录

cp /home/mysql/audit-plugin-mysql-5.7-1.1.12-999/lib/libaudit_plugin.so /usr/lib64/mysql/plugin/

4.3.2 修改插件权限

chmod a+x /usr/lib64/mysql/plugin/libaudit_plugin.so

4.3.3 设置审计参数

1、查看mysql目录

whereis mysqld
mysqld: /usr/sbin/mysqld /usr/share/man/man8/mysqld.8.gz

2、执行初始配置

cd /home/mysql/audit-plugin-mysql-5.7-1.1.12-999/utils
chmod a+x offset-extract.sh
./offset-extract.sh /usr/sbin/mysqld
//offsets for: /usr/sbin/mysqld (5.7.39)
{"5.7.39","b57179ad7574dba5b56dcaff83a37c22", 7832, 7880, 3640, 4800, 456, 360, 0, 32, 64, 160, 544, 7996, 4368, 3656, 3664, 3668, 6080, 2072, 8, 7064, 7104, 7088, 13480, 148, 672, 0},

4.3.4 修改数据库审计配置

vi /etc/my.cnf

添加如下配置

[mysqld]
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' 

4.3.5 重启数据库生效配置

syetemctl restart mysqld

4.3.6 安装数据库插件

登录mysql,安装插件

mysql> install plugin audit soname 'libaudit_plugin.so';

4.3.7 查看插件

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     |
+----------------------------+----------+--------------------+--------------------+---------+
45 rows in set (0.00 sec)

4.3.8 查看插件版本

mysql> show global status like 'AUDIT_version'; 
+---------------+------------+
| Variable_name | Value      |
+---------------+------------+
| Audit_version | 1.1.12-999 |
+---------------+------------+
1 row in set (0.00 sec)

4.3.9 查看审计日志

审计日志存在于mysql-audit.json,查看文件路径

find / -name mysql-audit.json

查看日志

tail -f /var/lib/mysql/mysql-audit.json
{"msg-type":"header","date":"1677578116432","audit-version":"1.1.12-999","audit-protocol-version":"1.0","hostname":"ecs-f50a-1025352","mysql-version":"5.7.39","mysql-program":"/usr/sbin/mysqld","mysql-socket":"/var/lib/mysql/mysql.sock","mysql-port":"0","server_pid":"16123"}

以上为MySQL的数据库审计功能的安装步骤。