MySQL5.6 audit审计插件安装初探

作者：我家自动化 | 2024-06-04 14:01:45

踩

audit-plugin-mysql-5.6下载

(? : / \* . * ? \* / | \s) * ? [^{'} | "] (? . * ?) (? <! - -) [^{'} | "] (? : / \* . * ? \* / | \s) * ?

$(?:/\*.*?\*/|\s)*?['|"](?.*?)(?<!--\\)['|"](?:/\*.*?\*/|\s)*?$ |password(?:/\*.*?\*/|\s)*?(?:for(?:/\*.*?\*/|\s)*?\S+?)?(?:/\*.*?\*/|\s)*?=(?:/\*.*?\*/|\s)*?['|"](?.*?)(?<!--\\)['|"]|password(?:/\*.*?\*/|\s)*?['|"](?.*?)(?<!--\\)['|"] |
| audit_record_objs | |
| audit_sess_connect_attrs | ON |
| audit_socket_creds | ON |
| audit_uninstall_plugin | OFF |
| audit_validate_checksum | ON |
| audit_validate_offsets_extended | ON |
| audit_whitelist_cmds | BEGIN,COMMIT,PING |
| audit_whitelist_users | |
+---------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
29 rows in set (0.00 sec)

audit记录的命令,默认为记录所有命令

可以设置为任意dml、dcl、ddl的组合

如：audit_record_cmds=select,insert,delete,update

audit_record_objs

audit记录操作的对象,默认为记录所有对象,

可以用SET GLOBAL audit_record_objs=NULL设置为默认

也可以指定为下面的格式

audit_record_objs=,test.*,mysql.*,information_schema.*

测试如下:
查看是否开启audit功能:
mysql> show variables like 'audit_json_file';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| audit_json_file | OFF |
+-----------------+-------+
1 row in set (0.00 sec)

开启audit功能:
mysql> set global audit_json_file=on;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'audit_json_file';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| audit_json_file | ON |
+-----------------+-------+
1 row in set (0.00 sec)

审计test库的所有对象:
mysql> SET GLOBAL audit_record_objs='test.*';
Query OK, 0 rows affected (0.00 sec)

审计test库的insert,update和delete操作：
mysql> set global audit_record_cmds='insert,delete,update';
Query OK, 0 rows affected (0.00 sec)

查看记录文件的路径和名称信息:
mysql> show variables like 'audit_json_log_file';
+---------------------+------------------+
| Variable_name | Value |
+---------------------+------------------+
| audit_json_log_file | mysql-audit.json |
+---------------------+------------------+
1 row in set (0.00 sec)

文件路径如下:
mysql> system find / -name mysql-audit.json
/usr/local/mysql/data/mysql-audit.json

对test库下的person表做insert,update和delete测试:

mysql> insert into person values('5','liuyb','29');
Query OK, 1 row affected (0.03 sec)

mysql> update person set age='55' where name='dsf';
Query OK, 2 rows affected (0.04 sec)
Rows matched: 2 Changed: 2 Warnings: 0

mysql> delete from person where name='cf';
Query OK, 1 row affected (0.03 sec)

mysql> select * from person;
+----+----------+------+
| id | name | age |
+----+----------+------+
| 2 | dsf | 55 |
| 3 | dsf | 55 |
| 4 | chenfeng | 38 |
| 5 | liuyb | 29 |
+----+----------+------+
4 rows in set (0.00 sec)

打开/usr/local/mysql/data/mysql-audit.json文件查看审计数据:
[root@localhost lib]# cat /usr/local/mysql/data/mysql-audit.json
{"msg-type":"header","date":"1494389691599","audit-version":"1.1.2-694","audit-protocol-version":"1.0","hostname":"localhost","mysql-version":"5.6.27-log","mysql-program":"/usr/local/mysql/bin/mysqld","mysql-socket":"/tmp/mysql.sock","mysql-port":"3306","server_pid":"41506"}
{"msg-type":"activity","date":"1494389798201","thread-id":"6","query-id":"53","user":"root","priv_user":"root","ip":"","host":"localhost","connect_attrs":{"_os":"Linux","_client_name":"libmysql","_pid":"41662","_client_version":"5.6.27","_platform":"x86_64","program_name":"mysql"},"rows":"1","cmd":"insert","objects":[{"db":"test","name":"person","obj_type":"TABLE"}],"query":"insert into person values('5','liuyb','29')"}
{"msg-type":"activity","date":"1494389835611","thread-id":"6","query-id":"55","user":"root","priv_user":"root","ip":"","host":"localhost","connect_attrs":{"_os":"Linux","_client_name":"libmysql","_pid":"41662","_client_version":"5.6.27","_platform":"x86_64","program_name":"mysql"},"rows":"2","cmd":"update","objects":[{"db":"test","name":"person","obj_type":"TABLE"}],"query":"update person set age='55' where name='dsf'"}
{"msg-type":"activity","date":"1494390005360","thread-id":"6","query-id":"60","user":"root","priv_user":"root","ip":"","host":"localhost","connect_attrs":{"_os":"Linux","_client_name":"libmysql","_pid":"41662","_client_version":"5.6.27","_platform":"x86_64","program_name":"mysql"},"rows":"1","cmd":"delete","objects":[{"db":"test","name":"person","obj_type":"TABLE"}],"query":"delete from person where name='cf'"}

可以看到审计结果里有对test库person表的操作记录(insert,update和delete),至此，audit插件安装成功.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/15498/viewspace-2138826/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/15498/viewspace-2138826/

本文内容由网友自发贡献，转载请注明出处：https://www.wpsshop.cn/w/我家自动化/article/detail/672163