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.

Encrypt audit logs

MySQL-compatible tenant audit logs support recording data in plaintext or encrypted format. For details, see the related topics at the end of this topic.

Create a filter

You can create a filter to enable the auditing mode for a MySQL-compatible tenant. The filter supports filtering based on event types (Class) and further filtering by event types (Event Type) or event fields (Event Field) in the specified event types. It also supports combining multiple event fields with AND or OR logic.

Syntax

Create a filter by using the AUDIT_LOG_FILTER_SET_FILTER function. The syntax is as follows:

AUDIT_LOG_FILTER_SET_FILTER('filter_name', 'definition_of_filters');

The following table describes the parameters.

The following table describes the parameters of the filtering configurations.

• Event type filtering: You can specify the event types to filter further by using the event parameter in the class parameter in the JSON string. For example, you can specify {"class": [{"name": "connection", "event": [{"name": "disconnect"}]}]} to specify the disconnect sub-type in the connection event type.
• Event field filtering: You can specify the fields to filter by using the field parameter in the event parameter in the JSON string. For example, you can specify "event": [{"name": "read", "log": {"field": {"name": "table_name", "value": "t1"}}}] to specify the table_name field of the read sub-type in the connection event type.
• Logical combination: You can use AND or OR to combine multiple fields.

The supported sub-types of the event type filtering are as follows:

The supported fields for event field filtering are as follows:

The following table describes the supported fields for the connection, general, and table_access event types.

For more information about the supported SQL types for the general_sql_command field, see the related topics at the end of this topic.

Hierarchical structure, differences, and default behavior of the log field

The log record behavior depends on the value of the log field and whether a class or event item is specified.

Other notes:

• The column of Level in the preceding table is an abbreviation for the hierarchical structure. The parameters in the hierarchical structure are as follows:
  • filter.log indicates the log attribute under the filter object, for example, {"filter": {"log": true}}.
  • filter.class[].log indicates the log attribute in a single element of the class array under the filter object, for example, {"filter": {"class": [{"log": true}]}}.
  • filter.class[].event[].log indicates the log attribute in a single element of the event array under a single element of the class array under the filter object, for example, {"filter":{"class":[{"name":"table_access","event":[{"name":"read","log":true}]}]}}.
• The following statement in the documentation is not repeated configuration: the configuration of the outer log field is the total switch, and the configuration of the log field of the class or event level is the override or condition filter.

Here are some simple JSON filter examples. For more filter examples, see the topic about the AUDIT_LOG_FILTER_SET_FILTER function in the related topics at the end of this topic.

• The log field is set to true to record all events (equivalent to recording all classes):

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

• Specify the event parameter to filter for connect events (the connection type is specified only for establishing a connection, not for disconnect events):

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

Example

In this example, we create a filter named log_all to record all events.

1. 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
     

2. View the definition of the filter in the mysql.audit_log_filter view. For the full syntax and examples, see the related topics at the end of this topic.

   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 columns.

   Column Name	Description
   NAME	The name of the filter.
   FILTER	The definition of the filter.

Configure filters

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

Syntax

Set a filter on a user by using the AUDIT_LOG_FILTER_SET_USER function.

AUDIT_LOG_FILTER_SET_USER('user_name', 'filter_name');

The following table describes the fields.

Example

1. 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)
     

2. View the mapping between auditing filters and users in the mysql.audit_log_user view. For more information, see the related documentation at the end of this topic.

   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.

   Field	Description
   USER	The username.
   HOST	The hostname.
   FILTERNAME	The filter name.

Enable auditing

Enable the auditing feature for the MySQL-compatible tenant by setting the parameter audit_log_enable. For more information, see the related documentation at the end of this topic.

obclient> ALTER SYSTEM SET audit_log_enable=TRUE;

References

• Set an auditing log policy, including writing, compression, rotation, cleanup, and encryption: Set an auditing rule
• Filter creation function: AUDIT_LOG_FILTER_SET_FILTER
• Filter configuration function: AUDIT_LOG_FILTER_SET_USER
• Filter view: mysql.audit_log_filter
• Filter user mapping view: mysql.audit_log_user
• Enable auditing parameter: audit_log_enable
• List of general_sql_command statement types: Appendix: Complete list of SQL types supported by general_sql_command