Activating Auditing for MariaDB and MySQL in 5 Minutes

Introduction

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:

  • Timestamp
  • Server-Host
  • User
  • Client-Host
  • Connection-ID
  • Query-ID
  • Operation
  • Database
  • Table
  • Error-Code

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.

Download

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';

Installation

The MariaDB Audit Plugin library needs to be loaded by the server. This can be done by

  1. using a startup parameter,
  2. an entry in my.cnf or
  3. 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

Configuration

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

Ralf Gebhardt is Principal Sales Engineer at SkySQL Ab with the primary responsibility for Germany, Austria and Switzerland. Ralf has over 20 years industry experience in Software Development, Support, Training and Consulting. Ralf started at MySQL GmbH as Sales Engineer in 2002 up until 2011 through the acquisition of Sun Microsystems and Oracle. In 2011 Ralf rejoined the original founder of MySQL at SkySQL Ab.
i-fanchou

Doesn't work on Percona 5.6.15 ?

Hi Ralf,

I've tried to apply audit plugin on Percona server 5.6.15(CentOS 6) according the above.
But I got error message(ERROR 2013 (HY000): Lost connection to MySQL server during query) when I "SET GLOBAL server_audit_logging=ON;".
Any idea?

Ivan

Ralf Gebhardt

New Version 1.1.7 available soon

Hi Ivan, we will release version 1.1.7 soon. Includes a fix for Percona Server 5.6.

i-fanchou

Doesn't work on Percona 5.6.15 ?

Hi Ralf,

I used the newest one(1.1.6) and the server still running after I got the error message. I also tried version 1.1.5, but got the same error message on "SET GLOBAL server_audit_events='CONNECT,QUERY,TABLE'" step. Besides, I've tried to apply it on Percona server 5.5(CentOS) and it works correctly. Any idea about this problem?

regards,
Ivan

Ralf Gebhardt

Doesn't work on Percona 5.6.15 ?

Hi Ivan,

which version of the MariaDB Audit Plugin have you used. Did you try with the newest one (1.1.6)? Was your server still running after you have executed the statement?

Best
Ralf

i-fanchou

Doesn't work on Percona 5.6.15 ?

Hi Ralf,

I used the newest one(1.1.6) and the server still running after I got the error message. I also tried version 1.1.5, but got the same error message on "SET GLOBAL server_audit_events='CONNECT,QUERY,TABLE'" step. Besides, I've tried to apply it on Percona server 5.5(CentOS) and it works correctly.

regards,
Ivan

oliverhoch

statement truncation - line size

is it possible to set line length to an arbitrary value?
we're using hibernate, resulting in rather long statements. in the audit log the statements get truncated. of course this is sensible, because else on inserting a blob value could easyly result in length of some MB.
but truncation on about 950 characters or something like that is too harsh and renders your plung useless for our purposes.
is there a way to set line length to say 2 or 3K?

Oliver

Ralf Gebhardt

statement truncation - line size

Hi Oliver,

with the current version of the MariaDB Audit Plugin it is not possible to truncate the line length. A truncation also could lead to removing field or table names from the query, which makes the auditing useless. If you are only interested in the information about which tables have been accessed for reads and writes, and you are using MariaDB, you could disable QUERY events completely. The table events would still give you what you need (as long as you are not using the query cache).
I will forward your comment to our engineering to see, if there are other ideas. What we want to avoid is the need of heavy parsing in the Audit Plugin itself.

Ralf

ujmoser

MariaDB audit plugin

Hello Ralph,

is there a recording of the webinar. I was busy in a course at the time of broadcast so I could not watch.

Kind regards

Ulrich

Ralf Gebhardt

OnDemand Webinar

Hi Ulrich,

The-On-Demand Webinar is available here - http://www.skysql.com/why-skysql/webinars/mariadb-audit-plugin-overview.

Ralf Gebhardt
didierwiroth

server_audit_mode=0

1) thanks for the amazing plugin, this was a really important and missing feature!!!
2) what exactly is the server_audit_mode variable, this variable is not mentioned in the doc?
3) what are the different possible values of server_audit_mode (by default it is 0)?
thx!

Ralf Gebhardt

server_audit_mode=0

Thanks for your positive feedback!

The server_audit_mode variable is only used for internal testing and is not of any meaning for the normal usage of the MariaDB Audit Plugin.