Sep
09
2015
--

Percona Server audit log plugin best practices

Auditing your database means tracking access and changes to your data and db objects. The Audit Log Plugin has been shipped with Percona Server since 5.5.37/5.6.17, for a little over 12 months. Prior to the Audit Log Plugin, you had to work in darker ways to achieve some incarnation of an audit trail.

We have seen attempts at creating audit trails using approaches such as ‘sniffing the wire’, init files, in-schema ‘on update’ fields, triggers, proxies and trying to parse the traditional logs of MySQL (slow, general, binary, error). All of these attempts miss a piece of the pie, i.e. if you’re sniffing tcp traffic you’ll miss local connections, parsing binary logs you’re missing any reads. Your reasons for audit logging might be down to compliance requirements (HIPAA, PCI DSS) or you may need a way to examine database activity or track the connections incoming.

Over the past months I’ve met many support requests with the answer ‘install an audit plugin’. These requests have been varied but they have ranged from; finding out if a user is still active and if the impact of decommissioning it, the frequency of specific queries and checking if a slave is being written to name but a few.

So then, lets look at installation. In general we desire installation of the Audit Plugin on an existing instance. We discussed in previous Percona Blog posts, the installation of the plugin is trivial but lets recap. Lets perform a couple of basic checks before we run the install command from the client. First, query MySQL for the location of the plugins directory;

mysql> show global variables like 'plugin_dir';
+---------------+--------------------------+
| Variable_name | Value                    |
+---------------+--------------------------+
| plugin_dir    | /usr/lib64/mysql/plugin/ |
+---------------+--------------------------+
1 row in set (0.00 sec)

Once that’s known we’ll check that the audit log plugin shared library is present;

[moore@randy ~]$ ls -l /usr/lib64/mysql/plugin/audit*
-rwxr-xr-x. 1 root root 42976 Jul  1 09:24 /usr/lib64/mysql/plugin/audit_log.so

Great, we are in good shape to move to the client and install;

mysql> install plugin audit_log soname 'audit_log.so';
Query OK, 0 rows affected (0.00 sec)
mysql> select * from mysql.plugin;
+-------------------------------+--------------+
| name                          | dl           |
+-------------------------------+--------------+
| audit_log                     | audit_log.so |
...
+-------------------------------+--------------+
8 rows in set (0.00 sec)

Voila! It’s that simple. So, what does that provide us? Well now thanks to our default variables we’ve got the following options set;

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_handler         | FILE          |
| audit_log_policy          | ALL           |
| audit_log_rotate_on_size  | 0             |
| audit_log_rotations       | 0             |
| audit_log_strategy        | ASYNCHRONOUS  |
| audit_log_syslog_facility | LOG_USER      |
| audit_log_syslog_ident    | percona-audit |
| audit_log_syslog_priority | LOG_INFO      |
+---------------------------+---------------+
12 rows in set (0.00 sec)

So what we can tell from that output is that our audit plugin is enabled, it’s logging out to the default location ({datadir}/audit.log) and we’re grabbing all events (ALL) on the server and sending the output in XML format (OLD). From the list of variables above we’ve only got one dynamic variable. This means to change the logfile location or the format we need to put these options into our my.cnf and restart the instance. Not very convenient. Personally, it’s my preference to store the audit.log file away from my datadir.

I also dislike the XML formats in favour of the JSON log format. It is also advised, especially on busier systems, to enable the rotation options, audit_log_rotate_on_size and audit_log_rotations so that you don’t end up filling your disk with a huge audit log. Restarting your production instances isn’t extremely convenient but you’ll be happy to learn there is another way.

Let’s rewind to before we installed the plugin. We had checked the existence of our plugin shared library and were itching to run the install command. Now we can open our my.cnf file and add our preferred options prior to installation. Whilst it’s far from a secret, not many will know that the in the plugin installation phase, MySQL will re-read the my.cnf file to check for configuration relevant to the plugin. So let’s add some variables here;

## Audit Logging ##
audit_log_policy=ALL
audit_log_format=JSON
audit_log_file=/var/log/mysql/audit.log
audit_log_rotate_on_size=1024M
audit_log_rotations=10

A quick review of the above. I intend to log all events in JSON format to the /var/log/mysql location. I will rotate each time the active log file hits 1G and this will circulate 10 files meaning I will not have more than 10G of audit logs on my filesystem.

Now with our predefined configuration in the my.cnf we can install the plugin from cold and begin with our preferred options;

mysql> show global variables like 'audit%';
Empty set (0.00 sec)
mysql> install plugin audit_log soname 'audit_log.so';
Query OK, 0 rows affected (0.00 sec)
mysql> show global variables like 'audit%';
+---------------------------+--------------------------+
| Variable_name             | Value                    |
+---------------------------+--------------------------+
| audit_log_buffer_size     | 1048576                  |
| audit_log_file            | /var/log/mysql/audit.log |
| audit_log_flush           | OFF                      |
| audit_log_format          | JSON                     |
| audit_log_handler         | FILE                     |
| audit_log_policy          | ALL                      |
| audit_log_rotate_on_size  | 1073741824               |
| audit_log_rotations       | 10                       |
| audit_log_strategy        | ASYNCHRONOUS             |
| audit_log_syslog_facility | LOG_USER                 |
| audit_log_syslog_ident    | percona-audit            |
| audit_log_syslog_priority | LOG_INFO                 |
+---------------------------+--------------------------+
12 rows in set (0.00 sec)

Something to remember; if you add these variables before installation of the plugin and you restart your instance or suffer a crash, your instance will not start.

[moore@randy ~]$ sudo systemctl restart mysql
[moore@randy ~]$ sudo egrep 'ERROR' /var/log/mysqld.log
2015-09-02 11:55:16 8794 [ERROR] /usr/sbin/mysqld: unknown variable 'audit_log_policy=ALL'
2015-09-02 11:55:16 8794 [ERROR] Aborting

When all up and running we can check that the content is finding it’s way to our log file by opening it up and taking a look. Our JSON output will store a new line of JSON per event, here’s an example:

{"audit_record":{"name":"Query","record":"1067824616_2015-09-02T10:04:26","timestamp":"2015-09-02T10:54:53 UTC","command_class":"show_status","connection_id":"6","status":0,"sqltext":"SHOW /*!50002 GLOBAL */ STATUS","user":"pct[pct] @ localhost [127.0.0.1]","host":"localhost","os_user":"","ip":"127.0.0.1"}}

compare that with the ‘OLD’ XML output format that spans multiple lines making parsing a more difficult task:

<AUDIT_RECORD
 NAME="Query"
 RECORD="2745742_2015-09-02T21:12:10"
 TIMESTAMP="2015-09-02T21:12:22 UTC"
 COMMAND_CLASS="show_status"
 CONNECTION_ID="8"
 STATUS="0"
 SQLTEXT="SHOW /*!50002 GLOBAL */ STATUS"
 USER="pct[pct] @ localhost [127.0.0.1]"
 HOST="localhost"
 OS_USER=""
 IP="127.0.0.1"
/>

Cost

One of the common assumptions of invoking the Audit Plugin is that it’s going to take an almighty hit on load. Logging all connections, queries and admin statements…surely? Well not so true. I spent some time observing the impact to the resources on a humbly specc’d home server. A small machine running quad core Xeon, 32G of RAM and a Samsung PRO SSD with a 72k rpm disk for the logs. Here are a collection of the graphs to illustrate that the impact of turning on the Audit Logging in asynchronous mode, as you will see the results are encouragingly showing little impact on activation of full logging. In each image, audit logging was set off and subsequently on.

percona_cpu_off_onpercona_disk_off_onpercona_off_on

Summary

We can install the Percona Audit plugin with our preferred options on a running system without interrupting it by adding our variables to the my.cnf. By performing this prior to the installing the plugin gives us best practice options without needing to restart the instance for static variables to take effect. Due to the lightweight nature of the audit plugin you can add this new log file to track access and changes to the data without the performance hit of the slow or general log. The audit log is a great aid to debugging and can serve as a security measure and malpractice deterrent.

The post Percona Server audit log plugin best practices appeared first on MySQL Performance Blog.

May
20
2014
--

Database auditing alternatives for MySQL

Database auditing is the monitoring of selected actions of database users. It doesn’t protect the database in case privileges are set incorrectly, but it can help the administrator detect mistakes.

Audits are needed for security. You can track data access and be alerted to suspicious activity. Audits are required for data integrity. They are the only way to validate that changes made to data are correct and legal.

There are several regulations that require database audits:

  • Sarbanes-Oxley (SOX) Act of 2002 is a US federal law that regulates how financial data must be handled and protected.
  • Payment Card Industry Data Security Standard, otherwise known as PCI-DSS is an international standard developed to protect cardholder’s data.
  • Health Insurance Portability and Accountability Act (HIPAA) enacted by the U.S. Congress to protect medical and personal information.

MySQL since version 5.5.3 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.

McAfee MySQL Audit Plugin
This plugin is available for MySQL versions 5.1, 5.5, 5.6. It does not officially support Percona Server and MariaDB. It doesn’t use the Audit API and has better verbosity and better filtering features. This is achieved by binary patching the server at runtime inserting the hooks which extract data stored in known offsets in memory. Thus, the plugin is sensitive to any changes of server code.

Summary:

  • json log format
  • log to file or UNIX socket (allows to log with syslog-ng)
  • filter logged events by users, databases and tables, commands (insert, update, delete)

Oracle Enterprise Audit Log Plugin
Oracle provides this audit plugin as a part of the MySQL Enterprise pack. It uses the MySQL Audit API and is able to log RESULT and CONNECT events. The plugin has support for two XML-based formats.

Summary:

  • XML format
  • log to file
  • filter by event type

MariaDB Audit Plugin
MariaDB developers extended the MySQL Audit API by adding fields for existing events and adding new TABLE event which notifies of operation with tables (read, write, create, drop, alter). The plugin can still be used with MySQL and Percona Server but MariaDB’s additions will not be available.

Summary:

  • CSV log format
  • log to file or syslog
  • filter by users, event types

Percona Server Audit Log feature
Percona has developed an audit log feature that is a part of Percona Server since 5.5.35-37.0 and 5.6.17-65.0. It’s goal is to be compatible with Oracle’s Enterprise Audit Plugin providing a similar set of features for Percona Server users. It asynchronously logs all queries and connections in order to “audit” Percona Server usage, without the overhead of the General Query Log. The Audit Log feature can be very beneficial for web applications that deal with sensitive data (e.g., credit card numbers or medical records) and require security compliance (e.g., HIPAA or SOX). Administrators of multi-tenant applications or MySQL as a service can easily audit data access from a security and performance standpoint when using the Audit Log feature in Percona Server. The Audit Log feature is helpful for investigating and troubleshooting issues and auditing performance, too. The Audit Log feature can be dynamically enabled (does not require a server restart).

The post Database auditing alternatives for MySQL appeared first on MySQL Performance Blog.

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