MySQL 5.6 Reference Manual Including MySQL NDB Cluster 7.3-7.4 Reference Guide
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.
As of MySQL 5.6.20, to filter audited events based on the originating account, set one of these system variables at server startup or runtime:
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
format. By default, both variables are
user_name
@host_name
NULL
, in which case, no account filtering
is done and auditing occurs for all accounts.
Modifications to
audit_log_include_accounts
or
audit_log_exclude_accounts
affect only connections created subsequent to the
modification, not existing connections.
Example: To enable audit logging only for the
user1
and 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';
Only one of
audit_log_include_accounts
or
audit_log_exclude_accounts
can be non-NULL
at a time:
If you set
audit_log_include_accounts
,
the server sets
audit_log_exclude_accounts
to NULL
.
If you attempt to set
audit_log_exclude_accounts
,
an error occurs unless
audit_log_include_accounts
is NULL
. In this case, you must first
clear
audit_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 that
SHOW VARIABLES
displays
NULL
as an empty string. To avoid this, use
SELECT
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 the
ANSI_QUOTES
SQL mode is enabled because in
that mode double quotes signify identifier quoting, not string
quoting.
As of MySQL 5.6.20, to filter audited events based on status, set these system variables at server startup or runtime:
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), 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;
Before MySQL 5.6.20,
audit_log_connection_policy
and
audit_log_statement_policy
are not available. Instead, use
audit_log_policy
at server
startup or runtime. 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.
As of MySQL 5.6.20,
audit_log_policy
is still
available but can be set only at server startup. At runtime,
it is a read-only variable. Its use at startup works as
follows:
If you do not set
audit_log_policy
or set
it to its default of ALL
, any explicit
settings for
audit_log_connection_policy
or
audit_log_statement_policy
apply as specified. If not specified, they default to
ALL
.
If you set
audit_log_policy
to a
non-ALL
value, that value takes
precedence over and is used to set
audit_log_connection_policy
and
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 |
---|---|---|
LOGINS |
ALL |
NONE |
QUERIES |
NONE |
ALL |
NONE |
NONE |
NONE |