Query and Password Filtering with the MariaDB Audit Plugin

Posted on May 5, 2015 by

The MariaDB Audit Plugin has been included in MariaDB Server by default since version 5.5.37 and 10.0.9. It’s also pre-loaded in MariaDB Enterprise. The Audit Plugin as of version 1.2.0 includes new filtering options which are very useful. This article explains some aspects of them. However, if you haven’t installed and used the plugin, you may want to read first a few other documents:

Filtering by Event Type

To appreciate the new features in the MariaDB Audit Plugin, you’ll need to understand how this plugin handles filtering in general. The filtering options in version 1.1.x are based on defining the type of an event. Which event type that’s used for logging can be configured using the global server variable, server_audit_events. There are three event types: a CONNECT; a TABLE, which is available in MariaDB only; and a QUERY.

The CONNECT event type handles connecting to a server or disconnecting from it. If this event type is defined in server_audit_events, connects, disconnects, and failed connects, including the related error code, are logged in an audit log file or system log.

Using the TABLE event type, the Audit Plugin will log several activities related to tables: when table objects are opened for read or write, and when table objects are created, altered, renamed, or dropped. It will log these actions without having to do complex parsing of the queries. To use this event type, you’ll have to make some changes on the server itself. This feature is available only with version 5.5.31 or a newer version of MariaDB Server.

An audit at the table level will allow you to log access to real table objects used by a queries even when the queries themselves do not directly include table objects. This includes, for example, queries that use views or stored procedures.

The QUERY event type is used to log the queries themselves. All queries sent to the server are handled by this event type and logged. The full queries are always logged, together with any error codes. The query statements aren’t parsed, though. This keeps the overhead of the audit plugin to a minimum.

If you don’t want to log all of these long queries, or if you’re only interested in the creation, change, or removal of objects but want to log DDL (Data Definition Language) statements, you can use the Audit Plugin to do just that to some extent. You can get what you want by just logging the TABLE and CONNECT events. In this way, any CREATE, ALTER, and RENAME statements for table objects are logged. If you are also interested in DDL (e.g., CREATE DATABASE), or if you’re using the Audit Plugin with MySQL and not MariaDB Server, you’ll need to use MariaDB Audit Plugin Version 1.2.0. Just remember that only the MariaDB Server can provide the TABLE events.

New Filtering Options for Queries

The MariaDB Audit Plugin has two new options for the server_audit_events server variable: QUERY_DML and QUERY_DDL. These options can be used, instead of using the QUERY option, to log only DML (Data Manipulation Language) or DDL statements in the audit log or system log. Using one of these options will result in parsing of query strings. This does requires a small overhead for the audit plugin.

The option QUERY still can be used. It’s not equivalent, though, to using both QUERY_DML and QUERY_DDL. There are many queries that are neither DDL nor DML (e.g., GRANT statements). By using the old option, QUERY, you can avoid parsing and thereby reduce some overhead.

Password Filtering

As already mentioned, version 1.1 of the MariaDB Audit Plugin logs queries without any parsing of the queries. This means that passwords included in queries are logged as plain text in the audit log or system log. That’s a security vulnerability. This has been changed, though, in version 1.2.0. Passwords are now replaced by asterisks (i.e., "*****") in the logs.

Be aware, though, that passwords given with functions PASSWORD() or OLD_PASSWORD() in DML statements will still be logged as plain text in queries. Key strings used with encrypt functions like ENCODE() and AES_ENCRYPT() are also still logged in plain text.

Download and Install

You can download and install the MariaDB Audit Plugin from mariadb.com/resources/downloads. If you’re using the newest version of MariaDB Server, you won’t have to download MariaDB Audit Plugin 1.2.0 separately, as it is included in MariaDB Server already. With MariaDB Enterprise the Audit-Plugin is pre-loaded and auditing just has to be activated.

For more information about the Audit Plugin refer to https://mariadb.com/kb/en/mariadb/about-the-mariadb-audit-plugin/