Monday, 20 November 2017

Auditing MySQL 5.6 With Mcafee Audit Plugin

Database auditing is the monitoring of selected actions of database users. It doesn’t protect the database in case privileges are set incorrectly, but it can help the administrator detect mistakes.

Why you need to audit your database?

  • Audits are needed for security and required for data integrity.
  • If there is any bad behavior, you can investigate suspicious activity.
  • To determine users from inappropriate actions.
  • To track users who connects the server .

In this blog i will show how to setup a Mcaffe audit plugin for our mysql database.

There are many audit plugin solutions like percona audit plugin, MySQL enterprise audit plugin etc.

Installing of Mcaffe audit plugin is very easy with simple steps.
I am using MySQL version 5.6.38 on CentOS release 6.9

You need to download the proper plugin file based on your MySQL version , you can yours from here :

Download the file to your server and unzip it.

[root@mydbtest ~]# wget
--2017-11-17 22:46:04--
Connecting to||:80... connected.
HTTP request sent, awaiting response... 200 OK
Length: 510814 (499K) [application/zip]
Saving to: `'

100%[==================================================================================================================================================================>] 510,814     --.-K/s   in 0.1s    

2017-11-17 22:46:07 (3.78 MB/s) - `' saved [510814/510814]

[root@mydbtest ~]# ls
anaconda-ks.cfg  install.log  install.log.syslog
[root@mydbtest ~]# unzip
  creating: audit-plugin-mysql-5.6-1.1.5-752/
  creating: audit-plugin-mysql-5.6-1.1.5-752/lib/
 inflating: audit-plugin-mysql-5.6-1.1.5-752/lib/  
 inflating: audit-plugin-mysql-5.6-1.1.5-752/COPYING  
 inflating: audit-plugin-mysql-5.6-1.1.5-752/THIRDPARTY.txt  
 inflating: audit-plugin-mysql-5.6-1.1.5-752/README.txt  
 inflating: audit-plugin-mysql-5.6-1.1.5-752/plugin-name.txt  
  creating: audit-plugin-mysql-5.6-1.1.5-752/utils/
 inflating: audit-plugin-mysql-5.6-1.1.5-752/utils/  
[root@mydbtest ~]#

Next we need  to copy the to MySQL plugin dir.
To get the MySQL plugin dir execute the below query on MySQL command line

mysql> SHOW VARIABLES LIKE 'plugin_dir';
| Variable_name | Value                    |
| plugin_dir    | /usr/lib64/mysql/plugin/ |
1 row in set (0.00 sec)


Next copy the plugin to that path

[root@mydbtest ~]# cp audit-plugin-mysql-5.6-1.1.5-752/lib/ /usr/lib64/mysql/plugin/

In order for smooth work, Mcafee MySQL Audit Plugin needs to extract some offsets from MySQL server, some of them are build-in the the Audit code, but some aren’t (see for more information).

GDB will be necessary to extract the offsets, use yum install gdb or apt-get install gdb

[root@mydbtest ~]# yum install gdb
Loaded plugins: fastestmirror
Setting up Install Process
 gdb.x86_64 0:7.2-92.el6  

[root@mydbtest ~]#

Now extract the files by running the script which can be found in your plugin file

[root@mydbtest ~]# cd audit-plugin-mysql-5.6-1.1.5-752
[root@mydbtest audit-plugin-mysql-5.6-1.1.5-752]# ls
COPYING  README.txt  THIRDPARTY.txt  lib  plugin-name.txt  utils
[root@mydbtest audit-plugin-mysql-5.6-1.1.5-752]# cd utils/
[root@mydbtest utils]# ls
[root@mydbtest utils]#
[root@mydbtest utils]# which mysqld
[root@mydbtest utils]#
[root@mydbtest utils]# sh /usr/sbin/mysqld
//offsets for: /usr/sbin/mysqld (5.6.38)
{"5.6.38","4a69e494a346098095c132dc050a6289", 6992, 7040, 4000, 4520, 72, 2704, 96, 0, 32, 104, 136, 7128, 4392, 2800, 2808, 2812, 536, 0, 0, 6360, 6384, 6368, 13048, 548, 516},
[root@mydbtest utils]#

Next is the section to edit your cnf file and add the  below variables and values,

# add under the [mysqld] section of your .cnf
audit_offsets=6992, 7040, 4000, 4520, 72, 2704, 96, 0, 32, 104, 136, 7128, 4392, 2800, 2808, 2812, 536, 0, 0, 6360, 6384, 6368, 13048, 548, 516

plugin-load   : to enable audit log
audit_offsets   : Generated offset values from the script
audit_json_file : To create an json file format of your audits
audit_json_log_file : Location of the audit file {default is data directory}

These are the settings which has to be done finally we need to restart the MySQL server for the changes to take place.

Note : make sure the audit log file exists and have permission of mysql if the path is set to a different location ,else MySQl will fail to start.

[root@mydbtest ~]# touch /var/log/mysql_audit.log
[root@mydbtest ~]# chown -R mysql.mysql /var/log/mysql_audit.log

[root@mydbtest ~]#
[root@mydbtest ~]# service mysqld restart
Stopping mysqld:                                           [  OK  ]
Starting mysqld:                                           [  OK  ]
[root@mydbtest ~]#

Now you can confirm the audit log by checking the MySQL server and you can the json log file.

mysql> show variables like 'audit%';

You can view the generated audit log file

[root@mydbtest ~]# cat /var/log/mysql_audit.log
{"msg-type":"activity","date":"1510941658021","thread-id":"2","query-id":"4","user":"root","priv_user":"root","ip":"","host":"localhost","connect_attrs":{"_os":"Linux","_client_name":"libmysql","_pid":"14476","_client_version":"5.6.38","_platform":"x86_64","program_name":"mysql"},"pid":"14476","os_user":"root","appname":"mysql","rows":"1","status":"0","cmd":"select","query":"select @@version_comment limit 1"}

There are also some configurations that you can enable to change the way the plugin works

Now your MySQL is been under audit, there are lot of variables which can be seen from the above link.
Most of the AUDIT Plugin system variables can be changed dynamically while the server is running by means of the SET statement.