mysql審計的示例分析

發布時間：2021-11-01 15:06:20 來源：億速云閱讀：113 作者：小新欄目：MySQL數據庫

這篇文章給大家分享的是有關mysql審計的示例分析的內容。小編覺得挺實用的，因此分享給大家做個參考，一起跟隨小編過來看看吧。

mysql審計插件

mysql審計的示例分析 offset-extract.txt

| GPL |
| PERFORMANCE_SCHEMA | ACTIVE | STORAGE ENGINE | NULL | GPL |
| FEDERATED | DISABLED | STORAGE ENGINE | NULL | GPL |
| ARCHIVE | ACTIVE | STORAGE ENGINE | NULL | GPL |
| partition | ACTIVE | STORAGE ENGINE | NULL | GPL |
| AUDIT | ACTIVE | AUDIT | libaudit_plugin.so | GPL |
+----------------------------+----------+--------------------+--------------------+---------+
43 rows in set (0.00 sec)可以看到最后一行有AUDIT libaudit_plugin.so的字樣，說明安裝成功.

mysql> SHOW GLOBAL VARIABLES LIKE 'audit%';
+---------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value |
+---------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| audit_before_after | after |
| audit_checksum | |
| audit_client_capabilities | OFF |
| audit_delay_cmds | |
| audit_delay_ms | 0 |
| audit_force_record_logins | OFF |
| audit_header_msg | ON |
| 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__usr_local_mysql_data_3306 |
| audit_json_socket_retry | 10 |
| audit_offsets | |
| audit_offsets_by_version | ON |
| audit_password_masking_cmds | CREATE_USER,GRANT,SET_OPTION,SLAVE_START,CREATE_SERVER,ALTER_SERVER,CHANGE_MASTER,UPDATE |
| audit_password_masking_regex | identified(?:/\*.*?\*/|\s)*?by(?:/\*.*?\*/|\s)*?(?:password)?(?:/\*.*?\*/|\s)*?['|"](?.*?)(?<!--\\)['|"]|password(?:/\*.*?\*/|\s)*?\((?:/\*.*?\*/|\s)*?['|"](?.*?)(?<!--\\)['|"](?:/\*.*?\*/|\s)*?\)|password(?:/\*.*?\*/|\s)*?(?:for(?:/\*.*?\*/|\s)*?\S+?)?(?:/\*.*?\*/|\s)*?=(?:/\*.*?\*/|\s)*?['|"](?.*?)(?<!--\\)['|"]|password(?:/\*.*?\*/|\s)*?['|"](?.*?)(?<!--\\)['|"] |<br --&gt="" |="" audit_record_cmds="" ="" || 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: 0mysql> 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插件安裝成功.

ps:比較重要的地方。。。。

Offset Extraction指定，目前macfee audit plugin還沒有提供統一的API的，所以要手動提取offset

1) 下載官方腳本：https://raw.github.com/mcafee/mysql-audit/master/offset-extract/offset-extract.sh

2) chmod u+x offset-extract.sh

3) ./offset-extract.sh /usr/local/mysql/bin/mysqld

[root@10_5_12_15 audit-plugin-mysql-5.5]# ./offset-extract.sh /usr/local/mysql/bin/mysqld
//offsets for: /usr/local/mysql/bin/mysqld (5.5.21-rel25.1-log)
{"5.5.21-rel25.1","a877e71dd4ddc965390714ae8be2e540", 6456, 6504, 4064, 4504, 104, 2576},

上面的6456, 6504, 4064, 4504, 104, 2576數字要添加到/etc/my.cnf的 [mysqld]下

4、配置和驗證

1）在/etc/my.cnf中的[mysqld]下添加下面兩行

#Audit Plugin#
plugin-load=AUDIT=libaudit_plugin.so
audit_offsets=6456, 6504, 4064, 4504, 104, 2576

感謝各位的閱讀！關于“mysql審計的示例分析”這篇文章就分享到這里了，希望以上內容可以對大家有一定的幫助，讓大家可以學到更多知識，如果覺得文章不錯，可以把它分享出去讓更多的人看到吧！

向AI問一下細節

亚洲激情专区-91九色丨porny丨老师-久久久久久久女国产乱让韩-国产精品午夜小视频观看

mysql審計的示例分析

mysql審計插件

猜你喜歡

亚洲激情专区-91九色丨porny丨老师-久久久久久久女国产乱让韩-国产精品午夜小视频观看

mysql審計的示例分析

mysql審計插件

猜你喜歡

最新資訊

相關推薦

相關標簽