Enable security audit

This topic describes how to audit specific events by using a series of filters.

Audit scope

A filter can filter events by account, event type, and event attribute, and determine whether to audit the events that meet the filter conditions.

Limitations and considerations

• An expression must be placed directly and uniquely in an output column of a SELECT statement without being limited by a parent expression. The output column is an item specified in the SELECT statement.
• You cannot write expressions in subqueries.
• After you define a filter, you must configure it for a user to make it take effect.
• One filter corresponds to multiple users. You can configure one filter for multiple users, but one user can have only one filter.
• You must specify an audit filter for a session when you create it. The audit filter applies to the session throughout its lifecycle.

Create filters

You can create a filter to enable the audit mode for a MySQL tenant. The filter can filter audit events by event type. At present, you can create three types of filters to specify to record all events, record none of the events, and record only logins and logouts.

Syntax

You can use the AUDIT_LOG_FILTER_SET_FILTER function to create a filter. The syntax is as follows:

AUDIT_LOG_FILTER_SET_FILTER('filter_name', 'definition_of_filters');

The following table describes the fields in the syntax.

At present, you can create three types of filters:

• A filter to specify to record all events

  {
  "filter": {
      "log": true
  }
  }
  

  or

  {
  "filter": {
      "log": true,
      "class": [
      { "name": "connection" },
      { "name": "general" },
      { "name": "table_access" }
      ]
  }
  }
  

• A filter to specify to record none of the events

  {
  "filter": {
      "log": false
  }
  }
  

• A filter to specify to record only logins and logouts

   {
  "filter": {
      "log": true,
      "class": [
      { "name": "connection" }
      ]
  }
  }
  

The following table describes the types of audit events.

Examples

Create a filter named log_all to specify to record all events.

obclient [test]>SELECT AUDIT_LOG_FILTER_SET_FILTER('log_all', '{ "filter": { "log": true } }');

• If the DDL operation is successful, the expression returns OK.

  +-------------------------------------------------------------------------+
  | AUDIT_LOG_FILTER_SET_FILTER('log_all', '{ "filter": { "log": true } }') |
  +-------------------------------------------------------------------------+
  | OK                                                                      |
  +-------------------------------------------------------------------------+
  1 row in set
  

• If the DDL operation fails, the SELECT statement is still successfully executed, and the expression outputs an error message.

  obclient [test]>SELECT AUDIT_LOG_FILTER_SET_FILTER('log_err', '1');
  

  The return result is as follows:

  +---------------------------------------------+
  | AUDIT_LOG_FILTER_SET_FILTER('log_err', '1') |
  +---------------------------------------------+
  | ERROR: JSON parsing error.                  |
  +---------------------------------------------+
  1 row in set
  

You can query the mysql.audit_log_filter view for the definitions of audit filters.

obclient [test]> select * from mysql.audit_log_filter;

The return result is as follows:

+---------+-------------------------------+
| NAME    | FILTER                        |
+---------+-------------------------------+
| log_all | { "filter": { "log": true } } |
+---------+-------------------------------+
1 row in set (0.003 sec)

The following table describes the fields in the syntax.

Configure filters

You must configure a filter for a user before the background thread can generate audit logs.

Syntax

You can use the AUDIT_LOG_FILTER_SET_USER function to configure a filter for a user.

AUDIT_LOG_FILTER_SET_USER('user_name', 'filter_name');

The following table describes the fields in the syntax.

Examples

Configure the log_all filter for the user001 user.

obclient [test]> SELECT AUDIT_LOG_FILTER_SET_USER('user001', 'log_all');

• If the DDL operation is successful, the expression returns OK.

  +-------------------------------------------------+
  | AUDIT_LOG_FILTER_SET_USER('user001', 'log_all') |
  +-------------------------------------------------+
  | OK                                              |
  +-------------------------------------------------+
  1 row in set
  

• If the DDL operation fails, the SELECT statement is still successfully executed, and the expression outputs an error message.

  obclient [test]>SELECT AUDIT_LOG_FILTER_SET_USER('log_err', '1');
  

  The return result is as follows:

  +--------------------------------------------+
  | AUDIT_LOG_FILTER_SET_USER('log_err', '1')  |
  +--------------------------------------------+
  | ERROR: Invalid character in the user name. |
  +--------------------------------------------+
  1 row in set (0.001 sec)
  

You can query the mysql.audit_log_user view for the mappings between audit filters and users.

obclient [test]> select * from mysql.audit_log_user;

The return result is as follows:

+---------+------+------------+
| USER    | HOST | FILTERNAME |
+---------+------+------------+
| user001 | %    | log_all    |
+---------+------+------------+
1 row in set (0.003 sec)

The following table describes the fields in the syntax.

Enable security audit

You can use the audit_log_enable parameter to specify whether to enable security audit in a MySQL tenant.

obclient> ALTER SYSTEM SET audit_log_enable=TRUE;