8.4.5.10 Legacy Mode Audit Log Filtering (original) (raw)

8.4.5.10 Legacy Mode Audit Log 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.

Legacy Event Filtering by Account

To filter audited events based on the originating account, set one (not both) of the following system variables at server startup or runtime. These deprecated variables apply only for legacy audit log filtering.

audit_log_include_accounts: The accounts to include in audit logging. If this variable is set, only these accounts are audited.
audit_log_exclude_accounts: The accounts to exclude from audit logging. If this variable is set, all but these accounts are audited.

The value for either variable can be NULL or a string containing one or more comma-separated account names, each in_`username`_@_`hostname`_ format. By default, both variables areNULL, in which case, no account filtering is done and auditing occurs for all accounts.

Modifications toaudit_log_include_accounts oraudit_log_exclude_accounts affect only connections created subsequent to the modification, not existing connections.

Example: To enable audit logging only for theuser1 and user2 local host accounts, set theaudit_log_include_accounts system variable like this:

SET GLOBAL audit_log_include_accounts = 'user1@localhost,user2@localhost';

Only one ofaudit_log_include_accounts oraudit_log_exclude_accounts can be non-NULL at a time:

If you setaudit_log_include_accounts, the server setsaudit_log_exclude_accounts to NULL.
If you attempt to setaudit_log_exclude_accounts, an error occurs unlessaudit_log_include_accounts is NULL. In this case, you must first clearaudit_log_include_accounts by setting it to NULL.

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

If you inspect the value of either variable, be aware thatSHOW VARIABLES displaysNULL as an empty string. To displayNULL as NULL, useSELECT instead:

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 does not work if theANSI_QUOTES SQL mode is enabled because in that mode double quotes signify identifier quoting, not string quoting.

Legacy Event Filtering by Status

To filter audited events based on status, set the following system variables at server startup or runtime. These deprecated variables apply only for legacy audit log filtering. For JSON audit log filtering, different status variables apply; seeAudit Log Options and Variables.

audit_log_connection_policy: Logging policy for connection events
audit_log_statement_policy: Logging policy for statement events

Each variable takes a value of ALL (log all associated events; this is the default),ERRORS (log only failed events), orNONE (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 asaudit_log_connection_policy andaudit_log_statement_policy. It can be set only at server startup.

Note

The audit_log_policy legacy-mode system variable is deprecated as of MySQL 8.0.34.

At runtime, it is a read-only variable. It takes a value ofALL (log all events; this is the default),LOGINS (log connection events),QUERIES (log statement events), orNONE (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 ofaudit_log_policy at startup works as follows:

If you do not setaudit_log_policy or set it to its default of ALL, any explicit settings foraudit_log_connection_policy oraudit_log_statement_policy apply as specified. If not specified, they default toALL.
If you setaudit_log_policy to a non-ALL value, that value takes precedence over and is used to setaudit_log_connection_policy andaudit_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

LOGINS ALL NONE

QUERIES NONE ALL

NONE NONE NONE

Startup audit_log_policy Value	Resulting audit_log_connection_policy Value	Resulting audit_log_statement_policy Value
LOGINS	ALL	NONE
QUERIES	NONE	ALL
NONE	NONE	NONE