Why Do We Need Audit Logs?

Database auditing is a core practice for monitoring and securing your database. It helps maintain availability, security, and compliance by recording activities performed within the system. Good audit logs allow database administrators and security teams to:

• Track the activities of users.
• Detect malicious or accidental changes (e.g., dropped tables, privilege escalations).
• Provide a trail for debugging issues.

In MySQL versions 8.0 and earlier, this was handled by the audit_log_plugin. Starting with MySQL 8.4 (LTS), this legacy plugin is deprecated and replaced by a more powerful and flexible tool: the Audit Log Filter.

Audit Plugin vs. The New Audit Log Filter

The new Audit Log Filter is a significant upgrade. It allows you to create a set of rules that decides precisely which events are written to the audit log. Without filters, everything is logged, which can lead to huge log files and heavy performance overhead. With filters, you can choose to log only specific activities, like logins, DML queries, or actions from certain users.

Here’s a direct comparison:

How to Install the Audit Log Filter

Step 1: Install the Component

First, install the component in your MySQL database.

mysql> INSTALL COMPONENT 'file://component_audit_log_filter';Query OK, 0 rows affected (0.09 sec)

Step 2: Verify the Installation

Next, verify the audit log component is installed by checking the global variables.

Understanding Key Configuration Variables

Audit_log_filter.strategy

Audit_log_filter.strategy variable controls how the audit log plugin writes events to the log file.

ASYNCHRONOUS (default)

• Events are first stored in a memory buffer before being flushed to the log file
  
• Gives faster performance because writing to disk happens in batches.
• if the server crashes before a flush, some recent events might be lost.
  

SYNCHRONOUS

• Each event is written directly to the log file before returning control.
  
• Safer as no events are lost even if MySQL crashes.
  
• Slower because every logged action must wait for the disk write to complete.

Other Important Variables

• ‍audit_log_filter.buffer_size: Defines the memory buffer size used before flushing events to the audit log file
• ‍audit_log_filter.compression: Audit logs will consumes the space to reduce the space compression method will be used for the log file
• ‍audit_log_filter.database: Defines the schema where audit log filter rules are stored.(default mysql).
• ‍audit_log_filter.disable: we can turn off the audit logging in the DB  (ON/OFF).
• ‍audit_log_filter.encryption:  Defines the encryption method for audit log files and helps secure sensitive log data, especially in regulated environments
• ‍audit_log_filter.file: Specifies the full path and name of the audit log file.
• ‍audit_log_filter.format: Log file format. Options: OLD (XML-like), NEW (JSON-like).‍
• audit_log_filter.rotate_on_size: Controls log rotation based on file size

To make these settings permanent across restarts, add them to your my.cnf file:

[mysqld]
audit_log_filter.buffer_size=1048576
audit_log_filter.disable=OFF
audit_log_filter.file=audit_filter.log
audit_log_filter.format=NEW
audit_log_filter.database=mysql
audit_log_filter.handler=FILE
audit_log_filter.strategy=ASYNCHRONOUS

‍

Setting Up Filter Storage

After installing the component, you must run an SQL script that creates the system tables needed to store your filter definitions. The component provides the filtering engine, while this script provides the storage for your rules. 

Why are both needed

• Component: provides the filtering functionality.
  
• Install script: provides the storage (tables) and schema objects to hold your filter definitions.

root@ip-172-31-45-230:~# mysql</usr/share/mysql/audit_log_filter_linux_install.sql
root@ip-172-31-45-230:~#

‍

Creating and Assigning Filters

Filters are defined using JSON and managed with built-in functions.

{
  "filter": {
    "rule": {
      "event_class": "query",
      "command_class": "select",
      "log": true
    }
  }
}

• ‍event_class: The category of event (query, connection, table_access, etc.)
• ‍command_class: The SQL command type (select, update, insert, create, etc.).
• ‍log: true to log the event, false to ignore it.

By using the functions below we can create the audit log filter and we can assign the filters to the users 

Filters are created using the function

SELECT audit_log_filter_set_filter(<filter_name>, <json_definition>);

and assigned to users with

SELECT audit_log_filter_set_user(<user>, <filter_name>);

By using the methods below, we can create the filters 

‍

Method 1: Rule-Based Filtering

This method is flexible and allows you to define specific conditions and rules (e.g., exclude SELECT, log only failed logins, log DML only for particular schema, table, user).

‍

Example 1: Filter to log only DDL queries

This filter logs only CREATE, ALTER, and DROP commands and applies it to all users (%).

SELECT audit_log_filter_set_filter(
  'log_ddl',
  '{ "filter": { "rule": { "event_class": "query", "command_class": ["create","alter","drop"], "log": true } } }'
);

SELECT audit_log_filter_set_user('%', 'log_ddl');

Example 2: To exclude SELECT statements

This uses a ruleset: the first rule prevents SELECT queries from being logged, and the second rule logs everything else.

{
  "filter": {
    "rules": [
      { "rule": { "log": false, "event_class": "query", "command_class": "select" } },
      { "rule": { "log": true } }
    ]
  }
}

Example 3: Log only failed logins

This filter logs only failed connection attempts, which is useful for security monitoring.

SELECT audit_log_filter_set_filter(
  'log_failed_logins',
  '{
     "filter": {
       "rule": {
         "event_class": "connection",
         "connection_type": "failed_login",
         "log": true
       }
     }
   }'
);

SELECT audit_log_filter_set_user('%', 'log_failed_logins');

‍

Method 2: Class-Based Filtering

‍

Example 1: Log Multiple Event Classes

Here, we create a filter to log a wide range of activities by specifying multiple classes like connection, table_access, ddl, and query.

SET @fj = '{
  "filter": {
    "class": [
      {
        "name": "connection",
        "event": [
          { "name": "connect" },
          { "name": "disconnect" }
        ]
      },
      {
        "name": "table_access",
        "operation": [
          { "name": "read" },
          { "name": "write" }
        ]
      },
      {
        "name": "dcl"
      },
      {
        "name": "ddl"
      },
      {
        "name": "query"
      }
    ]
  }
}';

SELECT audit_log_filter_set_filter('log_queries', @fj);

Example 2: Log DML for a Particular Database

You can also scope a class-based filter to a specific schema. This example logs all INSERT, UPDATE, and DELETE operations, but only for the test database.

SET @dml_schema = '{
  "filter": {
    "class": [
      {
        "name": "table_access",
        "schema": "test",
        "operation": [
          { "name": "insert" },
          { "name": "update" },
          { "name": "delete" }
        ]
      }
    ]
  }
}';

SELECT audit_log_filter_set_filter('log_dml_schema', @dml_schema);

‍

How to Manage Your Filters

Verifying and Removing Filters

To see all created filters, query the audit_log_filter table:

mysql> select * from mysql.audit_log_filter\G
*************************** 1. row ***************************
filter_id: 1
     name: log_all
   filter: {"filter": {"log": true}}
*************************** 2. row ***************************
filter_id: 2
     name: log_queries
   filter: {"filter": {"class": [{"name": "connection", "event": [{"name": "connect"}, {"name": "disconnect"}]}, {"name": "table_access", "operation": [{"name": "read"}, {"name": "write"}]}, {"name": "dcl"}, {"name": "ddl"}, {"name": "query"}]}}
*************************** 3. row ***************************
filter_id: 3
     name: exclude_failed_login
   filter: {"filter": {"rules": [{"rule": {"log": false, "event_class": "connection", "connection_type": "failed_login"}}, {"rule": {"log": true}}]}}
*************************** 4. row ***************************
filter_id: 4
     name: exclude_selects
   filter: {"filter": {"rules": [{"rule": {"log": false, "event_class": "query", "command_class": "select"}}, {"rule": {"log": true}}]}}
4 rows in set (0.00 sec)

To remove an existing filter, use the audit_log_filter_remove_filter() function:

mysql> SELECT audit_log_filter_remove_filter('log_queries');
+-----------------------------------------------+
| audit_log_filter_remove_filter('log_queries') |
+-----------------------------------------------+
| OK                                            |
+-----------------------------------------------+
1 row in set (0.02 sec)

Checking User Filter Assignments

To see which filters are assigned to which users, query the audit_log_user table:

mysql> SELECT * FROM mysql.audit_log_user;
+----------+----------+----------------+
| username | userhost | filtername     |
+----------+----------+----------------+
| %        | %        | log_all        |
| user2    | %        | log_dml_schema |
+----------+----------+----------------+
2 rows in set (0.00 sec)

‍‍

Audit log functions:

For visual learners or those who want to see these concepts in action, our team hosted a detailed webinar on mastering the new Audit Log Filter in MySQL 8.4.

For a permanent reference, you can also download the complete presentation here.

The Audit Log Filter in MySQL 8.4 provides a flexible and performance-optimized approach to database auditing. With JSON-based filters, you can easily capture security-relevant activities, reduce noise by excluding non-essential queries, and meet your specific compliance requirements.

Properly configuring database auditing is a critical step in securing your data. If you need expert assistance in hardening your MySQL environment, optimizing performance, or managing your database infrastructure, the team at Mydbops is here to help.