Friday, 2 February 2018

MySQL Database Auditing in version 5.6


The requirement to track access to database servers and the data itself is not that new. Whereas some Users are only searching for a solution to trace connects to the database, there are others which need detailed logging for any access to a table, including client host, server host and the type of access to a table. 

Audits are needed for security. You can track data access and be alerted to suspicious activity. Audits are required for data integrity. They are the only way to validate that changes made to data are correct and legal.

Furthermore, auditing regulations cover access to Database Servers, Enterprises have to ensure that they comply with laws and industry standards. 

Below are the auditing options available in mysql :

McAfee MySQL Audit Plugin

This plugin is available for MySQL versions 5.1, 5.5, 5.6. It does not officially support Percona Server and MariaDB. It doesn’t use the Audit API and has better verbosity and better filtering features. This is achieved by binary patching the server at runtime inserting the hooks which extract data stored in known offsets in memory.

Oracle Enterprise Audit Log Plugin

Oracle provides this audit plugin as a part of the MySQL Enterprise pack. It uses the MySQL Audit API and is able to log RESULT and CONNECT events. The plugin has support for two XML-based formats.

MariaDB Audit Plugin

MariaDB developers extended the MySQL Audit API by adding fields for existing events and adding new TABLE event which notifies of operation with tables (read, write, create, drop, alter). The plugin can still be used with MySQL and Percona Server but MariaDB’s additions will not be available.


MySQL since version 5.5.3 provides the Audit Plugin API which can be used to write an Audit Plugin. The API provides notification for the following events:

  • messages written to general log (LOG)
  • messages written to error log (ERROR)
  • query results sent to client (RESULT)
  • logins (including failed) and disconnects (CONNECT)


Download the plugin :

$ wget https://downloads.mariadb.com/Audit-Plugin/MariaDB-Audit-Plugin/server_audit-1.3.0.tar.gz

Extract the tar file :

$ tar -zxvf server_audit-1.3.0.tar.gz

$ cd linux-x86-64

Check the audit plugin directory :

mysql> show global variables like 'plugin_dir';
+---------------+--------------------------+
| Variable_name | Value                    |
+---------------+--------------------------+
| plugin_dir    | /usr/lib64/mysql/plugin/ |
+---------------+--------------------------+
1 row in set (0.10 sec)

Copy the audit so file to plugin directory :

sudo install server_audit.so /usr/lib64/mysql/plugin/

Change the ownership of plugin to mysql:

$ cd /usr/lib64/mysql/plugin/

$ chown mysql:mysql server_audit.so

Edit the my.cnf file and add following lines 

$ vi /etc/my.cnf

###MySQL Audit #############
plugin-load=server_audit=server_audit.so
server_audit_logging=ON
server_audit_output_type=FILE
server_audit_file_path=/var/log/mysql/mysql-audit.log
server_audit_file_rotate_size=1000000
server_audit_file_rotations=9

Restart the mysql instance for loading the plugin


Login to mysql instance and verify the plugins 

mysql > SHOW PLUGINS;

|----------------------------------------------------------------------------------------|
| SERVER_AUDIT               | ACTIVE   | AUDIT              | server_audit.so | GPL     |
+----------------------------+----------+--------------------+-----------------+---------+


mysql> SELECT * FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME='SERVER_AUDIT'\G
*************************** 1. row ***************************
           PLUGIN_NAME: SERVER_AUDIT
        PLUGIN_VERSION: 1.3
         PLUGIN_STATUS: ACTIVE
           PLUGIN_TYPE: AUDIT
   PLUGIN_TYPE_VERSION: 3.2
        PLUGIN_LIBRARY: server_audit.so
PLUGIN_LIBRARY_VERSION: 1.4
         PLUGIN_AUTHOR:  Alexey Botchkov (MariaDB Corporation)
    PLUGIN_DESCRIPTION: Audit the server activity
        PLUGIN_LICENSE: GPL
           LOAD_OPTION: ON
1 row in set (0.00 sec)

Configuration of Audit plugin 

By installing the MariaDB Audit Plugin new variables are available to configure the MariaDB Audit Plugin. Lets do a first check to see the default configuration by executing:

mysql> SHOW GLOBAL VARIABLES LIKE 'server_audit%';
+-------------------------------+--------------------------------+
| Variable_name                 | Value                          |
+-------------------------------+--------------------------------+
| server_audit_events           |                                |
| server_audit_excl_users       |                                |
| server_audit_file_path        | /var/log/mysql/mysql-audit.log |
| server_audit_file_rotate_now  | OFF                            |
| server_audit_file_rotate_size | 1000000                        |
| server_audit_file_rotations   | 9                              |
| server_audit_incl_users       |                                |
| server_audit_logging          | ON                             |
| server_audit_mode             | 1                              |
| server_audit_output_type      | file                           |
| server_audit_query_log_limit  | 1024                           |
| server_audit_syslog_facility  | LOG_USER                       |
| server_audit_syslog_ident     | mysql-server_auditing          |
| server_audit_syslog_info      |                                |
| server_audit_syslog_priority  | LOG_INFO                       |
+-------------------------------+--------------------------------+
15 rows in set (0.00 sec)

The MariaDB Audit Plugin knows three types of events, CONNECT, QUERY and TABLE. 

Set the appropriate events which needs to be logged

mysql> SET GLOBAL server_audit_events='CONNECT,QUERY,TABLE';

We now should find a file audit_server.log in path /var/log/mysql/mysql-audit.log and it should include our last SET statement already.

$ tail /var/log/mysql/mysql-audit.log

We are nearly finished, auditing for our MariaDB Server is now enabled. To make the changes to the configuration of the MariaDB Audit Plugin permanent, we now need to add these settings to my.cnf

$ server_audit_events=CONNECT,QUERY,TABLE

To Uninstall the plugin 

mysql > UNINSTALL PLUGIN server_audit;


Logging User Activities



The Audit Plugin will log the database activities of all users, or only the users that you specify. A database activity is defined as a query event or a table event. Connect events are logged for all users.



The following example shows how to add a new username to the server_audit_incl_users variable without removing previous usernames:

SET GLOBAL server_audit_incl_users = CONCAT(@@global.server_audit_incl_users, ',Maria');

Excluding or Including Users :

server_audit_excl_users=valerianus,rocky

0 comments:

Post a Comment