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 (although it is not currently available for MySQL 5.7 - see MDEV-9106) and Percona Server.
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
[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
By default, logging is set to off. To enable it, set the server_audit_logging variable to
There are 6 types of log records.
|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.
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:
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:
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||Time at which the event occurred. If syslog is used, the format is defined by |
|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.|
|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
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'.
- MariaDB Audit Plugin - Set Up a Remote Log File using rsyslog - mariadb.com blog