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');

Purpose

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 operation will overwrite it.

• definition_of_filters: specifies the configuration of the audit filter in JSON format. At the level of event type (Class), you can configure event type subtypes (Event Type), event fields (Event Field), and the AND / OR combinations between fields:

  • Event type subtype (Event Type): specifies the subtypes of the specified event type for further filtering. In the JSON string, specify the related field by using the name parameter under event in class, for example, {"class": [{"name": "connection", "event": [{"name": "disconnect"}]}]}.
  • Event field (Event Field) filtering: specifies the fields of the specified event type for further filtering. You can set the field parameter to filter specific fields (field) of the connection, general, and table_access event types. In the JSON string, specify the related field by using the log parameter under event, for example, "event": [{"name": "read", "log": {"field": {"name": "table_name", "value": "t1"}}}].
  • Logical combination: supports the AND / OR combinations between multiple fields.

Supported event type subtypes:

Supported event fields:

The connection, general, and table_access event types are supported. Each event type has multiple event fields. For more information, see the table below:

For more information about the SQL types supported by general_sql_command, see the related documentation at the end of this topic.

log field hierarchy, differences, and default behavior

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

Additional notes:

• The Level column in the table is a shorthand for the path notation, where:
  • filter.log refers to the log property under the filter object, for example, {"filter": {"log": true}}.
  • filter.class[].log refers to the log property 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 property of a single element in the event array of a single element in the class array under the filter object, for example, {"filter":{"class":[{"name":"table_access","event":[{"name":"read","log":true}]}]}}.
• The "outer log" in the JSON and the "local log" in the class/event levels are not redundant configurations: the former is the global switch, while the latter is a local override or conditional filter.

Common filters in JSON format

The class attribute can be extended to define more event types, event fields, and logical combinations. The following examples show several common methods of extending the class attribute:

• Track all events.

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

• Does not record all events.

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

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

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

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

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

  Alternatively, multiple items on the same level can be merged into an array (equivalent approach): If the same type of items appear multiple times with different values at the same level in the filter definition, these items can be merged into the array of a single object, preserving only one object:

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

Event type

• Retain only connect under connection (excluding disconnect).

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

• A single event entry in the event array can explicitly include log to indicate whether to record events that meet the conditions of that entry. For example, you can select multiple event types for the same table_access and specify whether to log events of each type.

  {
    "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 fields (event field) and logical grouping

• Records events of connect and disconnect for the connection objects and events of insert, delete, and update for the table_access objects.

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

• Logical operators and and or can be nested to construct complex conditions. The following filter applies only to events in the general and status event categories and records events that satisfy either of the two and groups: First group: general_command.str equals Query and general_command.length equals 5. Second group: 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 expression must be a string constant, and the output is of the string type.

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

Examples

• Create a filter named log_all that records 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
  

• When a DDL statement fails, the SELECT statement still executes successfully, and the output of the expression is an 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

• general_sql_command statement types: Appendix: Supported SQL types of the general_sql_command statement
• Enable security audit