Set an auditing rule

After you enable security auditing, you must set specific security auditing rules to audit user operations.

Set a log strategy

You can set the auditing log strategy by using the audit_log_strategy parameter. This parameter determines how auditing logs are written. Valid values are ASYNCHRONOUS, PERFORMANCE, and SYNCHRONOUS. Choose a log strategy based on your business needs to balance performance and log integrity. For more information about this parameter, see the related documentation at the end of this topic.

• ASYNCHRONOUS: writes logs asynchronously. When the buffer is full, the system waits for the buffer to be synchronized. This is the default strategy.

• PERFORMANCE: writes logs asynchronously. When the buffer is full, data is discarded.

• SYNCHRONOUS: writes logs synchronously.

For example, to set the auditing log write strategy to asynchronous mode (with synchronous wait when the buffer is full):

obclient [test]> ALTER SYSTEM SET audit_log_strategy='ASYNCHRONOUS';

When asynchronous log writing is enabled, you can control the buffer size with the audit_log_buffer_size parameter. For more information about this parameter, see the related topic at the end of this topic.

Set a log format

You can set the auditing log format using the audit_log_format parameter. Currently, only the CSV format is supported. For more information about this parameter, see the related topic at the end of this topic.

obclient [test]> ALTER SYSTEM SET audit_log_format='CSV';

Configure data masking

You can specify whether to record SQL statements in auditing logs using the audit_log_query_sql parameter. Valid values are ALL and NONE. For more information about this parameter, see the related topic at the end of this topic.

• ALL: Records all query SQL statements.

• NONE: Does not record query SQL statements.

For example, to prevent auditing logs from recording query SQL statements, set the audit_log_query_sql parameter to NONE:

obclient [test]> ALTER SYSTEM SET audit_log_query_sql='NONE';

When query SQL statements are recorded, the auditing file applies data masking to the following types of statements:

• CMD: For statements containing sensitive information such as passwords or AK/SK (for example, create user, backup/restore, modify configuration), the system records *** for these CMD statements. For other CMD statements, the original SQL is recorded.
• Unable to determine statement type: If the statement type cannot be identified (such as when parsing fails), the system records ***.

Set the output path

You can use the audit_log_path parameter to specify the output path for auditing logs, supporting both local and cloud storage. If you do not specify a value for audit_log_path, the default path ${WORK_DIR}/audit is used. For more information about this parameter, see the related documentation at the end of this topic.

• Local storage

  For the same tenant, auditing logs from different OBServer nodes are stored in separate directories. In a single cluster, you can configure different tenants to use the same output path; in this case, the directory structure includes an additional tenant_id layer. The directory structure for local storage is as follows:

  - ${audit_log_path}
  - tenant_id_1
  - ip1:port1
      - audit.log.20231031080646317[.zst]
      - audit.log.20231031084835263[.zst]
      - audit.log
  - ip2:port2
      - audit.log.20231031072457838[.zst]
      - audit.log.20231031093023607[.zst]
      - audit.log.20231031101213751[.zst]
      - audit.log
  - tenant_id_2
  

  Here is an example of setting the output directory for auditing logs to a local path:

  obclient [test]> ALTER SYSTEM SET audit_log_path='file:///logs/audit';
  

• Cloud storage

  OceanBase Database allows multiple OBServer nodes to output logs to the same oss/nfs directory. To ensure the performance of concurrent append writes and to support later compression and encryption, only archived files are stored in cloud storage. Append writes occur in local files under the audit directory. When consuming auditing logs from cloud storage, some of the most recent records may exist only on the local OBServer node and are not yet visible downstream.

  Here is an example of setting the output directory for auditing logs to OSS:

  obclient [test]> ALTER SYSTEM SET audit_log_path = 'oss://ob-audit/audit/?host=$OSS_HOST&access_id=$OSS_ACCESS_ID&access_key=$OSS_ACCESS_KEY';
  

  Here, $OSS_HOST, $OSS_ACCESS_ID, and $OSS_ACCESS_KEY should be replaced with the actual host address, access key ID, and access key, respectively. The access key will be stored in encrypted form.

Configure log archiving

Auditing records are continuously appended to the audit.log file. You can configure log archiving by setting the audit_log_rotate_on_size parameter. When the size of audit.log reaches the specified value, a new file is automatically generated. Log archiving only controls the size of individual log files; it does not handle file cleanup. For more information about this parameter, see the related documentation at the end of this topic.

For example, to set the maximum size of each auditing log file to 256 MB:

obclient [test]> ALTER SYSTEM SET audit_log_rotate_on_size='256M';

Set log cleanup

The audit_log_max_size and audit_log_prune_seconds parameters control the capacity and retention period of archived auditing logs. Both parameters can take effect simultaneously. When a new log file is generated during log archiving, the system checks whether log cleanup is needed. The total log size is calculated separately for each OBServer.

To control the maximum auditing log size on a single OBServer, set the audit_log_max_size parameter. The default value is 0 MB, which means auditing logs are not retained.

For example, to set the maximum auditing log size on a single OBServer to 1 GB:

obclient [test]> ALTER SYSTEM SET audit_log_max_size='1G';

To control the maximum auditing log retention period, set the audit_log_prune_seconds parameter. The default value is 0, which means auditing logs are not retained. The value is specified in seconds, and you do not need to include a unit when setting the parameter.

For example, to set the maximum auditing log retention period to 2 hours:

obclient [test]> ALTER SYSTEM SET audit_log_prune_seconds=7200;

For more information about this parameter, see the related documentation at the end of this topic.

(Optional) Set log compression

You can set the audit_log_compression parameter to enable log compression. If log compression is enabled, archived log files are generated in compressed format. Only the ZSTD compression algorithm is supported, and streaming compression is available. Compressed files can be directly decompressed using the external tool zstd. For more information about this parameter, see the related documentation at the end of this topic.

For example, to set the compression algorithm for auditing logs to ZSTD:

obclient [test]> ALTER SYSTEM SET audit_log_compression='ZSTD';

Enable log encryption (optional)

MySQL tenants support encrypted storage of audit logs. After enabling encryption, archived log files can be stored on disk with a key identifier, facilitating combined use with strategies such as compression, rotation, and cleanup. Refer to the steps in the following section to perform the operations in sequence as needed.

Enable log encryption

The audit_log_encryption parameter controls whether to enable audit log encryption. Valid values include:

• NONE: No encryption is enabled. This is the default value.
• AES: Encryption is enabled. The algorithm used is stream encryption with AES-256-CBC (stream encryption during archive write).

For example, to enable audit log encryption for the current tenant:

obclient [test]> ALTER SYSTEM SET audit_log_encryption='AES';

For a complete description of the parameters, see the related documentation at the end of this topic.

Log archiving and password generation

Call the following functions to implement log archiving and password generation:

• AUDIT_LOG_ENCRYPTION_PASSWORD_SET(): No parameters. This function triggers the archiving of the currently writing audit log and generates a new encryption key. Subsequent newly generated archive files will be encrypted using the new key. For more information, see the related documentation at the end of this topic.

Example:

obclient [test]> SELECT AUDIT_LOG_ENCRYPTION_PASSWORD_SET();

Log password retrieval

Call the following functions to retrieve the encryption password of a log file:

• AUDIT_LOG_ENCRYPTION_PASSWORD_GET(pwd_id): The pwd_id parameter is an integer that indicates the key identifier after the .enc. segment in the encrypted archive file name, such as 1001. If this parameter is omitted, the password corresponding to the latest pwd_id is returned. For more information, see the related documentation at the end of this topic.

Retrieve the encryption password of the latest archived log:

obclient [test]> SELECT AUDIT_LOG_ENCRYPTION_PASSWORD_GET();

Retrieve the encryption password of a specified log:

obclient [test]> SELECT AUDIT_LOG_ENCRYPTION_PASSWORD_GET('1682089200');

Log decryption

Audit log encryption uses interfaces provided by OpenSSL. To encrypt or decrypt log files with OpenSSL, you must specify a key and IV, or a password. Example command:

# -d indicates decryption.
# -aes-256-cbc specifies to use the AES-256-CBC algorithm.
# -pass pass:password indicates to use password as the password.
# -md sha256 indicates that the SHA-256 algorithm is used.
# -in encrypted_log_file specifies the input encrypted log file.
# -out decrypted_file specifies the output decrypted log file.
openssl enc -d -aes-256-cbc -pass pass:password -md sha256 -in encrypted_log_file -out decrypted_file

Appendix: Log file name format

The name of an archived audit log file consists of a timestamp suffix, followed by a segment indicating whether compression and/or encryption is enabled (pwd_id serves as the key identifier, corresponding to view and function parameters):

Here, timestamp follows the same rotation naming rule as demonstrated in the "Set output path" section of this topic. If compression is enabled, the compression extension depends on the configuration and implementation (currently, when the compression algorithm is ZSTD, it is .zst).

References

• Set a log strategy: audit_log_strategy
• Set the log buffer size during asynchronous write: audit_log_buffer_size
• Set the log format: audit_log_format
• Select SQL statements from audit records for data desensitization: audit_log_query_sql
• Set the log output path: audit_log_path
• Set the log archive size: audit_log_rotate_on_size
• Set the log cleanup size: audit_log_max_size
• Set the log cleanup time: audit_log_prune_seconds
• Set log compression: audit_log_compression
• Set log encryption: audit_log_encryption
• Functions for setting log archiving and decryption: AUDIT_LOG_ENCRYPTION_PASSWORD_SET
• Functions for querying log archiving and decryption: AUDIT_LOG_ENCRYPTION_PASSWORD_GET