ralfgebhardt1

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.

Tags: 

About the Author

ralfgebhardt1's picture

Ralf Gebhardt is Principal Sales Engineer working with MariaDB and MySQL.

didierwiroth

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!
ralfgebhardt1

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.
ulrichmoser

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
ralfgebhardt1

Hi Ulrich, you get access to the recording via http://www.skysql.com/why-skysql/webinars/mariadb-audit-plugin-overview Best Ralf
ralfgebhardt1

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

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
ralfgebhardt1

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
i-fanchou

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
ralfgebhardt1

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

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
i-fanchou

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
ralfgebhardt1

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

With MySQL 5.5.37 and Windows x64 the engine produces an error and does not load the plug-in. I have attached the error messages below and the configuration used. Please let me know how to resolve this issue. Many thanks for your help. [ERROR] Can't open shared library 'C:\Program Files\MySQL\MySQL Server 5.5\lib\plugin\server_audit.dll' (errno: 127 The specified procedure could not be found.) [ERROR] Couldn't load plugin named 'server_audit' with soname 'server_audit.dll'. mysql> show global variables like '%version%'; innodb_version 5.5.37 protocol_version 10 slave_type_conversions version 5.5.37-log version_comment MySQL Community Server (GPL) version_compile_machine x86 version_compile_os Win64 [mysqld] port=3306 basedir="C:\Program Files\MySQL\MySQL Server 5.5\" datadir="C:\ProgramData\MySQL\MySQL Server 5.5\data\" character-set-server=utf8 default-storage-engine=INNODB plugin-load=server_audit=server_audit.dll server_audit=FORCE_PLUS_PERMANENT ...
ralfgebhardt1

My first thought would be that this is a mismatch of debug and non-debug versions for the audit plugin and the server itself. Please check this and let me know if you need further help.
kwongli

Hello Ralf. How are you? I got the following error when I tried to install the plugin with the following command: INSTALL PLUGIN server_audit SONAME 'server_audit.so'; ERROR 1126 (HY000): Can't open shared library 'server_audit.so' (errno: 0 API version for AUDIT plugin is too different) I am running MySQL 5.5.8 GPL. Is it too old? What is the minimum version I need for the audit plugin? Thank you. Regards, Kwong
suresh_gautam_g

ERROR 1126 (HY000): Can't open shared library '/usr/lib64/mysql/plugin/server_audit.so' (errno: 13 /usr/lib64/mysql/plugin/server_audit.so: undefined symbol: _my_thread_var)
suresh_gautam_g

ERROR 1126 (HY000): Can't open shared library '/usr/lib64/mysql/plugin/server_audit.so' (errno: 13 /usr/lib64/mysql/plugin/server_audit.so: undefined symbol: _my_thread_var) Above error was happened while using Server version: 5.7.5-m15 MySQL Community Server (GPL) in Centos release 6.5, any workaround for this?

Newsletter Signup

Subscribe to get MariaDB tips, tricks and news updates in your inbox: