Auditing MySQL Users With McAfee Plugin
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 ;-)
