Enable security auditing

A series of filters are used to audit specific events.

Auditing scope

Filters can be applied based on dimensions such as account, event type, event attributes, and more. For each filter, you can choose whether to audit the filtered events.

Limitations and considerations

• An expression must be placed directly and uniquely in an output column (select item) of a SELECT statement, and cannot have a parent expression.
• Expressions cannot be used in subqueries.
• After defining a filter, you must assign it to a user for it to take effect.
• A filter can be assigned to multiple users, but each user can have only one filter.
• When a connection is established, the current session determines which auditing filter to use, and this filter remains unchanged throughout the session lifecycle.

Create a filter

You can create a filter to enable the auditing mode for a MySQL-compatible tenant. The filter allows you to selectively filter auditing events. The following three options are available: record all events, do not record any events, and record only login and logout events.

Syntax

You can execute the AUDIT_LOG_FILTER_SET_FILTER function (expression) to create a filter. The syntax is as follows:

AUDIT_LOG_FILTER_SET_FILTER('filter_name', 'definition_of_filters');

The following table describes the parameters.

You can create filters of the following types:

• Record all events.

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

  or

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

• Do not record any events.

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

• Record only login and logout events.

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

The following table describes the auditing event types.

Example

Create a filter named log_all to record all events.

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

• When the DDL statement is executed successfully, the expression returns OK.

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

• When the DDL statement fails, the SELECT statement still succeeds, and the output result is 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
  

View the definition of the auditing filter in the mysql.audit_log_filter view.

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 fields are described as follows:

Configure filters

After you configure filters for the corresponding users, the background thread can print auditing logs.

Syntax

You can call the AUDIT_LOG_FILTER_SET_USER function (expression) to set a filter on a user.

AUDIT_LOG_FILTER_SET_USER('user_name', 'filter_name');

The following table describes the parameters.

Example

Assign the log_all filter to the user001 user.

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

• When the DDL statement is executed successfully, the expression returns OK.

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

• When the DDL statement fails, the SELECT statement still succeeds, and the output result of the expression is 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 view the mapping between auditing filters and users through the mysql.audit_log_user view.

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 query result.

Enable auditing

Enable the auditing feature for the MySQL-compatible tenant by setting the parameter audit_log_enable.

obclient> ALTER SYSTEM SET audit_log_enable=TRUE;