6.5.5.6 Audit Log Filtering

Note

This section describes how audit log filtering works as of if
the audit log plugin and the accompanying audit tables and
UDFs are installed. If the plugin is installed but not the
accompanying audit tables and UDFs, the plugin operates in
legacy filtering mode, described in
Section 6.5.5.7, “Legacy Mode Audit Log Filtering”. Legacy mode is
filtering behavior as it was prior to MySQL 5.7.13; that is,
before the introduction of rule-based filtering.

In legacy filtering mode, the audit log plugin had the
capability of controlling logging of audited events by filtering
them based on the account from which events originate or event
status. Current filtering capabilities are extended:

Audited events can be filtered using these characteristics:

User account

Audit event class

Audit event subclass

Value of event fields such as those that indicate
operation status or SQL statement executed

Audit filtering is rule based:

A filter definition creates a set of auditing rules.
Definitions can be configured to include or exclude
events for logging based on the characteristics just
described.

Filter rules have the capability of blocking (aborting)
execution of qualifying events, in addition to existing
capabilities for event logging.

Multiple filters can be defined, and any given filter
can be assigned to any number of user accounts.

It is possible to define a default filter to use with
any user account that has no explicitly assigned filter.

Audit filters can be defined, displayed, and modified using
an SQL interface based on user-defined functions (UDFs).

Audit filter definitions are stored in the tables in the
mysql system database.

Within a given session, the value of the read-only
audit_log_filter_id system
variable indicates whether a filter has been assigned to the
session.

Note

By default, rule-based audit log filtering logs no auditable
events for any users. To log all auditable events for all
users, use the following statements, which create a simple
filter to enable logging and assign it to the default account:

Alternatively, should you prefer to avoid granting the
SUPER privilege while still
permitting users to access specific filtering functions,
“wrapper” stored programs can be defined. This
technique is described in the context of keyring UDFs in
Section 6.5.4.8.2, “Using General-Purpose Keyring Functions”; it can be adapted for
use with filtering UDFs.

The audit_log plugin operates in legacy
mode if it is installed but the accompanying audit tables
and functions are not created. The plugin writes these
messages to the error log at server startup:

[Warning] Plugin audit_log reported: 'Failed to open the audit log filter tables.'
[Warning] Plugin audit_log reported: 'Audit Log plugin supports a filtering,
which has not been installed yet. Audit Log plugin will run in the legacy
mode, which will be disabled in the next release.'

By default, no accounts have a filter assigned, so no
processing of auditable events occurs for any account.

Suppose that instead you want the default to be to log only
connection-related activity (for example, to see connect,
change-user, and disconnect events, but not the SQL statements
users execute while connected). To achieve this, define a
filter (shown here named log_conn_events)
that enables logging only of events in the
connection class, and assign that filter to
the default account, represented by the %
account name:

Filtering of current sessions for the account remains
unaffected. Subsequent connections from the account are
filtered using the new filter. For the filter shown here,
that means no logging for new connections from
user1@localhost.

For audit log filtering, user name and host name comparisons
are case-sensitive. This differs from comparisons for
privilege checking, for which host name comparisons are not
case-sensitive.

To remove a filter, do this:

SELECT audit_log_filter_remove_filter('log_nothing');

Removing a filter also unassigns it from any users to whom it
has been assigned, including any current sessions for those
users.

The filtering UDFs just described affect audit filtering
immediately and update the audit log tables in the
mysql system database that store filters
and user accounts (see Section 6.5.5.8.1, “Audit Log Tables”). It
is also possible to modify the audit log tables directly using
statements such as INSERT,
UPDATE, and
DELETE, but such changes do not
affect filtering immediately. To flush your changes and make
them operational, call
audit_log_filter_flush():

SELECT audit_log_filter_flush();

To determine whether a filter has been assigned to the current
session, check the session value of the read-only
audit_log_filter_id system
variable. If the value is 0, no filter is assigned. A nonzero
value indicates the internally maintained ID of the assigned
filter:

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:

Logging Specific Event Subclasses

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:

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.27, “Connection Event Fields”
indicates the permitted fields for connection events.

Table 6.27 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 "::undefined": Undefined

or "::tcp/ip": TCP/IP

or "::socket": Socket

or "::named_pipe": Named pipe

or "::ssl": TCP/IP with
encryption

or "::shared_memory": Shared
memory

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.

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:

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

read event:

SELECT

INSERT ... SELECT (for tables
referenced in SELECT clause)

REPLACE ... SELECT (for tables
referenced in SELECT clause)

UPDATE ... WHERE (for tables
referenced in WHERE clause)

HANDLER ... READ

delete event:

DELETE

TRUNCATE TABLE

insert event:

INSERT

INSERT ... SELECT (for table
referenced in INSERT clause)

REPLACE

REPLACE ... SELECT (for table
referenced in REPLACE clause

LOAD DATA INFILE

LOAD XML INFILE

update event:

UPDATE

UPDATE ... WHERE (for tables
referenced in UPDATE clause)

Blocking Execution of Specific Events

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.

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.

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_xxx_policy
system variables are used for the legacy mode audit log (see
Section 6.5.5.7, “Legacy Mode Audit Log Filtering”). With
rule-based audit log filtering, those variables remain
visible (for example, using SHOW
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:

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 is NULL. 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 is NULL. 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: The number of
milliseconds to sleep as an unsigned integer.

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: The user account name as
a string.

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: The user account name as
a string.

string_find(text, substr)

Checks whether the substr value is
contained in the text value. This
search is case-sensitive.

Arguments:

text: The text string to search.

substr: The substring to search
for in text.

Replacing a User Filter

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