使用mcafee 审计插件对mysql进行审计
1 拷贝文件并授权 # cp libaudit_plugin.so /data/mysql/install/lib/plugin/
chmod 744 libaudit_plugin.so
2 加载审计插件：mysql> install plugin audit soname 'libaudit_plugin.so';
3 开启审计 set global audit_json_file=ON;
4 配置审计策略 （模式记录所有操作，配置策略后因不明原因只记录update和delete）
mysql> set global audit_record_cmds='update,delete,create,grant,alter,rename,drop';
5 查看audit策略 show variables like '%audit%';
6 查看审计文件： cat /data/mysql/data/mysql-audit.json
7 维护 1 文件切换 mv /data/mysql/data/mysql-audit.json /data/mysql/data/mysql-audit.json_bak;sql>set global audit_json_file=ON;
8 开机自启动审计（配置my.cnf）需要先使用mysql> install plugin audit soname 'libaudit_plugin.so';加载在修改my.cnf，否则无法启动mysql
#20220929 add for mysql audit
plugin-load=TRELLIX=libaudit_plugin.so
audit_record_cmds='update,delete,create,grant,alter,rename,drop'
audit_json_file=ON
[root@mgr-wjh-test-4 lib]# cp libaudit_plugin.so /data/mysql/install/lib/plugin/
[root@mgr-wjh-test-4 lib]# cd /data/mysql/install/lib/plugin/
[root@mgr-wjh-test-4 plugin]# chmod 744 libaudit_plugin.so
select version()
show global status like '%audit_version%'
show plugins;
mysql> install plugin audit soname 'libaudit_plugin.so';
Query OK, 0 rows affected (4.18 sec)
mysql> show global status like '%audit%';
+------------------------+------------+
| Variable_name | Value |
+------------------------+------------+
| Audit_protocol_version | 1.0 |
| Audit_version | 1.1.12-999 |
+------------------------+------------+
2 rows in set (0.01 sec)
mysql>show plugins;
| clone | ACTIVE | CLONE | mysql_clone.so | GPL |
| group_replication | ACTIVE | GROUP REPLICATION | group_replication.so | GPL |
| AUDIT | ACTIVE | AUDIT | libaudit_plugin.so | GPL
开启审计
show variables like 'audit_json';
set global audit_json_file=ON;
data >ll mysql-audit.json'
mysql> show variables like '%audit_json%'; #查看审计开启状态
+---------------------------------+-----------------------------------------------------+
| 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 |
| audit_json_log_file | mysql-audit.json |
| audit_json_socket | OFF |
| audit_json_socket_name | /var/run/db-audit/mysql.audit__data_mysql_data_3307 |
| audit_json_socket_retry | 10 |
| audit_json_socket_write_timeout | 1000 |
+---------------------------------+-----------------------------------------------------+
10 rows in set (0.01 sec)
mysql> set global audit_json_file=ON; #开启审计
Query OK, 0 rows affected (0.00 sec)
mysql> system ls -l /data/mysql/data/mysql-audit.json #检查审计文件
-rw-r----- 1 mysql mysql 720 9月 29 13:53 /data/mysql/data/mysql-audit.json
mysql>
mysql> select sleep(2); #执行查询-验证是否被记录
+----------+
| sleep(2) |
+----------+
| 0 |
+----------+
1 row in set (2.00 sec)
mysql> system cat /data/mysql/data/mysql-audit.json
{"msg-type":"header","date":"1664430780808","audit-version":"1.1.12-999","audit-protocol-version":"1.0","hostname":"mgr-wjh-test-4","mysql-version":"8.0.28","mysql-program":"/data/mysql/install/bin/mysqld","mysql-socket":"/data/mysql/tmp/mysql.sock","mysql-port":"3307","server_pid":"13477"}
{"msg-type":"activity","date":"1664430780809","thread-id":"10","query-id":"20","user":"root","priv_user":"root","ip":"","host":"localhost","_pid":"15870","_platform":"x86_64","_os":"Linux","_client_name":"libmysql","os_user":"root","_client_version":"8.0.28","program_name":"mysql","pid":"15870","os_user":"root","appname":"/data/mysql/install/bin/mysql","status":"0","cmd":"set_option","query":"set global audit_json_file=ON"}
{"msg-type":"activity","date":"1664430945691","thread-id":"10","query-id":"21","user":"root","priv_user":"root","ip":"","host":"localhost","_pid":"15870","_platform":"x86_64","_os":"Linux","_client_name":"libmysql","os_user":"root","_client_version":"8.0.28","program_name":"mysql","pid":"15870","os_user":"root","appname":"/data/mysql/install/bin/mysql","rows":"1","status":"0","cmd":"select","query":"select sleep(2)"} ##"cmd":"select","query":"select sleep(2)" 查询操作已经被记录。
mysql>
操作记录：1查看数据库；2创建数据库；3选择数据库；4建yqtable表;5创建用户testa;6授权用户
show databases;
create database yqtest;
use yqtest;
CREATE TABLE `yqtable` (
`id` int NOT NULL AUTO_INCREMENT COMMENT 'ID',
`user_name` varchar(50) NOT NULL DEFAULT '' COMMENT '人员名称',
PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1
create user testa@'%' identified by ''
grant all privileges on *.* to testa@‘%’
操作日志：
mysql> show databases; #11查看数据库；2创建数据库；3选择数据库；4建yqtable表;5创建用户testa;6授权用户；7刷新权限
+---------------------+
| Database |
+---------------------+
| bi_board |
| bms |
| crm |
| information_schema |
| master |
| mysql |
| nacos_k8s_test_db |
| oms |
| orc |
| performance_schema |
| seata_server |
| sys |
| sys_app |
| sys_authority |
| sys_configuration |
| sys_dict |
| sys_dict_test |
| sys_document |
| sys_file |
| sys_idp |
| sys_message |
| sys_short_generator |
| sys_strategy |
| sysbench |
| tms |
| tpcc |
| vendor |
| wms_visualboard |
| xwms_wh |
+---------------------+
29 rows in set (0.02 sec)
mysql> create database yqtest; # 1查看数据库；#22创建数据库；3选择数据库；4建yqtable表;5创建用户testa;6授权用户；7刷新权限
Query OK, 1 row affected (0.00 sec)
mysql> use yqtest; # 1查看数据库；2创建数据库；#33选择数据库；4建yqtable表;5创建用户testa;6授权用户；7刷新权限
Database changed
mysql> CREATE TABLE `yqtable` (
`id` int NOT NULL AUTO_INCREMENT COMMENT 'ID',
`user_name` varchar(50) NOT NULL DEFAULT '' COMMENT '人员名称',
PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 ; # 1查看数据库；2创建数据库；3选择数据库；#44建yqtable表;5创建用户testa;6授权用户；7刷新权限
Query OK, 0 rows affected (0.03 sec)
mysql> create user testa@'%' identified by '123456'; # 1查看数据库；2创建数据库；3选择数据库；4建yqtable表;#55创建用户testa;6授权用户；7刷新权限
Query OK, 0 rows affected (0.08 sec)
mysql> GRANT ALL PRIVILEGES ON YQTEST.* TO testa@'%'; # 1查看数据库；2创建数据库；3选择数据库；4建yqtable表;5创建用户testa;#66授权用户；7刷新权限
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges; # 1查看数据库；2创建数据库；3选择数据库；4建yqtable表;5创建用户testa;6授权用户；#77刷新权限
Query OK, 0 rows affected (0.00 sec)
mysql> system cat /data/mysql/data/mysql-audit.json
{"msg-type":"header","date":"1664430780808","audit-version":"1.1.12-999","audit-protocol-version":"1.0","hostname":"mgr-wjh-test-4","mysql-version":"8.0.28","mysql-program":"/data/mysql/install/bin/mysqld","mysql-socket":"/data/mysql/tmp/mysql.sock","mysql-port":"3307","server_pid":"13477"}
{"msg-type":"activity","date":"1664430780809","thread-id":"10","query-id":"20","user":"root","priv_user":"root","ip":"","host":"localhost","_pid":"15870","_platform":"x86_64","_os":"Linux","_client_name":"libmysql","os_user":"root","_client_version":"8.0.28","program_name":"mysql","pid":"15870","os_user":"root","appname":"/data/mysql/install/bin/mysql","status":"0","cmd":"set_option","query":"set global audit_json_file=ON"}
{"msg-type":"activity","date":"1664430945691","thread-id":"10","query-id":"21","user":"root","priv_user":"root","ip":"","host":"localhost","_pid":"15870","_platform":"x86_64","_os":"Linux","_client_name":"libmysql","os_user":"root","_client_version":"8.0.28","program_name":"mysql","pid":"15870","os_user":"root","appname":"/data/mysql/install/bin/mysql","rows":"1","status":"0","cmd":"select","query":"select sleep(2)"}
{"msg-type":"activity","date":"1664431569159","thread-id":"11","query-id":"0","user":"root","priv_user":"root","ip":"","host":"localhost","_pid":"15870","_platform":"x86_64","_os":"Linux","_client_name":"libmysql","os_user":"root","_client_version":"8.0.28","program_name":"mysql","pid":"15870","os_user":"root","appname":"/data/mysql/install/bin/mysql","cmd":"Connect","query":"Connect"}
{"msg-type":"activity","date":"1664431657367","thread-id":"11","query-id":"25","user":"root","priv_user":"root","ip":"","host":"localhost","_pid":"15870","_platform":"x86_64","_os":"Linux","_client_name":"libmysql","os_user":"root","_client_version":"8.0.28","program_name":"mysql","pid":"15870","os_user":"root","appname":"/data/mysql/install/bin/mysql","rows":"29","status":"0","cmd":"show_databases","objects":[{"db":"","name":"","obj_type":"TABLE"},{"db":"information_schema","name":"SCHEMATA","obj_type":"VIEW"},{"db":"mysql","name":"schemata","obj_type":"TABLE"},{"db":"mysql","name":"catalogs","obj_type":"TABLE"},{"db":"mysql","name":"collations","obj_type":"TABLE"},{"db":"mysql","name":"character_sets","obj_type":"TABLE"}],"query":"show databases"} # #111查看数据库；2创建数据库；3选择数据库；#44建yqtable表;5创建用户testa;6授权用户；7刷新权限
{"msg-type":"activity","date":"1664431681956","thread-id":"11","query-id":"26","user":"root","priv_user":"root","ip":"","host":"localhost","_pid":"15870","_platform":"x86_64","_os":"Linux","_client_name":"libmysql","os_user":"root","_client_version":"8.0.28","program_name":"mysql","pid":"15870","os_user":"root","appname":"/data/mysql/install/bin/mysql","status":"0","cmd":"create_db","query":"create database yqtest"} # 1查看数据库；##222创建数据库；3选择数据库；4建yqtable表;5创建用户testa;6授权用户；7刷新权限
{"msg-type":"activity","date":"1664431686733","thread-id":"11","query-id":"27","user":"root","priv_user":"root","ip":"","host":"localhost","_pid":"15870","_platform":"x86_64","_os":"Linux","_client_name":"libmysql","os_user":"root","_client_version":"8.0.28","program_name":"mysql","pid":"15870","os_user":"root","appname":"/data/mysql/install/bin/mysql","rows":"1","status":"0","cmd":"select","query":"SELECT DATABASE()"}# 1查看数据库；##222创建数据库；3选择数据库；4建yqtable表;5创建用户testa;6授权用户；7刷新权限
{"msg-type":"activity","date":"1664431686734","thread-id":"11","query-id":"28","user":"root","priv_user":"root","ip":"","host":"localhost","_pid":"15870","_platform":"x86_64","_os":"Linux","_client_name":"libmysql","os_user":"root","_client_version":"8.0.28","program_name":"mysql","pid":"15870","os_user":"root","appname":"/data/mysql/install/bin/mysql","rows":"1","status":"0","cmd":"Init DB","objects":[{"db":"yqtest","obj_type":"DATABASE"}],"query":"Init DB"} # 1查看数据库；2创建数据库；##333选择数据库；4建yqtable表;5创建用户testa;6授权用户；7刷新权限
{"msg-type":"activity","date":"1664431686738","thread-id":"11","query-id":"29","user":"root","priv_user":"root","ip":"","host":"localhost","_pid":"15870","_platform":"x86_64","_os":"Linux","_client_name":"libmysql","os_user":"root","_client_version":"8.0.28","program_name":"mysql","pid":"15870","os_user":"root","appname":"/data/mysql/install/bin/mysql","rows":"30","status":"0","cmd":"show_databases","objects":[{"db":"","name":"","obj_type":"TABLE"},{"db":"information_schema","name":"SCHEMATA","obj_type":"VIEW"},{"db":"mysql","name":"schemata","obj_type":"TABLE"},{"db":"mysql","name":"catalogs","obj_type":"TABLE"},{"db":"mysql","name":"collations","obj_type":"TABLE"},{"db":"mysql","name":"character_sets","obj_type":"TABLE"}],"query":"show databases"}
{"msg-type":"activity","date":"1664431686745","thread-id":"11","query-id":"30","user":"root","priv_user":"root","ip":"","host":"localhost","_pid":"15870","_platform":"x86_64","_os":"Linux","_client_name":"libmysql","os_user":"root","_client_version":"8.0.28","program_name":"mysql","pid":"15870","os_user":"root","appname":"/data/mysql/install/bin/mysql","status":"0","cmd":"show_tables","objects":[{"db":"","name":"","obj_type":"TABLE"},{"db":"information_schema","name":"TABLES","obj_type":"VIEW"},{"db":"mysql","name":"tables","obj_type":"TABLE"},{"db":"mysql","name":"schemata","obj_type":"TABLE"},{"db":"mysql","name":"catalogs","obj_type":"TABLE"},{"db":"mysql","name":"collations","obj_type":"TABLE"},{"db":"mysql","name":"tablespaces","obj_type":"TABLE"},{"db":"mysql","name":"table_stats","obj_type":"TABLE"}],"query":"show tables"}
{"msg-type":"activity","date":"1664431690960","thread-id":"11","query-id":"31","user":"root","priv_user":"root","ip":"","host":"localhost","_pid":"15870","_platform":"x86_64","_os":"Linux","_client_name":"libmysql","os_user":"root","_client_version":"8.0.28","program_name":"mysql","pid":"15870","os_user":"root","appname":"/data/mysql/install/bin/mysql","status":"0","cmd":"create_table","objects":[{"db":"yqtest","name":"yqtable","obj_type":"TABLE"}],"query":"CREATE TABLE `yqtable` (\n `id` int NOT NULL AUTO_INCREMENT COMMENT 'ID',\n `user_name` varchar(50) NOT NULL DEFAULT '' COMMENT '人员名称',\n PRIMARY KEY (`id`) USING BTREE\n) ENGINE=InnoDB AUTO_INCREMENT=1"}# 1查看数据库；2创建数据库；3选择数据库；##444建yqtable表;5创建用户testa;6授权用户；7刷新权限
{"msg-type":"activity","date":"1664431715892","thread-id":"11","query-id":"32","user":"root","priv_user":"root","ip":"","host":"localhost","_pid":"15870","_platform":"x86_64","_os":"Linux","_client_name":"libmysql","os_user":"root","_client_version":"8.0.28","program_name":"mysql","pid":"15870","os_user":"root","appname":"/data/mysql/install/bin/mysql","status":"0","cmd":"create_user","query":"create user testa@'%' identified by '***'"}# 1查看数据库；2创建数据库；3选择数据库；4建yqtable表;##555创建用户testa;6授权用户；7刷新权限
{"msg-type":"activity","date":"1664431750846","thread-id":"11","query-id":"33","user":"root","priv_user":"root","ip":"","host":"localhost","_pid":"15870","_platform":"x86_64","_os":"Linux","_client_name":"libmysql","os_user":"root","_client_version":"8.0.28","program_name":"mysql","pid":"15870","os_user":"root","appname":"/data/mysql/install/bin/mysql","status":"0","cmd":"grant","query":"GRANT ALL PRIVILEGES ON YQTEST.* TO testa@'%'"}# 1查看数据库；2创建数据库；3选择数据库；4建yqtable表;5创建用户testa;##666授权用户；7刷新权限
{"msg-type":"activity","date":"1664431756624","thread-id":"11","query-id":"34","user":"root","priv_user":"root","ip":"","host":"localhost","_pid":"15870","_platform":"x86_64","_os":"Linux","_client_name":"libmysql","os_user":"root","_client_version":"8.0.28","program_name":"mysql","pid":"15870","os_user":"root","appname":"/data/mysql/install/bin/mysql","status":"0","cmd":"flush","query":"flush privileges"}# 1查看数据库；2创建数据库；3选择数据库；4建yqtable表;5创建用户testa;6授权用户；##777刷新权限
mysql>
远端操作：
1、desc yqtable;
{"msg-type":"activity","date":"1664432670617","thread-id":"12","query-id":"62","user":"dbadmin","priv_user":"dbadmin","ip":"192.168.36.129","host":"192.168.36.129","_os":"Win64","_client_name":"libmysql","_pid":"30560","_thread":"47356","_platform":"AMD64","_client_version":"10.1.43","rows":"2","status":"0","cmd":"show_fields","objects":[{"db":"","name":"","obj_type":"TABLE"},{"db":"information_schema","name":"COLUMNS","obj_type":"VIEW"},{"db":"mysql","name":"columns","obj_type":"TABLE"},{"db":"mysql","name":"tables","obj_type":"TABLE"},{"db":"mysql","name":"schemata","obj_type":"TABLE"},{"db":"mysql","name":"catalogs","obj_type":"TABLE"},{"db":"mysql","name":"collations","obj_type":"TABLE"},{"db":"mysql","name":"character_sets","obj_type":"TABLE"}],"query":"desc yqtable"}
2、insert into yqtable(user_name) values('wzf0072')
{"msg-type":"activity","date":"1664432708862","thread-id":"12","query-id":"69","user":"dbadmin","priv_user":"dbadmin","ip":"192.168.36.129","host":"192.168.36.129","_os":"Win64","_client_name":"libmysql","_pid":"30560","_thread":"47356","_platform":"AMD64","_client_version":"10.1.43","rows":"1","status":"0","cmd":"insert","objects":[{"db":"yqtest","name":"yqtable","obj_type":"TABLE"}],"query":"insert into yqtable(user_name) values('wzf0072')"}
3、select * from yqtable
{"msg-type":"activity","date":"1664432721320","thread-id":"12","query-id":"76","user":"dbadmin","priv_user":"dbadmin","ip":"192.168.36.129","host":"192.168.36.129","_os":"Win64","_client_name":"libmysql","_pid":"30560","_thread":"47356","_platform":"AMD64","_client_version":"10.1.43","rows":"1","status":"0","cmd":"select","objects":[{"db":"yqtest","name":"yqtable","obj_type":"TABLE"}],"query":"select * from yqtable"}
