Documentation Home
Security in MySQL
Related Documentation Download this Excerpt
PDF (US Ltr) - 1.6Mb
PDF (A4) - 1.6Mb
HTML Download (TGZ) - 330.4Kb
HTML Download (Zip) - 339.0Kb


Security in MySQL  /  ...  /  Audit Log Functions

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

Each audit log UDF returns a string that indicates whether the operation succeeded. OK indicates success. ERROR: message indicates failure.

These audit log UDFs are available:

  • audit_log_encryption_password_get()

    Retrieves the current audit log encryption password as a binary string. The password is fetched from the MySQL keyring, which must be enabled or an error occurs. Any keyring plugin can be used; for instructions, see Section 7.4, “The MySQL Keyring”.

    For additional information about audit log encryption, see Audit Log File Encryption.

    Arguments:

    None.

    Return value:

    The password string for success (up to 766 bytes), or NULL and an error for failure.

    Example:

    mysql> SELECT audit_log_encryption_password_get();
    +-------------------------------------+
    | audit_log_encryption_password_get() |
    +-------------------------------------+
    | secret                              |
    +-------------------------------------+
  • audit_log_encryption_password_set(password)

    Sets the audit log encryption password and stores it in the MySQL keyring, which must be enabled or an error occurs. Any keyring plugin can be used; for instructions, see Section 7.4, “The MySQL Keyring”.

    For additional information about audit log encryption, see Audit Log File Encryption.

    Arguments:

    password: The password string. The maximum permitted length is 766 bytes.

    Return value:

    1 for success, 0 for failure.

    Example:

    mysql> SELECT audit_log_encryption_password_set(password);
    +---------------------------------------------+
    | audit_log_encryption_password_set(password) |
    +---------------------------------------------+
    | 1                                           |
    +---------------------------------------------+
  • 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().

    Arguments:

    None.

    Return value:

    A string that indicates whether the operation succeeded. OK indicates success. ERROR: message indicates failure.

    Example:

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

    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.

    Arguments:

    • filter_name: A string that specifies the filter name.

    Return value:

    A string that indicates whether the operation succeeded. OK indicates success. ERROR: message indicates failure.

    Example:

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

    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.

    Arguments:

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

    Return value:

    A string that indicates whether the operation succeeded. OK indicates success. ERROR: message indicates failure.

    Example:

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

    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.

    Arguments:

    • filter_name: A string that specifies the filter name.

    • definition: A JSON value that specifies the filter definition.

    Return value:

    A string that indicates whether the operation succeeded. OK indicates success. ERROR: message indicates failure.

    Example:

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

    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.

    Arguments:

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

    • filter_name: A string that specifies the filter name.

    Return value:

    A string that indicates whether the operation succeeded. OK indicates success. ERROR: message indicates failure.

    Example:

    mysql> SELECT audit_log_filter_set_user('user1@localhost', 'SomeFilter');
    +------------------------------------------------------------+
    | audit_log_filter_set_user('user1@localhost', 'SomeFilter') |
    +------------------------------------------------------------+
    | OK                                                         |
    +------------------------------------------------------------+
  • audit_log_read([arg])

    Reads events from the audit log and returns a binary JSON string containing an array of audit events. If the audit log format is not JSON, an error occurs.

    Each event in the return value is a JSON hash, except that the last array element may be a JSON null value to indicate no following events are available to read.

    For the first call to audit_log_read() within a session, pass a bookmark indicating where to begin reading. If the final value of the returned array is not a JSON null value, there are more events following those just read and audit_log_read() can be called without or with a bookmark argument. Without an argument, reading continues with the next unread event. With a bookmark argument, reading continues from the bookmark.

    If the final value of the returned array is a JSON null value, there are no more events left to be read and the next call to audit_log_read() must include a bookmark argument.

    To obtain a bookmark for the most recently written event, call audit_log_read_bookmark().

    For additional information about audit log-reading functions, see Audit Log File Reading.

    Arguments:

    arg: An optional bookmark, represented as a string containing a JSON hash that indicates where and how much to read. The following items are significant in the arg value (other items are ignored):

    • timestamp, id: The location within the audit log of the first event to read. Both items must be present to completely specify a position.

    • max_array_length: The maximum number of events to read from the log. If omitted, the default is to read to the end of the log or until the read buffer is full, whichever comes first.

    Return value:

    A binary JSON string containing an array of audit events for success, or NULL and an error for failure.

    Example:

    mysql> SELECT audit_log_read(audit_log_read_bookmark());
    +-----------------------------------------------------------------------+
    | audit_log_read(audit_log_read_bookmark())                             |
    +-----------------------------------------------------------------------+
    | [ {"timestamp":"2018-01-15 22:41:24","id":0,"class":"connection", ... |
    +-----------------------------------------------------------------------+
  • audit_log_read_bookmark()

    Returns a binary JSON string representing a bookmark for the most recently written audit log event. If the audit log format is not JSON, an error occurs.

    The bookmark is a JSON hash with timestamp and id items indicating the event position within the audit log. It is suitable for passing to audit_log_read() to indicate to that function where to begin reading.

    For additional information about audit log-reading functions, see Audit Log File Reading.

    Arguments:

    None.

    Return value:

    A binary JSON string containing a bookmark for success, or NULL and an error for failure.

    Example:

    mysql> SELECT audit_log_read_bookmark();
    +-------------------------------------------------+
    | audit_log_read_bookmark()                       |
    +-------------------------------------------------+
    | { "timestamp": "2018-01-15 21:03:44", "id": 0 } |
    +-------------------------------------------------+