Installing and testing the MySQL Enterprise Audit plugin
March 14, 2014 Leave a comment
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)
|
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. |
|
Tony is the author of Twenty Forty-Four: The League of Patriots
Visit http://2044thebook.com for more information. |
info [at] Scripting
Scripting MySQL 