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 : 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.

Care should be taken with binlog-do-db

MySQL Replication is a built-in feature of MySQL that enables data from one MySQL database server (the master) to be copied to one or more MySQL database servers (the slaves).

I have explained the architecture and variables used  in my previous blogs  Replication Architecture   and Replication Variables respectively.

Binlog-do-db is an replication filter which is used in the master server. It is used to capture  the changes happened in the mysql to binary logs  only for the databases mentioned in it.

ie . binlog_do_db=test       (# where test is the database name)

What happens :

If your replication is running with the above variable declared only the changes happening to tables under test schema should be logged.

But if you trying to create an user and provide grants,  mysql will log those changes also to the binary logs.

Below is the master status of my server.

mysql> show master status;

+-------------------+----------+--------------+------------------+-------------------+

| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

+-------------------+----------+--------------+------------------+-------------------+

| mydb07-bin.000001 |      154 | test         |                  |                   |

+-------------------+----------+--------------+------------------+-------------------+

You can see i have declared binlog-do-db to logs for schema test.

My binlog contents:-

[root@mydb07 ~]# mysqlbinlog --base64-output=DECODE-ROWS  /var/lib/mysql/mydb07-bin.000001  

/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;

/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;

DELIMITER /*!*/;

# at 4

#170920 10:48:55 server id 1  end_log_pos 123 CRC32 0x942281e9 Start: binlog v 4, server v 5.7.19-log created 170920 10:48:55 at startup

# Warning: this binlog is either in use or was not closed properly.

ROLLBACK/*!*/;

BINLOG '

P/rBWQ8BAAAAdwAAAHsAAAABAAQANS43LjE5LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA

AAAAAAAAAAAAAAAAAAA/+sFZEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA

AemBIpQ=

'/*!*/;

# at 123

#170920 10:48:55 server id 1  end_log_pos 154 CRC32 0xc85f522a Previous-GTIDs

# [empty]

SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;

DELIMITER ;

# End of log file

/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

[root@mydb07 ~]#

Creating an user :

mysql> create user test_user@localhost identified by 'Test@789';

Query OK, 0 rows affected (0.01 sec)

mysql>

Checking binlog contents:-

[root@mydb07 ~]# mysqlbinlog --base64-output=DECODE-ROWS  /var/lib/mysql/mydb07-bin.000001  

/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;

/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;

DELIMITER /*!*/;

# at 4

#170920 10:48:55 server id 1  end_log_pos 123 CRC32 0x942281e9 Start: binlog v 4, server v 5.7.19-log created 170920 10:48:55 at startup

# Warning: this binlog is either in use or was not closed properly.

ROLLBACK/*!*/;

BINLOG '

P/rBWQ8BAAAAdwAAAHsAAAABAAQANS43LjE5LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA

AAAAAAAAAAAAAAAAAAA/+sFZEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA

AemBIpQ=

'/*!*/;

# at 123

#170920 10:48:55 server id 1  end_log_pos 154 CRC32 0xc85f522a Previous-GTIDs

# [empty]

# at 154

#170920 11:00:59 server id 1  end_log_pos 219 CRC32 0xb2cddbf2 Anonymous_GTID last_committed=0 sequence_number=1 rbr_only=no

SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;

# at 219

#170920 11:00:59 server id 1  end_log_pos 412 CRC32 0x14a2986a Query thread_id=4 exec_time=0 error_code=0

SET TIMESTAMP=1505885459/*!*/;

SET @@session.pseudo_thread_id=4/*!*/;

SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;

SET @@session.sql_mode=1436549152/*!*/;

SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;

/*!\C utf8 *//*!*/;

SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=8/*!*/;

SET @@session.lc_time_names=0/*!*/;

SET @@session.collation_database=DEFAULT/*!*/;

CREATE USER 'test_user'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*BC93E6B8DE8D55D91B316057474434B4E6CEC4CC'

/*!*/;

SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;

DELIMITER ;

# End of log file

/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

[root@mydb07 ~]#

You can see the user creation statement was also logged.

When you are in a replication environment where slave already have that user will give an replication failure.

So how to overcome this scenario,

Whenever creating an user or providing grants or dropping a user do the below.

First select an schema apart from the schema involved in the binlog-do-db, it will be better to use mysql schema.

Creating an user  :

mysql>

mysql> use mysql

Database changed

mysql> create user test_user_new@localhost identified by 'Test_new@123';

Query OK, 0 rows affected (0.00 sec)

On selecting mysql schema, it will not allow mysql to log the changes to binary logs as test schema is not involved here.

Also creating a table on test schema on using it  (For test purpose)

mysql> use test

Database changed

mysql> create table tbl_a (id int not null);

Query OK, 0 rows affected (0.02 sec)

mysql>

Checking binlog contents:-

[root@mydb07 ~]# mysqlbinlog --base64-output=DECODE-ROWS  /var/lib/mysql/mydb07-bin.000001

/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;

/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;

DELIMITER /*!*/;

# at 4

#170920 10:48:55 server id 1  end_log_pos 123 CRC32 0x942281e9 Start: binlog v 4, server v 5.7.19-log created 170920 10:48:55 at startup

# Warning: this binlog is either in use or was not closed properly.

ROLLBACK/*!*/;

# at 123

#170920 10:48:55 server id 1  end_log_pos 154 CRC32 0xc85f522a Previous-GTIDs

# [empty]

# at 154

#170920 11:00:59 server id 1  end_log_pos 219 CRC32 0xb2cddbf2 Anonymous_GTID last_committed=0 sequence_number=1 rbr_only=no

SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;

# at 219

#170920 11:00:59 server id 1  end_log_pos 412 CRC32 0x14a2986a Query thread_id=4 exec_time=0 error_code=0

SET TIMESTAMP=1505885459/*!*/;

SET @@session.pseudo_thread_id=4/*!*/;

SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;

SET @@session.sql_mode=1436549152/*!*/;

SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;

/*!\C utf8 *//*!*/;

SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=8/*!*/;

SET @@session.lc_time_names=0/*!*/;

SET @@session.collation_database=DEFAULT/*!*/;

CREATE USER 'test_user'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*BC93E6B8DE8D55D91B316057474434B4E6CEC4CC'

/*!*/;

# at 412

#170920 11:18:48 server id 1  end_log_pos 477 CRC32 0xb6c4f262 Anonymous_GTID last_committed=1 sequence_number=2 rbr_only=no

SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;

# at 477

#170920 11:18:48 server id 1  end_log_pos 587 CRC32 0x039a506b Query thread_id=6 exec_time=0 error_code=0

use `test`/*!*/;

SET TIMESTAMP=1505886528/*!*/;

create table tbl_a (id int not null)

/*!*/;

SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;

DELIMITER ;

# End of log file

/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

Here you can see only the table creation statement logged and not user creation.

Similarly when you are creating a table on test schema while schema mysql is used it will also not be logged.

mysql>

mysql> use mysql

Database changed

mysql> create user test_user_new_2@localhost identified by 'Test_new@123';

Query OK, 0 rows affected (0.00 sec)

mysql> create table tbl_b (id int not null);

Query OK, 0 rows affected (0.01 sec)

Checking binlog contents:-

[root@mydb07 ~]# mysqlbinlog --base64-output=DECODE-ROWS  /var/lib/mysql/mydb07-bin.000001

/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;

/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;

DELIMITER /*!*/;

# at 4

#170920 10:48:55 server id 1  end_log_pos 123 CRC32 0x942281e9 Start: binlog v 4, server v 5.7.19-log created 170920 10:48:55 at startup

# Warning: this binlog is either in use or was not closed properly.

ROLLBACK/*!*/;

# at 123

#170920 10:48:55 server id 1  end_log_pos 154 CRC32 0xc85f522a Previous-GTIDs

# [empty]

# at 154

#170920 11:00:59 server id 1  end_log_pos 219 CRC32 0xb2cddbf2 Anonymous_GTID last_committed=0 sequence_number=1 rbr_only=no

SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;

# at 219

#170920 11:00:59 server id 1  end_log_pos 412 CRC32 0x14a2986a Query thread_id=4 exec_time=0 error_code=0

SET TIMESTAMP=1505885459/*!*/;

SET @@session.pseudo_thread_id=4/*!*/;

SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;

SET @@session.sql_mode=1436549152/*!*/;

SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;

/*!\C utf8 *//*!*/;

SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=8/*!*/;

SET @@session.lc_time_names=0/*!*/;

SET @@session.collation_database=DEFAULT/*!*/;

CREATE USER 'test_user'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*BC93E6B8DE8D55D91B316057474434B4E6CEC4CC'

/*!*/;

# at 412

#170920 11:18:48 server id 1  end_log_pos 477 CRC32 0xb6c4f262 Anonymous_GTID last_committed=1 sequence_number=2 rbr_only=no

SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;

# at 477

#170920 11:18:48 server id 1  end_log_pos 587 CRC32 0x039a506b Query thread_id=6 exec_time=0 error_code=0

use `test`/*!*/;

SET TIMESTAMP=1505886528/*!*/;

create table tbl_a (id int not null)

/*!*/;

SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;

DELIMITER ;

# End of log file

/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

[root@mydb07 ~]#

So when you want to create a table in test schema you need to always select (use) it (use test) .