8.4.5.5 Configuring Audit Logging Characteristics (original) (raw)

8.4.5.5 Configuring Audit Logging Characteristics

This section describes how to configure audit logging characteristics, such as the file to which the audit log plugin writes events, the format of written events, whether to enable log file compression and encryption, and space management.

Note

Encryption capabilities described here apply as of MySQL 8.0.17, with the exception of the section that compares current encryption capabilities to the previous more-limited capabilities; seeAudit Log File Encryption Prior to MySQL 8.0.17.

For additional information about the functions and system variables that affect audit logging, seeAudit Log Functions, andAudit Log Options and Variables.

The audit log plugin can also control which audited events are written to the audit log file, based on event content or the account from which events originate. SeeSection 8.4.5.7, “Audit Log Filtering”.

Naming Conventions for Audit Log Files

To configure the audit log file name, set theaudit_log_file system variable at server startup. The default name isaudit.log in the server data directory. For best security, write the audit log to a directory accessible only to the MySQL server and to users with a legitimate reason to view the log.

The plugin interprets theaudit_log_file value as composed of an optional leading directory name, a base name, and an optional suffix. If compression or encryption are enabled, the effective file name (the name actually used to create the log file) differs from the configured file name because it has additional suffixes:

The effective audit log file name is the name resulting from the addition of applicable compression and encryption suffixes to the configured file name. For example, if the configuredaudit_log_file value isaudit.log, the effective file name is one of the values shown in the following table.

Enabled Features Effective File Name
No compression or encryption audit.log
Compression audit.log.gz
Encryption audit.log.pwd_id.enc
Compression, encryption audit.log.gz.pwd_id.enc

pwdid indicates the ID of the password used to encrypt or decrypt a file.pwdid format is_pwdtimestamp-seq_, where:

Here are some example pwdid password ID values:

20190403T142359-1
20190403T142400-1
20190403T142400-2

To construct the corresponding keyring IDs for storing passwords in the keyring, the audit log plugin adds a prefix of audit_log- to the_pwdid_ values. For the example password IDs just shown, the corresponding keyring IDs are:

audit_log-20190403T142359-1
audit_log-20190403T142400-1
audit_log-20190403T142400-2

The ID of the password currently used for encryption by the audit log plugin is the one having the largest_pwdtimestamp_ value. If multiple passwords have that pwdtimestamp value, the current password ID is the one with the largest sequence number. For example, in the preceding set of password IDs, two of them have the largest timestamp,20190403T142400, so the current password ID is the one with the largest sequence number (2).

The audit log plugin performs certain actions during initialization and termination based on the effective audit log file name:

Selecting Audit Log File Format

To configure the audit log file format, set theaudit_log_format system variable at server startup. These formats are available:

For details about each format, seeSection 8.4.5.4, “Audit Log File Formats”.

Enabling the Audit Log Flush Task

Starting in MySQL 8.0.34, MySQL Enterprise Audit provides the capability of setting a refresh interval to dispose of the in-memory cache automatically. A flush task configured using theaudit_log_flush_interval_seconds system variable has a value of zero by default, which means the task is not scheduled to run.

When the task is configured to run (the value is non-zero), MySQL Enterprise Audit attempts to call thescheduler component at its initialization and configure a regular, recurring flush of its memory cache:

Similarly, MySQL Enterprise Audit calls the scheduler component at its deinitialization and unconfigures the recurring flush that it has scheduled. It keeps an active reference to the scheduler registration service until the scheduled task is unregistered, ensuring that thescheduler component cannot be unloaded while there are active scheduled jobs. All of the results from executing the scheduler and its tasks are written to the server error log.

To schedule an audit log flush task:

  1. Confirm that the scheduler component is loaded and enabled. The component is enabled (ON) by default (seecomponent_scheduler.enabled).
SELECT * FROM mysql.components;  
+--------------+--------------------+----------------------------+  
| component_id | component_group_id | component_urn              |  
+--------------+--------------------+----------------------------+  
|            1 |                  1 | file://component_scheduler |  
+--------------+--------------------+----------------------------+
  1. Install the audit_log plugin, if it is not installed already (seeSection 8.4.5.2, “Installing or Uninstalling MySQL Enterprise Audit”).
  2. Start the server usingaudit_log_flush_interval_seconds and set the value to a number greater than 59. The upper limit of the value varies by platform. For example, to configure the flush task to recur every two minutes:
$> mysqld --audit_log_flush_interval_seconds=120

For more information, see theaudit_log_flush_interval_seconds system variable.

Adding Query Statistics for Outlier Detection

In MySQL 8.0.30 and later, you can extend log files in JSON format with optional data fields to show the query time, the number of bytes sent and received, the number of rows returned to the client, and the number of rows examined. This data is available in the slow query log for qualifying queries, and in the context of the audit log it similarly helps to detect outliers for activity analysis. The extended data fields can be added only when the audit log is in JSON format (audit_log_format=JSON), which is not the default setting.

The query statistics are delivered to the audit log through component services that you set up as an audit log filtering function. The services are namedmysql_audit_print_service_longlong_data_source andmysql_audit_print_service_double_data_source. You can choose either data type for each output item. For the query time, longlong outputs the value in microseconds, and double outputs the value in seconds.

You add the query statistics using theaudit_log_filter_set_filter() audit log function, as the service element of the JSON filtering syntax, as follows:

SELECT audit_log_filter_set_filter('QueryStatistics',  
                                   '{ "filter": { "class": { "name": "general", "event": { "name": "status", "print" : '
                                   '{ "service": { "implementation": "mysql_server", "tag": "query_statistics", "element": [ '
                                   '{ "name": "query_time",     "type": "double" }, '
                                   '{ "name": "bytes_sent",     "type": "longlong" }, '
                                   '{ "name": "bytes_received", "type": "longlong" }, '
                                   '{ "name": "rows_sent",      "type": "longlong" }, '
                                   '{ "name": "rows_examined",  "type": "longlong" } ] } } } } } }');

For the bytes_sent andbytes_received fields to be populated, the system variablelog_slow_extra must be set toON. If the system variable is value isOFF, a null value is written to the log file for these fields.

If you want to stop collecting the query statistics, use theaudit_log_filter_set_filter() audit log function to remove the filter, for example:

SELECT audit_log_filter_remove_filter('QueryStatistics');
Compressing Audit Log Files

Audit log file compression can be enabled for any logging format.

To configure audit log file compression, set theaudit_log_compression system variable at server startup. Permitted values areNONE (no compression; the default) andGZIP (GNU Zip compression).

If both compression and encryption are enabled, compression occurs before encryption. To recover the original file manually, first decrypt it, then uncompress it. SeeManually Uncompressing and Decrypting Audit Log Files.

Encrypting Audit Log Files

Audit log file encryption can be enabled for any logging format. Encryption is based on user-defined passwords (with the exception of the initial password that the audit log plugin generates). To use this feature, the MySQL keyring must be enabled because audit logging uses it for password storage. Any keyring component or plugin can be used; for instructions, see Section 8.4.4, “The MySQL Keyring”.

To configure audit log file encryption, set theaudit_log_encryption system variable at server startup. Permitted values areNONE (no encryption; the default) andAES (AES-256-CBC cipher encryption).

To set or get an encryption password at runtime, use these audit log functions:

mysql> SELECT KEY_ID FROM performance_schema.keyring_keys  
       WHERE KEY_ID LIKE 'audit_log%'  
       ORDER BY KEY_ID;  
+-----------------------------+  
| KEY_ID                      |  
+-----------------------------+  
| audit_log-20190415T152248-1 |  
| audit_log-20190415T153507-1 |  
| audit_log-20190416T125122-1 |  
| audit_log-20190416T141608-1 |  
+-----------------------------+

For additional information about audit log encryption functions, see Audit Log Functions.

When the audit log plugin initializes, if it finds that log file encryption is enabled, it checks whether the keyring contains an audit log encryption password. If not, the plugin automatically generates a random initial encryption password and stores it in the keyring. To discover this password, invokeaudit_log_encryption_password_get().

If both compression and encryption are enabled, compression occurs before encryption. To recover the original file manually, first decrypt it, then uncompress it. SeeManually Uncompressing and Decrypting Audit Log Files.

Manually Uncompressing and Decrypting Audit Log Files

Audit log files can be uncompressed and decrypted using standard tools. This should be done only for log files that have been closed (archived) and are no longer in use, not for the log file that the audit log plugin is currently writing. You can recognize archived log files because they have been renamed by the audit log plugin to include a timestamp in the file name just after the base name.

For this discussion, assume thataudit_log_file is set toaudit.log. In that case, an archived audit log file has one of the names shown in the following table.

Enabled Features Archived File Name
No compression or encryption audit.timestamp.log
Compression audit.timestamp.log.gz
Encryption audit.timestamp.log.pwd_id.enc
Compression, encryption audit.timestamp.log.gz.pwd_id.enc

As discussed in Naming Conventions for Audit Log Files,pwdid format is_pwdtimestamp-seq_. Thus, the names of archived encrypted log files actually contain two timestamps. The first indicates file rotation time, and the second indicates when the encryption password was created.

Consider the following set of archived encrypted log file names:

audit.20190410T205827.log.20190403T185337-1.enc
audit.20190410T210243.log.20190403T185337-1.enc
audit.20190415T145309.log.20190414T223342-1.enc
audit.20190415T151322.log.20190414T223342-2.enc

Each file name has a unique rotation-time timestamp. By contrast, the password timestamps are not unique:

To uncompress a compressed log file manually, usegunzip, gzip -d, or equivalent command. For example:

gunzip -c audit.timestamp.log.gz > audit.timestamp.log

To decrypt an encrypted log file manually, use theopenssl command. For example:

openssl enc -d -aes-256-cbc -pass pass:password -md sha256
    -in audit.timestamp.log.pwd_id.enc
    -out audit.timestamp.log

To execute that command, you must obtain_password_, the encryption password. To do this, useaudit_log_encryption_password_get(). For example, if the audit log file name isaudit.20190415T151322.log.20190414T223342-2.enc, the password ID is 20190414T223342-2 and the keyring ID isaudit-log-20190414T223342-2. Retrieve the keyring password like this:

SELECT audit_log_encryption_password_get('audit-log-20190414T223342-2');

If both compression and encryption are enabled for audit logging, compression occurs before encryption. In this case, the file name has .gz and._`pwdid`_.enc suffixes added, corresponding to the order in which those operations occur. To recover the original file manually, perform the operations in reverse. That is, first decrypt the file, then uncompress it:

openssl enc -d -aes-256-cbc -pass pass:password -md sha256
    -in audit.timestamp.log.gz.pwd_id.enc
    -out audit.timestamp.log.gz
gunzip -c audit.timestamp.log.gz > audit.timestamp.log
Audit Log File Encryption Prior to MySQL 8.0.17

This section covers the differences in audit log file encryption capabilities prior to and as of MySQL 8.0.17, which is when password history was implemented (which includes password archiving and expiration). It also indicates how the audit log plugin handles upgrades to MySQL 8.0.17 or higher from versions lower than 8.0.17.

Feature Prior to MySQL 8.0.17 As of MySQL 8.0.17
Number of passwords Single password only Multiple passwords permitted
Encrypted log file names .enc suffix .pwd_id.enc suffix
Password keyring ID audit_log audit_log-pwd_id
Password history No Yes

Prior to MySQL 8.0.17, there is no password history, so setting a new password makes the old password inaccessible, rendering MySQL Enterprise Audit unable to read log files encrypted with the old password. Should you anticipate a need to decrypt those files manually, you must maintain a record of previous passwords.

If audit log file encryption is enabled when you upgrade to MySQL 8.0.17 or higher from a lower version, the audit log plugin performs these upgrade actions:

Space Management of Audit Log Files

The audit log file has the potential to grow quite large and consume a great deal of disk space. If you are collecting the optional query time and size statistics, which are available from MySQL 8.0.30, this increases the space requirements. The query statistics are only supported with JSON format.

To manage the space used, employ these methods:

To configure audit log file space management, use the following system variables:

Note

For JSON-format log files, rotation also occurs when the value of theaudit_log_format_unix_timestamp system variable is changed at runtime. However, this does not occur for space-management purposes, but rather so that, for a given JSON-format log file, all records in the file either do or do not include the time field.

Note

Rotated (renamed) log files are not removed automatically. For example, with size-based log file rotation, renamed log files have unique names and accumulate indefinitely. They do not rotate off the end of the name sequence. To avoid excessive use of space:

The following sections describe log file rotation and pruning in greater detail.

Manual Audit Log File Rotation (Before MySQL 8.0.31)

Note

From MySQL 8.0.31, theaudit_log_flush variable and this method of audit log file rotation are deprecated; expect support to be removed in a future version of MySQL.

If audit_log_rotate_on_size is 0 (the default), no log rotation occurs unless performed manually. In this case, the audit log plugin closes and reopens the log file when theaudit_log_flush value changes from disabled to enabled. Log file renaming must be done externally to the server. Suppose that the log file name isaudit.log and you want to maintain the three most recent log files, cycling through the namesaudit.log.1 throughaudit.log.3. On Unix, perform rotation manually like this:

  1. From the command line, rename the current log files:
mv audit.log.2 audit.log.3  
mv audit.log.1 audit.log.2  
mv audit.log audit.log.1

This strategy overwrites the currentaudit.log.3 contents, placing a bound on the number of archived log files and the space they use. 2. At this point, the plugin is still writing to the current log file, which has been renamed toaudit.log.1. Connect to the server and flush the log file so the plugin closes it and reopens a new audit.log file: 

SET GLOBAL audit_log_flush = ON;

audit_log_flush is special in that its value remains OFF so that you need not disable it explicitly before enabling it again to perform another flush.

Note

If compression or encryption are enabled, log file names include suffixes that signify the enabled features, as well as a password ID if encryption is enabled. If file names include a password ID, be sure to retain the ID in the name of any files you rename manually so that the password to use for decryption operations can be determined.

Note

For JSON-format logging, renaming audit log files manually makes them unavailable to the log-reading functions because the audit log plugin can no longer determine that they are part of the log file sequence (seeSection 8.4.5.6, “Reading Audit Log Files”). Consider settingaudit_log_rotate_on_size greater than 0 to use size-based rotation instead.

Manual Audit Log File Rotation (From MySQL 8.0.31)

If audit_log_rotate_on_size is 0 (the default), no log rotation occurs unless performed manually.

To rotate the audit log file manually, run SELECT audit_log_rotate(); to rename the current audit log file and open a new audit log file. Files are renamed according to the conventions described inNaming Conventions for Audit Log Files.

The AUDIT_ADMIN privilege is required to use theaudit_log_rotate() function.

Managing the number of archived log files (the files that have been renamed) and the space they use is a manual task that involves removing archived audit log files that are no longer needed from your file system.

The content of audit log files that are renamed using theaudit_log_rotate() function can be read byaudit_log_read() function.

Automatic Audit Log File Rotation

If audit_log_rotate_on_size is greater than 0, settingaudit_log_flush has no effect. Instead, whenever a write to the current log file causes its size to exceed theaudit_log_rotate_on_size value, the audit log plugin automatically renames the current log file and opens a new current log file using the original name.

Automatic size-based rotation also occurs under these conditions:

The plugin renames the original file by inserting a timestamp just after its base name. For example, if the file name isaudit.log, the plugin renames it to a value such as audit.20210115T140633.log. The timestamp is a UTC value in_`YYYYMMDD`_T_`hhmmss`_ format. For XML logging, the timestamp indicates rotation time. For JSON logging, the timestamp is that of the last event written to the file.

If log files are encrypted, the original file name already contains a timestamp indicating the encryption password creation time (see Naming Conventions for Audit Log Files). In this case, the file name after rotation contains two timestamps. For example, an encrypted log file namedaudit.log.20210110T130749-1.enc is renamed to a value such asaudit.20210115T140633.log.20210110T130749-1.enc.

Audit Log File Pruning

The audit log plugin supports pruning of rotated JSON-format audit log files, if automatic log file rotation is enabled. To use this capability:

Pruning of JSON-format log files, if enabled, occurs as follows:

For pruning based on combined rotated log file size, if the combined size is greater than the limit specified byaudit_log_max_size, the audit log plugin removes the oldest files until their combined size does not exceed the limit.

For pruning based on rotated log file age, the pruning point is the current time minus the value ofaudit_log_prune_seconds. In rotated JSON-format log files, the timestamp part of each file name indicates the timestamp of the last event written to the file. The audit log plugin uses file name timestamps to determine which files contain only events older than the pruning point, and removes them.

Write Strategies for Audit Logging

The audit log plugin can use any of several strategies for log writes. Regardless of strategy, logging occurs on a best-effort basis, with no guarantee of consistency.

To specify a write strategy, set theaudit_log_strategy system variable at server startup. By default, the strategy value isASYNCHRONOUS and the plugin logs asynchronously to a buffer, waiting if the buffer is full. You can tell the plugin not to wait (PERFORMANCE) or to log synchronously, either using file system caching (SEMISYNCHRONOUS) or forcing output with async() call after each write request (SYNCHRONOUS).

For asynchronous write strategy, theaudit_log_buffer_size system variable is the buffer size in bytes. Set this variable at server startup to change the buffer size. The plugin uses a single buffer, which it allocates when it initializes and removes when it terminates. The plugin does not allocate this buffer for nonasynchronous write strategies.

Asynchronous logging strategy has these characteristics:

With asynchronous logging, the integrity of the log file may be compromised if a problem occurs during a write to the file or if the plugin does not shut down cleanly (for example, in the event that the server host exits unexpectedly). To reduce this risk, setaudit_log_strategy to use synchronous logging.

A disadvantage of PERFORMANCE strategy is that it drops events when the buffer is full. For a heavily loaded server, the audit log may have events missing.