18.104.22.168 Legacy Mode Audit Log Filtering
This section describes legacy audit log filtering, which applies under either of these circumstances:
Before MySQL 5.7.13, that is, prior to the introduction of rule-based audit log filtering described in Section 22.214.171.124, “Audit Log Filtering”.
As of MySQL 5.7.13, if the
audit_logplugin is installed but not the accompanying audit tables and UDFs needed for rule-based filtering.
The audit log plugin can filter audited events. This enables you to control whether audited events are written to the audit log file based on the account from which events originate or event status. Status filtering occurs separately for connection events and statement events.
To filter audited events based on the originating account, set one of these system variables at server startup or runtime:
The value for either variable can be
NULL or a string containing one or more comma-separated account names, each in
format. By default, both variables are
NULL, in which case, no account filtering is done and auditing occurs for all accounts.
Example: To enable audit logging only for the
user2 local host account accounts, set the
audit_log_include_accounts system variable like this:
SET GLOBAL audit_log_include_accounts = 'user1@localhost,user2@localhost';
-- This sets audit_log_exclude_accounts to NULL SET GLOBAL audit_log_include_accounts = value; -- This fails because audit_log_include_accounts is not NULL SET GLOBAL audit_log_exclude_accounts = value; -- To set audit_log_exclude_accounts, first set -- audit_log_include_accounts to NULL SET GLOBAL audit_log_include_accounts = NULL; SET GLOBAL audit_log_exclude_accounts = value;
mysql> SHOW VARIABLES LIKE 'audit_log_include_accounts'; +----------------------------+-------+ | Variable_name | Value | +----------------------------+-------+ | audit_log_include_accounts | | +----------------------------+-------+ mysql> SELECT @@audit_log_include_accounts; +------------------------------+ | @@audit_log_include_accounts | +------------------------------+ | NULL | +------------------------------+
If a user name or host name requires quoting because it contains a comma, space, or other special character, quote it using single quotes. If the variable value itself is quoted with single quotes, double each inner single quote or escape it with a backslash. The following statements each enable audit logging for the local
root account and are equivalent, even though the quoting styles differ:
SET GLOBAL audit_log_include_accounts = 'root@localhost'; SET GLOBAL audit_log_include_accounts = '''root''@''localhost'''; SET GLOBAL audit_log_include_accounts = '\'root\'@\'localhost\''; SET GLOBAL audit_log_include_accounts = "'root'@'localhost'";
The last statement will not work if the
ANSI_QUOTES SQL mode is enabled because in that mode double quotes signify identifier quoting, not string quoting.
To filter audited events based on status, set the following system variables at server startup or runtime. These variables apply only for legacy audit log filtering. For JSON audit log filtering, different status variables apply; see Audit Log Options and Variables.
Each variable takes a value of
ALL (log all associated events; this is the default),
ERRORS (log only failed events), or
NONE (do not log events). For example, to log all statement events but only failed connection events, use these settings:
SET GLOBAL audit_log_statement_policy = ALL; SET GLOBAL audit_log_connection_policy = ERRORS;
Another policy system variable,
audit_log_policy, is available but does not afford as much control as
audit_log_statement_policy. It can be set only at server startup. At runtime, it is a read-only variable. It takes a value of
ALL (log all events; this is the default),
LOGINS (log connection events),
QUERIES (log statement events), or
NONE (do not log events). For any of those values, the audit log plugin logs all selected events without distinction as to success or failure. Use of
audit_log_policy at startup works as follows:
If you do not set
audit_log_policyor set it to its default of
ALL, any explicit settings for
audit_log_statement_policyapply as specified. If not specified, they default to
If you set
audit_log_policyto a non-
ALLvalue, that value takes precedence over and is used to set
audit_log_statement_policy, as indicated in the following table. If you also set either of those variables to a value other than their default of
ALL, the server writes a message to the error log to indicate that their values are being overridden.
Startup audit_log_policy Value Resulting audit_log_connection_policy Value Resulting audit_log_statement_policy Value