Percona Audit Log Filter

Overview

Tessell currently provides the Audit Log feature for MySQL 8.0 Community Edition using the Percona MySQL 8.0 Audit Log Plugin. However, starting with MySQL 8.4, the legacy audit log plugin is deprecated and replaced by a more powerful and flexible tool called the Audit Log Filter component.

From MySQL 8.4 onwards, Tessell introduced the Audit Log Filter component based on the open-source Percona Audit Log Filter. This is implemented as a MySQL component instead of a plugin.

The audit_log_filter component allows administrators to selectively monitor, log, and even block database activities at a fine-grained level, significantly reducing log volume and performance overhead compared to logging all events.

This component produces a log file containing records of server activity.

Key Characteristics

Rule-Based Filtering

Audit Log Filtering is based on rules defined in JSON format. The filter rule definition has the ability to include or exclude events based on event class, event type, user, or host.

Audit Log Filter Tables

Filter definitions and user assignments are stored in system tables within the mysql database:

• mysql.audit_log_filter

• mysql.audit_log_user

Management Interface

Filters are managed using an SQL interface via built-in functions.

Privilege Requirement

The AUDIT_ADMIN privilege is required to manage audit filters and their configuration.

---

Key Functions for Managing Filters

Administrators can use the following SQL functions to configure the audit log filter component:

Function	Description
audit_log_filter_set_filter(filter_name, definition)	Defines or updates a named filter using a JSON definition
audit_log_filter_remove_filter(filter_name)	Removes a filter definition and unassigns it from users
audit_log_filter_set_user(user_name, filter_name)	Assigns a defined filter to a user account or the default account %
audit_log_filter_remove_user(user_name)	Removes the filter assignment from a user account
audit_log_filter_flush()	Reloads filter configurations from system tables after manual table modifications

⚠️ Warning: audit_log_filter_flush() should only be used if the tables were directly modified using INSERT, UPDATE, or DELETE.

---

Audit Log Filter Settings

Tessell supports the following parameters for the Percona Audit Log Filter component.

Parameter	Default Value	Valid Values	Description
audit_log_filter.strategy	ASYNCHRONOUS	ASYNCHRONOUS, PERFORMANCE, SEMISYNCHRONOUS, SYNCHRONOUS	Defines logging method. Static parameter
audit_log_filter.rotate_on_size	10485760	0-18446744073709551615	Log rotates when reaching this size. Dynamic Parameter
audit_log_filter.read_buffer_size	32768	4096-18446744073709547520	Buffer size for reading JSON audit log file. Dynamic Parameter
audit_log_filter.max_size	104857600	0-18446744073709551615	Maximum combined size of audit log files before pruning. Dynamic Parameter
audit_log_filter.format_unix_timestamp	OFF	ON, OFF	Adds Unix timestamp to JSON logs. Dynamic Parameter
audit_log_filter.format	JSON	OLD, NEW, JSON	Defines audit log file format. Static Parameter
audit_log_filter.disable	OFF	ON, OFF	Enables or disables audit logging. Dynamic Parameter
audit_log_filter.buffer_size	1048576	4096-18446744073709547520	Buffer size for asynchronous logging. Static Parameter

For detailed parameter descriptions, refer to the Percona Audit Log Filter Variables documentation.

---

MySQL Audit Log Filter Support Matrix

Tessell-managed MySQL Database	Supported via Option Profile
MySQL Community Edition 8.4	✅ Yes
MySQL Community Edition 8.0	❌ No
MySQL Community Edition 5.7	❌ No

---

Enabling Audit Log Filter

You can enable the Percona Audit Log Filter on a MySQL DB instance using an Option Profile.

When an Option Profile is attached to a DB service:

• Tessell installs the Audit Log Filter component

• The database instance is automatically rebooted

⚠️ Warning: It is recommended to perform this operation during a maintenance window or low traffic period.

During installation, two tables are created in the mysql system database:

• audit_log_filter

• audit_log_user

These tables store:

• Audit filter definitions

• User-to-filter mappings

Together they are referred to as audit log filter tables.

When applied to a DB cluster, the option profile is propagated to all nodes:

• Primary

• HA

• Read Replicas

• DR

---

Steps to Enable Audit Log Filter

1. Sign in to the Tessell Console.

2. From the left navigation pane, go to Governance and open the DB Governance App.

3. Go to the Options tab from the menu.

4. Click Create to create a new Option Profile.

5. In the Source details Section

   • DB Engine: MySQL

   • Version: 8.4

   • Profile Name: Provide a unique name

   • Description: Optional

6. In the Option Section

   • Click Option Settings and it opens a pop up window to configure audit parameter settings.

   • Configure the audit parameters.

   • Click Submit to save the changes.

7. Click Create to create the Option Profile with Audit Log enabled.

8. Once created, Open My Services App from DB Services in the left navigation pane.

9. Select your DB service.

10. Click ︙ in right side of the pane and select Change Option Profile from the menu.

11. Select your newly created Option Profile.

12. Check the consent checkbox to apply changes immediately.

13. Click Apply.

Tessell automatically reboots the db instance when you attach the option profile. Once option profile is associated with the DB instance, you should see status as “In-sync” for Option profile in the Instance tab of your DB service.

Creating Audit Log Filters

After installing the component, users must define audit log filters.

Filters determine:

• Which events should be logged

• Which events should be ignored

Benefits include:

• Logging only security or compliance-related activities

• Reducing unnecessary log noise

• Applying granular auditing for specific users or hosts

This provides both flexibility and efficiency while minimizing performance overhead.

Examples

Log All Events for All Users

SELECT audit_log_filter_set_filter(
'log_all_events',
'{ "filter": {"log": true } }'
);

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

Log Only Connection Events

SELECT audit_log_filter_set_filter(
'log_connects',
'{
  "filter": {
    "log": false,
    "class": [
      {
        "name": "connection",
        "event": [
          { "name": "connect", "log": true },
          { "name": "disconnect", "log": true }
        ]
      }
    ]
  }
}');

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

Log All Queries For All Users

SELECT audit_log_filter_set_filter(
'log_queries',
'{
  "filter": {
    "class": [
      {
        "name": "connection",
        "log": false
      },
      {
        "name": "general",
        "event": {
          "name": [
            "log"
          ]
        }
      }
    ]
  }
}');

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

Log All Queries For Specific Users

(Log all queries for users such as app_user1, app_user2)

SELECT audit_log_filter_set_filter(
'log_queries',
'{
  "filter": {
    "class": [
      {
        "name": "connection",
        "log": false
      },
      {
        "name": "general",
        "event": {
          "name": [
            "log"
          ]
        }
      }
    ]
  }
}');

SELECT audit_log_filter_set_user('app_user1@%', 'log_queries');
SELECT audit_log_filter_set_user('app_user2@%', 'log_queries');

Log DML & Select operations For All Users

SELECT audit_log_filter_set_filter(
'log_dml_selects',
'{
  "filter": {
    "class": {
      "name": "table_access",
      "event": {
        "name": [ "insert", "update", "delete", "read"],
        "log": true
      }
    }
  }
}');


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

Log Only Selects For All Users

SELECT audit_log_filter_set_filter(
'log_selects',
'{
  "filter": {
    "class": {
      "name": "table_access",
      "event": {
        "name": "read",
        "log": true
      }
    }
  }
}');


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

Log DDL For All Users

SELECT audit_log_filter_set_filter(
'log_ddl',
'{
  "filter": {
    "class": [
      {
        "name": "connection",
        "log": false
      },

      {
        "name": "query",
        "event": [
          {
            "name": "start",
            "log": {
              "or": [
                { "field": { "name": "sql_command_id", "value": "truncate"} },
                { "field": { "name": "sql_command_id", "value": "create_table"} },
                { "field": { "name": "sql_command_id", "value": "alter_table"} },
                { "field": { "name": "sql_command_id", "value": "drop_table"} }
              ]
            }
          }
        ]
      }
    ]
  }
}');

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

Note: A full list of SQL_COMMANDS can be obtained from the below query.

SELECT NAME FROM performance_schema.setup_instruments WHERE NAME LIKE 'statement/sql/%' ORDER BY NAME;

Verifying the Filters

To view all configured audit log filters, query the audit log filter tables.

SELECT * FROM mysql.audit_log_filter;
SELECT * FROM mysql.audit_log_user;

Removing Filters

To remove an existing audit log filter, use the audit_log_filter_remove_filter() function. This function deletes the specified filter definition from the audit log filter tables.

SELECT audit_log_filter_remove_filter('filter_name');

Modifying Audit Log Filter Settings

After you enable the Percona Audit Log Filter, you can modify the option settings. You can only modify parameter values in a custom-created option profile; you can't change the parameter values in a default option profile.

When you modify dynamic parameters, changes are applied to DB instances immediately without a reboot. Static parameters require a database restart, which Tessell automatically performs reboot of the DB instance.

Steps

1. Sign in to Tessell Console.

2. Navigate to Governance → DB Governance App.

3. Open the Options tab.

4. Select your option profile.

5. Click Option Settings → Percona Audit Log Filter.

6. Modify parameters.

7. Click Submit and Save.

Disabling Audit Log Filter

To disable the audit log filter for all connections, Set the below parameter using Option Profile.

audit_log_filter.disable = ON

Steps

1. Sign in to Tessell Console.

2. Navigate to Governance → DB Governance App.

3. Go to the Options tab.

4. Select the option profile.

5. Modify audit_log_filter.disable to ON.

6. Click Submit and Save to begin the modification of the DB instance associated with the option profile.

Viewing and Downloading Audit Logs

Audit logs can be viewed per node with time-based filtering.

Steps

1. Sign in to Tessell Console.

2. Navigate to DB Services → My Services App.

3. Select your MySQL DB service.

4. Go to the Logs tab.

5. Select mysql_audit_log.

6. Use filters to view logs.

7. To download the logs, Click the Download icon.

8. .zip file is generated.

9. Save it to your local system.

Limitations

• Replica nodes do not generate audit logs even if the audit log filter configuration is present. This is a known issue in Percona Audit Log Filter. Until Percona releases a fix, users can open a support ticket with Tessell to enable audit logging on replica nodes (HA / Read Replica / DR).

• Tessell currently does not support uninstalling the Percona Audit Log Filter component directly from the Tessell UI. If you need to remove the component from a database service, please contact Tessell Support for assistance.