MySQL Enterprise Audit Plugin

The MySQL Enterprise Audit plugin enables MySQL Server to produce a log file containing an audit record of server activity. The log content includes when clients connect and disconnect, and what actions they perform while connected, such as which databases and tables they access.

You can add statistics for the time and size of each query to detect outliers. By default, audit plugin logs are disabled, and you have to define audit plugin filters to enable logging auditable events for all or specific users.

Related Topics

Granting Audit Administration Privileges

By default, the HeatWave administrator user you defined while creating the DB system has the AUDIT_ADMIN privilege. You can grant audit administration privileges to more users.

Using a Command-Line Client

Use a command-line client such as MySQL Client or MySQL Shell to grant audit administration privileges to more users.

  1. Connect to the DB system using the command-line client of your choice.
  2. Run the following command to grant the user, User001, the audit administration privilege:
    GRANT AUDIT_ADMIN ON *.* TO <User001>;

Defining Audit Plugin Filters

You have to define audit plugin filters to enable logging auditable events for all or specific users. By default, audit plugin logs are disabled.

Using a Command-Line Client

Use a command-line client such as MySQL Client or MySQL Shell to define audit plugin filters to enable logging auditable events for all or specific users.

This task requires the following:
  • MySQL version 8.0.34-u2 or higher.
  1. Connect to the DB system using the command-line client of your choice.
  2. Define audit filters to enable logging. See Writing Audit Log Filter Definitions.
    1. To audit all events, run the following command:
      SELECT audit_log_filter_set_filter('log_all', '{ "filter": { "log": true } }');
    2. To audit connection events only, run the following command:
      SELECT audit_log_filter_set_filter('log_conn_events','{ "filter": { "class": { "name": "connection" } } }');
    3. To view audit filters, run the following command:
      SELECT * FROM mysql_audit.audit_log_filter;
  3. Assign the filters you created in Step 2 to users. You can assign the audit filters to all users or to a specific user.
    1. To assign the default audit filter to log all events from any account, use the wildcard character %:
      SELECT audit_log_filter_set_user('%', 'log_all');
    2. To assign the default audit filter to log all connect events from any account, use the wildcard character %:
      SELECT audit_log_filter_set_user('%', 'log_conn_events');
    3. To assign the default audit filter to log all events from a specific user such as user_dba, run the following command:
      SELECT audit_log_filter_set_user('user_dba@%', 'log_all');
    4. To view the assigned rules, run the following command:
      SELECT * FROM mysql_audit.audit_log_user;
    5. To unassign the rules from the user, user_dba, run the following command:
      SELECT audit_log_filter_remove_user('user_dba@%');
  4. (Optional) You can insert your own event data with the component, audit_api_message_emit. See Audit Message Function. For example:
    SELECT audit_api_message_emit_udf('component_text', 'producer_text', 'message_text', 'key1', 'value1', 'key2', 123, 'key3', NULL) AS 'Message';

Accessing and Analyzing Audit Data

Use the audit data to monitor the DB system.

Using a Command-Line Client

Use a command-line client such as MySQL Client or MySQL Shell to access and analyze audit data.

  1. Connect to the DB system using the command-line client of your choice.
  2. To view any new logs since you last checked, run the following command. This ensures you are always updated with the latest audit logs without revisiting older logs.
    SELECT audit_log_read(audit_log_read_bookmark());
  3. To extract audit logs starting from a particular timestamp, provide additional parameters within the audit_log_read() function:
    SELECT audit_log_read('{ "start": { "timestamp": "2023-08-24 12:30:00" }, "max_array_length": 500 }');
  4. To view the audit data in an easier to read format, use the JSON_PRETTY() and CONVERT() functions:
    SELECT JSON_PRETTY(CONVERT(audit_log_read( ... ) USING UTF8MB4));
  5. To transform data to tabular format, use the MySQL JSON functions. For example, you can transform a subset of the JSON name-value pairs into a structured table format, making it easier to interact with and analyze data:
    SELECT @@server_uuid as server_uuid, ts, class, event, login_ip,login_user,connection_id,
     status,connection_type,_client_name,_client_version,
     command,sql_command,command_status
    FROM
    JSON_TABLE
    (
      AUDIT_LOG_READ( '{ "start": {\"timestamp\": \"2023-08-16 15:33:37\"}, \"max_array_length\": 10 }' ), 
      '$[*]'
      COLUMNS
      ( 
        ts TIMESTAMP PATH '$.timestamp',
        class VARCHAR(20) PATH '$.class',
        event VARCHAR(80) PATH '$.event',      
        login_ip VARCHAR(200) PATH '$.login.ip',
        login_user VARCHAR(200) PATH '$.login.user',
        connection_id VARCHAR(80) PATH '$.connection_id',
        status INT PATH '$.connection_data.status',
        connection_type VARCHAR(40) PATH '$.connection_data.connection_type',
        _client_name VARCHAR(80) PATH '$.connection_data.connection_attributes._client_name',
        _client_version VARCHAR(80) PATH '$.connection_data.connection_attributes._client_version',
        command VARCHAR(40) PATH '$.general_data.command',
        sql_command VARCHAR(40) PATH '$.general_data.sql_command',
        command_status VARCHAR(40) PATH '$.general_data.status'
       )) as audit_log;
  6. To further refine the data extraction, use WHERE clauses in the SQL statements. For example, WHERE connection_type <> 'SSL'.