Skip to content

How to enable MariaDB auditing

The following logs can be received from MariaDB:

Type Description Supported versions
CONNECT Connects, disconnects and failed connects—including the error code
QUERY Queries executed and their results in plain text, including failed queries due to syntax or permission errors
TABLE Tables affected by query execution
QUERY_DDL Similar to QUERY, but filters only DDL-type queries (CREATE, ALTER, DROP, RENAME and TRUNCATE statements—except CREATE/DROP [PROCEDURE / FUNCTION / USER] and RENAME USER (they're not DDL) 5.5.42, 10.0.17, 10.1.4
QUERY_DML Similar to QUERY, but filters only DML-type queries (DO, CALL, LOAD DATA/XML, DELETE, INSERT, SELECT, UPDATE, HANDLER and REPLACE statements) 5.5.42, 10.0.17, 10.1.4
QUERY_DML_NO_SELECT Similar to QUERY_DML, but doesn't log SELECT queries. (since version 1.4.4) (DO, CALL, LOAD DATA/XML, DELETE, INSERT, UPDATE, HANDLER and REPLACE statements) 5.5.42, 10.0.17, 10.1.4
QUERY_DCL Similar to QUERY, but filters only DCL-type queries (CREATE USER, DROP USER, RENAME USER, GRANT, REVOKE and SET PASSWORD statements) 5.5.43, 10.0.18, 10.1.5

To start logging events through MariaDB, follow these steps:

1.Enable the plugin in mariadb conf file (default /etc/mysql/mariadb.conf.d/50-server.cnf) with the command:

sudo nano /etc/mysql/mariadb.conf.d/50-server.cnf

2.Add the following lines below ([mariadb])

[mariadb]
plugin_load_add = server_audit
server_audit=FORCE_PLUS_PERMANENT
server_audit_logging=ON
server_audit_output_type=SYSLOG
server_audit_events=CONNECT,QUERY,TABLE,QUERY_DDL,QUERY_DML,QUERY_DCL
server-audit-syslog-facility=LOG_USER
server_audit_syslog_priority=LOG_INFO
server_audit_syslog_ident=mariadb

3.Restart the service in order to apply the changes with the command:

sudo systemctl restart mariadb.service

4.Check logs by tailing the syslog file (default /var/log/syslog) with the command:

sudo tail --f /var/log/syslog

Alt text