Auditing MySQL Users With McAfee Plugin

Written by Ivan Groenewold | Sep 20, 2016 4:00:00 AM

It is a common auditing requirement to log user connection events, including whether or not authentication was successful. There are a number of alternatives available for MySQL, but unfortunately there is no built-in functionality at the time of this writing. In this post we will discuss auditing MySQL users with McAfee plugin, which is available under GPL Version 2 license.

Installation instructions

These apply to debian-based hosts, but instructions for red hat are similar.

1. Download latest plugin release from github.

At the time of this post for MySQL 5.5 you can get:

wget https://bintray.com/mcafee/mysql-audit-plugin/download_file?file_path=1.0.9-release%2Faudit-plugin-mysql-5.5-1.0.9-585-linux-x86_64.zip

2. Decompress

unzip audit-plugin-mysql-5.5-1.0.9-585-linux-x86_64.zip

3. Copy the plugin file to MySQL plugin dir

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

cp audit-plugin-mysql-5.5-1.0.9-585/lib/libaudit_plugin.so /usr/lib/mysql/plugin/

4. Calculate offsets of internal structures.

The plugin watches the database in-memory structures to capture events. This step is needed only if the plugin can't automatically determine the offsets in your installed platform/version. You will see an error in the mysql error log about this if that's the case, and the plugin will refuse to load:

mysql> INSTALL PLUGIN AUDIT SONAME 'libaudit_plugin.so';
 ERROR 1123 (HY000): Can't initialize function 'AUDIT'; Plugin initialization function failed.

If that's the case, perform the following steps: 4.a Install gdb if not present

apt-get install gdb

4.b Install MySQL debug symbols

wget https://www.percona.com/downloads/Percona-Server-5.5/Percona-Server-5.5.31-30.3/deb/precise/x86_64/percona-server-5.5-dbg_5.5.31-rel30.3-520.precise_amd64.deb
 dpkg -i percona-server-5.5-dbg_5.5.31-rel30.3-520.precise_amd64.deb

4.c Get the offset extractor

wget https://raw.github.com/mcafee/mysql-audit/master/offset-extract/offset-extract.sh
 chmod +x offset-extract.sh

4.d Run the offset extractor

See also https://github.com/mcafee/mysql-audit/wiki/Troubleshooting

./offset-extract.sh /usr/sbin/mysqld /usr/lib/debug/usr/sbin/mysqld.debug

depending on version, debug symbols might be on this file instead (do not confuse with /usr/lib/debug/usr/sbin/mysqld-debug):

 ./offset-extract.sh /usr/sbin/mysqld /usr/lib/debug/usr/sbin/mysqld

5. Setup the plugin options in my.cnf.

This example will audit login/logout events and write in json format:

plugin-load=AUDIT=libaudit_plugin.so
 audit_offsets=7704, 7760, 4392, 5032, 88, 2720, 96, 0, 32, 104, 152, 7880
 audit_json_file=1
 audit_json_log_file=/var/log/mysql/mysql-audit.json
 audit_record_cmds='connect,Failed Login,Quit'

6. Install the plugin First option is to install at mysql start.

This is recommended for prod environments: a. Add to my.cnf under [mysqld] section:

plugin-load=AUDIT=libaudit_plugin.so

b. restart mysql Dynamic install is also possible:

INSTALL PLUGIN AUDIT SONAME 'libaudit_plugin.so';

7. Verify the plugin is loaded successfully

 mysql> show plugins;
 +--------------------------------+----------+--------------------+--------------------+---------+
 | Name | Status | Type | Library | License |
 +--------------------------------+----------+--------------------+--------------------+---------+
 | binlog | ACTIVE | STORAGE ENGINE | NULL | GPL |
 | mysql_native_password | ACTIVE | AUTHENTICATION | NULL | GPL |
 | mysql_old_password | ACTIVE | AUTHENTICATION | NULL | GPL |
 | MRG_MYISAM | ACTIVE | STORAGE ENGINE | NULL | GPL |
 | MEMORY | ACTIVE | STORAGE ENGINE | NULL | GPL |
 | CSV | ACTIVE | STORAGE ENGINE | NULL | GPL |
 | MyISAM | ACTIVE | STORAGE ENGINE | NULL | GPL |
 | PERFORMANCE_SCHEMA | ACTIVE | STORAGE ENGINE | NULL | GPL |
 | BLACKHOLE | ACTIVE | STORAGE ENGINE | NULL | GPL |
 | FEDERATED | DISABLED | STORAGE ENGINE | NULL | GPL |
 | InnoDB | ACTIVE | STORAGE ENGINE | NULL | GPL |
 | INNODB_RSEG | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
 | INNODB_UNDO_LOGS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
 | INNODB_TRX | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
 | INNODB_LOCKS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
 | INNODB_LOCK_WAITS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
 | INNODB_CMP | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
 | INNODB_CMP_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
 | INNODB_CMPMEM | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
 | INNODB_CMPMEM_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
 | INNODB_SYS_TABLES | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
 | INNODB_SYS_TABLESTATS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
 | INNODB_SYS_INDEXES | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
 | INNODB_SYS_COLUMNS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
 | INNODB_SYS_FIELDS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
 | INNODB_SYS_FOREIGN | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
 | INNODB_SYS_FOREIGN_COLS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
 | INNODB_SYS_STATS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
 | INNODB_TABLE_STATS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
 | INNODB_INDEX_STATS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
 | INNODB_BUFFER_POOL_PAGES | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
 | INNODB_BUFFER_POOL_PAGES_INDEX | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
 | INNODB_BUFFER_POOL_PAGES_BLOB | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
 | XTRADB_ADMIN_COMMAND | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
 | INNODB_CHANGED_PAGES | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
 | INNODB_BUFFER_PAGE | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
 | INNODB_BUFFER_PAGE_LRU | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
 | INNODB_BUFFER_POOL_STATS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
 | ARCHIVE | ACTIVE | STORAGE ENGINE | NULL | GPL |
 | partition | ACTIVE | STORAGE ENGINE | NULL | GPL |
 | AUDIT | ACTIVE | DAEMON | libaudit_plugin.so | GPL |
 +--------------------------------+----------+--------------------+--------------------+---------+
 41 rows in set (0.00 sec)
 
 mysql> show global status like 'AUDIT_version';
 +---------------+-----------+
 | Variable_name | Value |
 +---------------+-----------+
 | Audit_version | 1.0.9-585 |
 +---------------+-----------+
 1 row in set (0.00 sec)

8. Check plugin is working:

tail /var/log/mysql/mysql-audit.json

If it's working, the output will look like this:

{"msg-type":"header","date":"1473856656558","audit-version":"1.0.9-585","audit-protocol-version":"1.0","hostname":"test_host","mysql-version":"5.5.31-30.3-log","mysql-program":"/usr/sbin/mysqld","mysql-socket":"/var/run/mysqld/mysqld.sock","mysql-port":"3306"}
 {"msg-type":"activity","date":"1473856741263","thread-id":"3007689","query-id":"0","user":"root","priv_user":"root","host":"localhost","cmd":"Connect","query":"Connect"}
 {"msg-type":"activity","date":"1473856741296","thread-id":"3007689","query-id":"519639755","user":"root","priv_user":"root","host":"localhost","cmd":"Quit","query":"Quit"}

9. Add log rotation.

The plugin output file can be flushed with the following:

 set global audit_json_file_flush=on;

When it is executed you will see the following output in the mysql error log:

[Note] Audit Plugin: success opening file: mysql-audit.json.
 [Note] Audit Plugin: Log flush complete.

So to roll over the log, rename the log file before issuing the command above. You can also setup logrotate to do this with the postrotate action. This is left as an exercise to the reader ;-)

Audit options

Here is a brief description of the most interesting plugin options, taken from the official docs: audit_record_cmds: Comma separated list of commands to log to the audit trail. For example: insert,update,delete. audit_record_objs: Comma separated list of objects (tables) to log to the audit trail. Table name should be specified as: database.table. Wild cards are supported and it is possible to specify: .mytable or mydb.. Specify: {} as part of the list to include the empty set to catch also cases where an activity has no objects (for example connect and quit). audit_whitelist_users: Comma separated list of white-listed users whose queries are not recorded. Specify: {} as part of the list to include the empty user. audit_whitelist_cmds: Comma separated list of white-listed cmds whose queries are not recorded. Introduced at version 1.0.6.

MySQL Database Consulting

Looking to optimize your MySQL use?

View full post