Planet MySQL :: Planet MySQL - Archives - Introducing the MariaDB Audit Plugin

Wed, 2014-09-03 07:48anatoliydimitrov

Auditing is an essential task for monitoring your database environment. Auditing information can help you troubleshoot performance or application issues, and lets you see exactly what SQL queries are being processed. MariaDB's Audit Plugin provides auditing functionality for not only MariaDB, but MySQL as well.

If you installed MariaDB from its official packages, you probably already have the plugin on your system, even though it's neither installed nor enabled by default. Look for a file called server_audit.so (in Linux) or server_audit.dll (in Windows) inside your plugins directory (/usr/lib64/mysql/plugin/ in CentOS 6). If you are not sure which is your plugins directory, run the query SHOW VARIABLES LIKE 'plugin_dir';.

If you don't find the plugin file inside your plugins directory, download it and place it in the plugins directory manually. Ensure that the MariaDB server can read the file by giving it 755 permissions and root user ownership in Linux.

Next, install the plugin using the command INSTALL PLUGIN server_audit SONAME 'server_audit';. To confirm the plugin is installed and enabled, run the query show plugins;. You should see it appear in the list like this:

+-----------------------------+----------+--------------------+-----------------+---------+
| Name                        | Status   | Type               | Library         | License |
+-----------------------------+----------+--------------------+-----------------+---------+
| SERVER_AUDIT                | ACTIVE   | AUDIT              | server_audit.so | GPL     |
+-----------------------------+----------+--------------------+-----------------+---------+

The MariaDB Audit Plugin has enough variables to let you fine-tune your auditing so you can concentrate on just the events and statements important to you. You can see the currently set variables with the command show global variables like "server_audit%";:

MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE "server_audit%";
+-------------------------------+-----------------------+
| 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          | ON                    |
| server_audit_mode             | 0                     |
| 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              |
+-------------------------------+-----------------------+

These variables should be specified in the MariaDB server configuration file (/etc/my.cnf.d/server.cnf in CentOS) in the [mysqld] section in order to be persistent between server restarts. To have the variable server_audit_logging set to ON, for example, add the line server_audit_logging=ON to the file.

Here are some of the most important variables:

server_audit_logging – Enables audit logging; if it's not set to ON, audit events will not be recorded and the audit plugin will not do anything.
server_audit_events – Specifies the events you wish to have in the log. By default the value is empty, which means that all events are recorded. The options are: CONNECTION (users connecting and disconnecting), QUERY (queries and their result), and TABLE (which tables are affected by the queries).
server_audit_excl_users, server_audit_incl_users – These variables specify which users' activity should be excluded from or included in the audit. server_audit_incl_users has the higher priority. By default, all users' activity is recorded.
server_audit_output_type – By default auditing output is sent to a file. The other option is syslog, meaning all entries go to the syslog facility.
server_audit_syslog_facility, server_audit_syslog_priority – Specifies the syslog facility and the priority of the events that should go to syslog.

Usage

Once you have the audit plugin configured and running, you can examine the log file, which by default is /var/lib/mysql/server_audit.log in CentOS. There you will find all the events that have been enabled by the server_audit_logging variable. For example, CONNECTION entries will show you the user and from where connects and disconnects occur, like this:

20140901 15:33:43,localhost.localdomain,root,localhost,5,0,CONNECT,,,0
20140901 15:45:42,localhost.localdomain,root,localhost,5,0,DISCONNECT,,,0

Examples of TABLE and QUERY entries look like these:

20140901 15:19:44,localhost.localdomain,root,localhost,4,133,WRITE,employees,salaries,
20140901 15:19:44,localhost.localdomain,root,localhost,4,133,QUERY,employees,'DELETE FROM salaries LIMIT 100',0

The first entry shows that there were WRITE operations on the database employees and the table salaries. The query that made the WRITE changes follows: DELETE FROM salaries LIMIT 100. The order of these statements will be always the same – first the TABLE event and then the QUERY event that caused it.

A READ operation looks like:

20140901 15:20:02,localhost.localdomain,root,localhost,4,134,READ,employees,salaries,
20140901 15:20:05,localhost.localdomain,root,localhost,4,134,QUERY,employees,'SELECT * FROM salaries LIMIT 100',0

Reading the audit log file is great for a quick casual look, but it's not a practical way to monitor database logs. Chances are you'll be better off using the syslog option and taking advantage of tools that report on syslogs.

You can send the audit entries to a syslog facility such as rsyslod (which is the default for CentOS and most Linux distributions) by changing the value of the variable server_audit_output_type to syslog, and you can make this setting permanent by adding the line server_audit_output_type=syslog to the [mysqld] part of the file /etc/my.cnf.d/server.cnf and then restarting the MariaDB service.

Once you do that, you should see syslog events for the USER facility with INFO priority appearing in the syslog. By default, CentOS is configured to receive such events in the /var/log/messages file, in which you should then see lines such as:

Sep  1 16:05:35 localhost mysql-server_auditing:  localhost.localdomain,root,localhost,3,0,CONNECT,,,0
Sep  1 16:05:39 localhost mysql-server_auditing:  localhost.localdomain,root,localhost,3,3,QUERY,,'SHOW GLOBAL VARIABLES LIKE "server_audit%"',0

From this point on you can do almost anything with the audit log entries, including sending them to a centralized syslog server or processing them for certain type of events of interest. See blog post MariaDB Audit Plugin - Set Up a Remote Log File using rsyslog.

As you can see, the MariaDB Audit Plugin can help DBAs by providing information that's useful for tasks from simple troubleshooting to corporate compliance logging.

Tags: Howto Plugins About the Author Anatoliy Dimitrov

Anatoliy Dimitrov is an open source enthusiast with substantial professional experience in databases and web/middleware technologies. He is as interested in technical writing and documentation as in practical work on complex IT projects. His favourite databases are MariaDB (sometimes MySQL) and PostgreSQL. He is currently graduating his master's degree in IT and aims to a PhD in Bionformatics in his home town University of Sofia.

MySQL Links