While MySQL Enterprise Edition includes a proprietary audit plugin, MariaDB provides an open-source alternative known as the server_audit plugin. This tool allows administrators to track database activity, monitoring user connections, queries, and table access for security and compliance purposes.

Installing the Server Audit Plugin

MariaDB versions 10.x typically include the server_audit.so library by default. To verify the plugin directory on your system, execute the following command in the MariaDB shell:

SHOW VARIABLES LIKE 'plugin_dir';

Once the directory is identified, confirm that server_audit.so exists within that path. If the file is present, you can load the plugin dynamically without a restart:

INSTALL PLUGIN server_audit SONAME 'server_audit.so';

To ensure the plugin remains active after a server reboot, add the following entry to your my.cnf or server.cnf configuration file under the [mariadb] or [mysqld] section:

[mariadb]
plugin_load_add = server_audit

Activating and Managing Logging to Files

By default, auditing is disabled even after the plugin is installed. To enable it and verify its current state, use thece commands:

SET GLOBAL server_audit_logging = ON;
SHOW GLOBAL STATUS LIKE 'server_audit%';

To make the logging persistent and define specific behaviors, configure the following parameters in your configuration file:

[mariadb]
server_audit_logging = ON
# Define where logs are stored
server_audit_file_path = /var/log/mysql/audit_activity.log
# Limit log size to 512MB
server_audit_file_rotate_size = 536870912
# Keep 5 rotated log files
server_audit_file_rotations = 5
# Specify events to track (e.g., DDL and DML only)
server_audit_events = 'QUERY_DDL,QUERY_DML'
# Force the plugin to remain loaded
server_audit = FORCE_PLUS_PERMANENT

Key configuration variables include:

• server_audit_events: Controls the granularity of logging (CONNECT, QUERY, TABLE). Use QUERY_DDL and QUERY_DML to exclude SELECT statements.
• server_audit_incl_users: A comma-separated list of specific users to monitor.
• server_audit_excl_users: Users whose activities should be ignored.

Diverting Audit Logs to Syslog

In environments where centralized log management is required, the plugin can transmit data to the system logger instead of a flat file.

SET GLOBAL server_audit_output_type = SYSLOG;

To separate MariaDB audit events from standard system logs in /var/log/messages, modify your rsyslog configuration (e.g., /etc/rsyslog.d/mariadb-audit.conf):

if $programname == 'mysql-server_auditing' then /var/log/mariadb/audit.log
& stop

Restart the rsyslog service to apply the routing changes:

systemctl restart rsyslog

Plugin Removal Procedure

If the plugin is no longer needed, it must be removed from both the running instance and the configuration files.

1. Remove the server_audit entries from the my.cnf file.
2. Execute the uninstall command in the database shell:

UNINSTALL PLUGIN server_audit;

3. Restart the MariaDB service to completely clear the plugin variables from the global scope.

Comparison: MariaDB Audit Plugin vs. init-connect/Binlog

Auditing through the server_audit plugin offers several advantages over traditional methods like init-connect combined with binary logs:

1. Comprehensive Coverage: The audit plugin can monitor users with SUPER privileges, whereas init-connect scripts do not execute for those users.
2. Ease of Management: Unlike init-connect, which requires granting INSERT permissions on log tables to every database user, the plugin operates independently of user permissions.
3. Flexibility: Configuration changes to the audit plugin can often be applied at runtime, while changes to binary logging or init-connect mechanisms often require service restarts.
4. Output Options: The plugin supports structured output to both dedicated files and Syslog, facilitating better integration with Security Information and Event Management (SIEM) systems.