MariaDB Audit Plugin - Logging Events
Contents
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, DROP, RENAME and TRUNCATE statements. Exceptions are CREATE/DROP [PROCEDURE / FUNCTION / USER] and RENAME USER - they are not DDL). | 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 (DO, CALL, LOAD DATA/XML, DELETE, INSERT, UPDATE, HANDLER and REPLACE statements). | 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 (CREATE USER, DROP USER, RENAME USER, GRANT, REVOKE and SET PASSWORD statements.) | 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'; ...
The server_audit_query_log_limit variable was added to allow to control the length of the log record. Before introducing this variable, the length of the log record was truncated for long query strings.
See Also
- MariaDB Audit Plugin - Set Up a Remote Log File using rsyslog - mariadb.com blog
