Documentation Home
MySQL 5.7 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 35.5Mb
PDF (A4) - 35.5Mb
PDF (RPM) - 34.6Mb
EPUB - 8.7Mb
HTML Download (TGZ) - 8.5Mb
HTML Download (Zip) - 8.5Mb
HTML Download (RPM) - 7.3Mb
Eclipse Doc Plugin (TGZ) - 9.3Mb
Eclipse Doc Plugin (Zip) - 11.5Mb
Man Pages (TGZ) - 201.5Kb
Man Pages (Zip) - 306.8Kb
Info (Gzip) - 3.3Mb
Info (Zip) - 3.3Mb
Excerpts from this Manual

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

7.5.4.7 Audit Log Reference

The following discussion serves as a reference to these MySQL Enterprise Audit components:

  • Audit log tables

  • Audit log functions

  • Audit log system variables

  • Audit log status variables

To install the audit_log tables and functions, use the instructions provided in Section 7.5.4.2, “Installing or Uninstalling MySQL Enterprise Audit”. Unless those components are installed, the audit_log plugin operates in legacy mode. See Section 7.5.4.6.3, “Legacy Mode Audit Log Filtering”.

7.5.4.7.1 Audit Log Tables

MySQL Enterprise Audit uses tables in the mysql system database for persistent storage of filter and user account data. The tables can be accessed only by users with privileges for that database. The tables use the MyISAM storage engine.

If these tables are missing, the audit_log plugin operates in legacy mode. See Section 7.5.4.6.3, “Legacy Mode Audit Log Filtering”.

The audit_log_filter table stores filter definitions. The table has these columns:

  • NAME

    The filter name.

  • FILTER

    The filter definition associated with the filter name. Definitions are stored as JSON values.

The audit_log_user table stores user account information. The table has these columns:

  • USER

    The user name part of an account. For an account user1@localhost, the USER part is user1.

  • HOST

    The host name part of an account. For an account user1@localhost, the HOST part is localhost.

  • FILTERNAME

    The name of the filter assigned to the account. The filter name associates the account with a filter defined in the audit_log_filter table.

7.5.4.7.2 Audit Log Functions

This section describes, for each audit log user-defined function (UDF), its purpose, calling sequence, and return value. For information about the conditions under which these UDFs can be invoked, see Section 7.5.4.6, “Audit Log Filtering”.

Each audit log UDF returns OK for success, ERROR: message for failure.

These audit log UDFs are available:

  • audit_log_filter_flush()

    Calling any of the other filtering UDFs affects operational audit log filtering immediately and updates the audit log tables. If instead you modify the contents of those tables directly using statements such as INSERT, UPDATE, and DELETE, the changes do not affect filtering immediately. To flush your changes and make them operational, call audit_log_filter_flush().

    audit_log_filter_flush() affects all current sessions and detaches them from their previous filters. Current sessions are no longer logged unless they disconnect and reconnect, or execute a change-user operation.

    If this function fails, an error message is returned and the audit log is disabled until the next successful call to audit_log_filter_flush().

    Syntax:

    STRING audit_log_filter_flush()
    

    Arguments:

    None.

    Return values:

    OK for success, ERROR: message for failure.

    Example:

    mysql> SELECT audit_log_filter_flush();
    +--------------------------+
    | audit_log_filter_flush() |
    +--------------------------+
    | OK                       |
    +--------------------------+
    
  • audit_log_filter_remove_filter()

    Given a filter name, removes the filter from the current set of filters. It is not an error for the filter not to exist.

    If a removed filter is assigned to any user accounts, those users stop being filtered (they are removed from the audit_log_user table). Termination of filtering includes any current sessions for those users: They are detached from the filter and no longer logged.

    Syntax:

    STRING audit_log_filter_remove_filter(STRING filter_name)
    

    Arguments:

    • filter_name: The filter name as a string.

    Return values:

    OK for success, ERROR: message for failure.

    Example:

    mysql> SELECT audit_log_filter_remove_filter('SomeFilter');
    +----------------------------------------------+
    | audit_log_filter_remove_filter('SomeFilter') |
    +----------------------------------------------+
    | OK                                           |
    +----------------------------------------------+
    
  • audit_log_filter_remove_user()

    Given a user account name, cause the user to be no longer assigned to a filter. It is not an error if the user has no filter assigned. Filtering of current sessions for the user remains unaffected. New connections for the user are filtered using the default account filter if there is one, and are not logged otherwise.

    If the name is %, the function removes the default account filter that is used for any user account that has no explicitly assigned filter.

    Syntax:

    STRING audit_log_filter_remove_user(STRING user_name)
    

    Arguments:

    • user_name: The user account name as a string in user_name@host_name format, or % to represent the default account.

    Return values:

    OK for success, ERROR: message for failure.

    Example:

    mysql>t; SELECT audit_log_filter_remove_user('user1@localhost');
    +-------------------------------------------------+
    | audit_log_filter_remove_user('user1@localhost') |
    +-------------------------------------------------+
    | OK                                              |
    +-------------------------------------------------+
    
  • audit_log_filter_set_filter()

    Given a filter name and definition, adds the filter to the current set of filters. If the filter already exists and is used by any current sessions, those sessions are detached from the filter and are no longer logged. This occurs because the new filter definition has a new filter ID that differs from its previous ID.

    Syntax:

    STRING audit_log_filter_set_filter(STRING filter_name, STRING definition)
    

    Arguments:

    • filter_name: The filter name as a string.

    • definition: The filter definition as a JSON value.

    Return values:

    OK for success, ERROR: message for failure.

    Example:

    mysql>t; SET @f = '{ "filter": { "log": false } }';
    mysql>t; SELECT audit_log_filter_set_filter('SomeFilter', @f);
    +-----------------------------------------------+
    | audit_log_filter_set_filter('SomeFilter', @f) |
    +-----------------------------------------------+
    | OK                                            |
    +-----------------------------------------------+
    
  • audit_log_filter_set_user()

    Given a user account name and a filter name, assigns the filter to the user. A user can be assigned only one filter, so if the user was already assigned a filter, the assignment is replaced. Filtering of current sessions for the user remains unaffected. New connections are filtered using the new filter.

    As a special case, the name % represents the default account. The filter is used for connections from any user account that has no explicitly assigned filter.

    Syntax:

    STRING audit_log_filter_set_user(STRING user_name, STRING filter_name)
    

    Arguments:

    • user_name: The user account name as a string in user_name@host_name format, or % to represent the default account.

    • filter_name: The filter name as a string.

    Return values:

    OK for success, ERROR: message for failure.

    Example:

    mysql>t; SELECT audit_log_filter_set_user('user1@localhost', 'SomeFilter');
    +------------------------------------------------------------+
    | audit_log_filter_set_user('user1@localhost', 'SomeFilter') |
    +------------------------------------------------------------+
    | OK                                                         |
    +------------------------------------------------------------+
    
7.5.4.7.3 Audit Log Option and Variable Reference

Table 7.25 Audit Log Option/Variable Reference

NameCmd-LineOption FileSystem VarStatus VarVar ScopeDynamic
audit-logYesYes    
audit_log_buffer_sizeYesYesYes GlobalNo
audit_log_connection_policyYesYesYes GlobalYes
audit_log_current_session  Yes BothNo
Audit_log_current_size   YesGlobalNo
Audit_log_event_max_drop_size   YesGlobalNo
Audit_log_events   YesGlobalNo
Audit_log_events_filtered   YesGlobalNo
Audit_log_events_lost   YesGlobalNo
Audit_log_events_written   YesGlobalNo
audit_log_exclude_accountsYesYesYes GlobalYes
audit_log_fileYesYesYes GlobalNo
audit_log_flush  Yes GlobalYes
audit_log_formatYesYesYes GlobalNo
audit_log_include_accountsYesYesYes GlobalYes
audit_log_policyYesYesYes GlobalNo
audit_log_rotate_on_sizeYesYesYes GlobalYes
audit_log_statement_policyYesYesYes GlobalYes
audit_log_strategyYesYesYes GlobalNo
Audit_log_total_size   YesGlobalNo
Audit_log_write_waits   YesGlobalNo

7.5.4.7.4 Audit Log Options and Variables

This section describes the command options and system variables that control operation of MySQL Enterprise Audit. If values specified at startup time are incorrect, the audit_log plugin may fail to initialize properly and the server does not load it. In this case, the server may also produce error messages for other audit log settings because it will not recognize them.

To control the activation of the audit_log plugin, use this option:

If the audit_log plugin is enabled, it exposes several system variables that permit control over logging:

mysql> SHOW VARIABLES LIKE 'audit_log%';
+-----------------------------+--------------+
| Variable_name               | Value        |
+-----------------------------+--------------+
| audit_log_buffer_size       | 1048576      |
| audit_log_connection_policy | ALL          |
| audit_log_current_session   | OFF          |
| audit_log_exclude_accounts  |              |
| audit_log_file              | audit.log    |
| audit_log_filter_id         | 0            |
| audit_log_flush             | OFF          |
| audit_log_format            | NEW          |
| audit_log_include_accounts  |              |
| audit_log_policy            | ALL          |
| audit_log_rotate_on_size    | 0            |
| audit_log_statement_policy  | ALL          |
| audit_log_strategy          | ASYNCHRONOUS |
+-----------------------------+--------------+

You can set any of these variables at server startup, and some of them at runtime.

  • audit_log_buffer_size

    Introduced5.7.9
    Command-Line Format--audit_log_buffer_size=value
    System VariableNameaudit_log_buffer_size
    Variable ScopeGlobal
    Dynamic VariableNo
    Permitted Values (32-bit platforms)Typeinteger
    Default1048576
    Min Value4096
    Max Value4294967295
    Permitted Values (64-bit platforms)Typeinteger
    Default1048576
    Min Value4096
    Max Value18446744073709547520

    When the audit log plugin writes events to the log asynchronously, it uses a buffer to store event contents prior to writing them. This variable controls the size of that buffer, in bytes. The server adjusts the value to a multiple of 4096. The plugin uses a single buffer, which it allocates when it initializes and removes when it terminates. The plugin allocates this buffer only if logging is asynchronous.

  • audit_log_connection_policy

    Introduced5.7.9
    Command-Line Format--audit_log_connection_policy=value
    System VariableNameaudit_log_connection_policy
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted ValuesTypeenumeration
    DefaultALL
    Valid ValuesALL
    ERRORS
    NONE

    The policy controlling how the audit log plugin writes connection events to its log file. The following table shows the permitted values.

    ValueDescription
    ALLLog all connection events
    ERRORSLog only failed connection events
    NONEDo not log connection events
    Note

    At server startup, any explicit value given for audit_log_connection_policy may be overridden if audit_log_policy is also specified, as described in Section 7.5.4.5, “Audit Log Logging Control”.

  • audit_log_current_session

    Introduced5.7.9
    System VariableNameaudit_log_current_session
    Variable ScopeGlobal, Session
    Dynamic VariableNo
    Permitted ValuesTypeboolean
    Defaultdepends on filtering policy

    Whether audit logging is enabled for the current session. The session value of this variable is read only. It is set when the session begins based on the values of the audit_log_include_accounts and audit_log_exclude_accounts system variables. The audit log plugin uses the session value to determine whether to audit events for the session. (There is a global value, but the plugin does not use it.)

  • audit_log_exclude_accounts

    Introduced5.7.9
    Command-Line Format--audit_log_exclude_accounts=value
    System VariableNameaudit_log_exclude_accounts
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted ValuesTypestring
    DefaultNULL

    The accounts for which events should not be logged. The value should be NULL or a string containing a list of one or more comma-separated account names. For more information, see Section 7.5.4.5, “Audit Log Logging Control”.

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

  • audit_log_file

    Introduced5.7.9
    Command-Line Format--audit_log_file=file_name
    System VariableNameaudit_log_file
    Variable ScopeGlobal
    Dynamic VariableNo
    Permitted ValuesTypefile name
    Defaultaudit.log

    The name of the file to which the audit log plugin writes events. The default value is audit.log. If the value of audit_log_file is a relative path name, the server interprets it relative to the data directory. If the value is a full path name, the server uses the value as is. A full path name may be useful if it is desirable to locate audit files on a separate file system or directory. For security reasons, the audit log file should be written to a directory accessible only to the MySQL server and users with a legitimate reason to view the log. For more information, see Section 7.5.4.5, “Audit Log Logging Control”.

  • audit_log_filter_id

    Introduced5.7.13
    System VariableNameaudit_log_filter_id
    Variable ScopeGlobal, Session
    Dynamic VariableNo
    Permitted ValuesTypeinteger

    The session value of this variable indicates the internally maintained ID of the audit filter for the current session. A value of 0 means that the session has no filter assigned.

  • audit_log_flush

    Introduced5.7.9
    System VariableNameaudit_log_flush
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted ValuesTypeboolean
    DefaultOFF

    When this variable is set to enabled (1 or ON), the audit log plugin closes and reopens its log file to flush it. (The value remains OFF so that you need not disable it explicitly before enabling it again to perform another flush.) Enabling this variable has no effect unless audit_log_rotate_on_size is 0. For more information, see Section 7.5.4.5, “Audit Log Logging Control”.

  • audit_log_format

    Introduced5.7.9
    Command-Line Format--audit_log_format=value
    System VariableNameaudit_log_format
    Variable ScopeGlobal
    Dynamic VariableNo
    Permitted Values (>= 5.7.9)Typeenumeration
    DefaultNEW
    Valid ValuesOLD
    NEW

    The audit log file format. Permitted values are OLD and NEW (default NEW). For details about the new format, see Section 7.5.4.4, “The Audit Log File”. For details about the old format, see The Audit Log File in MySQL 5.6 Reference Manual.

    If you change the value of audit_log_format, use this procedure to avoid writing log entries in one format to an existing log file that contains entries in a different format:

    1. Stop the server.

    2. Rename the current audit log file manually.

    3. Restart the server with the new value of audit_log_format. The audit log plugin will create a new log file, which will contain log entries in the selected format.

  • audit_log_include_accounts

    Introduced5.7.9
    Command-Line Format--audit_log_include_accounts=value
    System VariableNameaudit_log_include_accounts
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted ValuesTypestring
    DefaultNULL

    The accounts for which events should be logged. The value should be NULL or a string containing a list of one or more comma-separated account names. For more information, see Section 7.5.4.5, “Audit Log Logging Control”.

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

  • audit_log_policy

    Introduced5.7.9
    Command-Line Format--audit_log_policy=value
    System VariableNameaudit_log_policy
    Variable ScopeGlobal
    Dynamic VariableNo
    Permitted ValuesTypeenumeration
    DefaultALL
    Valid ValuesALL
    LOGINS
    QUERIES
    NONE

    The policy controlling how the audit log plugin writes events to its log file. The following table shows the permitted values.

    ValueDescription
    ALLLog all events
    LOGINSLog only login events
    QUERIESLog only query events
    NONELog nothing (disable the audit stream)

    audit_log_policy can be set only at server startup. At runtime, it is a read-only variable. Two other system variables, audit_log_connection_policy and audit_log_statement_policy, provide finer control over logging policy and can be set either at startup or at runtime. If you use audit_log_policy at startup instead of the other two variables, the server uses its value to set those variables. For more information about the policy variables and their interaction, see Section 7.5.4.5, “Audit Log Logging Control”.

  • audit_log_rotate_on_size

    Introduced5.7.9
    Command-Line Format--audit_log_rotate_on_size=N
    System VariableNameaudit_log_rotate_on_size
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted ValuesTypeinteger
    Default0

    If the audit_log_rotate_on_size value is greater than 0, the audit log plugin closes and reopens its log file if a write to the file causes its size to exceed this value. The original file is renamed to have a timestamp extension.

    If the audit_log_rotate_on_size value is 0, the plugin does not close and reopen its log based on size. Instead, use audit_log_flush to close and reopen the log on demand. In this case, rename the file externally to the server before flushing it.

    For more information about audit log file rotation and timestamp interpretation, see Section 7.5.4.5, “Audit Log Logging Control”.

    If you set this variable to a value that is not a multiple of 4096, it is truncated to the nearest multiple. (Thus, setting it to a value less than 4096 has the effect of setting it to 0 and no rotation occurs.)

  • audit_log_statement_policy

    Introduced5.7.9
    Command-Line Format--audit_log_statement_policy=value
    System VariableNameaudit_log_statement_policy
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted ValuesTypeenumeration
    DefaultALL
    Valid ValuesALL
    ERRORS
    NONE

    The policy controlling how the audit log plugin writes statement events to its log file. The following table shows the permitted values.

    ValueDescription
    ALLLog all statement events
    ERRORSLog only failed statement events
    NONEDo not log statement events
    Note

    At server startup, any explicit value given for audit_log_statement_policy may be overridden if audit_log_policy is also specified, as described in Section 7.5.4.5, “Audit Log Logging Control”.

  • audit_log_strategy

    Introduced5.7.9
    Command-Line Format--audit_log_strategy=value
    System VariableNameaudit_log_strategy
    Variable ScopeGlobal
    Dynamic VariableNo
    Permitted ValuesTypeenumeration
    DefaultASYNCHRONOUS
    Valid ValuesASYNCHRONOUS
    PERFORMANCE
    SEMISYNCHRONOUS
    SYNCHRONOUS

    The logging method used by the audit log plugin. The following table describes the permitted values.

    Table 7.26 Audit Log Strategies

    ValueMeaning
    ASYNCHRONOUSLog asynchronously, wait for space in output buffer
    PERFORMANCELog asynchronously, drop request if insufficient space in output buffer
    SEMISYNCHRONOUSLog synchronously, permit caching by operating system
    SYNCHRONOUSLog synchronously, call sync() after each request

7.5.4.7.5 Audit Log Status Variables

If the audit_log plugin is enabled, it exposes several status variables that provide operational information.


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