Experience with McAfee Mysql Audit Plugin

This blog provides step by step tutorial for setting up McAfee MySQL Audit Plugin in MySQL-Server-5.5.37 and platform Linux 2.6.39-400.17.1.e16uek.x86_64 #1

It is difficult task comparing with others auditing tasks.Auditing much important for all DBMS.Comparing with other DBMS MySQL auditing need to be third party plugins otherwise use MySQL Enterprise Audit Log Plugin.Using general log its performance degradation in production environment.

Third party plugin as McAfee MySQL Audit Plugin.McAfee and this plugin provide audit capabilities for MySQL Database and MySQL started supporting for plugins API since MySQL 5.1.

Below processes successfully tested in my local environment.

Prerequisites:

• Installing McAfee Audit Plugin needs MySQL 5.1 to latest version of MySQL Server.
• Only Linux Environment is feasible for McAfee Audit Plugin.

Step 1 : Download Audit plugin for your version

[root@selva]# wget https://github.com/downloads/McAfee/mysql-audit/audit-plugin-MySQL-5.5-1.0.5-473-Linux-x86_64

Step 2 : Extract the audit-plugin-mysql-5.5-1.0.5-473-linux-x86_64.zip

[root@selva]# unzip audit-plugin-mysql-5.5-1.0.5-473-linux-x86_64.zip

Step 3 : Check the location of plugin directory in Linux using MySQL server 

[root@selva]# mysql -u root -p
mysql>show global variables like 'plugin_dir';

The plugin_dir values in my environment /usr/lib64/mysql/plugin/

Step 4 : Copy the unziped plugin into plugin directory

[root@selva]# cp ./audit-plugin-mysql-5.5/lib/libaudit_plugin.so /usr/lib64/mysql/plugin/

Step 5 : When we done copy the plugin into directory use install plugin following the command

mysql>INSTALL PLUGIN AUDIT SONAME 'libaudit_plugin.so';

When you installing this command you lucky gey Query OK result.Otherwise you will get any of below different errors in installation,

• SQL Error (1126):can't open shared library '/usr/lib64/mysql/plugin/libaudit_plugin.so'(errno:2 /usr/lib64/mysql/plugin/libaudit_plugin.so:undefined symbol:_cxa_pure_virtual)
• SQL Error (1123):can't initialize function 'AUDIT';Plugin initialization function failed.

Using this command you will get error log path for mysql server 

mysql>show global variables like 'log_error';

You will get these error in log_error file , 

• 140417 12:06:07 [Note] Audit Plugin: setup_offsets audit_offsets: (null) validate_checksum: 1 offsets_by_version: 1
• 140417 12:06:07 [Note] Audit Plugin: mysqld: /usr/sbin/mysqld (b77de3909af62707ca068be61a64f406)
• 140417 12:06:07 [Note] Audit Plugin: Couldn't find proper THD offsets for: 5.5.37
• 140417 12:06:07 [ERROR] Plugin 'AUDIT' init function returned error.
• 140417 12:06:07 [Note] Audit Plugin: deinit

Never get worried failure leads to success , Use correct version of audit plugin you will get install soon.Once you struggling in this do one more things,try this all plugin for your required environment surely you will get result.Follow below link to get all versions audit plugins,

http://dl.bintray.com/mcafee/mysql-audit-plugin/#audit-plugin-mysql-5.5-1.0.5-464-linux-i386.zip

Step 6 : Once you successfully installed , check 

mysql>show plugins;

You will get list of plugins installed in the mysql server.You can find audit plugin version you have installed in the mysql server,

mysql>SHOW GLOBAL STATUS LIKE 'AUDIT_version';

Step 7 : Once audit plugin installed and loaded into mysql server , we can set the audit plugin confguration system variables,check available audit plugin variables using below command,

mysql>show global variables like 'audit%';

Check available audit plugin configuration variables below link,

https://github.com/mcafee/mysql-audit/wiki/Configuration 

Step 8 : We must need to set only three or four configuration variables , changes made in my.cnf file or via command.we can set this using command,no need to add via my.cnf file.

All audit activities store in the JSON format.It supports directly auditing activities to a file or a Unix socket .

we need to set , audit_json_file disable/enable , audit_record_cmds like DDL or DCL and Connect,Failed Login,Quit , audit_record_objs like databases (mysql,*).

mysql>set global audit_json_file=1;
mysql>set global audit_record_cmds='Connect,Quit,Failed Login,insert,drop,create';
mysql>set global audit_record_objs='mysql,sakila';

After completing this check again , for all configuration variables is updated .

mysql>show global variables like 'audit%';

Step 9 : Now check the mysql-audit.json file in mysql datadir it will update the result.

Note:Will Truncate the mysql-audit.json file will be replace the data once you have did any changes in databases.If you deleted the mysql-audit.json need to install 'libaudit_plugin.so' again.

Step 10 : Steps will not complete mysql-audit.json file will growth like general log , so we need to backup or update into another file , keep log in separate json files . forthcoming posts will update export json file data into mysql table and the processes.

Thank You,

Be Better Than You Were Yesterday