I recently had to do some customer work involving the McAfee MySQL Audit Plugin and would like to share my experience in this post.
Auditing user activity in MySQL has traditionally been challenging. Most data can be obtained from the slow or general log, but this involves a lot of data you don’t need too, and isn’t flexible at all. The specific problem of logging failed connection attempts has been discussed on a previous post in our blog.
Starting with 5.1, the new plugin API gives us more flexibility by allowing users to extend the server’s functionality with their own code, and this is what the McAffee plugin does.
Installation and configuration are straightforward following the available instructions. The only extra step I had to take was to extract the offsets for the Percona Server version I was using for the test (5.5.28-29.1). This is needed as the plugin needs the offset to some MySQL data structures that, the plugin authors say, aren’t exposed by a consistent API. If you also need to do this, the details are clearly explained here.
The plugin writes its output in json format, and supports writing it directly to a file, or to a unix socket, which means you can write a script to listen on this socket and process the audit records as you wish.
Performance-wise, I did basic tests on the VM I was working in and didn’t get significant differences between either output option, or between using the plugin or enabling the general log. Bear in mind these were basic tests (just a few mysqlslap runs with increasing levels of concurrency), but initially, I would think the advantage of the plugin is its flexibility, and not its performance, which seems to be on par with having the general log enabled.
The flexibility comes from the three variables that can be set to control what is logged by the plugin:
– audit_record_cmds : This is the list of commands you want written to the log (all the lists in these variables are comma separated). As pointed here, anything that would generate a write to the general log will be sent to the plugin, and you can control if it gets written on not with this list. I tested this with “connect,Quit” to log successful and failed connections. Yes, it had to be a capital Q in Quit for that to work, and no, my code-fu was not enough to understand why that is the case. Maybe someone more knowledgeable in MySQL internals can enlighten me here.
– audit_record_objs : List of database objects (tables, according to the docs) for which you want events written to the log.
– audit_whitelist_users : This one is undocumented on the wiki at the time of writing, and is a list of users for which you do not want events written to the log.
Just for reference, these are the lines I had to add to my config file for the plugin to work (plus one commented line for switching between file and socket for output):
plugin-load=AUDIT=libaudit_plugin.so
audit_offsets=6464, 6512, 4072, 4512, 104, 2584
audit_json_file=1
audit_json_socket_name=/tmp/audit.sock
#audit_json_socket=1
audit_json_log_file=/var/lib/mysql/audit.log
audit_record_cmds=connect,Quit
Notice the audit_offsets that I mentioned had to be extracted due to this Percona Server version not being included in the binary.
And here’s a few sample output lines generated by the plugin with this configuration:
{"msg-type":"activity","date":"1369155747373","thread-id":"6439","query-id":"0","user":"debian-sys-maint","priv_user":"debian-sys-maint","host":"localhost","cmd":"Connect","query":"Connect"}
{"msg-type":"activity","date":"1369155747373","thread-id":"6439","query-id":"219309","user":"debian-sys-maint","priv_user":"debian-sys-maint","host":"localhost","cmd":"Quit","query":"Quit"}
{"msg-type":"activity","date":"1369155747383","thread-id":"6440","query-id":"0","user":"debian-sys-maint","priv_user":"debian-sys-maint","host":"localhost","cmd":"Connect","query":"Connect"}
In conclusion, the plugin API seems to be opening new possibilities of extending MySQL’s behavior in a way that, once set up, is transparent to users, and the McAfee MySQL Audit Plugin is only one of example of what can be achieved with it. It is a very good one for me, since I think proper audit trail support has been an important missing feature on the server, which has made using MySQL in PCI or SOX compliant environments, to name just two, artificially complicated, as one had to rely on too much info (general log) or external help (snort or similar IDS).
Can different filters be combined? For example, all the select queries in mysql.user from a particular user.
Miguel: Filters can be combined, but there’s no way (that I know) to achieve what you want with this plugin. You could add mysql.user to the audit_record_objs variable, for example, but not indicate a specific user for which you want to audit changes.
Fernando,
And what is about it (most confusing option):
audit_offsets=6464, 6512, 4072, 4512, 104, 2584
Peter:
The docs say these are offsets in the mysqld binary to some data structures. By looking at the offset extraction script it seems these are query_id, thread_id, main_security_ctx, command and lex from THD, and comment from LEX.
“Quit” will log failed connections. But is there any way to log failed queries?
Have you tried to get the offsets for the latest version of Percona? I installed percona via yum so it seems that debug symbols are not included.
Here’s details of my perconal install and running the extract script. Do you by chance have a set of audit offsets that work with this version?
[root@db4 ~]# ./extract_offsets.sh /usr/sbin/mysqld _Z16acl_authenticateP3THDjj
//offsets for: /usr/sbin/mysqld (5.6.14-56)
offsets.gdb:2: Error in sourced command file:
No symbol table is loaded. Use the “file” command.,
[root@db4 ~]# mysql –version
mysql Ver 14.14 Distrib 5.6.14, for Linux (x86_64) using EditLine wrapper
[root@db4 ~]# yum list installed | grep Percona
Percona-Server-client-56.x86_64 5.6.14-rel62.0.483.rhel6 @Percona
Percona-Server-devel-56.x86_64 5.6.14-rel62.0.483.rhel6 @Percona
Percona-Server-server-56.x86_64 5.6.14-rel62.0.483.rhel6 @Percona
Percona-Server-shared-56.x86_64 5.6.14-rel62.0.483.rhel6 @Percona
[root@db4 ~]#
Hi Jeremiah,
Try to install the debuginfo package:
https://www.percona.com/redir/downloads/Percona-Server-5.6/LATEST/RPM/rhel6/x86_64/Percona-Server-56-debuginfo-5.6.14-rel62.0.483.rhel6.x86_64.rpm
Regards.
Hi Guys,
Im trying to install audit plugin into Percona 5.6.14 on Centos 5
When i try to get the offset i get this error
./offset-extract.sh /usr/sbin/mysqld Z16aclauthenticateP3THDjj
//offsets for: /usr/sbin/mysqld (5.6.14-56)
/root/flavian/mysql-audit/offset-extract/offsets.gdb:2: Error in sourced command file:
No symbol table is loaded. Use the “file” command.,
Installed Packages:
Percona-Server-56-debuginfo.x86_64 5.6.14-rel62.0.483.rhel5 installed
Percona-Server-client-56.x86_64 5.6.14-rel62.0.483.rhel5 installed
Percona-Server-devel-56.x86_64 5.6.14-rel62.0.483.rhel5 installed
Percona-Server-server-56.x86_64 5.6.14-rel62.0.483.rhel5 installed
Percona-Server-shared-56.x86_64 5.6.14-rel62.0.483.rhel5 installed
Also when i try to load the plugin im getting
mysql> INSTALL PLUGIN AUDIT SONAME ‘libaudit_plugin.so’;
ERROR 1126 (HY000): Can’t open shared library ‘/usr/lib64/mysql/plugin/libaudit_plugin.so’ (errno: 2 /usr/lib64/mysql/plugin/libaudit_plugin.so: undefined symbol: Z16aclauthenticateP3THDjj)
ll -h /usr/lib64/mysql/plugin/libaudit_plugin.so
total 1.2M
-rwxr-xr-x 1 root root 721K Jan 10 04:27 libaudit_plugin.so
I Also tried to compile audit plugin with percona source code but no luck with dat too.
This is wat i get on 2nd last step of compile
CXX=’gcc -static-libgcc’ CC=’gcc -static-libgcc’ ./configure –with-mysql=Percona-Server-5.6.14 –with-mysql-libservices=Percona-Server-5.6.14-rel62.0/libservices/libmysqlservices.a
checking build system type… x86_64-unknown-linux-gnu
checking host system type… x86_64-unknown-linux-gnu
checking target system type… x86_64-unknown-linux-gnu
checking for a BSD-compatible install… /usr/bin/install -c
checking whether build environment is sane… yes
checking for a thread-safe mkdir -p… /bin/mkdir -p
checking for gawk… gawk
checking whether make sets $(MAKE)… yes
checking how to create a ustar tar archive… gnutar
checking whether to enable debugging… no
checking for mysql source code… configure: error: Failed to find required header file include/my_dir.h in Percona-Server-5.6.13, check the path and make sure you’ve run ‘./configure …. && cd include && make’ in MySQL 5.1 sources dir or ‘cmake . && make’ in MySQL 5.5 sources dir.
Hi Flavian,
I’m Percona’s community manager. This post is several months old – the best place to ask specific questions is on our MySQL discussions forums at https://www.percona.com/forums/
I invite you to join the community of your MySQL peers and also our own Percona experts (free to join and participate). I hope to see you there!
Hi,
I am not able to generate offset on ubuntu 14.04 and mysql version – 5.5.50-0ubuntu0.14.04.1
while running below code caught error:
./offset-extract.sh /usr/sbin/mysqld
//offsets for: /usr/sbin/mysqld (5.5.50-0ubuntu0.14.04.1)
offsets.gdb:2: Error in sourced command file:
No symbol table is loaded. Use the “file” command.,
Can anyone help me!!