Documentation Home
MySQL 5.7 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 37.9Mb
PDF (A4) - 38.0Mb
PDF (RPM) - 37.3Mb
HTML Download (TGZ) - 10.3Mb
HTML Download (Zip) - 10.3Mb
HTML Download (RPM) - 8.9Mb
Man Pages (TGZ) - 216.8Kb
Man Pages (Zip) - 329.5Kb
Info (Gzip) - 3.4Mb
Info (Zip) - 3.4Mb
Excerpts from this Manual

MySQL 5.7 Reference Manual  /  ...  /  Audit Log Filtering

6.5.5.6 Audit Log Filtering

Note

This section describes how audit log filtering works as of MySQL 5.7.13 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.6.3, “Legacy Mode Audit Log Filtering”. Legacy mode is the behavior that applies prior to MySQL 5.7.13; that is, before the introduction of rule-based filtering.

Prior to MySQL 5.7.13, 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. As of MySQL 5.7.13, 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 based on the characteristics just described.

    • 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.

The following list provides a brief summary of the UDFs that implement the SQL interface for audit filtering control:

  • audit_log_filter_set_filter(): Define a filter

  • audit_log_filter_remove_filter(): Remove a filter

  • audit_log_filter_set_user(): Start filtering a user account

  • audit_log_filter_remove_user(): Stop filtering a user account

  • audit_log_filter_flush(): Flush manual changes to the filter tables to affect ongoing filtering

For usage examples and complete details about the filtering functions, see Section 6.5.5.6.1, “Using Audit Log Filtering Functions”, and Section 6.5.5.7.2, “Audit Log Functions”.

The audit log filtering UDFs are subject to these constraints:

  • To use any filtering UDF, the audit_log plugin must be enabled. Otherwise, an error occurs:

    mysql> SELECT audit_log_filter_flush();
    +----------------------------------------------------------------------------+
    | audit_log_filter_flush()                                                   |
    +----------------------------------------------------------------------------+
    | ERROR: audit_log plugin has not been installed with INSTALL PLUGIN syntax. |
    +----------------------------------------------------------------------------+

    The audit tables must also exist or an error occurs:

    mysql> SELECT audit_log_filter_flush();
    +--------------------------------------------------+
    | audit_log_filter_flush()                         |
    +--------------------------------------------------+
    | ERROR: Could not reinitialize audit log filters. |
    +--------------------------------------------------+

    To install the audit_log plugin, see Section 6.5.5.2, “Installing or Uninstalling MySQL Enterprise Audit”.

  • To use any filtering UDF, a user must possess the SUPER privilege. Otherwise, an error occurs:

    mysql> SELECT audit_log_filter_flush()\G
    *************************** 1. row ***************************
    audit_log_filter_flush(): ERROR: Request ignored for 'user1'@'localhost'.
                              SUPER_ACL needed to perform operation

    To grant the SUPER privilege to a user account, use this statement:

    GRANT SUPER ON *.* TO user;

    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.6.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 UDFs are not created. These messages are written 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.'

    In legacy mode, filtering can be done based only on event account or status. For details, see Section 6.5.5.6.3, “Legacy Mode Audit Log Filtering”.

6.5.5.6.1 Using Audit Log Filtering Functions

Before using the audit log user-defined functions (UDFs), install them according to the instructions provided in Section 6.5.5.2, “Installing or Uninstalling MySQL Enterprise Audit”.

The audit log filtering functions enable filtering control by providing an interface to create, modify, and remove filter definitions and assign filters to user accounts.

Filter definitions are JSON values. For information about using JSON data in MySQL, see Section 11.6, “The JSON Data Type”. This section shows some simple filter definitions. For more information about filter definitions, see Section 6.5.5.6.2, “Writing Audit Log Filter Definitions”.

When a connection arrives, the audit_log plugin determines which filter to use for the new session by searching for the user account name in the current filter assignments:

  • If a filter is assigned to the user, that filter is used.

  • Otherwise, if no assignment exists, but there is a filter assigned to the default account (%), the default filter is used.

  • Otherwise, no audit events are logged for the session.

If a change-user operation occurs during a session (see Section 27.8.7.3, “mysql_change_user()”), filter assignment for the session is updated using the same rules but for the new user.

By default, no accounts have a filter assigned, so no logging 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:

SET @f = '{ "filter": { "class": { "name": "connection" } } }';
SELECT audit_log_filter_set_filter('log_conn_events', @f);
SELECT audit_log_filter_set_user('%', 'log_conn_events');

Now connections from any account that has no explicitly defined filter use this default account filter.

To assign a filter explicitly to a particular user account or accounts, define the filter, then assign it to the relevant accounts:

SELECT audit_log_filter_set_filter('log_all', '{ "filter": { "log": true } }');
SELECT audit_log_filter_set_user('user1@localhost', 'log_all');
SELECT audit_log_filter_set_user('user2@localhost', 'log_all');

Now full logging is enabled for user1@localhost and user2@localhost. Connections from other accounts continue to be filtered using the default account filter.

To disassociate a user account from its current filter, either unassign the filter or assign a different filter:

  • Unassign the filter from the user account:

    SELECT audit_log_filter_remove_user('user1@localhost');

    Filtering of current sessions for the account remains unaffected. Subsequent connections from the account are filtered using the default account filter if there is one, and are not logged otherwise.

  • Assign a different filter to the user account:

    SELECT audit_log_filter_set_filter('log_nothing', '{ "filter": { "log": false } }');
    SELECT audit_log_filter_set_user('user1@localhost', 'log_nothing');

    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.7.1, “Audit Log Tables”). It is also possible to modify those 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. If the value is nonzero, it is the internally maintained ID of the assigned filter:

mysql> SELECT @@audit_log_filter_id;
+-----------------------+
| @@audit_log_filter_id |
+-----------------------+
|                     2 |
+-----------------------+
6.5.5.6.2 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 is true if no class or event item is specified, and false otherwise (in which case, class or event can include their own log item).

Logging Specific Event Classes

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" }
    ]
  }
}
Note

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" ] }
    ]
  }
}
Logging Specific Event Subclasses

To enable logging for specific event subclasses, use an event item:

{
  "filter": {
    "class": [
      {
        "name": "connection",
        "event": [
          { "name": "connect" },
          { "name": "disconnect" }
        ]
      },
      { "name": "general" },
      {
        "name": "table_access",
        "event": [
          { "name": "insert" },
          { "name": "delete" },
          { "name": "update" }
        ]
      },
    ]
  }
}

Table 6.22, “Subclass Values Per Event Class” describes the permitted subclass values for each event class.

Table 6.22 Subclass Values Per Event Class

Event Class Event Subclass Description
connection connect Connection initiation (successful or unsuccessful)
change_user User re-authentication with different user/password during session
disconnect Connection termination
general status General operation information
table_access read Table read statements, such as SELECT or INSERT INTO ... SELECT
delete Table delete statements, such as DELETE or TRUNCATE TABLE
insert Table insert statements, such as INSERT or REPLACE
update Table update statements, such as UPDATE

Inclusive and Exclusive Logging

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, and table_access events:

{
  "filter": {
    "log": true,
    "class": [
      {
        "name": "connection",
        "event": [
          { "name": "connect", "log": false },
          { "name": "disconnect", "log": false }
        ]
      },
      { "name": "general", "log": false }
    ]
  }
}
Testing Event Field Values

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

Table 6.23 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:
0 or "::undefined": Undefined
1 or "::tcp/ip": TCP/IP
2 or "::socket": Socket
3 or "::named_pipe": Named pipe
4 or "::ssl": SSL
5 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 indicates the status code of an operation and its details. Table 6.24, “General Event Fields” indicates the permitted fields for general events.

Table 6.24 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:

mysql> SELECT NAME FROM performance_schema.setup_instruments
       WHERE NAME LIKE 'statement/sql/%' ORDER BY NAME;
+---------------------------------------+
| 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.25, “Table-Access Event Fields” indicates the permitted fields for table-access events.

Table 6.25 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 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)

Logical Operators

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 } }
              ]
            }
          ]
        }
      }
    }
  }
}
Referencing Predefined Variables

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_xxx_policy system variables are used for the legacy mode audit log (see Section 6.5.5.6.3, “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:

  • 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.26, “audit_log_connection_policy_value Values” shows the permitted values and the corresponding audit_log_connection_policy values.

    Table 6.26 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 "::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.

  • 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.27, “audit_log_policy_value Values” shows the permitted values and the corresponding audit_log_policy values.

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

  • 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.28, “audit_log_statement_policy_value Values” shows the permitted values and the corresponding audit_log_statement_policy values.

    Table 6.28 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 "::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.

Referencing Predefined Functions

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

    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:

{
  "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 for table_access events, either update or delete.

  • If the update or delete table_access event occurs on the temp_1 or temp_2 table, the filter is replaced with the internal one (without an id, 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 the main 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:

UPDATE temp_1, temp_3 SET temp_1.a=21, temp_3.a=23;

The statement generates multiple table_access events, but the audit log file will contain only general / status entries.

Note

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.

6.5.5.6.3 Legacy Mode Audit Log Filtering
Note

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 6.5.5.6, “Audit Log Filtering”.

  • As of MySQL 5.7.13, if the audit_log plugin 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.

Event Filtering by Account

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 user_name@host_name format. By default, both variables are 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:

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

Event Filtering by Status

To filter audited events based on status, set these system variables at server startup or runtime:

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_connection_policy and 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_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 ValueResulting audit_log_connection_policy ValueResulting audit_log_statement_policy Value
    LOGINSALLNONE
    QUERIESNONEALL
    NONENONENONE
Event Filtering Reporting

To check the effect of filtering, you can inspect the values of these status variables:

  • Audit_log_events: The number of events handled by the audit log plugin, whether or not they were written to the log based on filtering policy.

  • Audit_log_events_filtered: The number of events handled by the audit log plugin that were filtered (not written to the log) based on filtering policy.

  • Audit_log_events_written: The number of events written to the audit log.


User Comments
Sign Up Login You must be logged in to post a comment.