Auditing MySQL Users With McAfee Plugin

Posted in: MySQL, Open Source, Technical Track

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.

email

Interested in working with Ivan? Schedule a tech call.

Lead Database Consultant

3 Comments. Leave new

Very thorough explanation. Thank you.

Reply

Hi

maybe u can help … :-)

the following record appears in my audit log:
{“msg-type”:”activity”,”date”:”1528632467176″,”thread-id”:”107355″,”query-id”:”1220825″,”user”:”myuser”,”priv_user”:”myuser”,”ip”:”127.0.0.1″,”host”:”localhost”,”connect_attrs”:{“_runtime_version”:”1.7.0_111″,”_client_version”:”5.1.38″,”_client_name”:”MySQL Connector Java”,”_client_license”:”GPL”,”_runtime_vendor”:”Oracle Corporation”},”status”:”0″,”cmd”:”set_option”,”query”:”SET autocommit=1″}

what I would like is to catch all of the events with related to set_option. in the above line from audit log:
“cmd”:”set_option”
but from these events, I would like to exclude only the events fiting this pattern:
“query”:”SET autocommit=1″
“query”:”SET autocommit=0″

I failed doing it by defining “audit_whitelist_cmds” .

how do I do it?

Thanks

Reply
Ivan Groenewold
June 12, 2018 10:12 am

Hi liav, I think the plugin doesn’t offer the granularity to do that, but I suggest you open an issue in the plugin’s gihub page. Perhaps they will implement it.

Reply

Leave a Reply

Your email address will not be published. Required fields are marked *