Installing and testing the MySQL Enterprise Audit plugin

MySQL Enterprise Edition includes the most comprehensive set of advanced features, management tools and technical support to achieve the highest levels of MySQL scalability, security, reliability, and uptime. It reduces the risk, cost, and complexity in developing, deploying, and managing business-critical MySQL applications.

(from http://www.mysql.com/products/enterprise/)

MySQL Enterprise Audit provides an easy to use, policy-based auditing solution that helps organizations to implement stronger security controls and to satisfy regulatory compliance.

As more sensitive data is collected, stored and used online, database auditing becomes an essential component of any security strategy. To guard against the misuse of information, popular compliance regulations including HIPAA, Sarbanes-Oxley PDF, and the PCI Data Security Standard require organizations to track access to information.

Download the White Paper: MySQL Enterprise Edition Product Guide

To meet these demands, organizations must be able to produce an audit trail of information to help track who does what to which piece of data. This includes login and logoff attempts, attempts to access a database or a table, changes to database schema and much more.

MySQL Enterprise Audit gives DBAs the tools they need to add auditing compliance to their new and existing applications by enabling them to:

  • Dynamically enable/disable audit stream
  • Implement policies that log all or selected login or query activities
  • Automatically rotate audit log files based on size
  • Integrate XML-based audit log stream with MySQL, Oracle and other third party solutions

(from http://www.mysql.com/products/enterprise/audit.html)

MySQL Enterprise Audit is implemented using a server plugin. MySQL Enterprise Audit uses the open MySQL Audit API to enable standard, policy-based monitoring and logging of connection and query activity executed on specific MySQL servers. Designed to meet the Oracle audit specification, MySQL Enterprise Audit provides an out of box, easy to use auditing and compliance solution for applications that are governed by both internal and external regulatory guidelines.

When installed, the audit plugin enables MySQL Server to produce a log file containing an audit record of server activity. The log contents include when clients connect and disconnect, and what actions they perform while connected, such as which databases and tables they access.

(from http://dev.mysql.com/doc/refman/5.6/en/mysql-enterprise-audit.html)

Installing and testing the MySQL Enterprise Audit plugin
Manual: http://dev.mysql.com/doc/refman/5.6/en/audit-log-plugin.html

Check that Enterprise Edition is installed:

The thread pool plugin requires the MySQL Enterprise Edition, and is not available in the Community Edition. After you have installed MySQL Enterprise Edition from http://edelivery.oracle.com, you can check from mysql to make sure that you have the correct version:

mysql> SHOW VARIABLES LIKE ‘version%’;
+-------------------------+---------------------------------------------------------+
| Variable_name           | Value                                                   |
+-------------------------+---------------------------------------------------------+
| version                 | 5.6.14-enterprise-commercial-advanced-log               |
| version_comment         | MySQL Enterprise Server - Advanced Edition (Commercial) |
| version_compile_machine | x86_64                                                  |
| version_compile_os      | osx10.7                                                 |
+-------------------------+---------------------------------------------------------+
4 rows in set (0.00 sec)

Before you attempt to install the plugin, you may verify that the thread pool plugin is in the plugins directory:

In the my.cnf or my.ini file, check for the location of the plugin directory (plugin_dir).

[mysqld]
plugin_dir=/path/to/plugin/directory

If the plugin directory value is not located in the my.cnf or my.ini file, check the location from within mysql:

mysql> SHOW VARIABLES like ‘plugin_dir’;
+---------------+------------------------------+
| Variable_name | Value                        |
+---------------+------------------------------+
| plugin_dir    | /usr/local/mysql/lib/plugin/ |
+---------------+------------------------------+
1 row in set (0.00 sec)

Check to see if the thread pool plugin (thread_pool.so) is in the plugin directory:

$ ls -l /usr/local/mysql/lib/plugin/audit_log.so
-rwxr-xr-x+ 1 _mysql  wheel  38828 Sep 10 03:58 /usr/local/mysql/lib/plugin/audit_log.so

Installing the Enterprise Audit plugin:

To load the plugin at server startup, use the –plugin-load option to name the object file that contains the plugin. With this plugin-loading method, the option must be given each time the server starts. You may also put the following lines in your my.cnf file:

[mysqld]
plugin-load=audit_log.so

If object files have a suffix different from .so on your system, substitute the correct suffix (for example, .dll on Windows). Alternatively, to register the plugin at runtime, use this statement (changing the suffix as necessary):

mysql> INSTALL PLUGIN audit_log SONAME ‘audit_log.so’;

INSTALL PLUGIN loads the plugin, and also registers it in the mysql.plugins table to cause the plugin to be loaded for each subsequent normal server startup.

If the plugin is loaded with –plugin-load or has been previously registered with INSTALL PLUGIN, you can use the –audit-log option at server startup to control plugin activation. For example, to load the plugin and prevent it from being removed at runtime, use these options:

[mysqld]
plugin-load=audit_log.so
audit-log=FORCE_PLUS_PERMANENT

If it is desired to prevent the server from running without the audit plugin, use –audit-log with a value of FORCE or FORCE_PLUS_PERMANENT to force server startup to fail if the plugin does not initialize successfully.

Optional audit variables: http://dev.mysql.com/doc/refman/5.6/en/audit-log-plugin-options-variables.html

To verify that the audit_log was loaded successfully:

mysql> SHOW VARIABLES LIKE ‘audit_log%’;
+---------------------------+--------------+
| Variable_name             | Value        |
+---------------------------+--------------+
| audit_log_buffer_size     | 1048576      |
| audit_log_file            | audit.log    |
| audit_log_flush           | OFF          |
| audit_log_format          | OLD          |
| audit_log_policy          | ALL          |
| audit_log_rotate_on_size  | 0            |
| audit_log_strategy        | ASYNCHRONOUS |
+---------------------------+--------------+
7 rows in set (0.00 sec)

or

mysql> SELECT * FROM information_schema.PLUGINS where PLUGIN_NAME = ‘audit_log’\G
*************************** 1. row ***************************
           PLUGIN_NAME: audit_log
        PLUGIN_VERSION: 0.1
         PLUGIN_STATUS: ACTIVE
           PLUGIN_TYPE: AUDIT
   PLUGIN_TYPE_VERSION: 3.1
        PLUGIN_LIBRARY: audit_log.so
PLUGIN_LIBRARY_VERSION: 1.4
         PLUGIN_AUTHOR: Oracle
    PLUGIN_DESCRIPTION: Auditing events logger
        PLUGIN_LICENSE: PROPRIETARY
           LOAD_OPTION: ON
1 row in set (0.02 sec)

Testing Enterprise Audit

Check to see if the audit log file was created. The default location is in the MySQL data directory. To locate the data directory:

mysql> SHOW VARIABLES WHERE Variable_Name = ‘datadir’;
+---------------+---------------------------------------------+
| Variable_name | Value                                       |
+---------------+---------------------------------------------+
| datadir       | /volumes/wv_server_raid_01/mysql_data/data/ |
+---------------+---------------------------------------------+
1 row in set (0.00 sec)

Then list that file to see if it was created:

# ls -l /volumes/wv_server_raid_01/mysql_data/data/audit.log
-rw-rw----  1 mysql  mysql  0 Mar  6 20:07 audit.log

Now, let’s test the plugin. You will need to create a user and a test database to use: (you may need to change the permissions to fit your testing scenario)

mysql> CREATE USER ‘audit_test_user’@’localhost’ IDENTIFIED BY ‘audittest123’;
Query OK, 0 rows affected (0.49 sec)

mysql> GRANT ALL PRIVILEGES ON *.* TO ‘audit_test_user’@’localhost’;
Query OK, 0 rows affected (0.02 sec)

mysql> create database audit_test;
Query OK, 1 row affected (0.00 sec)

Once the user has been created, erase the contents of the log file so you can only see the audit trail of this user:

# > /volumes/wv_server_raid_01/mysql_data/data/audit.log

Next, login with the audit_test_user:

# mysql -uaudit_test_user -paudittest123
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 375
Server version: 5.6.14-enterprise-commercial-advanced-log MySQL Enterprise Server - Advanced Edition (Commercial)

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

mysql> 

(You may ignore the warning about using the password on the command line)

As the audit test user, create a simple table in the audit_test database, populate the table, and perform a select:

mysql> use audit_test;
Database changed

mysql> CREATE TABLE audit_test_table (firstname VARCHAR(20), lastname VARCHAR(20));
Query OK, 0 rows affected (0.05 sec)

mysql> INSERT INTO audit_test_table values (“John”, “Smith”);
Query OK, 1 row affected (0.47 sec)

mysql> select * from audit_test_table;
+------------+----------+
| firstname  | lastname |
+------------+----------+
| John       | Smith    |
+------------+----------+
1 row in set (0.00 sec)

You may now inspect the contents of the /usr/local/mysql/data/audit_log file:
(this output has been manually formatted for easier viewing)


# cat /usr/local/mysql/data/audit.log
<AUDIT_RECORD TIMESTAMP=”2014-03-07T01:25:49 UTC”
RECORD_ID=”13376_2014-03-06T01:18:10”
NAME=”Connect”
CONNECTION_ID=”377”
STATUS=”0”
STATUS_CODE=”0”
USER=”audit_test_user”
OS_LOGIN=””
HOST=”localhost”
IP=””
COMMAND_CLASS=”connect”
PRIV_USER=”audit_test_user”
PROXY_USER=””
DB=””/>

<AUDIT_RECORD TIMESTAMP=”2014-03-07T01:25:49 UTC”
RECORD_ID=”13377_2014-03-06T01:18:10”
NAME=”Query”
CONNECTION_ID=”377”
STATUS=”0”
STATUS_CODE=”0”
USER=”audit_test_user[audit_test_user] @ localhost []”
OS_LOGIN=””
HOST=”localhost”
IP=””
COMMAND_CLASS=”select”
SQLTEXT=”select @@version_comment limit 1”/>

<AUDIT_RECORD TIMESTAMP=”2014-03-07T01:25:56 UTC”
RECORD_ID=”13378_2014-03-06T01:18:10”
NAME=”Query”
CONNECTION_ID=”377”
STATUS=”0”
STATUS_CODE=”0”
USER=”audit_test_user[audit_test_user] @ localhost []”
OS_LOGIN=””
HOST=”localhost”
IP=””
COMMAND_CLASS=”select”
SQLTEXT=”SELECT DATABASE()”/>

<AUDIT_RECORD TIMESTAMP=”2014-03-07T01:25:56 UTC”
RECORD_ID=”13379_2014-03-06T01:18:10”
NAME=”Init DB”
CONNECTION_ID=”377”
STATUS=”0”
STATUS_CODE=”0”
USER=”audit_test_user[audit_test_user] @ localhost []”
OS_LOGIN=””
HOST=”localhost”
IP=””
COMMAND_CLASS=””/>

<AUDIT_RECORD TIMESTAMP=”2014-03-07T01:26:00 UTC”
RECORD_ID=”13380_2014-03-06T01:18:10”
NAME=”Query”
CONNECTION_ID=”377”
STATUS=”0”
STATUS_CODE=”0”
USER=”audit_test_user[audit_test_user] @ localhost []”
OS_LOGIN=””
HOST=”localhost”
IP=””
COMMAND_CLASS=”create_table”
SQLTEXT=”CREATE TABLE audit_test_table (firstname VARCHAR(20), lastname VARCHAR(20))”/>

<AUDIT_RECORD TIMESTAMP=”2014-03-07T01:26:14 UTC”
RECORD_ID=”13382_2014-03-06T01:18:10”
NAME=”Query”
CONNECTION_ID=”377”
STATUS=”0”
STATUS_CODE=”0”
USER=”audit_test_user[audit_test_user] @ localhost []”
OS_LOGIN=””
HOST=”localhost”
IP=””
COMMAND_CLASS=”insert”
SQLTEXT=”INSERT INTO audit_test_table values ("John", "Smith")”/>

<AUDIT_RECORD TIMESTAMP=”2014-03-07T01:26:23 UTC”
RECORD_ID=”13383_2014-03-06T01:18:10”
NAME=”Query”
CONNECTION_ID=”377”
STATUS=”0”
STATUS_CODE=”0”
USER=”audit_test_user[audit_test_user] @ localhost []”
OS_LOGIN=””
HOST=”localhost”
IP=””
COMMAND_CLASS=”select”
SQLTEXT=”select * from audit_test_table”/>

MySQL Enterprise Audit is now configured and ready to use. To stop Enterprise Audit, issue this command:

mysql> UNINSTALL PLUGIN audit_log SONAME ‘audit_log.so’;

This command will fail if the audit-log=FORCE_PLUS_PERMANENT variable was used.

Removing the test objects

To remove the audit_test_user user and drop the audit_test database:

DROP USER ‘audit_test_user’@’localhost’;
FLUSH PRIVILEGES;
DROP DATABASE audit_test;

 


Tony Darnell is a Principal Sales Consultant for MySQL, a division of Oracle, Inc. MySQL is the world’s most popular open-source database program. Tony may be reached at info [at] ScriptingMySQL.com and on LinkedIn.
About these ads

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 39 other followers

%d bloggers like this: