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 : http://dl.bintray.com/mcafee/mysql-audit-plugin/
I used http://dl.bintray.com/mcafee/mysql-audit-plugin/audit-plugin-mysql-5.6-1.1.5-752-linux-x86_64.zip
Download the file to your server and unzip it.
[root@mydbtest ~]# wget http://dl.bintray.com/mcafee/mysql-audit-plugin/audit-plugin-mysql-5.6-1.1.5-752-linux-x86_64.zip
--2017-11-17 22:46:04-- http://dl.bintray.com/mcafee/mysql-audit-plugin/audit-plugin-mysql-5.6-1.1.5-752-linux-x86_64.zip
Connecting to akamai.bintray.com|104.81.24.171|:80... connected.
HTTP request sent, awaiting response... 200 OK
Length: 510814 (499K) [application/zip]
Saving to: `audit-plugin-mysql-5.6-1.1.5-752-linux-x86_64.zip'
100%[==================================================================================================================================================================>] 510,814 --.-K/s in 0.1s
2017-11-17 22:46:07 (3.78 MB/s) - `audit-plugin-mysql-5.6-1.1.5-752-linux-x86_64.zip' saved [510814/510814]
[root@mydbtest ~]# ls
anaconda-ks.cfg audit-plugin-mysql-5.6-1.1.5-752-linux-x86_64.zip install.log install.log.syslog
[root@mydbtest ~]# unzip audit-plugin-mysql-5.6-1.1.5-752-linux-x86_64.zip
Archive: audit-plugin-mysql-5.6-1.1.5-752-linux-x86_64.zip
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/libaudit_plugin.so
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/offset-extract.sh
[root@mydbtest ~]#
Next we need to copy the libaudit_plugin.so 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)
mysql>
Next copy the plugin to that path
[root@mydbtest ~]# cp audit-plugin-mysql-5.6-1.1.5-752/lib/libaudit_plugin.so /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 https://github.com/mcafee/mysql-audit/wiki/Troubleshooting 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
-----------------
1/1
-----------------
-----------------
Installed:
gdb.x86_64 0:7.2-92.el6
Complete!
[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
offset-extract.sh
[root@mydbtest utils]#
[root@mydbtest utils]# which mysqld
/usr/sbin/mysqld
[root@mydbtest utils]#
[root@mydbtest utils]# sh offset-extract.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
plugin-load=AUDIT=libaudit_plugin.so
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
audit_json_file=1
audit_json_log_file=/var/log/mysql_audit.log
plugin-load : to enable audit log
audit_offsets : Generated offset values from the offset-extract.sh 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":"1510941592450","thread-id":"1","query-id":"0","user":"UNKNOWN_MYSQL_USER","priv_user":"","ip":"","host":"localhost","connect_attrs":{"_os":"Linux","_client_name":"libmysql","_pid":"14472","_client_version":"5.6.38","_platform":"x86_64","program_name":"mysqladmin"},"pid":"14472","os_user":"root","appname":"/usr/bin/mysqladmin","cmd":"Connect","query":"Connect"}
{"msg-type":"activity","date":"1510941592450","thread-id":"1","query-id":"3","user":"UNKNOWN_MYSQL_USER","priv_user":"","ip":"","host":"localhost","connect_attrs":{"_os":"Linux","_client_name":"libmysql","_pid":"14472","_client_version":"5.6.38","_platform":"x86_64","program_name":"mysqladmin"},"pid":"14472","os_user":"root","appname":"/usr/bin/mysqladmin","cmd":"Quit","query":"Quit"}
{"msg-type":"activity","date":"1510941658020","thread-id":"2","query-id":"0","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","cmd":"Connect","query":"Connect"}
{"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
https://github.com/mcafee/mysql-audit/wiki/Configuration for more info
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.
