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, and it can be installed in any version from MariaDB 5.5.20. 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.

TypeDescriptionIntroduced
CONNECTIONLogs connects, disconnects and failed connects (including the error code).
QUERYQueries issued and their results (in plain text), including failed queries due to syntax or permission errors.
TABLEWhich tables were affected by query execution.
QUERY_DDLWorks as the 'QUERY' value, but filters only DDL-type queries (CREATE, ALTER, etc).1.2.0 (MariaDB 5.5.42)
QUERY_DMLWorks as the 'QUERY' value, but filters only DML-type queries (INSERT, UPDATE, etc).1.2.0 (MariaDB 5.5.42)

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.

Since version 1.2.0, passwords have been replaced by asterisks in the logs for certain queries, including:

GRANT
CREATE USER
CREATE MASTER
CREATE SERVER
ALTER SERVER

Passwords given with the PASSWORD() and OLD_PASSWORD() functions in DML statements will still be logged as plain text in queries, as will key strings used with encrypt functions such as ENCODE() and AES_ENCRYPT().

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]
Item loggedDescription
timestampTime at which the event occurred. If syslog is used, the format is defined by syslogd.
syslog_hostHost from which the syslog entry was received.
syslog_identFor identifying a system log entry, including the MariaDB server.
syslog_infoFor providing information for identifying a system log entry.
serverhostThe MariaDB server host name.
usernameConnected user.
hostHost from which the user connected.
connectionidConnection ID number for the related operation.
queryidQuery ID number, which can be used for finding the relational table events and related queries. For TABLE events, multiple lines will be added.
operationRecorded action type: CONNECT, QUERY, READ, WRITE, CREATE, ALTER, RENAME, DROP.
databaseActive database (as set by USE).
objectExecuted query for QUERY events, or the table name in the case of TABLE events.
retcodeReturn code of the logged operation.

New in version 1.2.0

MariaDB starting with 5.5.42

Version 1.2.0 of the Audit Plugin was introduced with MariaDB 5.5.42

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

See also

Comments

Comments loading...