MySQL Auditing

I got a task of auditing user activities for some sensitive database in one of our MySQL database servers. Auditing user activity was a tough task with earlier version of MySQL. We had to go through the slow query log or general log and find out our required data from these two files by scanning through lots of data. Which is obviously not a trivial task. But MySQL started supporting plugin API since MySQL 5.1 version and that changed the game. That leads to the arrival of MySQL AUDIT Plugin, which is a MySQL plugin from McAfee and this plugin provides audit capabilities for MySQL.

So in this example I will show you how to audit user activities like update,delete,drop,truncate for a particular database say okcl_sets_app. For this example I have used Ubuntu 12.04 with MySQL 5.5.24.

First I will show you how to install the plugin and then auditing some user activity without restarting the MySQL server.

Download the MySQL Audit plugin for your version of MySQL from the links provided in the page https://github.com/mcafee/mysql-audit/downloads

We have to copy the plugin file into MySQL’s plugin directory. To find the location of the plugin directory we can use the following command:

The plugin file is available in the zip binary distribution. Extract the zip file

# unzip audit-plugin-mysql-5.5-1.0.3-371-linux-i386.zip

The actual plugin file is inside of the lib folder of the extracted zip folder. Copy the plugin file (libaudit_plugin.so) into MySQL’s plugin directory.

# cp ./audit-plugin-mysql-5.5/lib/libaudit_plugin.so /usr/lib/mysql/plugin

Once the plugin file is copied, we can install the plugin using the following command:

Note: The above command requires INSERT privilege for the mysql.plugin table.

The INSTALL PLUGIN command loads and initializes the plugin and makes the plugin available for use. So there is no need to restart the MySQL server.

We may also install this plugin by inserting the following line

plugin-load=AUDIT=libaudit_plugin.so

in the [mysqld] section of MySQL configuration file. But this will require MySQL server restart to load the plugin.

To check whether the plugin has been installed and loaded successfully, we can use the SHOW PLUGINS command and check the line for AUDIT plugin.

We can find the version of our loaded audit plugin using the following command:

Our audit plugin is installed and loaded successfully, now we can see the default values for the configuration system variables of the audit plugin:

Audit plugin writes the auditing activities in JSON format. It supports writing auditing activities directly to a file, or to a unix socket.

Now I will enable JSON file auditing using the dynamic system variable.
audit_json_file: json log file Enable|Disable (1|0)

By default the plugin creates mysql-audit.json file inside MySQL datadir and writes audit trail to this file. We can change the file name and location by changing the audit_json_log_file system variable.

After enabling auditing, the plugin starts auditing all the user activities on the server, which will be large amount of data. We may restrict auditing data by specifying the commands that are to be audited and also we can specify the database/table that we need to audit.

As per my requirement I have to audit update,delete,drop,truncate for the database  okcl_sets_app

For that I will first specify the commands that are to be audited by changing the audit_record_cmds system variable.

Next I will specify the object(s) that I need to audit by changing the audit_record_objs system variables.

All done, we can check the audit settings whether everything is changed as per our requirement

Note: To make our audit configurations to persist across MySQL restart, add the required audit plugin system variables into the [mysqld] section of MySQL configuration file.

All the Audit Plugin’s system variables are available in this page: https://github.com/mcafee/mysql-audit/wiki/Configuration

Now I will update the table named TableName1 in okcl_sets_app database and lets see what we get in the Audit log file

Wow, wealth of information that we can collect transparently .