Percona Audit Log Plugin（mysql 审计）

原创

sql1101 2018-12-07 14:21:57 ©著作权

文章标签 审计 mysql percona 文章分类 MySQL 数据库

©著作权归作者所有：来自51CTO博客作者sql1101的原创作品，请联系作者获取转载授权，否则将追究法律责任

背景：当数据业务上或者其他的特殊情况时可能会进行审计，以便知道数据库当时所做的操作，今天给大家带来percona的审计插件

Percona Audit Log Plugin提供对特定服务器上执行的连接和查询活动的监视和记录。有关活动的信息将存储在XML日志文件中，其中每个事件将具有其NAME字段，其自己的唯一RECORD_ID字段和TIMESTAMP字段。此实现是MySQL Enterprise Audit Log Plugin的替代审计日志插件生成以下事件的日志：Audit - Audit事件表示审计日志记录已开始或已完成。记录开始时NAME字段为Audit，日志记录完成时为NoAudit。审计记录还包括服务器版本和命令行参数。

<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"="" />

1,安装：

审核日志插件随Percona Server一起提供，但默认情况下不会安装。要启用该插件，您必须运行以下命令

INSTALL PLUGIN audit_log SONAME 'audit_log.so';

验证插件是否安装成功

SHOW PLUGINS;

2，日志格式：

审核日志插件支持四种日志格式：OLD，NEW，JSON和CSV。 OLD和NEW格式基于XML，前者将日志记录属性输出为XML属性，后者输出为XML标记。记录的信息在所有四种格式中都是相同的。日志格式选择由audit_log_format变量控制。

<AUDIT_RECORD> <NAME>Quit</NAME> <RECORD>10902_2014-04-28T11:02:54</RECORD> <TIMESTAMP>2014-04-28T11:02:59 UTC</TIMESTAMP> <CONNECTION_ID>36</CONNECTION_ID> <STATUS>0</STATUS> <USER></USER> <PRIV_USER></PRIV_USER> <OS_LOGIN></OS_LOGIN> <PROXY_USER></PROXY_USER> <HOST></HOST> <IP></IP> <DB></DB> </AUDIT_RECORD>

3，实战：以下示例显示添加将受监控的用户

mysql> SET GLOBAL audit_log_include_accounts = 'user1@localhost,root@localhost'; Query OK, 0 rows affected (0.00 sec) If you you try to add users to both include and exclude lists server will show you the following error:

mysql> SET GLOBAL audit_log_exclude_accounts = 'user1@localhost,root@localhost'; ERROR 1231 (42000): Variable 'audit_log_exclude_accounts' can't be set to the value of 'user1@localhost,root@localhost' To switch from filtering by included user list to the excluded one or back, first set the currently active filtering variable to NULL:

mysql> SET GLOBAL audit_log_include_accounts = NULL; 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)

mysql> SET GLOBAL audit_log_exclude_accounts = "'user'@'host'"; Query OK, 0 rows affected (0.00 sec)

mysql> SET GLOBAL audit_log_exclude_accounts = '''user''@''host'''; Query OK, 0 rows affected (0.00 sec)

mysql> SET GLOBAL audit_log_exclude_accounts = ''user'@'host''; Query OK, 0 rows affected (0.00 sec) To see what users are currently in the on the list you can run:

mysql> SELECT @@audit_log_exclude_accounts; +------------------------------+ | @@audit_log_exclude_accounts | +------------------------------+ | 'user'@'host' | +------------------------------+ 1 row in set (0.00 sec)

--备注：监控的用户名必须和mysql.user里用户名一致，不然不生效

<AUDIT_RECORD NAME="Connect" RECORD="4971917_2016-08-22T09:09:10" TIMESTAMP="2016-08-22T09:12:21 UTC" CONNECTION_ID="6" STATUS="0" USER="user1" ;; this is a 'user' part of account in 5.7 PRIV_USER="user1" OS_LOGIN="" PROXY_USER="" HOST="localhost" ;; this is a 'host' part of account in 5.7 IP="" DB="" />

过渡掉user1(排除user1)

SET GLOBAL audit_log_exclude_accounts = 'user1@%';

总结： 1，数据库审计是一个非常实用和重要的功能 2，一般情况下不会开启这个功能，因为对性能消耗比较大 3，percona提供了这个功能，原生的Mysql社区版是没有的，只有企业版才有