Percona Server audit log plugin best practices

Percona Server audit log plugin best practices

PREVIOUS POST
NEXT POST

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;

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

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

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;

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;

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;

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.

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:

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

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.

PREVIOUS POST
NEXT POST

Share this post

Comments (7)

  • Luis Reply

    I hope soon can implement the characteristics like loggins events ddl/dml and excluding/including users, similar to mariadb plugin.

    September 14, 2015 at 11:00 am
  • honeybee Reply

    How do we stop the logging? is there a parameter we can set on/off?

    September 22, 2015 at 5:08 pm
  • Andrew Moore Reply

    @luis, agreed. The piece of functionality I desire most is more flexible filtering. There are two open bugs/blueprints for this to be added in the future.

    https://bugs.launchpad.net/percona-server/+bug/1455432
    https://bugs.launchpad.net/percona-server/+bug/1455430

    I hope that we can add this in near-future releases.

    @honeybee, the variable audit_log_policy can be set to NONE to cease the logging on your system. If this is a permanent change, add that value into your my.cnf file too.

    September 24, 2015 at 3:56 am
  • mark Reply

    hi,
    The test result is impressive !
    Can you show the test in detail ? I want to know the concurrency in this test

    December 21, 2016 at 11:00 pm
  • Ron Johnson Reply

    What about in a master-master cluster? Does installing it on one node force a re-read of all my.cnf files?

    December 22, 2016 at 4:05 am
  • Peter Duffy Reply

    I’ve been trying the audit_log plugin for about a week, and my experience has been fairly negative. I was concerned about the amount of data which would be produced if I installed the plugin on a production server, so I started by installing it on a test server with a fairly low throughput. All seemed well at first – but then I changed the variable “audit_log_policy” from “ALL” to “QUERY”, and soon afterwards, the users of the server reported that they could not contact the server any more. When we checked, we found that there was a vast number of connections which appeared to be hanging. The obvious next step was to reboot the server – but as an experiment, I changed audit_log_policy to “NONE” – and everything immediately started working again. The problem was too closely synchronised with the changes to the plugin variable for it to be a coincidence – and on this basis, it’s highly unlikely that we’ll be trying the audit_log plugin again.

    January 18, 2018 at 9:31 am
  • Peter Duffy Reply

    (correction – I changed audit_log_policy to “QUERIES”, not “QUERY”)

    January 18, 2018 at 9:32 am

Leave a Reply