Rechercher dans le manuel MySQL
6.4.5.7 Writing Audit Log Filter Definitions
Filter definitions are JSON
values. For information about using
JSON
data in MySQL, see
Section 11.6, “The JSON Data Type”.
Filter definitions have this form, where
actions
indicates how filtering takes
place:
{ "filter": actions }
The following discussion describes permitted constructs in filter definitions.
Logging All Events
To explicitly enable or disable logging of all events, use a
log
element in the filter:
{
"filter": { "log": true }
}
The log
value can be either
true
or false
.
The preceding filter enables logging of all events. It is equivalent to:
{
"filter": { }
}
Logging behavior depends on the log
value
and whether class
or
event
items are specified:
With
log
specified, its given value is used.Without
log
specified, logging istrue
if noclass
orevent
item is specified, andfalse
otherwise (in which case,class
orevent
can include their ownlog
item).
To log events of a specific class, use a
class
element in the filter, with its
name
field denoting the name of the class
to log:
{
"filter": {
"class": { "name": "connection" }
}
}
The name
value can be
connection
, general
, or
table_access
to log connection, general, or
table-access events, respectively.
The preceding filter enables logging of events in the
connection
class. It is equivalent to the
following filter with log
items made
explicit:
{
"filter": {
"log": false,
"class": { "log": true,
"name": "connection" }
}
}
To enable logging of multiple classes, define the
class
value as a
JSON
array element that names
the classes:
{
"filter": {
"class": [
{ "name": "connection" },
{ "name": "general" },
{ "name": "table_access" }
]
}
}
When multiple instances of a given item appear at the same level within a filter definition, the item values can be combined into a single instance of that item within an array value. The preceding definition can be written like this:
{
"filter": {
"class": [
{ "name": [ "connection", "general", "table_access" ] }
]
}
}
To select specific event subclasses, use an
event
item containing a
name
item that names the subclasses. The
default action for events selected by an
event
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" }
]
}
]
}
}
The event
item can also contain explicit
log
items to indicate whether to log
qualifying events. This event
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 }
]
The event
item can also indicate whether to
block qualifying events, if it contains an
abort
item. For details, see
Blocking Execution of Specific Events.
Table 6.27, “Event Class and Subclass Combinations” describes the permitted subclass values for each event class.
Table 6.27 Event Class and Subclass Combinations
Event Class | Event Subclass | Description |
---|---|---|
connection |
connect |
Connection initiation (successful or unsuccessful) |
connection |
change_user |
User re-authentication with different user/password during session |
connection |
disconnect |
Connection termination |
general |
status |
General operation information |
message |
internal |
Internally generated message |
message |
user |
Message generated by
audit_api_message_emit_udf() |
table_access |
read |
Table read statements, such as SELECT or
INSERT
INTO ... SELECT |
table_access |
delete |
Table delete statements, such as DELETE
or TRUNCATE TABLE |
table_access |
insert |
Table insert statements, such as INSERT
or REPLACE |
table_access |
update |
Table update statements, such as UPDATE |
Table 6.28, “Log and Abort Characteristics Per Event Class and Subclass Combination” describes for each event subclass whether it can be logged or aborted.
Table 6.28 Log and Abort Characteristics Per Event Class and Subclass Combination
Event Class | Event Subclass | Can be Logged | Can be Aborted |
---|---|---|---|
connection |
connect |
Yes | No |
connection |
change_user |
Yes | No |
connection |
disconnect |
Yes | No |
general |
status |
Yes | No |
message |
internal |
Yes | Yes |
message |
user |
Yes | Yes |
table_access |
read |
Yes | Yes |
table_access |
delete |
Yes | Yes |
table_access |
insert |
Yes | Yes |
table_access |
update |
Yes | Yes |
A filter can be defined in inclusive or exclusive mode:
Inclusive mode logs only explicitly specified items.
Exclusive mode logs everything but explicitly specified items.
To perform inclusive logging, disable logging globally and
enable logging for specific classes. This filter logs
connect
and disconnect
events in the connection
class, and events
in the general
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 the general
class:
{
"filter": {
"log": true,
"class":
{ "name": "general", "log": false }
}
}
This filter logs change_user
events in the
connection
class,
message
events, and
table_access
events:
{
"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 a field
item within the
log
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.
A connection event indicates when a connection-related activity occurs during a session, such as a user connecting to or disconnecting from the server. Table 6.29, “Connection Event Fields” indicates the permitted fields for connection events.
Table 6.29 Connection Event Fields
Field Name | Field Type | Description |
---|---|---|
status |
integer | Event status: 0: OK Otherwise: Failed |
connection_id |
unsigned integer | Connection ID |
user.str |
string | User name specified during authentication |
user.length |
unsigned integer | User name length |
priv_user.str |
string | Authenticated user name (account user name) |
priv_user.length |
unsigned integer | Authenticated user name length |
external_user.str |
string | External user name (provided by third-party authentication plugin) |
external_user.length |
unsigned integer | External user name length |
proxy_user.str |
string | Proxy user name |
proxy_user.length |
unsigned integer | Proxy user name length |
host.str |
string | Connected user host |
host.length |
unsigned integer | Connected user host length |
ip.str |
string | Connected user IP address |
ip.length |
unsigned integer | Connected user IP address length |
database.str |
string | Database name specified at connect time |
database.length |
unsigned integer | Database name length |
connection_type |
integer | Connection type:
or
or
or
or
or
or |
The "::
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.
xxx
"
A general event indicates the status code of an operation and its details. Table 6.30, “General Event Fields” indicates the permitted fields for general events.
Table 6.30 General Event Fields
Field Name | Field Type | Description |
---|---|---|
general_error_code |
integer | Event status: 0: OK Otherwise: Failed |
general_thread_id |
unsigned integer | Connection/thread ID |
general_user.str |
string | User name specified during authentication |
general_user.length |
unsigned integer | User name length |
general_command.str |
string | Command name |
general_command.length |
unsigned integer | Command name length |
general_query.str |
string | SQL statement text |
general_query.length |
unsigned integer | SQL statement text length |
general_host.str |
string | Host name |
general_host.length |
unsigned integer | Host name length |
general_sql_command.str |
string | SQL command type name |
general_sql_command.length |
unsigned integer | SQL command type name length |
general_external_user.str |
string | External user name (provided by third-party authentication plugin) |
general_external_user.length |
unsigned integer | External user name length |
general_ip.str |
string | Connected user IP address |
general_ip.length |
unsigned integer | Connection user IP address length |
general_command.str
indicates a command
name: Query
, Execute
,
Quit
, or Change user
.
A general event with the
general_command.str
field set to
Query
or Execute
contains general_sql_command.str
set to a
value that specifies the type of SQL command:
alter_db
,
alter_db_upgrade
,
admin_commands
, and so forth. These values
can be seen as the last components of the Performance Schema
instruments displayed by this statement:
- +---------------------------------------+
- | 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 |
- ...
A table-access event provides information about specific table accesses. Table 6.31, “Table-Access Event Fields” indicates the permitted fields for table-access events.
Table 6.31 Table-Access Event Fields
Field Name | Field Type | Description |
---|---|---|
connection_id |
unsigned integer | Event connection ID |
sql_command_id |
integer | SQL command ID |
query.str |
string | SQL statement text |
query.length |
unsigned integer | SQL statement text length |
table_database.str |
string | Database name associated with event |
table_database.length |
unsigned integer | Database name length |
table_name.str |
string | Table name associated with event |
table_name.length |
unsigned integer | Table name length |
The following list shows which statements produce which table-access events:
read
event:SELECT
INSERT ... SELECT
(for tables referenced inSELECT
clause)REPLACE ... SELECT
(for tables referenced inSELECT
clause)UPDATE ... WHERE
(for tables referenced inWHERE
clause)HANDLER ... READ
delete
event:DELETE
TRUNCATE TABLE
insert
event:INSERT
INSERT ... SELECT
(for table referenced inINSERT
clause)REPLACE
REPLACE ... SELECT
(for table referenced inREPLACE
clauseLOAD DATA
LOAD XML
update
event:UPDATE
UPDATE ... WHERE
(for tables referenced inUPDATE
clause)
event
items can include an
abort
item that indicates whether to
prevent qualifying events from executing. For example,
abort
enables rules to be written that
block execution of specific SQL statements.
The abort
item must appear within an
event
item. For example:
"event": {
"name": qualifying event subclass names
"abort": condition
}
For event subclasses selected by the name
item, the abort
action is true or false,
depending on condition
evaluation.
If the condition evaluates to true, the event is blocked.
Otherwise, the event continues executing.
The condition
specification can be
as simple as true
or
false
, or it can be more complex such that
evaluation depends on event characteristics.
This filter blocks INSERT
,
UPDATE
, and
DELETE
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 (see Table 6.28, “Log and Abort Characteristics Per Event Class and Subclass Combination”). For an event that cannot, the audit log writes a warning to the error log rather than blocking it.
For attempts to define a filter in which the
abort
item appears elsewhere than in an
event
item, an error occurs.
Logical operators (and
,
or
, not
) can be used in
log
items. This permits construction of
more advanced filtering configurations:
{
"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 a log
condition, use a variable
item, which tests
equality against a given value:
{
"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 the
audit_log_connection_policy_value
predefined variable, you can modify filter operation by
changing the value of the
audit_log_connection_policy
system variable.
The
audit_log_
system variables are used for the legacy mode audit log (see
Section 6.4.5.8, “Legacy Mode Audit Log Filtering”). With rule-based
audit log filtering, those variables remain visible (for
example, using xxx
_policySHOW 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 for variable
items:
audit_log_connection_policy_value
This variable corresponds to the value of the
audit_log_connection_policy
system variable. The value is an unsigned integer. Table 6.32, “audit_log_connection_policy_value Values” shows the permitted values and the correspondingaudit_log_connection_policy
values.Table 6.32 audit_log_connection_policy_value Values
Value Corresponding audit_log_connection_policy Value 0
or"::none"
NONE
1
or"::errors"
ERRORS
2
or"::all"
ALL
The
"::
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.xxx
"audit_log_policy_value
This variable corresponds to the value of the
audit_log_policy
system variable. The value is an unsigned integer. Table 6.33, “audit_log_policy_value Values” shows the permitted values and the correspondingaudit_log_policy
values.Table 6.33 audit_log_policy_value Values
Value Corresponding audit_log_policy Value 0
or"::none"
NONE
1
or"::logins"
LOGINS
2
or"::all"
ALL
3
or"::queries"
QUERIES
The
"::
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.xxx
"audit_log_statement_policy_value
This variable corresponds to the value of the
audit_log_statement_policy
system variable. The value is an unsigned integer. Table 6.34, “audit_log_statement_policy_value Values” shows the permitted values and the correspondingaudit_log_statement_policy
values.Table 6.34 audit_log_statement_policy_value Values
Value Corresponding audit_log_statement_policy Value 0
or"::none"
NONE
1
or"::errors"
ERRORS
2
or"::all"
ALL
The
"::
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.xxx
"
To refer to a predefined function in a log
condition, use a function
item, which takes
name
and args
values to
specify the function name and its arguments, respectively:
{
"filter": {
"class": {
"name": "general",
"event": {
"name": "status",
"log": {
"function": {
"name": "find_in_include_list",
"args": [ { "string": [ { "field": "user.str" },
{ "string": "@"},
{ "field": "host.str" } ] } ]
}
}
}
}
}
}
The function as specified in the name
item
should be the function name only, without parentheses or the
argument list. Arguments in the args
item,
if there is one, must be given in the order listed in the
function description. Arguments can refer to predefined
variables, event fields, or string or numeric constants.
The preceding filter determines whether to log
general
class status
events depending on whether the current user is found in the
audit_log_include_accounts
system variable. That user is constructed using fields in the
event.
The following list describes the permitted predefined
functions for function
items:
audit_log_exclude_accounts_is_null()
Checks whether the
audit_log_exclude_accounts
system variable isNULL
. This function can be helpful when defining filters that correspond to the legacy audit log implementation.Arguments:
None.
audit_log_include_accounts_is_null()
Checks whether the
audit_log_include_accounts
system variable isNULL
. This function can be helpful when defining filters that correspond to the legacy audit log implementation.Arguments:
None.
debug_sleep(millisec)
Sleeps for the given number of milliseconds. This function is used during performance measurement.
debug_sleep()
is available for debug builds only.Arguments:
millisec
: An unsigned integer that specifies the number of milliseconds to sleep.
find_in_exclude_list(account)
Checks whether an account string exists in the audit log exclude list (the value of the
audit_log_exclude_accounts
system variable).Arguments:
account
: A string that specifies the user account name.
find_in_include_list(account)
Checks whether an account string exists in the audit log include list (the value of the
audit_log_include_accounts
system variable).Arguments:
account
: A string that specifies the user account name.
string_find(text, substr)
Checks whether the
substr
value is contained in thetext
value. This search is case-sensitive.Arguments:
text
: The text string to search.substr
: The substring to search for intext
.
In some cases, the filter definition can be changed
dynamically. To do this, define a filter
configuration within an existing filter
.
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 the activate
element within a subfilter evaluates to
true
. Using activate
in
a top-level filter
is not permitted.
A new filter can be replaced with the original one by using a
ref
item inside the subfilter to refer to
the original filter id
.
The filter shown operates like this:
The
main
filter waits fortable_access
events, eitherupdate
ordelete
.If the
update
ordelete
table_access
event occurs on thetemp_1
ortemp_2
table, the filter is replaced with the internal one (without anid
, since there is no need to refer to it explicitly).If the end of the command is signalled (
general
/status
event), an entry is written to the audit log file and the filter is replaced with themain
filter.
The filter is useful to log statements that update or delete
anything from the temp_1
or
temp_2
tables, such as this one:
The statement generates multiple
table_access
events, but the audit log file
will contain only general
/
status
entries.
Any id
values used in the definition are
evaluated with respect only to that definition. They have
nothing to do with the value of the
audit_log_filter_id
system
variable.
Nederlandse vertaling
U hebt gevraagd om deze site in het Nederlands te bezoeken. Voor nu wordt alleen de interface vertaald, maar nog niet alle inhoud.Als je me wilt helpen met vertalingen, is je bijdrage welkom. Het enige dat u hoeft te doen, is u op de site registreren en mij een bericht sturen waarin u wordt gevraagd om u toe te voegen aan de groep vertalers, zodat u de gewenste pagina's kunt vertalen. Een link onderaan elke vertaalde pagina geeft aan dat u de vertaler bent en heeft een link naar uw profiel.
Bij voorbaat dank.
Document heeft de 26/06/2006 gemaakt, de laatste keer de 26/10/2018 gewijzigd
Bron van het afgedrukte document:https://www.gaudry.be/nl/mysql-rf-audit-log-filter-definitions.html
De infobrol is een persoonlijke site waarvan de inhoud uitsluitend mijn verantwoordelijkheid is. De tekst is beschikbaar onder CreativeCommons-licentie (BY-NC-SA). Meer info op de gebruiksvoorwaarden en de auteur.
Referenties
Deze verwijzingen en links verwijzen naar documenten die geraadpleegd zijn tijdens het schrijven van deze pagina, of die aanvullende informatie kunnen geven, maar de auteurs van deze bronnen kunnen niet verantwoordelijk worden gehouden voor de inhoud van deze pagina.
De auteur Deze site is als enige verantwoordelijk voor de manier waarop de verschillende concepten, en de vrijheden die met de referentiewerken worden genomen, hier worden gepresenteerd. Vergeet niet dat u meerdere broninformatie moet doorgeven om het risico op fouten te verkleinen.