Feb
15
2016
--

MySQL Auditing with MariaDB Auditing Plugin

MySQL MariaDB audit

MariaDB Auditing PluginThis 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 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 hasn’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

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.

May
15
2014
--

Introduction to the Percona Server Audit Log feature

Percona has developed an Audit Log feature that is now included in Percona Server since the recent 5.5 and 5.6 releases. This implementation is alternative to the MySQL Enterprise Audit Log Plugin: Percona re-implemented the Audit Plugin code as GPL as Oracle’s code was closed source. This post is a quick introduction to this plugin.

Installation
There are two ways to install the Percona MySQL Audit Plugin:

INSTALL PLUGIN audit_log SONAME 'audit_log.so';

or in my.cnf

[mysqld]
plugin-load="audit_log=audit_log.so"

Verify installation

mysql> SHOW PLUGINS\G
...
*************************** 38. row ***************************
  Name: audit_log
Status: ACTIVE
  Type: AUDIT
Library: audit_log.so
License: GPL
38 rows in set (0.00 sec)

Configuration
Let’s see variables provided by the Percona MySQL Audit Plugin:

mysql> show global variables like 'audit%';
+--------------------------+--------------+
| Variable_name            | Value        |
+--------------------------+--------------+
| audit_log_buffer_size    | 1048576      |
| audit_log_file           | audit.log    |
| audit_log_flush          | OFF          |
| audit_log_format         | OLD          |
| audit_log_policy         | ALL          |
| audit_log_rotate_on_size | 0            |
| audit_log_rotations      | 0            |
| audit_log_strategy       | ASYNCHRONOUS |
+--------------------------+--------------+
7 rows in set (0.00 sec)

The Percona MySQL Audit Plugin can log using the memory buffer to deliver better performance. Messages will be written into memory buffer first and then flushed to file in background. A certain amount of events can be lost in case of server crash or power outage. Another option is to log directly to file without using memory buffer. There is also an option to fsync every event.

Set audit_log_strategy to control log flushing:

  • ASYNCHRONOUS log using memory buffer, do not drop events if buffer is full
  • PERFORMANCE log using memory buffer, drop events if buffer is full
  • SEMISYNCHRONOUS log directly to file, do not fsync every event
  • SYNCHRONOUS log directly to file, fsync every event

audit_log_buffer_size specifies the size of memory buffer, it makes sense only for ASYNCHRONOUS and PERFORMANCE strategy.

Variable audit_log_file specifies the file to log into. It’s value can be path relative to datadir or absolute path.

The Percona MySQL Audit Plugin can automatically rotate log file based on size. Set audit_log_rotate_size to enable this feature. File is rotated when log grew in size to specified amount of bytes. Set audit_log_rotations to limit the number of log files to keep.

It is possible to log only logins or only queries by setting audit_log_policy value.

Log file format
Lets see how audit records look like

OLD format (audit_log_format = OLD):

<AUDIT_RECORD
 "NAME"="Connect"
 "RECORD"="2_2014-04-21T12:34:32"
 "TIMESTAMP"="2014-04-21T12:34:32 UTC"
 "CONNECTION_ID"="1"
 "STATUS"="0"
 "USER"="root"
 "PRIV_USER"="root"
 "OS_LOGIN"=""
 "PROXY_USER"=""
 "HOST"="localhost"
 "IP"=""
 "DB"=""
/>

NEW format (audit_log_format = NEW):

<AUDIT_RECORD>
 <NAME>Connect</NAME>
 <RECORD>17481_2014-04-21T12:39:03</RECORD>
 <TIMESTAMP>2014-04-21T12:39:05 UTC</TIMESTAMP>
 <CONNECTION_ID>4</CONNECTION_ID>
 <STATUS>0</STATUS>
 <USER>root</USER>
 <PRIV_USER>root</PRIV_USER>
 <OS_LOGIN></OS_LOGIN>
 <PROXY_USER></PROXY_USER>
 <HOST>localhost</HOST>
 <IP></IP>
 <DB>test</DB>
</AUDIT_RECORD>

The difference is that the audit record in the OLD format was written as a single element with attributes, while in the NEW format it is written as a single element with sub-elements.

A good idea of what each sub-element means can be found in Audit Plugin API documentation here: https://dev.mysql.com/doc/refman/5.6/en/writing-audit-plugins.html.

Performance
Lets compare performance of different audit_log_strategy modes. I used readonly sysbench on my laptop for it. Workload is CPU-bound with dataset fit in buffer pool and I set number of sysbench threads to the amount for which count of transactions per seconds is maximum.

sysbench TPS for Percona Audit Log Plugin

I got TPS drop for PERFORMANCE and ASYNCHRONOUS strategies around 7%, 9% for SEMISYNCHRONOUS and 98% for SYNCHRONOUS which shows that syncing every logged statement to disk is not the best thing for performance.

 

Conclusion
Of course any software has bugs and this plugin has plenty of them. Please give it a try and provide us your feedback. Report any issues here: https://bugs.launchpad.net/percona-server.

The post Introduction to the Percona Server Audit Log feature appeared first on MySQL Performance Blog.

May
06
2014
--

Percona Server 5.6.17-65.0 is now available

Percona Server version 5.6.17-65.0

Percona Server version 5.6.17-65.0

Percona is glad to announce the release of Percona Server 5.6.17-65.0 on May 6th, 2014. Downloads are available here and from the Percona Software Repositories.

Based on MySQL 5.6.17, including all the bug fixes in it, Percona Server 5.6.17-65.0 is the current GA release in the Percona Server 5.6 series. All of Percona’s software is open-source and free, all the details of the release can be found in the 5.6.17-65.0 milestone at Launchpad.

New Features:

  • Percona Server now supports Metrics for scalability measurement.
  • Percona Server now supports Audit Log Plugin.
  • Percona Server parser and query optimizer now support Multiple Clustering Keys when TokuDB engine is used.
  • Storage engine handler interface has been extended with new calls to notify the storage engine of imminent table or index scan. The calls are used by TokuDB to improve performance of these operations.
  • Percona Server packages are now available for Ubuntu 14.04.

Bugs Fixed:

  • Percona Server couldn’t be built with Bison 3.0. Bug fixed #1262439, upstream #71250 (Ryan Gordon).
  • Fixed the inadequate background LRU flushing for write workloads with InnoDB compression that could lead to lower performance. Bug fixed #1295268.
  • Percona Server debug packages were not built for the previous releases. Bug fixed #1298352.
  • Queries that no longer exceed long_query_time were written to the slow query log if they matched the previous long_query_time value when slow_query_log_use_global_control variable was set to all. Bug fixed #1016991.
  • When writing audit plugins it was not possible to get notifications for general-log events without enabling the general-log. Bug fixed #1182535 (upstream #60782).
  • mysqld_safe did not correctly parse flush_caches and numa_interleave options. Bug fixed #1231110.
  • Thread Pool would handle a new client connection without notifying Audit Plugin. Bug fixed #1282008.
  • Fixed a performance issue in extending tablespaces if running under fusionIO with atomic writes enabled. Bug fixed #1286114 (Jan Lindström).
  • Previous implementation of the log_slow_rate_type set to query with log_slow_rate_limit feature would log every nth query deterministically instead of each query having a 1/n probability to get logged. Fixed by randomly selecting the queries to be logged instead of logging every nth query. Bug fixed #1287650.
  • Percona Server source files were referencing Maatkit instead of Percona Toolkit. Bug fixed #1174779.
  • Maximum allowed value for log_slow_rate_limit was ULONG_MAX (ie. either 4294967295 or 18446744073709551615, depending on the platform). As it was unreasonable to configure the slow log for every four billionth session/query, new maximum allowed value is set to 1000. Bug fixed #1290714.

Other bugs fixed #1295523, #1299688 (upstream #72163) and #1272732.

Release notes for Percona Server 5.6.17-65.0 are available in our online documentation. Bugs can be reported on the launchpad bug tracker.

The post Percona Server 5.6.17-65.0 is now available appeared first on MySQL Performance Blog.

Powered by WordPress | Theme: Aeros 2.0 by TheBuckmaker.com