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