Chapter 7 Installing MySQL Enterprise Audit

MySQL Enterprise Audit enables standard, policy-based monitoring and logging of connection and query activity, providing an auditing and compliance solution for applications that are governed by both internal and external regulatory guidelines.

When installed, the audit plugin enables the MySQL server to produce a log file containing an audit record of server activity. The log contents include when clients connect and disconnect, and what actions they perform while connected, such as which databases and tables they access.

After you install the plugin, it writes an audit log file. By default, the file is named audit.log and is located in the data directory.

To install MySQL Enterprise Audit:

1. Run the audit_log_filter_linux_install.sql script located in the share directory of your MySQL installation.

   $> cd /usr/local/mysql
   $> bin/mysql -u root -p < /usr/local/mysql/share/audit_log_filter_linux_install.sql
   Enter password: (enter root password here)
   Result
   OK

2. Verify the plugin installation by logging in as root and examining the INFORMATION_SCHEMA.PLUGINS table or using the SHOW PLUGINS statement.

   $> cd /usr/local/mysql 
   $> bin/mysqladmin -u root -p version
   Enter password: (enter root password here)

   mysql> SELECT PLUGIN_NAME, PLUGIN_STATUS
          FROM INFORMATION_SCHEMA.PLUGINS
          WHERE PLUGIN_NAME LIKE 'audit%';
   +-------------+---------------+
   | PLUGIN_NAME | PLUGIN_STATUS |
   +-------------+---------------+
   | audit_log   | ACTIVE        |
   +-------------+---------------+

3. To prevent the plugin from being removed at runtime, add the --audit-log option under the [mysqld] option group in the MySQL configuration file (/etc/my.cnf) with a setting of FORCE_PLUS_PERMANENT.

   audit-log=FORCE_PLUS_PERMANENT

4. Restart the server to apply the configuration change:

   $> systemctl restart mysqld

5. By default, rule-based audit log filtering logs no auditable events for any users. To produce log-everything behavior with rule-based filtering, create a simple filter to enable logging and assign it to the default account:

   mysql> SELECT audit_log_filter_set_filter('log_all', '{ "filter": { "log": true } }');
   +-------------------------------------------------------------------------+
   | audit_log_filter_set_filter('log_all', '{ "filter": { "log": true } }') |
   +-------------------------------------------------------------------------+
   | OK                                                                      |
   +-------------------------------------------------------------------------+
   
   mysql> SELECT audit_log_filter_set_user('%', 'log_all');
   +-------------------------------------------------------------------------+
   | audit_log_filter_set_filter('log_all', '{ "filter": { "log": true } }') |
   +-------------------------------------------------------------------------+
   | OK                                                                      |
   +-------------------------------------------------------------------------+

   The filter assigned to % is used for connections from any account that has no explicitly assigned filter (which initially is true for all accounts).

6. To verify that events are being logged, issue a statement such as SHOW DATABASES and check the audit.log file contents for the log event.

   $> cd /usr/local/mysql 
   $> bin/mysqladmin -u root -p version
   Enter password: (enter root password here)

   mysql> SHOW DATABASES;
   +--------------------+
   | Database           |
   +--------------------+
   | information_schema |
   | mysql              |
   | performance_schema |
   | sys                |
   +--------------------+

   The SHOW DATABASES statement run as root@localhost writes a log event to audit.log similar to the following:

   <AUDIT_RECORD>
   <TIMESTAMP>2017-05-15T16:08:46 UTC</TIMESTAMP>
   <RECORD_ID>24683_2017-05-12T23:08:52</RECORD_ID>
   <NAME>Query</NAME>
   <CONNECTION_ID>25</CONNECTION_ID>
   <STATUS>0</STATUS>
   <STATUS_CODE>0</STATUS_CODE>
   <USER>root[root] @ localhost []</USER>
   <OS_LOGIN/>
   <HOST>localhost</HOST>
   <IP/>
   <COMMAND_CLASS>show_databases</COMMAND_CLASS>
   <SQLTEXT>SHOW DATABASES</SQLTEXT>
   </AUDIT_RECORD>

Note

Contents of the audit log file may contain sensitive information, such as the text of SQL statements. For security reasons, the file should be written to a directory accessible only to the MySQL server and users with a legitimate reason to view the log. The default audit log file is named audit.log and is located in the data directory. In this deployment, the data directory is owned by the mysql user. This location of the audit log file can be changed at server startup using the audit_log_file system variable.

Optionally, audit log files may also be encrypted. Encryption is based on a user-defined password. To use this feature, the MySQL keyring must be enabled because audit logging uses it for password storage. For more information, see Encrypting Audit Log Files.

For more information about configuring MySQL Enterprise Audit, see MySQL Enterprise Audit.