3.1.2.7 MySQL Enterprise Audit

HeatWave on AWS supports the MySQL Enterprise Audit plugin for MySQL Server 8.1.0 and later. The audit plugin enables the MySQL Server to produce a log file containing an audit record of server activities. The log contents can include information such as when clients connected and disconnected, what actions they performed while connected, and which databases and tables they accessed.

You can add statistics for the time and size of the queries to detect outliers. By default, the audit plugin is disabled. You have to enable the plugin and define audit plugin filters to log auditable events for all or specific users.

To use the database audit plugin, you need to:

• Connect to the DB System
• Use the Query Editor on the Workspaces page or your command-line client to perform the following operations.

Enable Audit Plugin

The audit plugin is disabled in the default MySQL configurations on HeatWave on AWS. Enable it by setting the MySQL server system variable audit_log_disable to OFF in a custom configuration for your MySQL server and then use it to create your new DB system or update an existing one (see Creating a MySQL Configuration for details on setting preferred values for User-Configurable System Variables).

Granting Audit Administration Privileges

By default, the administrator user you defined while creating the DB system has the audit administration privileges. You can grant the privileges to more users (for example, to the user User001) by running the following command:

GRANT AUDIT_ADMIN ON *.* TO <User001>;

Defining Audit Plugin Filters

Define your audit plugin filters to log auditable events for all or specific users. See Writing Audit Log Filter Definitions for details. Here are some sample filters and the commands to define them:

• To audit all events, run the following command:

  SELECT audit_log_filter_set_filter('log_all', '{ "filter": { "log": true } }');

• To audit connection events only, run the following command:

  SELECT audit_log_filter_set_filter('log_conn_events', '{"filter": {"class": { "name": "connection" }}}');

• To view audit filters, run the following command:

  SELECT * FROM mysql_audit.audit_log_filter;

Assign the filters you created above to all or specific users for them to access the audit data. For example:

• 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');

• 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');

• 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');

• To view the assigned rules, run the following command:

  SELECT * FROM mysql_audit.audit_log_user;

• To unassign the rules from the user user_dba, run the following command:

  SELECT audit_log_filter_remove_user('user_dba@%');

Accessing and Analyzing Audit Data

Use the audit data to monitor the DB system. Use these statements to access the audit data through the functions audit_log_read() and audit_log_read_bookmark() :

• To view any new logs since you last checked, run the following command (this ensures you are always updated with the latest audit log entries):

  SELECT audit_log_read(audit_log_read_bookmark());

• To extract audit log contents starting from a particular timestamp, provide additional parameters within the audit_log_read() function:

  SELECT audit_log_read('{ "start": { "timestamp": "2024-01-24 12:30:00" }, "max_array_length": 500 }');

• To view the audit data in an easier to read format, use the JSON_PRETTY() and CONVERT() functions; for example:

  SELECT JSON_PRETTY(CONVERT(audit_log_read( '{ "start": { "timestamp": "2024-01-24 12:30:00" }, "max_array_length": 500 }') USING UTF8MB4));

• To transform data into a 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\": \"2024-01-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;

• To further refine the data extraction, use WHERE clauses (for example, WHERE connection_type <> 'SSL') in the SQL statements.