Auditing MySQL With Mcafee Audit Plugin

mysql-audit

Audit MySQL isn’t an easy task by default, you can use some technics like tcpdump, write a parser for general log, use MySQL proxy, or you can use some of audit plugins available out there(Mcafee MySQL Audit Plugin or MySQL Enterprise Audit Log Plugin for example).

On this post I’ll cover the Mcafee MySQL Audit Plugin (https://github.com/mcafee/mysql-audit), on a follow-up post I’ll talk about MySQL Enterprise Audit Log Plugin.

The installation is easy and require just a few steps, I’m using MySQL 5.5 32 bits, so I’ll download the files for my MySQL version and architecture from https://github.com/mcafee/mysql-audit/downloads

[root@mysql-audit marcelo]# wget https://github.com/downloads/mcafee/mysql-audit/audit-plugin-mysql-5.5-1.0.3-371-linux-i386.zip
[root@mysql-audit marcelo]# unzip audit-plugin-mysql-5.5-1.0.3-371-linux-i386.zip
Archive:  audit-plugin-mysql-5.5-1.0.3-371-linux-i386.zip
   creating: audit-plugin-mysql-5.5/
   creating: audit-plugin-mysql-5.5/lib/
  inflating: audit-plugin-mysql-5.5/lib/libaudit_plugin.so  
  inflating: audit-plugin-mysql-5.5/COPYING  
  inflating: audit-plugin-mysql-5.5/THIRDPARTY.txt  
  inflating: audit-plugin-mysql-5.5/README.txt

Next step is to copy the libaudit_plugin.so to MySQL plugin dir:

mysql [localhost] {msandbox} ((none)) > SHOW VARIABLES LIKE 'plugin_dir';
+---------------+---------------------------------+
| Variable_name | Value                           |
+---------------+---------------------------------+
| plugin_dir    | /var/marcelo/5.5.33/lib/plugin/ |
+---------------+---------------------------------+
1 row in set (0.00 sec)

mysql [localhost] {msandbox} ((none)) > quit
Bye
[root@mysql-audit audit-plugin-mysql-5.5]# cp lib/libaudit_plugin.so /var/marcelo/5.5.33/lib/plugin/

In order to 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@mysql-audit marcelo]# wget https://raw.github.com/mcafee/mysql-audit/v1.0.3/offset-extract/offset-extract.sh
[root@mysql-audit marcelo]# ./offset-extract.sh 5.5.33/bin/mysqld
//offsets for: 5.5.33/bin/mysqld (5.5.33)
{"5.5.33","3172729c5bf6e81c8d87fe26fe248204", 3816, 3844, 2368, 2700, 44, 1656},

Now we are going to change our .cnf file to load and enable the plugin by default:

# add under the [mysqld] section of your .cnf
plugin-load=AUDIT=libaudit_plugin.so
audit_offsets=3816, 3844, 2368, 2700, 44, 1656
audit_json_file=1

Restart the server, by default, a file named mysql-audit.json is created on MySQL datadir, from now one, you can parse the file to extract the information that you think is relevant to you, see bellow some examples:

Get failed attempts to connect to MySQL server:

[root@mysql-audit data]# cat mysql-audit.json | grep '\"cmd\":\"Failed Login\"'
{"msg-type":"activity","date":"1381764013653","thread-id":"65","query-id":"0","user":"msandbox","priv_user":"","ip":"10.10.2.68","cmd":"Failed Login","query":"Failed Login"}
{"msg-type":"activity","date":"1381764016225","thread-id":"66","query-id":"0","user":"msandbox","priv_user":"","ip":"10.10.2.68","cmd":"Failed Login","query":"Failed Login"}
{"msg-type":"activity","date":"1381764026005","thread-id":"67","query-id":"0","user":"root","priv_user":"","ip":"10.10.2.68","cmd":"Failed Login","query":"Failed Login"}

Get sucessful attempts to connect to MySQL server:

[root@mysql-audit data]# cat mysql-audit.json | grep '\"cmd\":\"Connect\"'
{"msg-type":"activity","date":"1381763915626","thread-id":"60","query-id":"0","user":"msandbox","priv_user":"msandbox","ip":"10.10.2.68","cmd":"Connect","query":"Connect"}
{"msg-type":"activity","date":"1381763915626","thread-id":"61","query-id":"0","user":"msandbox","priv_user":"msandbox","ip":"10.10.2.68","cmd":"Connect","query":"Connect"}
{"msg-type":"activity","date":"1381763915626","thread-id":"62","query-id":"0","user":"msandbox","priv_user":"msandbox","ip":"10.10.2.68","cmd":"Connect","query":"Connect"}
{"msg-type":"activity","date":"1381763916006","thread-id":"64","query-id":"0","user":"msandbox","priv_user":"msandbox","ip":"10.10.2.68","cmd":"Connect","query":"Connect"}

Get all activities from a specific host

[root@mysql-audit data]# cat mysql-audit.json | grep '\"ip\":\"10.10.2.68\"'
{"msg-type":"activity","date":"1381763919836","thread-id":"60","query-id":"4798","user":"msandbox","priv_user":"msandbox","ip":"10.10.2.68","cmd":"select","objects":[{"db":"test","name":"sbtest8","obj_type":"TABLE"}],"query":"SELECT SUM(K) FROM sbtest8 WHERE id BETWEEN 151257 AND 151257+99"}
{"msg-type":"activity","date":"1381763919844","thread-id":"62","query-id":"4799","user":"msandbox","priv_user":"msandbox","ip":"10.10.2.68","cmd":"select","objects":[{"db":"test","name":"sbtest6","obj_type":"TABLE"}],"query":"SELECT c FROM sbtest6 WHERE id BETWEEN 141568 AND 141568+99 ORDER BY c"}
{"msg-type":"activity","date":"1381763919847","thread-id":"47","query-id":"4800","user":"msandbox","priv_user":"msandbox","ip":"10.10.2.68","cmd":"update","objects":[{"db":"test","name":"sbtest3","obj_type":"TABLE"}],"query":"UPDATE sbtest3 SET k=k+1 WHERE id=150189"}
{"msg-type":"activity","date":"1381763919848","thread-id":"60","query-id":"4801","user":"msandbox","priv_user":"msandbox","ip":"10.10.2.68","cmd":"select","objects":[{"db":"test","name":"sbtest8","obj_type":"TABLE"}],"query":"SELECT c FROM sbtest8 WHERE id BETWEEN 175916 AND 175916+99 ORDER BY c"}
{"msg-type":"activity","date":"1381763919869","thread-id":"64","query-id":"4803","user":"msandbox","priv_user":"msandbox","ip":"10.10.2.68","cmd":"select","objects":[{"db":"test","name":"sbtest10","obj_type":"TABLE"}],"query":"SELECT DISTINCT c FROM sbtest10 WHERE id BETWEEN 132850 AND 132850+99 ORDER BY c"}
{"msg-type":"activity","date":"1381763919881","thread-id":"57","query-id":"4784","user":"msandbox","priv_user":"msandbox","ip":"10.10.2.68","cmd":"commit","query":"COMMIT"}
{"msg-type":"activity","date":"1381763919930","thread-id":"37","query-id":"4802","user":"msandbox","priv_user":"msandbox","ip":"10.10.2.68","cmd":"commit","query":"COMMIT"}
{"msg-type":"activity","date":"1381764013653","thread-id":"65","query-id":"0","user":"msandbox","priv_user":"","ip":"10.10.2.68","cmd":"Failed Login","query":"Failed Login"}
{"msg-type":"activity","date":"1381764016225","thread-id":"66","query-id":"0","user":"msandbox","priv_user":"","ip":"10.10.2.68","cmd":"Failed Login","query":"Failed Login"}
{"msg-type":"activity","date":"1381764026005","thread-id":"67","query-id":"0","user":"root","priv_user":"","ip":"10.10.2.68","cmd":"Failed Login","query":"Failed Login"}

Get all DELETES on a specific table(sbtest8) from a specific host

[root@mysql-audit data]# cat mysql-audit.json | grep -i '\"cmd\":\"delete\"' | grep -i '\"name\":\"sbtest8\"' | grep '\"ip\":\"10.10.2.68\"'
{"msg-type":"activity","date":"1381763914163","thread-id":"53","query-id":"3366","user":"msandbox","priv_user":"msandbox","ip":"10.10.2.68","cmd":"delete","objects":[{"db":"test","name":"sbtest8","obj_type":"TABLE"}],"query":"DELETE FROM sbtest8 WHERE id=150652"}
{"msg-type":"activity","date":"1381763914947","thread-id":"44","query-id":"3513","user":"msandbox","priv_user":"msandbox","ip":"10.10.2.68","cmd":"delete","objects":[{"db":"test","name":"sbtest8","obj_type":"TABLE"}],"query":"DELETE FROM sbtest8 WHERE id=194099"}
{"msg-type":"activity","date":"1381763915718","thread-id":"36","query-id":"3630","user":"msandbox","priv_user":"msandbox","ip":"10.10.2.68","cmd":"delete","objects":[{"db":"test","name":"sbtest8","obj_type":"TABLE"}],"query":"DELETE FROM sbtest8 WHERE id=151405"}
{"msg-type":"activity","date":"1381763916273","thread-id":"48","query-id":"3799","user":"msandbox","priv_user":"msandbox","ip":"10.10.2.68","cmd":"delete","objects":[{"db":"test","name":"sbtest8","obj_type":"TABLE"}],"query":"DELETE FROM sbtest8 WHERE id=148546"}
{"msg-type":"activity","date":"1381763918698","thread-id":"40","query-id":"4437","user":"msandbox","priv_user":"msandbox","ip":"10.10.2.68","cmd":"delete","objects":[{"db":"test","name":"sbtest8","obj_type":"TABLE"}],"query":"DELETE FROM sbtest8 WHERE id=149492"}

As you can see, you can extract and adjust the log to fit your needs, there is 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) :

audit_record_cmds – list of commands that you would like to record, for example, if you want to record only failed connections and connections, change it to : audit_record_cmds=”Failed Login,Connect”
audit_record_objs – list of objects/tables that you would like to record activity, for example, if you want to record only activity on test database, change it to audit_record_objs=”test.*”, if you want to record activity only for table sbtest1 and sbtest2 change it to audit_record_objs=”test.sbtest1,test.sbtest2″

That is it, use you imagination and start to audit your MySQL server.

10 thoughts on “Auditing MySQL With Mcafee Audit Plugin

  1. Hi marceloaltmann,

    I never know you will see my comment in tommorrow or now.I have worked as per your steps to audit mysql login log,but my two days of struggling it not working to my system,

    I have installed mysql-server-5.5.34 ubuntu-12.10 but its giving struggle to configure with mysql audit

    [root@localhost Desktop]# ./offset-extract.sh /usr/libexec/mysqld
    //offsets for: /usr/libexec/mysqld (5.5.34)
    offsets.gdb:2: Error in sourced command file:
    No symbol table is loaded. Use the “file” command., getting this error i have tried different libaudit_plugin but its not working..

    Kindly help me to configure mcafee audt plugin..

  2. Hi marceloaltmann,

    I have missed to add plugin and offsets as per your requests..
    I have configured remaining with putty but only its working for DDL and DCL comments ,its not working for connect and failed login did you have any idea,anythink i missed.

    steps done by me,

    1.Download latest plugin and installed via putty.
    2.audit_json_file=1 via putty and added cmds,objs require to audit.
    3.Its working after this.

    Kindly help me..

  3. Hello marceloaltmann,

    I am installing McAfee Audit Plugin 1.1.6 in my Centos 7 MySQL version 5.6.40 followed by all installation instruction given on Wiki . After all troubleshooting I’m not able to Load Plugin either my.cnf or INSTALL PLUGIN.
    Always getting fail to initialize AUDIT error. Observed in ,MySQL error log and found “McAfee Audit Plugin: Couldn’t find proper THD offsets for: 5.6.40”.
    We have also extract offset and OFF checksum but not Loaded plugin.

    Please help me….

    1. Hi GetMySQL. Plugin is working on 5.6.40 see below logs:


      2018-05-15 14:23:23 4946 [Note] InnoDB: 5.6.40 started; log sequence number 1626007
      2018-05-15 14:23:23 4946 [Note] McAfee Audit Plugin: starting up. Version: 1.1.6 , Revision: 784 (64bit). MySQL AUDIT plugin interface version: 769 (0x301). MySQL Server version: 5.6.40.
      2018-05-15 14:23:23 4946 [Note] McAfee Audit Plugin: setup_offsets 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 validate_checksum: 1 offsets_by_version: 1
      2018-05-15 14:23:23 4946 [Note] McAfee Audit Plugin: mysqld: /usr/sbin/mysqld (d156a1659a2a6b64ca0ea3f5e4c77c5b)
      2018-05-15 14:23:23 4946 [Note] McAfee Audit Plugin: setup_offsets Audit_formatter::thd_offsets values: 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
      2018-05-15 14:23:23 4946 [Note] McAfee Audit Plugin: extended offsets validate res: MySQL thread id 123456, OS thread handle 0x0, query id 789 aud_tusr
      2018-05-15 14:23:23 4946 [Note] McAfee Audit Plugin: Validation passed. Using offsets from 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
      2018-05-15 14:23:23 4946 [Note] McAfee Audit Plugin: Set whitelist_cmds num: 3, value: BEGIN,COMMIT,PING
      2018-05-15 14:23:23 4946 [Note] McAfee Audit Plugin: Set password_masking_cmds num: 8, value: CREATE_USER,GRANT,SET_OPTION,SLAVE_START,CREATE_SERVER,ALTER_SERVER,CHANGE_MASTER,UPDATE
      2018-05-15 14:23:23 4946 [Note] McAfee Audit Plugin: Compile password_masking_regex res: [1]
      2018-05-15 14:23:23 4946 [Note] McAfee Audit Plugin: Set password_masking_regex value: [identified(?:/*.*?*/|s)*?by(?:/*.*?*/|s)*?(?:password)?(?:/*.*?*/|s)*?['|"](?.*?)(?<!\)['|"]|password(?:/*.*?*/|s)*?((?:/*.*?*/|s)*?['|"](?.*?)(?<!\)['|"](?:/*.*?*/|s)*?)|password(?:/*.*?*/|s)*?(?:for(?:/*.*?*/|s)*?S+?)?(?:/*.*?*/|s)*?=(?:/*.*?*/|s)*?['|"](?.*?)(?<!\)['|"]|password(?:/*.*?*/|s)*?['|"](?.*?)(?<!\)['|"]]
      2018-05-15 14:23:23 4946 [Note] McAfee Audit Plugin: Set json_socket_name str: [] value: [/var/run/db-audit/mysql.audit__var_lib_mysql_3306]
      2018-05-15 14:23:23 4946 [Note] McAfee Audit Plugin: bufsize for file [mysql-audit.json]: 1. Value of json_file_bufsize: 1.
      2018-05-15 14:23:23 4946 [Note] McAfee Audit Plugin: success opening file: mysql-audit.json.
      2018-05-15 14:23:23 4946 [Note] McAfee Audit Plugin: mem via 32bit mmap: 0x40739000 page size: 4096
      2018-05-15 14:23:23 4946 [Note] Audit Plugin: hot patching function: 0x6f1f20, trampolineFunction: 0x40739000 trampolinePage: 0x40739000
      2018-05-15 14:23:23 4946 [Note] McAfee Audit Plugin: hot patch for: mysql_execute_command (0x6f1f20) complete. Audit func: 0x7fcb95990f40, Trampoline address: 0x40739000, size: 6, used: 34.
      2018-05-15 14:23:23 4946 [Note] Audit Plugin: hot patching function: 0x6b6340, trampolineFunction: 0x40739030 trampolinePage: 0x40739000
      2018-05-15 14:23:23 4946 [Note] McAfee Audit Plugin: hot patch for: send_result_to_client (0x6b6340) complete. Audit func: 0x7fcb95990360, Trampoline address: 0x40739030, size: 6, used: 34.
      2018-05-15 14:23:23 4946 [Note] Audit Plugin: hot patching function: 0x6ee540, trampolineFunction: 0x40739060 trampolinePage: 0x40739000
      2018-05-15 14:23:23 4946 [Note] McAfee Audit Plugin: hot patch for: check_table_access (0x6ee540) complete. Audit func: 0x7fcb95990ce0, Trampoline address: 0x40739060, size: 6, used: 34.
      2018-05-15 14:23:23 4946 [Note] Audit Plugin: hot patching function: 0x6b15a0, trampolineFunction: 0x40739090 trampolinePage: 0x40739000
      2018-05-15 14:23:23 4946 [Note] McAfee Audit Plugin: hot patch for: open_tables (0x6b15a0) complete. Audit func: 0x7fcb95990050, Trampoline address: 0x40739090, size: 6, used: 34.
      2018-05-15 14:23:23 4946 [Note] McAfee Audit Plugin: Done initializing sql command names. status_vars_index: [141], com_status_vars: [0x125c600].
      2018-05-15 14:23:23 4946 [Note] McAfee Audit Plugin: Init completed successfully.
      2018-05-15 14:23:23 4946 [Note] Server hostname (bind-address): '*'; port: 3306
      2018-05-15 14:23:23 4946 [Note] IPv6 is available.
      2018-05-15 14:23:23 4946 [Note] - '::' resolves to '::';
      2018-05-15 14:23:23 4946 [Note] Server socket created on IP: '::'.
      2018-05-15 14:23:23 4946 [Note] Event Scheduler: Loaded 0 events
      2018-05-15 14:23:23 4946 [Note] /usr/sbin/mysqld: ready for connections.
      Version: '5.6.40' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Server (GPL)
      [root@localhost log]# mysql -e "show global status like 'AUDIT_version';"
      +---------------+-----------+
      | Variable_name | Value |
      +---------------+-----------+
      | Audit_version | 1.1.6-784 |
      +---------------+-----------+

      Make sure you have the right offsets and on Centos7 you need to either allow the audit plugin on setlinux or disabled it:


      setenforce 0;

      Here is the audit part of my.cnf


      [mysqld]
      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

Leave a Reply

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