MariaDB Audit Plugin 1.2

https://mariadb.com/kb/en/mariadb/about-the-mariadb-audit-plugin/

Auditing regulations are used by many enterprises to ensure they comply with laws and industry standards. Such regulations often require processes for tracking user access to data in databases.

To address this issue in MariaDB & MySQL, we have developed the MariaDB Audit Plugin.

The Audit Plugin comes standard as part of MariaDB Server and MariaDB Enterprise, so you do not need to download this separately. This download is provided for those wishing to add auditing to MySQL.

通过对MySQL进行审计，可以记录谁连接到服务器，使用了什么sql语句，访问了哪些表等。可以将审计内容存储到日志文件或者发送到本地的syslogd守护进程，以syslog的形式输出日志。
1、  mariadb的审计插件能工作在mariadb、mysql和percona server。插件为server_audit.so（windows为server_audit.dll）。该插件需要被拷贝到mysql的插件目录，MySQL的插件目录为：

加载安装该插件
[mysqld]
plugin-load=server_audit=server_audit.so
重启MySQL服务！
 
另一种安装该插件的方法是：
mysql> install plugin server_audit SONAME 'server_audit.so';
不需要重启MySQL服务！

查看全局变量



打开审计功能：
mysql> set global server_audit_logging=on;
 
变量可以通过set语句打开，但是服务重启后会失效，可以通过在配置文件添加避免这个问题：
[mysqld]
server_audit_logging=on

变量解释：
server_audit_events :指定记录事件的类型，用逗号分隔多个值（connect,query,table），如果开启了查询缓存（query cache），查询直接从缓存返回数据，将不会有table的记录；

Logging events
A full list of related system variables are detailed at Server_Audit System Variables, and status variables at Server_Audit Status Variables. Some of the major ones are highlighted below.
By default, logging is set to off. To enable it, set the server_audit_logging variable to ON.
There are 5 types of log records.
Type DescriptionIntroduced
CONNECTION Logs connects, disconnects and failed connects (including the error code).
QUERY Queries issued and their results (in plain text), including failed queries due to syntax or permission errors.
TABLE Which tables were affected by query execution.
QUERY_DDL Works as the 'QUERY' value, but filters only DDL-type queries (CREATE, ALTER, etc).1.2.0 (MariaDB 5.5.42)
QUERY_DML Works as the 'QUERY' value, but filters only DML-type queries (INSERT, UPDATE, etc).1.2.0 (MariaDB 5.5.42)
The server_audit_events variable specifies which of the five events to log, taking a comma-separated list of the event types as an argument, for example:


[mysqld]
...
server_audit_events=connect,query
Note that QUERY records are stored in plain text, which can be a security risk. As an alternative, TABLE records still indicate which user accessed which table, but do not record the full query and results. Also note that if the query cache is enabled, and a query is returned from the query cache, no TABLE records appear in the log, as the server didn't open or access any tables
server_audit_output_type : file （输出日志和syslog分离，则需要设置为file。也可以设置为syslog，表示输出日志到syslog中）；
server_audit_file_path  :  server_audit.log（server_audit.log的默认路径在mysql的数据目录），可以设置成其他路径；
server_audit_file_rotate_size : 1000000（设置server_audit.log大小达到限制后进行轮替）；
server_audit_file_rotations : 9 （日志文件数量限制为9个）；
server_audit_incl_users : 需要进行行为审计的用户；
server_audit_excl_users : 不需要进行行为审计的用户（connect不受该设置影响）；
server_audit_file_rotate_now : 设置该值为on表示强制进行日志轮替；
server_audit_syslog_ident : mysql-server_auditing（设置ident，作为syslog记录的一部分）；
server_audit_syslog_info : 指定字符串加入到syslog的输出中；

查看audit插件是否在运行状态：

server_audit_active :ON （表示server_audit插件在运行）；
server_audit_current_log : server_audit.log（审计日志路径和日志名）；
server_audit_last_error : 错误消息；
server_audit_writes_failed : 因错误没有记录的日志条目数
 
5、  设置变量
mysql> set GLOBAL server_audit_events='CONNECT,QUERY,TABLE';
或者只设置一种或两种事件类型，如（set GLOBAL server_audit_events='CONNECT'; set GLOBAL server_audit_events='CONNECT,TABLE';）
 
set GLOBAL server_audit_excl_users='xie'; （同时设置server_audit_excl_users和server_audit_incl_users两个变量，server_audit_incl_users的优先级较高）
 
 
mysql> set GLOBAL server_audit_file_rotate_size=2000000;（单位为字节）
 
mysql> set GLOBAL server_audit_file_rotations=10;
 
为了使MySQL重启设置的变量仍然生效，需要在配置文件添加相应配置：
[mysqld]
server_audit_events='CONNECT,QUERY,TABLE'
server_audit_logging=on
server_audit_excl_users='xie'
server_audit_file_rotate_size=2000000
server_audit_file_rotations=10
 
6、  审计用户行为：
/usr/local/mysql/bin/mysql -uxiao -p123456 -h192.168.139.131  -P3306
mysql> drop table t2;
mysql> delete from t1 where id=2;
mysql> insert into t1 values(5,'Nicholas');
mysql> update t1 set name='xie' where id=5;
mysql> truncate table t3;
mysql> drop database MariaDB;
mysql> create database MariaDB;
 mysql> create table MariaDB(id int not null Auto_increment,name varchar(20),address varchar(50),primary key(id));
 
 查看审计日志：
20140916 18:17:34,backup,xiao,192.168.139.128,4,9,QUERY,,'drop table t2',0   
20140916 19:16:03,backup,xiao,192.168.139.128,12,34,QUERY,,'delete from t1 where id=2',0
20140916 19:16:58,backup,xiao,192.168.139.128,12,36,QUERY,,'insert into t1 values(5,\'Nicholas\')',0
20140916 19:21:14,backup,xiao,192.168.139.128,12,37,QUERY,,'update t1 set name=\'xie\' where id=5',0
20140916 19:22:36,backup,xiao,192.168.139.128,12,39,QUERY,,'truncate table t3',0
20140916 19:23:42,backup,xiao,192.168.139.128,12,41,QUERY,,'drop database MariaDB',0
20140916 20:00:35,backup,xiao,192.168.139.128,12,49,QUERY,,'create database MariaDB',0
20140916 20:08:51,backup,xiao,192.168.139.128,12,57,QUERY,,'create table MariaDB(id int not null Auto_increment,name varchar(20),address varchar(50),primary key(id))',0
 
 审计日志格式解析：
[timestamp],[serverhost],[username],[host],[connectionid],[queryid],[operation],[database],[object],[retcode]
 
7、  卸载server_audit
mysql> UNINSTALL PLUGIN server_audit;
 mysql> show variables like '%audit%';
Empty set (0.00 sec)
 
为了防止server_audit 插件被卸载，需要在配置文件中添加:
[mysqld]
server_audit=FORCE_PLUS_PERMANENT
重启MySQL生效~
 
值得注意的是，应该在server_audit插件被安装好，并且已经运行之后添加这些配置，否则过早在配置文件添加这个选项，会导致MySQL发生启动错误！
 mysql> UNINSTALL PLUGIN server_audit;
ERROR 1702 (HY000): Plugin 'server_audit' is force_plus_permanent and can not be unloaded


8、  MariaDB Audit Plugin  VS  init-connect+binlog
1）、init-connect+binlog方案要求用户对日志表至少有insert权限，每添加一个新用户都要进行授权，显得比较麻烦；而MariaDB Audit Plugin默认会对所有用户进行行为审计，不需要对新添加的用户进行授权，MariaDB Audit Plugin还可以指定对哪些用户进行行为审计，哪些用户不需要进行行为审计；
2）、init-connect+binlog方案无法对具有super权限的用户进行行为审计，而MariaDB Audit Plugin可以对所有用户进行行为审计，包括具有super权限的用户；
3）、init-connect+binlog方案需要修改配置文件之后重启MySQL生效，而MariaDB Audit Plugin可以在线进行配置，无需重启服务生效；
4）、init-connect+binlog方案审计信息输出到binlog中，MariaDB Audit Plugin可以选择将审计信息输出到syslog或者自定义的路径；