[MySQL 101] Audit MySQL user activities with McAfee Audit Plugin

Storing, keeping track the user activities and alerting when abnormal action occur, and auditing is a abosultely important demand that every system administrators need.

In this article, we focus on Audit MySQL User Activities by using McAfee Audit Plugin: how to know when users login to MySQL, is there failed login (brute force???) or users change password...

When you - a DB admin - receive a request of audit, you will immediately think about built-in log of MySQL, general log as common. General Log is what exactly what we need, but General Log has too much information of query that we have to filter it or pay more time to cut it from your system before do something further. They ususally enable General Log when they need to debug, but the historical log is the pass you should forget when enable or disable sometime. So, McAfee make it right by creating a plugin that only log audit log to solve your headache.

Some candidates we should list here:

• MySQL Enterprise Audit: you should prepare your money for this, too much money.

• MariaDB Audit Plugin: built-in MariaDB installation package. You can NOT download separately, it mean you can not integrate it with MySQL. This is the link to download.

• Percona Audit Log Plugin: as MariaDB mindset, you have to work with Percona.

• McAfee Audit Plugin: Open source project, and still be maintain until now. Github: https://github.com/mcafee/mysql-audit. As you may know, McAfee is a giant cybersecurity company in the world, you may have a basic trust on them.

Setup & Configuration

Quite easy for newbie, or anyone knows how to work with Linux can do this. Just keep in mind that, installing the plugin need you to restart MySQL daemon to apply change.

The very first step, you need to download a version from here.

After downloading successfully, unzip it, you will receive the file: lib/libaudit_plugin.so.

Check the plugin directory of MySQL by the command:

mysql> SELECT @@plugin_dir;
+------------------------+
| @@plugin_dir           |
+------------------------+
| /usr/lib/mysql/plugin/ |
+------------------------+
1 row in set (0.00 sec)

Copy the file libaudit_plugin.so to /usr/lib/mysql/plugin/

$ sudo cp lib/libaudit_plugin.so /usr/lib/mysql/plugin/

Edit the /etc/my.cnf, add some configuration to [mysqld] block:

[mysqld]
# Install Plugin
plugin-load=AUDIT=libaudit_plugin.so
# Log some activities: login, failed login (bruteforce) 
audit_force_record_logins=1 
audit_json_log_file=/var/lib/mysql/data/mysql-audit.json
# Define log format as JSON
audit_json_file=1 
# Whitelist the users
audit_whitelist_users=root,user1,user2 
# Define the queries that log output will mask the password.
audit_password_masking_cmds=ALTER_USER,CREATE_USER,GRANT,SET_OPTION,SLAVE_START,CREATE_SERVER,ALTER_SERVER,CHANGE_MASTER,UPDATE 
# Define the query will be logged
audit_record_cmds=CREATE_USER,ALTER_USER

Save the changes, and restart mysql service:

$ sudo systemctl restart mysqld

Verify the plugin as below:

mysql> show plugins;
mysql> show global status like 'AUDIT_version';

If yours have the same output as figure above, the plugin has been installed. Next, you can check the file /var/lib/mysql/data/mysql-audit.json for audit log.

Example: