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.

Audit log encryption

Audit logs of a MySQL-compatible tenant can be stored on disks and exported externally in plaintext or encrypted format. For more information about how to configure this feature, see the relevant documentation 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 controls whether to record events based on the event class. In addition, it also supports filtering by event type and event field, and logical combinations of AND and OR conditions for event fields.

Syntax

You can execute 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 parameters are described as follows:

The parameters are described as follows:

• Event type: You can use this parameter to further specify the subtypes of an event. In the JSON string, specify the related fields using the name parameter under event under class. For example, {"class": [{"name": "connection", "event": [{"name": "disconnect"}]}]}.
• Event field: You can use this parameter to further specify the subtypes of an event. You can set the field parameter to filter specific fields for the three types of events: connection, general, and table_access. In the JSON string, specify the related fields using the log parameter under event. For example, "event": [{"name": "read", "log": {"field": {"name": "table_name", "value": "t1"}}}].
• Logical combination: You can use AND and OR to combine multiple fields.

The following table describes the subtypes of each event type.

The following table describes the fields that can be filtered for each event type.

The connection, general, and table_access event types support multiple fields. The following table describes the fields in detail.

To view the complete list of SQL statement types that the general_sql_command parameter supports, see the relevant documentation at the end of this topic.

Hierarchies, differences, and default behaviors of the log field

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

Other notes:

• The hierarchical level column in the table is an abbreviation for path notation. Specifically:
  • filter.log refers to the log attribute under the filter object. For example, {"filter": {"log": true}}.
  • filter.class[].log refers to the log attribute of a single element in the class array under the filter object. For example, {"filter": {"class": [{"log": true}]}}.
  • filter.class[].event[].log refers to the log attribute of a single element in the event array under a single element in the class array under the filter object. For example, {"filter":{"class":[{"name":"table_access","event":[{"name":"read","log":true}]}]}}.
• The "outermost log field in a JSON object" and the "inner log field in a class/event object" are not duplicate configurations. The former is the global switch, and the latter is for local override or conditional filtering.

The following is a minimal JSON example of a filter. For more examples, see the reference documentation for the AUDIT_LOG_FILTER_SET_FILTER function in the relevant documentation at the end of this topic.

• Record all events: Set log to true (which is equivalent to recording all classes).

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

• Record events where the event type is connect (only the connection establishment operation is recorded under the connection type; the disconnect operation is not included):

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

Example

This section provides a complete example.

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 auditing filter in the mysql.audit_log_filter view. For the complete syntax and examples of the view, see the relevant documentation 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 fields are described as follows:

   Field	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

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

AUDIT_LOG_FILTER_SET_USER('user_name', 'filter_name');

The following table describes the parameters.

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 through the mysql.audit_log_user view. For the complete syntax and examples of this view, 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 about this parameter, see the related documentation at the end of this topic.

obclient> ALTER SYSTEM SET audit_log_enable=TRUE;

References

• Set audit log policies, including write, compression, rotation, cleanup, and encryption: Set audit rules
• Filter creation function: AUDIT_LOG_FILTER_SET_FILTER
• Filter configuration function: AUDIT_LOG_FILTER_SET_USER
• View filters: mysql.audit_log_filter
• User-filter mapping view: mysql.audit_log_user
• Audit enablement parameter: audit_log_enable
• List of supported general_sql_command statement types: Appendix: Complete list of SQL types supported by general_sql_command