About the MariaDB Audit Plugin

The purpose of the MariaDB Audit Plugin is to log the server's activity. Records about who connected to the server, what queries ran and what tables were touched can be stored to the rotating log file or sent to the local syslogd.

MariaDB included the Audit Plugin by default from versions 10.0.10 and 5.5.37. The MariaDB Audit Plugin works for MariaDB, MySQL and Percona Server.

Versions

Installation

The plugin can be installed using the INSTALL PLUGIN or INSTALL SONAME commands, or with the plugin-load mysqld option (see Installing plugins). For example:

INSTALL PLUGIN server_audit SONAME 'server_audit';

The location of the plugin directory can be found by viewing the value of the plugin_dir variable, for example:

SHOW VARIABLES LIKE 'plugin_dir';
+---------------+------------------------+
| Variable_name | Value                  |
+---------------+------------------------+
| plugin_dir    | /usr/lib/mysql/plugin/ |
+---------------+------------------------+

You may want to prevent the plugin from being uninstalled with the UNINSTALL PLUGIN command. To do so, add

server_audit=FORCE_PLUS_PERMANENT

to the [mysqld] section of your my.cnf file. The plugin must already be installed, either earlier in the configuration file, or an earlier time the server was running, or else this line will cause an error when starting the server.

Now, if someone tries to uninstall the plugin, the operation will fail with the following error:

UNINSTALL PLUGIN server_audit;
ERROR 1702 (HY000): Plugin 'server_audit' is force_plus_permanent and cannot be unloaded

Logging events

A full list of related system variables are detailed at Server_Audit System Variables, and status variables at Server_Audit Status Variables. Some of the major ones are highlighted below.

By default, logging is set to off. To enable it, set the server_audit_logging variable to ON.

There are 5 types of log records.

The server_audit_events variable specifies which of the five events to log, taking a comma-separated list of the event types as an argument, for example:

[mysqld]
...
server_audit_events=connect,query

Note that QUERY records are stored in plain text, which can be a security risk. As an alternative, TABLE records still indicate which user accessed which table, but do not record the full query and results. Also note that if the query cache is enabled, and a query is returned from the query cache, no TABLE records appear in the log, as the server didn't open or access any tables.

Excluding or including users

By default events from all users are logged, but certain users can be excluded from logging by using the server_audit_excl_users variable. For example, to exclude users valerianus and rocky from having their events logged:

server_audit_excl_users=valerianus,rocky

This option is primarily used to exclude the activities of trusted applications.

Alternatively, server_audit_incl_users can be used to specifically include users. Both variables can be used, but if a user appears on both lists, server_audit_incl_users has a higher priority, and their activities will be logged.

Note that CONNECT events are always logged for all users, regardless of these two settings. Logging is also based on username only, not the username and hostname combination that MariaDB uses to determine privileges.

Audit log format

The default format for the audit log file is:

[timestamp],[serverhost],[username],[host],[connectionid],
[queryid],[operation],[database],[object],[retcode]

If the server_audit_output_type variable is set to SYSLOG instead of the default, FILE, the audit log file format will be as follows:

[timestamp][syslog_host][syslog_ident]:[syslog_info][serverhost],[username],[host],
[connectionid],[queryid],[operation],[database],[object],[retcode]

New in version 1.2.0

In the log, passwords are hidden (replaced with the "*" line ) for certain types of queries: These are:

GRANT
CREATE USER
CREATE MASTER
CREATE SERVER
ALTER SERVER

Note that passwords are NOT replaced for the PASSWORD() and OLD_PASSWORD() functions when they are used inside other query types, such as SELECT, INSERT, UPDATE, DELETE.

The server_audit_events variable now allows the 'QUERY_DDL' and 'QUERY_DML' values, so that DDL and DML-type queries can be filtered.

set global server_audit_events='query_ddl';
...
set global server_audit_events='query_dml';

There are other query types than DDL and DML, so 'QUERY_DDL, QUERY_DML' is not equal to 'QUERY'.