Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

MySQL Auditing With MariaDB Auditing Plugin

DZone's Guide to

MySQL Auditing With MariaDB Auditing Plugin

This blog will address how the MariaDB Auditing Plugin can help monitor database activity to help with security, accountability, and troubleshooting.

· Database Zone
Free Resource

Download the Guide to Open Source Database Selection: MySQL vs. MariaDB and see how the side-by-side comparison of must-have features will ease the journey. Brought to you in partnership with MariaDB.

MariaDB Auditing Plugin

This blog will address how the MariaDB Auditing Plugin can help monitor database activity to help with security, accountability, and troubleshooting.

Why Audit Your Databases?

Auditing is an essential task for monitoring your database environment. By auditing your database, you can achieve accountability for actions taken or content accessed within your environment. You will also deter users (or others) from inappropriate actions.

If there is any bad behavior, you can investigate suspicious activity. For example, if a user is deleting data from tables, the admins could audit all connections to the database and all deletions of rows. You can also use auditing to notify admins when an unauthorized user manipulates or deletes data or that a user has more privileges than expected.

Auditing Plugins Available for MySQL

As Sergei Glushchenko said in a previous blog, MySQL version 5.5.3 and later provides the Audit Plugin API, which can be used to write an audit plugin. The API provides notification for the following events:

  • messages written to general log (LOG)
  • messages written to error log (ERROR)
  • query results sent to client (RESULT)
  • logins (including failed) and disconnects (CONNECT)

All current audit plugins for MySQL provide an audit log as a result of their work. They differ in record format, filtering capabilities and verbosity of log records.

  • MySQL Enterprise Audit Plugin – This plugin is not open source and is only available with MySQL Enterprise, which has a significant cost attached to it. It is the most stable and robust.
  • Percona Audit Log Plugin – Percona provides an open source auditing solution that installs with Percona Server 5.5.37+ and 5.6.17+. This plugin has quite a few output features as it outputs XML, JSON and to syslog. Percona’s implementation is the first to be a drop-in replacement for MySQL Enterprise Audit Plugin. As it has some internal hooks to the server to be feature-compatible with Oracle’s plugin, it is not available as a standalone for other versions of MySQL. This plugin is actively maintained by Percona.
  • McAfee MySQL Audit Plugin – Around the longest and has been used widely. It is open source and robust, while not using the official auditing API. It isn’t updated as often as one may like. There haven’t been any new features in some time. It was recently updated to support MySQL 5.7.
  • MariaDB Audit Plugin – The only plugin that claims to support MySQL, Percona Server, and MariaDB. It is open source and constantly upgraded with new versions of MariaDB. Versions starting at 1.2 are most stable, and it may be risky to use versions below that in your production environment. Versions below 1.2 may be unstable, and I have seen it crash production servers. Older versions also log clear text passwords.

About the MariaDB Auditing Plugin

The MariaDB Auditing Plugin provides auditing functionality for not only MariaDB, but Percona Server and MySQL, as well. It is installed with MariaDB or available as a plugin for Percona Server and MySQL.

I worked with the MariaDB Auditing Plugin because I was using MySQL community, without an enterprise license, which means the Enterprise Plugin and Percona’s plugin are off the table. We wanted to use a plugin that used MySQL’s built-in auditing API, not a custom one that reads known memory blocks and is sensitive to upgrades such as McAfee’s plugin.

Get the Plugin

To get the MariaDB Auditing Plugin, download the .so from here: https://mariadb.com/products/connectors-plugins.

You can manually install the .so file to your plugin directory (ie /usr/lib/mysql/plugin on debian):

SHOW GLOBAL VARIABLES LIKE 'plugin_dir';

I highly recommend packaging it if you intend to do any automation (Chef, Puppet) or upgrades in the future.

Packaging

Similar steps can be performed with fpm.

Create a directory structure for the Debian package:

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

Copy plugin into package directory:

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

Debianize the package directory:

$ dh_make --createorig

Delete example files:

$ cd debian/ ; rm -f *.ex

Configure the package:

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

Build the .deb:

$ dpkg-buildpackage -us -uc

Verify package version:

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

Install

This is not required but highly recommended (INSTALL PLUGIN and UNINSTALL PLUGIN tend to fail for this plugin depending on what else is happening within your environment):

$ service mysql stop

Install with dpkg:

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

Configuration

Reference https://mariadb.com/kb/en/mariadb/server_audit-system-variables/ for more information on configuration.

Add to my.cnf (if you didn’t restart, you can set these in SQL with SET GLOBAL):

# load plugin
plugin-load=server_audit=server_audit.so
# do not allow users to uninstall plugin
server_audit=FORCE_PLUS_PERMANENT
# only audit connections and DDL queries
server_audit_events=CONNECT,QUERY_DDL
# enable logging
server_audit_logging=ON
# any users who don’t need auditing (csv)
server_audit_excl_users=’root’
# or can use server_audit_incl_users=’jayj’

Log Destination

When selecting the log destination, you want to use one method. It is dangerous to configure both, so decide ahead of time on your logging strategy.

# flat file
server_audit_output_type=FILE
server_audit_file_path=/var/log/mysql/audit.log
server_audit_file_rotate_size=1000000
server_audit_file_rotations=9
# syslog
server_audit_output_type=SYSLOG
server_audit_syslog_facility=LOG_LOCAL6
server_audit_syslog_ident=mysql_audit
server_audit_syslog_info=this-host.name
server_audit_syslog_priority=LOG_INFO

Verify Install

$ service mysql start
$ mysql
mysql> SHOW PLUGINS;
+-------------------------+----------+-----------------+-----------------+---------+
| Name                    | Status   | Type              | Library        | License |
+-------------------------+----------+-----------------+-----------------+---------+
...
| SERVER_AUDIT            | ACTIVE   | AUDIT             | server_audit.so| GPL     |
+-------------------------+----------+-----------------+------------------+--------+
24 rows in set (0.00 sec)
mysql> SELECT * FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME='SERVER_AUDIT'G
    *************************** 1. row ***************************
        PLUGIN_NAME: SERVER_AUDIT
        PLUGIN_VERSION: 1.2
        PLUGIN_STATUS: ACTIVE
        PLUGIN_TYPE: AUDIT
        PLUGIN_TYPE_VERSION: 3.2
        PLUGIN_LIBRARY: server_audit.so
        PLUGIN_LIBRARY_VERSION: 1.3
        PLUGIN_AUTHOR:  Alexey Botchkov (MariaDB Corporation)
        PLUGIN_DESCRIPTION: Audit the server activity
        PLUGIN_LICENSE: GPL
        LOAD_OPTION: FORCE_PLUS_PERMANENT
        1 row in set (0.01 sec)

Check the Logs

$ tail server_audit.log
20130927 01:00:00,localhost.localdomain,root,localhost,1,1,QUERY,,'SET GLOBAL server_audit_logging=ON',0

Rsyslog config

I recommend starting here and setting up an Elasticsearch cluster with Logstash and Kibana, also known as the ELK stack. This allows you to aggregate and search your logs to find problems. Here is a sample rsyslog configuration:

$ cat /etc/rsyslog.d/10-mysqlaudit.conf
# keep in /var/log as syslog user can’t access /var/log/mysql usually
/var/log/mysql-audit.log {
    daily
    rotate 7
    missingok
    create 640 syslog adm
    compress
    sharedscripts
    postrotate
    reload rsyslog >/dev/null 2>&1 || true
    endscript
}

Conclusion

The MariaDB Auditing Plugin is quick and easy to install and bring into your current logging or auditing solution.

Once you have installed auditing, you can detect problems with an authorization or access control implementation. It allows you to create audit policies that you expect will never generate an audit record because the data is protected. If these policies do generate audit records, then you know that the other security controls are not properly implemented.

Auditing information can help you troubleshoot performance or application issues and lets you see exactly what SQL queries are being processed.

Interested in reducing database costs by moving from Oracle Enterprise to open source subscription?  Read the total cost of ownership (TCO) analysis. Brought to you in partnership with MariaDB.

Topics:
mysql ,database ,mariadb ,audit

Published at DZone with permission of Peter Zaitsev, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

THE DZONE NEWSLETTER

Dev Resources & Solutions Straight to Your Inbox

Thanks for subscribing!

Awesome! Check your inbox to verify your email so you can start receiving the latest in tech news and resources.

X

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}