MySQL审计插件- Audit Log

一、插件介绍
二、插件安装
1.安装插件
2.查看插件
3.日志格式
三、插件的使用
1.开始审计
2.查看审计日志
四、审计种类
1.基于用户审计
2.基于命令审计
3.基于DB审计
一、插件介绍

Percona公司的官方介绍：

Percona Audit Log Plugin provides monitoring and logging of connection and query activity that were performed on specific server. Information about the activity will be stored in the XML log file where each event will have its NAME field, its own unique RECORD_ID field and a TIMESTAMP field. This implementation is alternative to the MySQL Enterprise Audit Log Plugin

支持审计的事件：

Audit - Audit event indicates that audit logging started or finished. NAME field will be Audit when logging started and NoAudit when logging finished. Audit record also includes server version and command-line arguments.
Example of the Audit event:

<AUDIT_RECORD
“NAME”=“Audit”
“RECORD”=“1_2014-04-29T09:29:40”
“TIMESTAMP”=“2014-04-29T09:29:40 UTC”
“MYSQL_VERSION”=“5.6.17-65.0-655.trusty”
“STARTUP_OPTIONS”="–basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql --log-error=/var/log/mysql/error.log --pid-file=/var/run/mysqld/mysqld.pid --socket=/var/run/mysqld/mysqld.sock --port=3306"
“OS_VERSION”=“x86_64-debian-linux-gnu”,
/>
Connect/Disconnect - Connect record event will have NAME field Connect when user logged in or login failed, or Quit when connection is closed. Additional fields for this event are CONNECTION_ID, STATUS, USER, PRIV_USER, OS_LOGIN, PROXY_USER, HOST, and IP. STATUS will be 0 for successful logins and non-zero for failed logins.
Example of the Disconnect event:

<AUDIT_RECORD
“NAME”=“Quit”
“RECORD”=“24_2014-04-29T09:29:40”
“TIMESTAMP”=“2014-04-29T10:20:13 UTC”
“CONNECTION_ID”=“49”
“STATUS”=“0”
“USER”=""
“PRIV_USER”=""
“OS_LOGIN”=""
“PROXY_USER”=""
“HOST”=""
“IP”=""
“DB”=""
/>
Query - Additional fields for this event are: COMMAND_CLASS (values come from the com_status_vars array in the sql/mysqld.cc` file in a MySQL source distribution. Examples are select, alter_table, create_table, etc.), CONNECTION_ID, STATUS (indicates error when non-zero), SQLTEXT (text of SQL-statement), USER, HOST, OS_USER, IP. Possible values for the NAME name field for this event are Query, Prepare, Execute, Change user, etc.
Example of the Query event:

<AUDIT_RECORD
“NAME”=“Query”
“RECORD”=“23_2014-04-29T09:29:40”
“TIMESTAMP”=“2014-04-29T10:20:10 UTC”
“COMMAND_CLASS”=“select”
“CONNECTION_ID”=“49”
“STATUS”=“0”
“SQLTEXT”=“SELECT * from mysql.user”
“USER”=“root[root] @ localhost []”
“HOST”=“localhost”
“OS_USER”=""
“IP”=""
/>

二、插件安装

1.安装插件
mysql> INSTALL PLUGIN audit_log SONAME ‘audit_log.so’;
Query OK, 0 rows affected (0.44 sec)

2.查看插件
mysql> show plugins;
±------------------------------±---------±-------------------±---------------------±--------+
| Name | Status | Type | Library | License |
±------------------------------±---------±-------------------±---------------------±--------+

| audit_log | ACTIVE | AUDIT | audit_log.so | GPL |
±------------------------------±---------±-------------------±---------------------±--------+
54 rows in set (0.00 sec)

3.日志格式

The audit log plugin supports four log formats: OLD, NEW, JSON, and CSV. OLD and NEW formats are based on XML, where the former outputs log record properties as XML attributes and the latter as XML tags. Information logged is the same in all four formats. The log format choice is controlled by audit_log_format variable.

Example of the OLD format:

<AUDIT_RECORD
“NAME”=“Query”
“RECORD”=“2_2014-04-28T09:29:40”
“TIMESTAMP”=“2014-04-28T09:29:40 UTC”
“COMMAND_CLASS”=“install_plugin”
“CONNECTION_ID”=“47”
“STATUS”=“0”
“SQLTEXT”=“INSTALL PLUGIN audit_log SONAME ‘audit_log.so’”
“USER”=“root[root] @ localhost []”
“HOST”=“localhost”
“OS_USER”=""
“IP”=""
/>
Example of the NEW format:

<AUDIT_RECORD>
Quit
10902_2014-04-28T11:02:54
2014-04-28T11:02:59 UTC
<CONNECTION_ID>36</CONNECTION_ID>
0

<PRIV_USER></PRIV_USER>
<OS_LOGIN></OS_LOGIN>
<PROXY_USER></PROXY_USER>



</AUDIT_RECORD>
Example of the JSON format:

{“audit_record”:{“name”:“Query”,“record”:“4707_2014-08-27T10:43:52”,“timestamp”:“2014-08-27T10:44:19 UTC”,“command_class”:“show_databases”,“connection_id”:“37”,“status”:0,“sqltext”:“show databases”,“user”:“root[root] @ localhost []”,“host”:“localhost”,“os_user”:"",“ip”:""}}

Example of the CSV format:

“Query”,“49284_2014-08-27T10:47:11”,“2014-08-27T10:47:23 UTC”,“show_databases”,“37”,0,“show databases”,“root[root] @ localhost []”,“localhost”,"",""

三、插件的使用

1.开始审计
mysql> SET GLOBAL audit_log_include_accounts = ‘user1@localhost,root@localhost’;

Query OK, 0 rows affected (0.00 sec)

mysql> select @@audit_log_include_accounts;
±-------------------------------+
| @@audit_log_include_accounts |
±-------------------------------+
| user1@localhost,root@localhost |
±-------------------------------+
1 row in set (0.00 sec)

下面会介绍，具体的审计范围。参考第四节。

2.查看审计日志
mysql> show variables like ‘%audit%’;
±----------------------------±-------------------------------+
| Variable_name | Value |
±----------------------------±-------------------------------+
| audit_log_buffer_size | 1048576 |
| audit_log_exclude_accounts | |
| audit_log_exclude_commands | |
| audit_log_exclude_databases | |
| audit_log_file | audit.log |
| audit_log_flush | OFF |
| audit_log_format | OLD |
| audit_log_handler | FILE |
| audit_log_include_accounts | user1@localhost,root@localhost |
| audit_log_include_commands | |
| audit_log_include_databases | |
| audit_log_policy | ALL |
| audit_log_rotate_on_size | 0 |
| audit_log_rotations | 0 |
| audit_log_strategy | ASYNCHRONOUS |
| audit_log_syslog_facility | LOG_USER |
| audit_log_syslog_ident | percona-audit |
| audit_log_syslog_priority | LOG_INFO |
±----------------------------±-------------------------------+

[root@szgl-mysql-jinyftest01-1154 data]# tail -f audit.log
COMMAND_CLASS=“show_variables”
CONNECTION_ID=“124”
STATUS=“0”
SQLTEXT=“show variables like ‘%audit%’”
USER=“root[root] @ localhost []”
HOST=“localhost”
OS_USER=""
IP=""
DB=""
/>

四、审计种类

1.基于用户审计
需要审计的用户：

mysql> SET GLOBAL audit_log_include_accounts = ‘user1@localhost,root@localhost’; Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)

不需要审计的用户

mysql> SET GLOBAL audit_log_exclude_accounts = ‘user1@localhost,root@localhost’;
Query OK, 0 rows affected (0.00 sec)

2.基于命令审计
mysql> SET GLOBAL audit_log_include_commands= ‘set_option,create_db’;

Query OK, 0 rows affected (0.00 sec)

3.基于DB审计
mysql> SET GLOBAL audit_log_include_databases = ‘test,mysql,db1’;
Query OK, 0 rows affected (0.00 sec)

mysql> SET GLOBAL audit_log_include_databases= ‘db1,```db3"`’;
Query OK, 0 rows affected (0.00 sec)

参考文献：https://www.percona.com/doc/percona-server/5.7/management/audit_log_plugin.html