等保审核 --- MySQL操作审计记录

环境介绍

• 系统版本: Centos 7
• MySQL版本: 5.7.19
• 架构: 主从架构
• 审计插件: audit-plugin-mysql-5.7-1.1.6-784-linux-x86_64.zip

操作过程

1. 安装MySQL 5.7.19与线上版本同步;
2. 安装Audit审计插件;
3. 开启Audit审计插件功能;
4. 配置就算重启数据库后依然会自动开启Audit;

配置说明:

/usr/local/mysql/data/mysql-audit.json为数据库的数据存储(data)目录,名称默认为此;

一、安装数据库

过程省略

二、安装Audit审计插件;

mysql> show variables like 'plugin_dir'
+---------------+------------------------------+
| Variable_name | Value |
+---------------+------------------------------+
| plugin_dir | /usr/local/mysql/lib/plugin/ |
+---------------+------------------------------+
~]# unzip audit-plugin-mysql-5.7-1.1.6-784-linux-x86_64.zip
~]# cd audit-plugin-mysql-5.7-1.1.6-784-linux-x86_64/lib
lib]# cp libaudit_plugin.so /usr/local/mysql/lib/plugin/
lib]# cd /usr/local/mysql/lib/plugin/
lib]# chmod 755 libaudit_plugin.so
mysql> install plugin AUDIT soname 'libaudit_plugin.so';
mysql> show plugins;
....
AUDIT | ACTIVE | AUDIT | libaudit_plugin.so | GPL
....

2.1 查看插件版本

mysql> show global status like '%audit%';
+------------------------+-----------+
| Variable_name | Value |
+------------------------+-----------+
| Audit_protocol_version | 1.0 |
| Audit_version | 1.1.6-784 |
+------------------------+-----------+

三、 开启Audit审计插件功能

mysql> show variables like '%audit_json_file%';
+-------------------------+-------+
| Variable_name | Value |
+-------------------------+-------+
| audit_json_file | OFF |
| audit_json_file_bufsize | 1 |
| audit_json_file_flush | OFF |
| audit_json_file_retry | 60 |
| audit_json_file_sync | 0 |
+-------------------------+-------+

# 开启插件服务
mysql> set global audit_json_file=1;

# 再次检查
mysql> show variables like '%audit_json_file%';
+-------------------------+-------+
| Variable_name | Value |
+-------------------------+-------+
| audit_json_file | ON |
| audit_json_file_bufsize | 1 |
| audit_json_file_flush | OFF |
| audit_json_file_retry | 60 |
| audit_json_file_sync | 0 |
+-------------------------+-------+

# 执行些SQL检查是否会记录;
mysql> show databases;
+--------------------+
| information_schema |
| menagerie |
| mysql |
| performance_schema |
| sys |
+--------------------+

# 查看日志是否有所记录
~]#    cat /usr/local/mysql/data/mysql-audit.json
{
"msg-type": "activity",
"date": "1598594856485",
"thread-id": "5",
"query-id": "37",
"user": "root",
"priv_user": "root",
"ip": "",
"host": "localhost",
"connect_attrs": {
"_os": "linux -
glibc2 .12 ","
_client_name ":"
libmysql ","
_pid ":"
21846 ","
_client_version ":"
5.7 .19 ","
_platform ":"
x86_64 ","
program_name ":"
mysql "},"
pid ":"
21846 ","
os_user ":"
root ","
appname ":". / mysql ","
rows ":"
5 ","
status ":"
0 ","
cmd ":"
show_databases ","
objects ":[{"
db ":"
information_schema ","
name ":" / tmp / #sql_47c4_0 ","
obj_type ":"
TABLE "}],"
query ":"
show databases "}

四、 配置就算重启数据库后依然会自动开启Audit;

~]# vim /etc/my.cnf
audit_json_file = on
plugin-load=AUDIT=libaudit_plugin.so

五、 配置审计指定命令

~] cd /app/audit-plugin-mysql-5.7-1.1.6-784/utils
utils] chmod +x offset-extract.sh
# 执行计算偏移量的脚本前需安装此服务;
utils] yum -y install gdb
# 计算偏移量;
utils] ./offset-extract.sh /usr/local/mysql/bin/mysqld
~] vim /etc/my.cnf
# 支持审计的SQL查看'http://bazaar.launchpad.net/~mysql/mysql-server/5.6/view/head:/sql/mysqld.cc#L3424'
audit_record_cmds='insert,update,delete,drop_db,create_db,alter_db,grant,truncate'
audit_offsets=7800, 7848, 3624, 4776, 456, 360, 0, 32, 64, 160, 536, 7964, 4352, 3648, 3656, 3660, 6048, 2072, 8, 7032, 7072, 7056, 13432, 148, 672