Auditando MySQL Com Mcafee Audit Plugin

mysql-audit

Auditar o MySQL com as funcionalidades padrão, não é uma tarefa fácil, pode se tentar algumas tecnicas utilizando tcpdump, escrever algum script baseado no general log (log geral), utilizar o MySQL proxy, ou pode se utilizar algum plugin designado a isso (como por exemplo Mcafee MySQL Audit Plugin ou MySQL Enterprise Audit Log Plugin) .

Neste poste vou abortar o Mcafee MySQL Audit Plugin (https://github.com/mcafee/mysql-audit), em um proximo post abortarei o MySQL Enterprise Audit Log Plugin.

A instalação é simples, requere apenas alguns passos, estou utilizando o MySQL 5.5 32 bits, então vou baixar a versão do plugin 32 bits para MySQL 5.5 em 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

Proximo passo é copiar o arquivo libaudit_plugin.so para o plugin dir do MySQL:

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/

Para que o plugin funcione, o Mcafee MySQL Audit Plugin precisa de alguns offsets do MySQL server, alguns já estão inclusos no código fonte, e outros não, precisamos extrair alguns offsets (veja https://github.com/mcafee/mysql-audit/wiki/Troubleshooting para mais informações).
Vamos precisar do GBD para extrair os offsets, o pacote pode ser obtido via 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},

Agora, vamos fazer algumas alterações no arquivo .cnf(my.cnf) para ativar o plugin:

# Adicione dentro da sessão [mysqld] 
plugin-load=AUDIT=libaudit_plugin.so
audit_offsets=3816, 3844, 2368, 2700, 44, 1656
audit_json_file=1

Agora o que precisamos fazer é apenas reinicar o serviço do MySQL, por padrão, um arquivo chamado mysql-audit.json é criado dentro do datadir do MySQL, basta agora tu extrair a informação que achares relevante, veja abaixo alguns exemplos:

Tentativas de conexão não bem sucedias:

[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"}

Tentativas de conexão bem sucedias:

[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"}

Todas as atividades de um host especifico:

[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"}

Todos os comandos DELETE em uma especifica tabela(sbtest8) de um especifico 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"}

Como pode ver, tu pode extrair e ajustar o log para se enquadrar as tuas necessidades, tu pode também adicionar algumas configurações ao plugin para mudar um pouco o seu comportamento (https://github.com/mcafee/mysql-audit/wiki/Configuration para mais informações) :

audit_record_cmds – lista de comandos que deseja gravar no log, por exemplo, se tu deseja gravar apenas tentativas de conexão configure esta opção como: audit_record_cmds=”Failed Login,Connect”
audit_record_objs – lista de banco de dados/tabelas que deseja gravar no log, por exemplo, se tu deseja gravar apenas atividades no banco de dados test, configure esta opcao como: audit_record_objs=”test.*” , se tu deseja gravar apenas atividades nas tabelas sbtest1 e sbtest2 do banco de dados chamado test, configure esta opção como: audit_record_objs=”test.sbtest1,test.sbtest2″

É isso ai, use sua imaginação e comece a auditar o seu MySQL.

10 thoughts on “Auditando MySQL Com 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

      1. Hi Marcelo.

        Thanks for reply me..

        We have resolved the isaue.

        After disabling Selinux it’s got resolved.

Deixe uma resposta

O seu endereço de e-mail não será publicado. Campos obrigatórios são marcados com *