mysql5.7安装audit plugin

mysql5.7安装audit plugin

档引用自:

https://www.modb.pro/db/88152 感谢叶同学

背景

原来在mysql5.7.24版本中,使用server_audit审计日志插件(来源MariaDB )无问题,但在5.7.34中却出现问题,现使用另外开源插件audit-plugin实现审计日志

环境信息

mysql版本

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.7.34    |
+-----------+


登录后复制

安装过程

下载地址

https://github.com/mcafee/mysql-audit

解压

[root@yejf]/opt# unzip audit-plugin-mysql-5.7-1.1.4-725-linux-x86_64.zip
Archive:  audit-plugin-mysql-5.7-1.1.4-725-linux-x86_64.zip
   creating: audit-plugin-mysql-5.7-1.1.4-725/
   creating: audit-plugin-mysql-5.7-1.1.4-725/lib/
  inflating: audit-plugin-mysql-5.7-1.1.4-725/lib/libaudit_plugin.so  
  inflating: audit-plugin-mysql-5.7-1.1.4-725/COPYING  
  inflating: audit-plugin-mysql-5.7-1.1.4-725/THIRDPARTY.txt  
  inflating: audit-plugin-mysql-5.7-1.1.4-725/README.txt  
  inflating: audit-plugin-mysql-5.7-1.1.4-725/plugin-name.txt  
   creating: audit-plugin-mysql-5.7-1.1.4-725/utils/
  inflating: audit-plugin-mysql-5.7-1.1.4-725/utils/offset-extract.sh 

查看当前mysql插件目录

mysql> show global variables like 'plugin_dir';
+---------------+--------------------------+
| Variable_name | Value                    |
+---------------+--------------------------+
| plugin_dir    | /usr/lib64/mysql/plugin/ |
+---------------+--------------------------+
1 row in set (0.01 sec)

拷贝插件到目录,并赋权

[root@yejf]/opt# cp /opt/audit-plugin-mysql-5.7-1.1.4-725/lib/libaudit_plugin.so /usr/lib64/mysql/plugin/
[root@yejf]/opt# chown -R mysql:mysql /usr/lib64/mysql/plugin/libaudit_plugin.so 

进入mysql命令窗口,安装插件报错

mysql> INSTALL PLUGIN AUDIT SONAME 'libaudit_plugin.so';
ERROR 1123 (HY000): Can't initialize function 'AUDIT'; Plugin initialization function failed.

解决方法

运行插件的sh命令

[root@yejf]/opt/audit-plugin-mysql-5.7-1.1.4-725/utils# ll
总用量 4.0K
-rw-r--r-- 1 root root 3.7K 7月  12 2017 offset-extract.sh
[root@yejf]/opt/audit-plugin-mysql-5.7-1.1.4-725/utils# which mysqld
/usr/sbin/mysqld
[root@yejf]/opt/audit-plugin-mysql-5.7-1.1.4-725/utils# chmod 755 offset-extract.sh 
[root@yejf]/opt/audit-plugin-mysql-5.7-1.1.4-725/utils# ./offset-extract.sh /usr/sbin/mysqld
//offsets for: /usr/sbin/mysqld (5.7.34)
{"5.7.34","4a211fa462e69504ab7907c4158b0b8a", 7832, 7880, 3640, 4800, 456, 360, 0, 32, 64, 160, 544, 7996, 4368, 3648, 3656, 3660, 6080, 2072, 8, 7064, 7104, 7088},
[root@yejf]/opt/audit-plugin-mysql-5.7-1.1.4-725/utils#

在配置文件/etc/my.cnf 添加配置

[mysqld]
audit_json_file = on
plugin-load=AUDIT=libaudit_plugin.so
audit_record_cmds='insert,delete,update,create,drop,alter,grant,truncate'
audit_json_log_file=/home/mysql/mysql-audit.json
audit_offsets = 7832, 7880, 3640, 4800, 456, 360, 0, 32, 64, 160, 544, 7996, 4368, 3648, 3656, 3660, 6080, 2072, 8, 7064, 7104, 7088

再使用命令可以安装成功

mysql> INSTALL PLUGIN AUDIT SONAME 'libaudit_plugin.so';
Query OK, 0 rows affected (0.42 sec)

重启数据库,验证插件配置正确,

[root@yejf]/opt# systemctl stop mysqld
[root@yejf]/opt# systemctl start mysqld

重启后能够正常登陆,查看安装的插件

mysql> show plugins;
+----------------------------+----------+--------------------+--------------------+---------+
| Name                       | Status   | Type               | Library            | License |
+----------------------------+----------+--------------------+--------------------+---------+
...
| AUDIT                      | ACTIVE   | AUDIT              | libaudit_plugin.so | GPL     |
+----------------------------+----------+--------------------+--------------------+---------+

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

查看插件有哪些可配置参数

show variables like '%audit%'\G;

验证审计日志

数据库中操作

mysql> create database test;
Query OK, 1 row affected (0.00 sec)

mysql> create table tmp(id int ,info varchar(100));
ERROR 1046 (3D000): No database selected
mysql> use test;
Database changed
mysql> create table tmp(id int ,info varchar(100));
Query OK, 0 rows affected (0.01 sec)

mysql> insert into tmp values(1,'test');
Query OK, 1 row affected (0.00 sec)

mysql> select * from tmp;
+------+------+
| id   | info |
+------+------+
|    1 | test |
+------+------+
1 row in set (0.00 sec)

mysql> insert into tmp values(2,'text');
Query OK, 1 row affected (0.00 sec)

mysql> delete from tmp where id=1;
Query OK, 1 row affected (0.01 sec)

mysql> truncate tmp;
Query OK, 0 rows affected (0.01 sec)

mysql> drop table tmp;
Query OK, 0 rows affected (0.00 sec)

mysql> drop database test;
Query OK, 0 rows affected (0.00 sec)

可以看到,审计日志记录了配置对应类型的成功的操作

select无配置,无记录此类型日志

有语法报错的sql也不记录日志

[root@yejf]~mysql# tail -100f /home/mysql/mysql-audit.json
{"msg-type":"activity","date":"1627004425347","thread-id":"2","query-id":"9","user":"root","priv_user":"root","ip":"","host":"localhost","connect_attrs":{"_os":"Linux","_client_name":"libmysql","_pid":"49658","_client_version":"5.7.34","_platform":"x86_64","program_name":"mysql"},"pid":"49658","os_user":"mysql","appname":"mysql","cmd":"create_db","query":"create database test"}
{"msg-type":"activity","date":"1627004470135","thread-id":"2","query-id":"15","user":"root","priv_user":"root","ip":"","host":"localhost","connect_attrs":{"_os":"Linux","_client_name":"libmysql","_pid":"49658","_client_version":"5.7.34","_platform":"x86_64","program_name":"mysql"},"pid":"49658","os_user":"mysql","appname":"mysql","cmd":"create_table","objects":[{"db":"test","name":"tmp","obj_type":"TABLE"}],"query":"create table tmp(id int ,info varchar(100))"}
{"msg-type":"activity","date":"1627004499523","thread-id":"2","query-id":"16","user":"root","priv_user":"root","ip":"","host":"localhost","connect_attrs":{"_os":"Linux","_client_name":"libmysql","_pid":"49658","_client_version":"5.7.34","_platform":"x86_64","program_name":"mysql"},"pid":"49658","os_user":"mysql","appname":"mysql","rows":"1","cmd":"insert","objects":[{"db":"test","name":"tmp","obj_type":"TABLE"}],"query":"insert into tmp values(1,'test')"}
{"msg-type":"activity","date":"1627004505564","thread-id":"2","query-id":"17","user":"root","priv_user":"root","ip":"","host":"localhost","connect_attrs":{"_os":"Linux","_client_name":"libmysql","_pid":"49658","_client_version":"5.7.34","_platform":"x86_64","program_name":"mysql"},"pid":"49658","os_user":"mysql","appname":"mysql","rows":"1","cmd":"insert","objects":[{"db":"test","name":"tmp","obj_type":"TABLE"}],"query":"insert into tmp values(2,'text')"}
{"msg-type":"activity","date":"1627004517432","thread-id":"2","query-id":"18","user":"root","priv_user":"root","ip":"","host":"localhost","connect_attrs":{"_os":"Linux","_client_name":"libmysql","_pid":"49658","_client_version":"5.7.34","_platform":"x86_64","program_name":"mysql"},"pid":"49658","os_user":"mysql","appname":"mysql","rows":"1","cmd":"delete","objects":[{"db":"test","name":"tmp","obj_type":"TABLE"}],"query":"delete from tmp where id=1"}
{"msg-type":"activity","date":"1627004529053","thread-id":"2","query-id":"19","user":"root","priv_user":"root","ip":"","host":"localhost","connect_attrs":{"_os":"Linux","_client_name":"libmysql","_pid":"49658","_client_version":"5.7.34","_platform":"x86_64","program_name":"mysql"},"pid":"49658","os_user":"mysql","appname":"mysql","cmd":"truncate","objects":[{"db":"test","name":"tmp","obj_type":"TABLE"}],"query":"truncate tmp"}
{"msg-type":"activity","date":"1627004532532","thread-id":"2","query-id":"20","user":"root","priv_user":"root","ip":"","host":"localhost","connect_attrs":{"_os":"Linux","_client_name":"libmysql","_pid":"49658","_client_version":"5.7.34","_platform":"x86_64","program_name":"mysql"},"pid":"49658","os_user":"mysql","appname":"mysql","cmd":"drop_table","objects":[{"db":"test","name":"tmp","obj_type":"TABLE"}],"query":"drop table tmp"}
{"msg-type":"activity","date":"1627004542257","thread-id":"2","query-id":"21","user":"root","priv_user":"root","ip":"","host":"localhost","connect_attrs":{"_os":"Linux","_client_name":"libmysql","_pid":"49658","_client_version":"5.7.34","_platform":"x86_64","program_name":"mysql"},"pid":"49658","os_user":"mysql","appname":"mysql","cmd":"drop_db","query":"drop database test"}

参数配置

可参考网址

https://github.com/mcafee/mysql-audit/wiki/Configuration

可用的审计插件系统变量:

  • audit_json_log_file : json 日志文件名。如果启用了 audit_json_file 选项,则会将审计跟踪写入此文件。值可以是绝对路径或相对于 MySQL 数据目录。默认值:mysql-audit.json。
  • audit_json_file : json 日志文件 ON|OFF。
  • audit_json_file_sync:json 日志文件同步周期。如果此变量的值大于 0,则每次 audit_json_file_sync 写入后,审计日志将同步到磁盘。默认值:0。
  • audit_json_file_flush:调用set global audit_json_file_flush=on 将导致日志文件刷新(关闭并重新打开日志)。这可用于类似于 MySQL 管理其日志文件的方式轮换日志。请参阅:http : dev.mysql.com/doc/refman/5.5/en/log-file-maintenance.html。如需进一步说明,请参阅问题#140。
  • audit_json_socket_name : json UNIX 套接字名称。如果启用了 audit_json_socket 选项,则会将审计跟踪写入此 UNIX 套接字。
  • audit_json_socket : json UNIX 套接字开|关。
  • audit_json_socket_write_timeout:提供写入 json 套接字的超时(以毫秒为单位)。当前值为 1000(= 1 秒)。零值禁用超时。目前,由于内部 MySQL API,最小实际超时为 1000 毫秒。(如果您提供较小但非零的值,则将其四舍五入。)我们希望未来的版本将提供更短的超时。在 1.1.3 版中引入。
  • audit_uninstall_plugin:审计卸载插件 ON|OFF(仅限命令行/conf 文件)。如果禁用,尝试通过 sql UNINSTALL 命令卸载 AUDIT 插件将失败。通过卸载插件提供额外的安全性。还可以防止CVE-2010-1621影响版本高达 5.1.46。
  • audit_validate_checksum : mysqld 二进制校验和验证 ON|OFF。请参阅安装页面中的故障排除部分。
  • audit_checksum:要验证的 mysqld 校验和(仅限命令行/conf 文件)。当存在 audit_offsets 时使用。如果指定的校验和与计算的不匹配,插件将不会加载。当手动设置偏移量并且您希望避免在升级 mysqld 后未经验证而使用手动偏移量时,这很有用。
  • audit_record_cmds:以逗号分隔的命令列表,用于记录到审计跟踪。例如:insert,update,delete
  • audit_record_objs:要记录到审计跟踪的对象(表)的逗号分隔列表。表名应指定为:database.table 。支持通配符,并且可以指定:*.mytablemydb.* 。指定:{} 作为列表的一部分以包含空集以捕获活动没有对象的情况(例如连接和退出)。
  • audit_whitelist_users:未记录查询的白名单用户的逗号分隔列表。指定:{} 作为列表的一部分包含空用户。
  • audit_whitelist_cmds:未记录查询的白名单 cmds 的逗号分隔列表。在 1.0.6 版中引入。
  • audit_force_record_logins:强制日志记录:连接、退出和登录失败命令,无论audit_record_cmdsaudit_record_objs 变量中的设置如何。开|关。默认值:关。在 1.0.8 版中引入。
  • audit_header_msg:标头消息记录 ON|OFF。默认值:开。在 1.0.6 版中引入。
  • audit_password_masking_cmds:将应用密码屏蔽正则表达式的逗号分隔命令列表。默认值包括可能包含密码子句的 MySQL 命令:CREATE_USER,GRANT,SET_OPTION,SLAVE_START,CREATE_SERVER,ALTER_SERVER,CHANGE_MASTER . 在 1.0.6 版中引入。
  • audit_password_masking_regex:用于密码屏蔽的符合 PCRE 的正则表达式。正则表达式将仅应用于具有在以下位置指定的命令类型的语句:audit_password_masking_cmds 。在 1.0.6 版引入。
  • audit_json_file_retry : json 日志文件重试间隔。如果插件无法打开/写入 json 日志文件,将以秒为单位重试打开每个指定的时间间隔。设置为 0 以禁用重试。默认为 60 秒。在 1.0.6 版中引入。
  • audit_json_socket_retry : json 套接字重试间隔。如果插件无法连接/写入 json 审计套接字,将重试连接每个指定的时间间隔(以秒为单位)。设置为 0 以禁用重试。默认为 10 秒。在 1.0.6 版中引入。
  • audit_json_file_bufsize:用于日志记录的 json 文件缓冲区大小(以字节为单位)。值 0 表示默认大小,值 1 表示无缓冲。最大值:262144 (256KB)。在记录大型语句(大于 4KB 的日志条目)时,较大的值可能会提高性能。默认为 0。如果在运行时需要更改,则执行刷新以使新值生效。默认为开。在 1.0.8 版中引入。
  • audit_client_capabilities:如果启用,插件将客户端功能位图的值作为无符号 64 位值发送。默认为关闭。在 1.1.1 版中引入。
  • audit_sess_connect_attrs:如果启用,插件会发送会话连接属性。默认为开。目前仅在 MySQL 5.6 和 5.7 上受支持。在 1.1.1 版中引入。注意:连接属性的日志记录还需要 MySQL 5.6 及更高版本的客户端才能连接到服务器。连接属性是在 5.6 中作为通信协议的一部分添加的。启用后,表单的 json 条目将添加到每个日志条目中:"connect_attrs":{"_os":"Linux","_client_name":"libmysql","_pid":"11450","_client_version":"5.6.20-68.0","_platform":"x86_64","program_name":"mysql"} . 有关 MySQL 中连接属性的更多信息,请参阅:https : dev.mysql.com/doc/refman/5.6/en/performance-schema-connection-attribute-tables.html。
  • audit_socket_creds:如果启用,插件会发送有关客户端进程的信息,例如 PID、应用程序名称和拥有它的用户名。默认为开。在 1.1.2 版中引入。
  • audit_before_after:控制插件是在执行当前 SQL 语句之前还是之后写入其日志记录,或两者兼而有之。可能的值'before''after''both' 。默认为'after' 。在 1.1.2 版中引入。

总结

  • 该插件未找到轮循覆盖的参数,即所有记录的日志都会记录到一个文件里,需要监控其大小增长情况,但通常只需要记录DDL操作,日志并不会很多

  • 如果对日志文件做了rm删除操作,需要重新开关一下插件,会重新生成日志文件

    mysql> set global audit_json_file=off;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> set global audit_json_file=on;
    Query OK, 0 rows affected (0.00 sec)
    
  • 如果想清空原先的日志,可以使用命令echo > mysql-audit.json 来清除,不影响当前日志的记录

updatedupdated2021-12-132021-12-13