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

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 /usr/lib64/mysql/plugin/

Change the ownership of plugin to mysql:

$ cd /usr/lib64/mysql/plugin/

$ chown mysql:mysql

Edit the my.cnf file and add following lines 

$ vi /etc/my.cnf

###MySQL Audit #############

Restart the mysql instance for loading the plugin

Login to mysql instance and verify the plugins 


| SERVER_AUDIT               | ACTIVE   | AUDIT              | | GPL     |
*************************** 1. row ***************************
        PLUGIN_VERSION: 1.3
         PLUGIN_AUTHOR:  Alexey Botchkov (MariaDB Corporation)
    PLUGIN_DESCRIPTION: Audit the server activity
           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 :


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Powered by

Up ↑

%d bloggers like this: