A series of filters are used to audit specific events.
Auditing scope
Filters can be applied based on dimensions such as account, event type, and event attributes. 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
SELECTstatement, 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. The filter supports selective filtering of audit event types. Currently, you can choose to record all events, record no events, or record only logins and logouts.
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:
Parameter |
Description |
|---|---|
| filter_name | The name of the filter.
Note
|
| definition_of_filters | The specific configurations of the auditing filter, which is in the JSON string format. The filter design principle of the current version is compatible with that of MySQL, but only audit event types can be filtered. |
Currently, the following filter configurations are supported:
Record all events.
{ "filter": { "log": true } }or
{ "filter": { "log": true, "class": [ { "name": "connection" }, { "name": "general" }, { "name": "table_access" } ] } }Record no events.
{ "filter": { "log": false } }Record only logins and logouts.
{ "filter": { "log": true, "class": [ { "name": "connection" } ] } }
Audit events are classified as follows:
Event type |
Description |
|---|---|
| connection | Login and logout. |
| table_access | DML statements. |
| general | CMD statements that fail to be parsed. |
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 a DDL statement is executed successfully, the expression returns
OK.+-------------------------------------------------------------------------+ | AUDIT_LOG_FILTER_SET_FILTER('log_all', '{ "filter": { "log": true } }') | +-------------------------------------------------------------------------+ | OK | +-------------------------------------------------------------------------+ 1 row in setWhen the DDL statement fails, the
SELECTstatement still succeeds, and the output result of the expression 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 an audit filter through 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:
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 audit 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 parameters are described as follows:
Parameter |
Description |
|---|---|
| user_name | Specifies the user name.
NoteThe
|
| filter_name | Specifies the filter name.
Note
|
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 setWhen the DDL statement fails, the
SELECTstatement 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)
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 fields are described as follows:
Field |
Description |
|---|---|
| USER | The username. |
| HOST | The hostname. |
| FILTERNAME | The filter name. |
Enable auditing
Enable the auditing feature for a user tenant by configuring the audit_log_enable configuration item.
obclient> ALTER SYSTEM SET audit_log_enable=TRUE;