About the MariaDB Audit Plugin
Contents
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 (including, since MariaDB 5.5.48, MariaDB 10.0.24 and MariaDB 10.1.11, MySQL 5.7) 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 6 types of log records.
Type | Description | Introduced |
---|---|---|
CONNECT | Logs connects, disconnects and failed connects (including the error code). | |
QUERY | Queries issued and their results (in plain text), including failed queries due to syntax or permission errors. | |
TABLE | Which tables were affected by query execution. | |
QUERY_DDL | Works as the 'QUERY' value, but filters only DDL-type queries (CREATE, ALTER, etc). | MariaDB 5.5.42. MariaDB 10.0.17, MariaDB 10.1.4 |
QUERY_DML | Works as the 'QUERY' value, but filters only DML-type queries (INSERT, UPDATE, etc). | MariaDB 5.5.42, MariaDB 10.0.17, MariaDB 10.1.4 |
QUERY_DCL | Works as the 'QUERY' value, but filters only DCL-type queries (GRANT, REVOKE, etc.) | MariaDB 5.5.43, MariaDB 10.0.18, MariaDB 10.1.5 |
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]
Item logged | Description |
---|---|
timestamp | Time at which the event occurred. If syslog is used, the format is defined by syslogd . |
syslog_host | Host from which the syslog entry was received. |
syslog_ident | For identifying a system log entry, including the MariaDB server. |
syslog_info | For providing information for identifying a system log entry. |
serverhost | The MariaDB server host name. |
username | Connected user. |
host | Host from which the user connected. |
connectionid | Connection ID number for the related operation. |
queryid | Query ID number, which can be used for finding the relational table events and related queries. For TABLE events, multiple lines will be added. |
operation | Recorded action type: CONNECT, QUERY, READ, WRITE, CREATE, ALTER, RENAME, DROP. |
database | Active database (as set by USE). |
object | Executed query for QUERY events, or the table name in the case of TABLE events. |
retcode | Return 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, MariaDB 10.0.17 and MariaDB 10.1.4
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'.
New in version 1.3.0
MariaDB starting with 5.5.43
Version 1.3.0 of the Audit Plugin was introduced with MariaDB 5.5.43, MariaDB 10.0.18 and MariaDB 10.1.5
The server_audit_events variable now allows the 'QUERY_DCL' value, so that DCL-type queries (GRANT, REWOKE) can be filtered.
set global server_audit_events='query_dcl'; ...
See also
- MariaDB Audit Plugin - Set Up a Remote Log File using rsyslog - mariadb.com blog