AUDIT_LOG_FILTER_SET_FILTER

Purpose

This function (expression) is used to create a filter.

Syntax

AUDIT_LOG_FILTER_SET_FILTER('filter_name', 'definition_of_filters');

Considerations

Parameters

• filter_name: specifies the name of the filter.

  Note

  AUDIT_LOG_FILTER_SET_FILTER is a CREATE OR REPLACE statement. If an object exists, the DDL statement will overwrite it.

• definition_of_filters: specifies the configuration of the audit filter in JSON format. In addition to the event type (Class), you can configure the event type subdivision (Event Type), event field (Event Field), and the AND/OR combination of field conditions:

  • Event type subdivision (Event Type): used to further subdivide and filter a specified event type. In the JSON string, specify the relevant fields through the name parameter under event under class, for example, {"class": [{"name": "connection", "event": [{"name": "disconnect"}]}]}.
  • Event field (Event Field) filtering: used to further subdivide and filter a specified event type. You can set the field parameter to filter specific fields (field) for the three types of events: connection, general, and table_access. In the JSON string, specify the relevant fields through the log parameter under event, for example, "event": [{"name": "read", "log": {"field": {"name": "table_name", "value": "t1"}}}].
  • Logical combination: supports logical combination of multiple fields using AND/OR.

Subtypes supported for event type subdivision:

Fields supported for event field filtering:

The connection, general, and table_access event types are included. Each event type supports multiple event fields. For details, see the following table:

To view the complete SQL types supported by general_sql_command, refer to the related documentation at the end of this topic.

Hierarchies, differences, and default behaviors of the log field

Logging behavior depends on the value of log and whether the class or event item is specified.

Other notes:

• The Hierarchy column in the table is an abbreviation for path notation, where:
  • 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}]}]}}.
• In the documentation, "outermost log in JSON" and "inner log in class/event hierarchy" are not duplicate configurations: the former is the global switch, and the latter is local override or conditional filtering.

Common filter JSON syntaxes

Based on class, you can further expand to subdivide event types, event fields, and logical combinations as shown in the table above. The following are some common syntaxes:

• Record all events.

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

• Do not record all events.

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

• Record only login and logout events (only connection class).

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

• Explicitly list all classes (an equivalent way to write recording all events).

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

  You can also merge multiple items at the same level into an array (an equivalent writing method): At the same level in the filter definition, if an item type appears multiple times with only different values (for example, multiple class elements each have different names), you can merge these values into an array of the same item, retaining only one object:

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

Event type subdivision (Event Type)

• Under connection, only connect is retained (excluding disconnect).

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

• A single event entry in the event array can explicitly include log to specify whether to record events that meet the conditions of that entry. The following example selects multiple event types under the same table_access and specifies whether to write each type to the audit:

  {
    "filter": {
      "log": true,
      "class": [
        {
          "name": "table_access",
          "event": [
            { "name": "read", "log": false },
            { "name": "insert", "log": true },
            { "name": "delete", "log": true },
            { "name": "update", "log": true }
          ]
        }
      ]
    }
  }
  

Event field (Event Field) and logical combination

• Record connect and disconnect events for connection, and insert, delete, and update events for table_access.

  {
    "filter": {
      "log": true,
      "class": [
        {
          "name": "connection",
          "event": [
            { "name": "connect" },
            { "name": "disconnect" }
          ]
        },
        { "name": "general" },
        {
          "name": "table_access",
          "event": [
            { "name": "insert" },
            { "name": "delete" },
            { "name": "update" }
          ]
        }
      ]
    }
  }
  

• The logical operators and and or can be nested to construct complex conditions. The following filter, on general/status events, records only those that satisfy one of two and groups within the log condition: the first group is general_command.str equals Query and general_command.length equals 5; the second group is general_command.str equals Execute and general_command.length equals 7.

  {
    "filter": {
      "class": {
        "name": "general",
        "event": {
          "name": "status",
          "log": {
            "or": [
              {
                "and": [
                  { "field": { "name": "general_command.str",    "value": "Query" } },
                  { "field": { "name": "general_command.length", "value": 5 } }
                ]
              },
              {
                "and": [
                  { "field": { "name": "general_command.str",    "value": "Execute" } },
                  { "field": { "name": "general_command.length", "value": 7 } }
                ]
              }
            ]
          }
        }
      }
    }
  }
  

Return value

The input must be a string constant, and the output is a string.

• If the DDL statement is executed successfully, the expression returns OK.
• If the DDL statement fails, the SELECT statement still executes successfully, and the output of the expression is the error message.

Examples

• Create a filter log_all to record all events.

  SELECT AUDIT_LOG_FILTER_SET_FILTER('log_all', '{ "filter": { "log": true } }');
  

  The return result is as follows:

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

• If the DDL statement fails, the SELECT statement still executes successfully, and the output of the expression is the error message.

  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
  

References

• List of supported general_sql_command statement types: Appendix: Complete list of SQL statements supported by general_sql_command
• Enable security auditing