Activating Auditing for MariaDB and MySQL in 5 Minutes
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. Furthermore, auditing regulations cover access to Database Servers, Enterprises have to ensure that they comply with laws and industry standards.
To allow our customers, but also the MariaDB and MySQL Community, to fulfill these requirements for MariaDB and MySQL, SkySQL has developed the MariaDB Audit Plugin. Support for this GPL licensed Audit Plugin is included in SkySQL's Advanced Subscription.
What do you get by using the MariaDB Audit Plugin? You get detailed information logged for any type of access from users to your database server and tables:
By using the MariaDB Auditing Plugin you only need some minutes to enable auditing for your MariaDB or MySQL Database Server. A restart of the Server is not needed, so you do not need to plan any downtime for the installation of the plugin.
The only requirement we have is that you are running MariaDB or MySQL Server with version 5.5 or newer (MySQL 5.5.14, MariaDB 5.5.20).
What I want to do now is to go through the minimal steps needed to get the Maria Audit Plugin installed, configured and the auditing activated. Further configurations, like filtering users or using the syslog, are not part of this blog, but I am sure that I or someone else will cover these options in another blog post.
Firstly we need to download the MariaDB Audit Plugin Library from http://www.skysql.com/downloads/mariadb-audit-plugin-beta. The library is included in a tarball (server_audit-VERSION.tar.gz ) as 32-bit and 64-bit version for Linux for both, debug and non-debug versions of MariaDB and MySQL. I am using server_audit_1.1.2.tar.gz here.
After extracting the library server_audit.so from the tarball we need to copy it to the plugin directory. If you do not know, in which directory your server is searching for the plugins, you can get this directory by checking the plugin_dir variable. This could be done using the mysql client by executing:
SHOW GLOBAL VARIABLES LIKE 'plugin_dir';
The MariaDB Audit Plugin library needs to be loaded by the server. This can be done by
- using a startup parameter,
- an entry in my.cnf or
- by executing a statement using the mysql client or a SQL GUI Client like SQLyog.
As we want to avoid a server restart we will choose the last option. The command we need to use is:
INSTALL PLUGIN server_audit SONAME 'server_audit.so';
The MariaDB Audit Plugin is now registered and will be loaded whenever the server is started. If you want to check if the plugin was loaded correctly, you can do this by querying the information schema using:
SELECT * from information_schema.plugins where plugin_name='server_audit';
You should get an output like:
PLUGIN_NAME: SERVER_AUDIT PLUGIN_VERSION: 1.1 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) PLUGIN_DESCRIPTION: Audit the server activity. PLUGIN_LICENSE: GPL LOAD_OPTION: ON PLUGIN_MATURITY: Beta PLUGIN_AUTH_VERSION: 1.1.2
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:
SHOW GLOBAL VARIABLES LIKE 'server_audit%';
The default configuration should be (for version 1.1.2):
+-------------------------------+-----------------------+ | Variable_name | Value | +-------------------------------+-----------------------+ | server_audit_events | | | server_audit_excl_users | | | server_audit_file_path | server_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 | OFF | | server_audit_mode | 1 | | server_audit_output_type | file | | server_audit_syslog_facility | LOG_USER | | server_audit_syslog_ident | mysql-server_auditing | | server_audit_syslog_info | | | server_audit_syslog_priority | LOG_INFO | +-------------------------------+-----------------------+
Using these default settings will create a log file server_audit.log in the data directory. The log file will be rotated when a file size of 1000000 Bytes is reached, nine files will be used before the first log file will be overwritten.
Before we can enable the audit logging we need to define, which types of events we want to be logged. The MariaDB Audit Plugin knows three types of events, CONNECT, QUERY and TABLE.
Let's leave the configuration itself for a moment to understand, what these events stand for.
By enabling logging for events of type CONNECT, connecting and disconnecting to/from the server will be added to the log. An unsuccessful connect will be logged as a failed connect including the error code.
If we want all statements added to the log, which have been sent by a client, we just need to enable the QUERY event logging. Note that the full statement including the values will be logged. To be able to identify successfully executed statements, the log also includes the error code.
Whereas the CONNECT and QUERY events do not need to be explained in detail, it might not be that clear what a TABLE event is about. This type of event is only available for MariaDB 5.5.31 and newer releases. MySQL Server does not provide the information needed by the MariaDB Audit Plugin to trace these events. To execute any type of query - directly, via a view or via stored procedures - tables need to be read, written, renamed, dropped or altered. In some cases we could determine the operation by parsing the query, but not if a view or stored procedure is used. By using TABLE events we do not need to parse the query. Any operation on a table triggered by query will result in an event the MariaDB Audit Plugin can catch to log it directly. This makes parsing needless and is of high value also because it can determine the access to tables even if views or stored procedures are used.
I would say that it’s wise to turn on the TABLE event if you have sensitive information in your database that you want to follow access to. Because then you won’t have to guess or analyze if a query in form of for example a stored procedure has touched the table with sensitive information.
You might even want to turn off QUERY logging if you have to be really sensitive about your data. Remember, the full query is logged including any sensitive data that might be in the query itself.
Lets go back to the configuration of the Maria Audit Plugin.
To get a full picture of what can be logged by the plugin, we will enable all types of events for our first test. Please remember, you need MariaDB version 5.5.31 or newer for logging events of type TABLE. We are doing this by executing:
SET GLOBAL server_audit_events='CONNECT,QUERY,TABLE';
You need to have the SUPER privilege to be able to change the Audit Plugin variables!
With this change we are ready to enable the auditing, which we now will do by saying:
SET GLOBAL server_audit_logging=ON;
We now should find a file audit_server.log in our data directory and it should include our last SET statement already.
#tail server_audit.log 20130927 01:00:00,localhost.localdomain,root,localhost,1,1,QUERY,,'SET GLOBAL server_audit_logging=ON',0
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. This ensures that the same configuration will be used after server restart.
[mysqld] ... server_audit_events=CONNECT,QUERY,TABLE server_audit_logging=on ...
You are ready to audit the access to your MariaDB or MySQL Database Server and it’s tables!
More on the Audit Plugin
Do you want to learn more about the MariaDB Audit-Plugin? Join Ralf Gebhardt on 5.12.2013 for his live webcast.
About the Author
Join Our Webinars
MySQL Tips and Tricks presented by SkySQL Experts
SkySQL is a global software vendor specialising in high availability and high performance MySQL. With a client base consisting of global brands such Google, Fusion-io, HP, Virgin Mobile, Craigslist, Harvard University and numerous small to medium businesses, SkySQL is perfectly suited to help with any of your MySQL needs.