Where the open source database community meets: Use code PERCONA75 and secure your spot for Percona Live. Register

Downloads

Blog

MySQL Auditing with MariaDB Auditing Plugin

February 15, 2016

Author

Manjot Singh

MariaDB

MySQL

Share this Post:

MariaDB no longer meeting your needs?

Migrate to Percona software for MySQL – an open source, production-ready, and enterprise-grade MySQL alternative.

Learn More

This blog explains how the MariaDB Auditing Plugin can help monitor database activity for security, accountability, and troubleshooting.

Why Audit Your Databases?

Auditing is essential for monitoring your database environment. It provides accountability for actions taken and data accessed, and helps deter inappropriate behavior.

Auditing also allows you to investigate suspicious activity. For example, if a user is deleting data, administrators can track connections and deletion events. You can also detect unauthorized access or privilege escalation.

Auditing Plugins Available for MySQL

MySQL 5.5.3+ provides an Audit Plugin API that allows plugins to capture events such as:

General log messages (LOG)
Error log messages (ERROR)
Query results sent to clients (RESULT)
Logins and disconnects (CONNECT)

Available plugins include:

MySQL Enterprise Audit Plugin – Proprietary, robust, requires paid license.
Percona Audit Log Plugin – Open source, supports XML/JSON/syslog, compatible with MySQL Enterprise API.
McAfee MySQL Audit Plugin – Mature, open source, but not API-based and less actively maintained.
MariaDB Audit Plugin – Open source, supports MySQL/Percona/MariaDB, actively maintained.

Note: Versions below 1.2 of the MariaDB plugin can be unstable and may log plaintext passwords.

About the MariaDB Auditing Plugin

The MariaDB Auditing Plugin works with MariaDB, Percona Server, and MySQL. It integrates with the MySQL audit API and is suitable when enterprise plugins are unavailable.

Get the Plugin

Download from: https://mariadb.com/products/connectors-plugins

Check plugin directory:

SHOW GLOBAL VARIABLES LIKE 'plugin_dir';

1	SHOW GLOBAL VARIABLES LIKE 'plugin_dir';

Packaging is recommended for automation and upgrades.

Packaging

$ mkdir mariadb-server-audit-plugin-1.2.0
$ cd mariadb-server-audit-plugin-1.2.0
$ mkdir -p usr/lib/mysql/plugin

$ mkdir mariadb-server-audit-plugin-1.2.0

$ cd mariadb-server-audit-plugin-1.2.0

$ mkdir -p usr/lib/mysql/plugin

$ cp /path/to/server_audit.so usr/lib/mysql/plugin

1	$ cp /path/to/server_audit.so usr/lib/mysql/plugin

$ dh_make --createorig

1	$ dh_make --createorig

$ cd debian/ ; rm -f *.ex

1	$ cd debian/ ; rm -f *.ex

$ echo "usr/lib/mysql/plugin/server_audit.so" > debian/install
$ echo "usr/lib/mysql/plugin/server_audit.so" > debian/source/include-binaries

1 2	$ echo "usr/lib/mysql/plugin/server_audit.so" > debian/install $ echo "usr/lib/mysql/plugin/server_audit.so" > debian/source/include-binaries

$ dpkg-buildpackage -us -uc

1	$ dpkg-buildpackage -us -uc

$ dpkg-deb -W mariadb-server-audit-plugin_1.2.0-1_amd64.deb

1	$ dpkg-deb -W mariadb-server-audit-plugin_1.2.0-1_amd64.deb

Install

$ service mysql stop

1	$ service mysql stop

$ dpkg -i mariadb-server-audit-plugin_1.2.0-1_amd64.deb

1	$ dpkg -i mariadb-server-audit-plugin_1.2.0-1_amd64.deb

Configuration

plugin-load=server_audit=server_audit.so
server_audit=FORCE_PLUS_PERMANENT
server_audit_events=CONNECT,QUERY_DDL
server_audit_logging=ON
server_audit_excl_users='root'

plugin-load=server_audit=server_audit.so

server_audit=FORCE_PLUS_PERMANENT

server_audit_events=CONNECT,QUERY_DDL

server_audit_logging=ON

server_audit_excl_users='root'

Log Destination

Choose one logging method:

# File
server_audit_output_type=FILE
server_audit_file_path=/var/log/mysql/audit.log

# Syslog
server_audit_output_type=SYSLOG
server_audit_syslog_facility=LOG_LOCAL6

# File

server_audit_output_type=FILE

server_audit_file_path=/var/log/mysql/audit.log

# Syslog

server_audit_output_type=SYSLOG

server_audit_syslog_facility=LOG_LOCAL6

Verify Installation

$ service mysql start
$ mysql
SHOW PLUGINS;

$ service mysql start

$ mysql

SHOW PLUGINS;

SELECT * FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME='SERVER_AUDIT'G

1	SELECT * FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME='SERVER_AUDIT'G

Check Logs

$ tail server_audit.log

1	$ tail server_audit.log

Rsyslog Configuration

/var/log/mysql-audit.log {
    daily
    rotate 7
    missingok
    create 640 syslog adm
    compress
}

/var/log/mysql-audit.log {

daily

rotate 7

missingok

create 640 syslog adm

compress

}

Conclusion

The MariaDB Auditing Plugin is easy to deploy and integrates well with existing logging systems.

It helps detect access control issues, enforce audit policies, and troubleshoot performance or application problems by capturing executed SQL queries.