Movatterモバイル変換

• Logging All Events

• Logging Specific Event Classes

• Logging Specific Event Subclasses

• Inclusive and Exclusive Logging

• Testing Event Field Values

• Blocking Execution of Specific Events

• Logical Operators

• Referencing Predefined Variables

• Referencing Predefined Functions

• Replacement of Event Field Values

• Replacing a User Filter

To explicitly enable or disable logging of all events, use alog item in the filter:

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

Thelog value can be eithertrue orfalse.

The preceding filter enables logging of all events. It is equivalent to:

{  "filter": { }}

Logging behavior depends on thelog value and whetherclass orevent items are specified:

To log events of a specific class, use aclass item in the filter, with itsname field denoting the name of the class to log:

{  "filter": {    "class": { "name": "connection" }  }}

Thename value can beconnection,general, ortable_access to log connection, general, or table-access events, respectively.

The preceding filter enables logging of events in theconnection class. It is equivalent to the following filter withlog items made explicit:

{  "filter": {    "log": false,    "class": { "log": true,               "name": "connection" }  }}

To enable logging of multiple classes, define theclass value as aJSON array element that names the classes:

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

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

To select specific event subclasses, use anevent item containing aname item that names the subclasses. The default action for events selected by anevent item is to log them. For example, this filter enables logging for the named event subclasses:

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

Theevent item can also contain explicitlog items to indicate whether to log qualifying events. Thisevent item selects multiple events and explicitly indicates logging behavior for them:

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

Theevent item can also indicate whether to block qualifying events, if it contains anabort item. For details, seeBlocking Execution of Specific Events.

Table 8.35, “Event Class and Subclass Combinations” describes the permitted subclass values for each event class.

Table 8.36, “Log and Abort Characteristics Per Event Class and Subclass Combination” describes for each event subclass whether it can be logged or aborted.

A filter can be defined in inclusive or exclusive mode:

To perform inclusive logging, disable logging globally and enable logging for specific classes. This filter logsconnect anddisconnect events in theconnection class, and events in thegeneral class:

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

To perform exclusive logging, enable logging globally and disable logging for specific classes. This filter logs everything except events in thegeneral class:

{  "filter": {    "log": true,    "class":      { "name": "general", "log": false }  }}

This filter logschange_user events in theconnection class,message events, andtable_access events, by virtue ofnot logging everything else:

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

To enable logging based on specific event field values, specify afield item within thelog item that indicates the field name and its expected value:

{  "filter": {    "class": {    "name": "general",      "event": {        "name": "status",        "log": {          "field": { "name": "general_command.str", "value": "Query" }        }      }    }  }}

Each event contains event class-specific fields that can be accessed from within a filter to perform custom filtering.

An event in theconnection class indicates when a connection-related activity occurs during a session, such as a user connecting to or disconnecting from the server.Table 8.37, “Connection Event Fields” indicates the permitted fields forconnection events.

The"::xxx" values are symbolic pseudo-constants that may be given instead of the literal numeric values. They must be quoted as strings and are case-sensitive.

An event in thegeneral class indicates the status code of an operation and its details.Table 8.38, “General Event Fields” indicates the permitted fields forgeneral events.

general_command.str indicates a command name:Query,Execute,Quit, orChange user.

Ageneral event with thegeneral_command.str field set toQuery orExecute containsgeneral_sql_command.str set to a value that specifies the type of SQL command:alter_db,alter_db_upgrade,admin_commands, and so forth. The availablegeneral_sql_command.str values can be seen as the last components of the Performance Schema instruments displayed by this statement:

mysql> SELECT NAME FROM performance_schema.setup_instruments       WHERE NAME LIKE 'statement/sql/%' ORDER BY NAME;+---------------------------------------+| NAME                                  |+---------------------------------------+| statement/sql/alter_db                || statement/sql/alter_db_upgrade        || statement/sql/alter_event             || statement/sql/alter_function          || statement/sql/alter_instance          || statement/sql/alter_procedure         || statement/sql/alter_server            |...

An event in thetable_access class provides information about a specific type of access to a table.Table 8.39, “Table-Access Event Fields” indicates the permitted fields fortable_access events.

The following list shows which statements produce which table-access events:

event items can include anabort item that indicates whether to prevent qualifying events from executing.abort enables rules to be written that block execution of specific SQL statements.

Theabort item must appear within anevent item. For example:

"event": {  "name":qualifying event subclass names  "abort":condition}

For event subclasses selected by thename item, theabort action is true or false, depending oncondition evaluation. If the condition evaluates to true, the event is blocked. Otherwise, the event continues executing.

Thecondition specification can be as simple astrue orfalse, or it can be more complex such that evaluation depends on event characteristics.

This filter blocksINSERT,UPDATE, andDELETE statements:

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

This more complex filter blocks the same statements, but only for a specific table (finances.bank_account):

{  "filter": {    "class": {      "name": "table_access",      "event": {        "name": [ "insert", "update", "delete" ],        "abort": {          "and": [            { "field": { "name": "table_database.str", "value": "finances" } },            { "field": { "name": "table_name.str", "value": "bank_account" } }          ]        }      }    }  }}

Statements matched and blocked by the filter return an error to the client:

ERROR 1045 (28000): Statement was aborted by an audit log filter

Not all events can be blocked (seeTable 8.36, “Log and Abort Characteristics Per Event Class and Subclass Combination”). For an event that cannot be blocked, the audit log writes a warning to the error log rather than blocking it.

For attempts to define a filter in which theabort item appears elsewhere than in anevent item, an error occurs.

Logical operators (and,or,not) permit construction of complex conditions, enabling more advanced filtering configurations to be written. The followinglog item logs onlygeneral events withgeneral_command fields having a specific value and length:

{  "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 } }              ]            }          ]        }      }    }  }}

To refer to a predefined variable in alog condition, use avariable item, which takesname andvalue items and tests equality of the named variable against a given value:

"variable": {  "name": "variable_name",  "value":comparison_value}

This is true ifvariable_name has the valuecomparison_value, false otherwise.

Example:

{  "filter": {    "class": {      "name": "general",      "event": {        "name": "status",        "log": {          "variable": {            "name": "audit_log_connection_policy_value",            "value": "::none"          }        }      }    }  }}

Each predefined variable corresponds to a system variable. By writing a filter that tests a predefined variable, you can modify filter operation by setting the corresponding system variable, without having to redefine the filter. For example, by writing a filter that tests the value of theaudit_log_connection_policy_value predefined variable, you can modify filter operation by changing the value of theaudit_log_connection_policy system variable.

Theaudit_log_xxx_policy system variables are used for the deprecated legacy mode audit log (seeSection 8.4.5.10, “Legacy Mode Audit Log Filtering”). With rule-based audit log filtering, those variables remain visible (for example, usingSHOW VARIABLES), but changes to them have no effect unless you write filters containing constructs that refer to them.

The following list describes the permitted predefined variables forvariable items:

To refer to a predefined function in alog condition, use afunction item, which takesname andargs items to specify the function name and its arguments, respectively:

"function": {  "name": "function_name",  "args":arguments}

Thename item should specify the function name only, without parentheses or the argument list.

Theargs item must satisfy these conditions:

If the number of arguments is incorrect or the arguments are not of the correct data types required by the function an error occurs.

Example:

{  "filter": {    "class": {      "name": "general",      "event": {        "name": "status",        "log": {          "function": {            "name": "find_in_include_list",            "args": [ { "string": [ { "field": "user.str" },                                    { "string": "@"},                                    { "field": "host.str" } ] } ]          }        }      }    }  }}

The preceding filter determines whether to loggeneral classstatus events depending on whether the current user is found in theaudit_log_include_accounts system variable. That user is constructed using fields in the event.

The following list describes the permitted predefined functions forfunction items:

Audit filter definitions support replacement of certain audit event fields, so that logged events contain the replacement value rather than the original value. This capability enables logged audit records to include statement digests rather than literal statements, which can be useful for MySQL deployments for which statements may expose sensitive values.

Field replacement in audit events works like this:

AsTable 8.43, “Event Fields Subject to Replacement” shows, currently the only replaceable fields are those that contain statement text (which occurs in events of thegeneral andtable_access classes). In addition, the only function permitted for specifying the replacement value isquery_digest. This means that the only permitted replacement operation is to replace statement literal text by its corresponding digest.

Because field replacement occurs at an early auditing stage (during filtering), the choice of whether to write statement literal text or digest values applies regardless of log format written later (that is, whether the audit log plugin produces XML or JSON output).

Field replacement can take place at differing levels of event granularity:

Within a filter definition, specify field replacement by including aprint item, which has this syntax:

"print": {  "field": {    "name": "field_name",    "print":condition,    "replace":replacement_value  }}

Within theprint item, itsfield item takes these three items to indicate how whether and how replacement occurs:

For example, this filter definition applies to all events in thegeneral class, replacing the statement literal text with its digest:

{  "filter": {    "class": {      "name": "general",      "print": {        "field": {          "name": "general_query.str",          "print": false,          "replace": {            "function": {              "name": "query_digest"            }          }        }      }    }  }}

The preceding filter uses thisprint item to unconditionally replace the statement literal text contained ingeneral_query.str by its digest value:

"print": {  "field": {    "name": "general_query.str",    "print": false,    "replace": {      "function": {        "name": "query_digest"      }    }  }}

print items can be written different ways to implement different replacement strategies. Thereplace item just shown specifies the replacement text using thisfunction construct to return a string representing the current statement digest:

"function": {  "name": "query_digest"}

Thequery_digest function can also be used in another way, as a comparator that returns a Boolean, which enables its use in theprint condition. To do this, provide an argument that specifies a comparison statement digest:

"function": {  "name": "query_digest",  "args": "digest"}

In this case,query_digest returnstrue orfalse depending on whether the current statement digest is the same as the comparison digest. Usingquery_digest this way enables filter definitions to detect statements that match particular digests. The condition in the following construct is true only for statements that have a digest equal toSELECT ?, thus effecting replacement only for statements that do not match the digest:

"print": {  "field": {    "name": "general_query.str",    "print": {      "function": {        "name": "query_digest",        "args": "SELECT ?"      }    },    "replace": {      "function": {        "name": "query_digest"      }    }  }}

To perform replacement only for statements that do match the digest, usenot to invert the condition:

"print": {  "field": {    "name": "general_query.str",    "print": {      "not": {        "function": {          "name": "query_digest",          "args": "SELECT ?"        }      }    },    "replace": {      "function": {        "name": "query_digest"      }    }  }}

Suppose that you want the audit log to contain only statement digests and not literal statements. To achieve this, you must perform replacement on all events that contain statement text; that is, events in thegeneral andtable_access classes. An earlier filter definition showed how to unconditionally replace statement text forgeneral events. To do the same fortable_access events, use a filter that is similar but changes the class fromgeneral totable_access and the field name fromgeneral_query.str toquery.str:

{  "filter": {    "class": {      "name": "table_access",      "print": {        "field": {          "name": "query.str",          "print": false,          "replace": {            "function": {              "name": "query_digest"            }          }        }      }    }  }}

Combining thegeneral andtable_access filters results in a single filter that performs replacement for all statement text-containing events:

{  "filter": {    "class": [      {        "name": "general",        "print": {          "field": {            "name": "general_query.str",            "print": false,            "replace": {              "function": {                "name": "query_digest"              }            }          }        }      },      {        "name": "table_access",        "print": {          "field": {            "name": "query.str",            "print": false,            "replace": {              "function": {                "name": "query_digest"              }            }          }        }      }    ]  }}

To perform replacement on only some events within a class, add items to the filter that indicate more specifically when replacement occurs. The following filter applies to events in thetable_access class, but performs replacement only forinsert andupdate events (leavingread anddelete events unchanged):

{  "filter": {    "class": {      "name": "table_access",      "event": {        "name": [          "insert",          "update"        ],        "print": {          "field": {            "name": "query.str",            "print": false,            "replace": {              "function": {                "name": "query_digest"              }            }          }        }      }    }  }}

This filter performs replacement forgeneral class events corresponding to the listed account-management statements (the effect being to hide credential and data values in the statements):

{  "filter": {    "class": {      "name": "general",      "event": {        "name": "status",        "print": {          "field": {            "name": "general_query.str",            "print": false,            "replace": {              "function": {                "name": "query_digest"              }            }          }        },        "log": {          "or": [            {              "field": {                "name": "general_sql_command.str",                "value": "alter_user"              }            },            {              "field": {                "name": "general_sql_command.str",                "value": "alter_user_default_role"              }            },            {              "field": {                "name": "general_sql_command.str",                "value": "create_role"              }            },            {              "field": {                "name": "general_sql_command.str",                "value": "create_user"              }            }          ]        }      }    }  }}

For information about the possiblegeneral_sql_command.str values, seeTesting Event Field Values.

In some cases, the filter definition can be changed dynamically. To do this, define afilter configuration within an existingfilter. For example:

{  "filter": {    "id": "main",    "class": {      "name": "table_access",      "event": {        "name": [ "update", "delete" ],        "log": false,        "filter": {          "class": {            "name": "general",            "event" : { "name": "status",                        "filter": { "ref": "main" } }          },          "activate": {            "or": [              { "field": { "name": "table_name.str", "value": "temp_1" } },              { "field": { "name": "table_name.str", "value": "temp_2" } }            ]          }        }      }    }  }}

A new filter is activated when theactivate item within a subfilter evaluates totrue. Usingactivate in a top-levelfilter is not permitted.

A new filter can be replaced with the original one by using aref item inside the subfilter to refer to the original filterid.

The filter shown operates like this:

The filter is useful to log statements that update or delete anything from thetemp_1 ortemp_2 tables, such as this one:

UPDATE temp_1, temp_3 SET temp_1.a=21, temp_3.a=23;

The statement generates multipletable_access events, but the audit log file contains onlygeneral /status entries.