Introducing the MySQL Audit Log Filter Plugin

December 28, 2023

Author

Oleksandr Kachan

Insight for DBAs

MySQL

Percona Software

Share this Post:

We’re happy to introduce Audit Log Filter — our newly upgraded audit plugin. Its functionality has been significantly improved in comparison to Audit Log and currently mirrors the functionality of the MySQL Enterprise Audit plugin. Starting with Percona Server for MySQL 8.0.34-26, the Audit Log Filter is available in a technical preview mode. You’re welcome to try it out and share your feedback with us.

New functionality

See what you can do with the Audit Log Filter plugin:

Configure rule-based auditable events filtering. You can filter events based on a user account, audit event class name, audit event subclass name, and audit event fields (database name, table name, operation status, and so on).

The plugin contains built-in functions for its own configuration. Use these functions to create, modify, and remove filtering rules and assign them to user accounts.

Create rules to replace sensitive data in SQL statements written to the log.

Block events that match specific criteria.

Write information that you got with SQL query via Query Attributes to the audit log.

Observe the following information in the optional data fields:
- query execution time
- the number of bytes sent or received
- the number of rows returned to the client, and
- the number of examined rows

Note: This information can be printed on the audit log along with the audit event data.

Encrypt the audit log files by using AES-256 encryption.

Compress the audit log files to reduce the storage space occupied with log files.

Dynamically enable or disable the auditing. A server restart is not required to add or adjust existing filtering rules.

Install the plugin

To install Audit Log Filter, proceed with the following steps:

Locate the audit_log_filter_linux_install.sql script in the share directory of your Percona Server for MySQL installation.

Run the script as follows:

$ mysql -u root -p < audit_log_filter_linux_install.sql

1	$ mysql -u root -p < audit_log_filter_linux_install.sql

The script creates the audit_log_filter and audit_log_user tables in the MySQL database and installs the plugin. You will need these tables to configure the plugin later. They are empty right after installation, so the plugin cannot execute any tasks immediately.

Configure the plugin

The audit_log_filter plugin uses JSON-encoded rules to configure its own behavior. To start working with the plugin, we suggest you create a filtering rule for logging all auditable events. Assign it as default. Filtering rules may be manipulated using an SQL interface based on function calls.

SELECT audit_log_filter_set_filter('log_all', '{"filter": {"log": true}}');<br>SELECT audit_log_filter_set_user('%', 'log_all');

1	SELECT audit_log_filter_set_filter('log_all', '{"filter": {"log": true}}');<br>SELECT audit_log_filter_set_user('%', 'log_all');

The filter assigned to % is used for connections from any account that has no explicitly assigned filtering rule. Once the configuration above is done, the plugin starts logging all auditable events into the audit_filter.log log file located in the data directory by default.

We hope you’re excited to try the new plugin out! See the recommended documentation for more information about its capacity and usage examples.