MySQL Audit

Error Log

You can audit failed login attempts by increasing the log_error_verbosity to include Warnings (in addition to Errors)...

SET GLOBAL log_error_verbosity=2;

1 = ERROR2 = ERROR, WARNING3 = ERROR,WARNING, INFORMATION

MySQL Enterprise Audit

TODO

Percona Audit Log Plugin

Check

SELECT * 

  FROM information_schema.PLUGINS 

 WHERE PLUGIN_NAME LIKE '%audit%';

SHOW variables LIKE 'audit%'; 

SHOW variables LIKE 'plugin%'; 

+---------------+------------------------+| Variable_name | Value                  |+---------------+------------------------+| plugin_dir    | /usr/lib/mysql/plugin/ |+---------------+------------------------+1 row in set (0.00 sec)

Enable

INSTALL PLUGIN audit_log SONAME 'audit_log.so'; 

SELECT * 

  FROM information_schema.PLUGINS 

 WHERE PLUGIN_NAME LIKE '%audit%';

*************************** 1. row ***************************          PLUGIN_NAME: audit_log        PLUGIN_VERSION: 0.2        PLUGIN_STATUS: ACTIVE          PLUGIN_TYPE: AUDIT  PLUGIN_TYPE_VERSION: 4.1        PLUGIN_LIBRARY: audit_log.soPLUGIN_LIBRARY_VERSION: 1.7        PLUGIN_AUTHOR: Percona LLC and/or its affiliates.    PLUGIN_DESCRIPTION: Audit log        PLUGIN_LICENSE: GPL          LOAD_OPTION: ON1 row in set (0.00 sec)

SHOW variables LIKE 'audit%'; 

+-----------------------------+---------------+| Variable_name               | Value         |+-----------------------------+---------------+| audit_log_buffer_size       | 1048576       || audit_log_exclude_accounts  |               || audit_log_exclude_commands  |               || audit_log_exclude_databases |               || audit_log_file              | audit.log     || audit_log_flush             | OFF           || audit_log_format            | OLD           || audit_log_handler           | FILE          || audit_log_include_accounts  |               || audit_log_include_commands  |               || audit_log_include_databases |               || audit_log_policy            | ALL           || audit_log_rotate_on_size    | 0             || audit_log_rotations         | 0             || audit_log_strategy          | ASYNCHRONOUS  || audit_log_syslog_facility   | LOG_USER      || audit_log_syslog_ident      | percona-audit || audit_log_syslog_priority   | LOG_INFO      |+-----------------------------+---------------+18 rows in set (0.00 sec)

 

+-----------------------------------------------| Description+-----------------------------------------------|||||| OFF, ON| OLD, NEW, CSV, JSON| FILE, SYSLOG|||| ALL, LOGINS, QUERIES, NONE| Size in bytes; 0 = Don't rotate| Defines how many log files should be kept when audit_log_rotate_on_size is non-zero and handler = FILE| ASYNCHRONOUS, PERFORMANCE, SEMISYNCHRONOUS, SYNCHRONOUS|||+-----------------------------------------------

Example Usage

Filtering by accounts...

SET GLOBAL audit_log_include_accounts = 'myuser@localhost,root@localhost';

SET GLOBAL audit_log_include_accounts = NULL;

SET GLOBAL audit_log_exclude_accounts = 'myuser@localhost,root@localhost';

SELECT @@audit_log_exclude_accounts;

SELECT @@audit_log_include_accounts; 

Filtering by SQL command...

SELECT name

  FROM performance_schema.setup_instruments

 WHERE name LIKE "statement/sql/%"

 ORDER BY name;


SET GLOBAL audit_log_include_commands= 'set_option,create_db';

SET GLOBAL audit_log_include_commands = NULL;

SET GLOBAL audit_log_exclude_commands= 'set_option,create_db';

Filtering by database...

SET GLOBAL audit_log_include_databases = 'mysql,mydb';

SET GLOBAL audit_log_exclude_databases = 'mysql,mydb';

MariaDB Audit Plugin

TODO

McAfee / Trellix mysql-audit Plugin

TODO

Bibliography