Set auditing rules

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, 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, see the related documentation 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, see the related documentation 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, see the related documentation 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, 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 the parameters, see the related documents 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 the parameters, see the related documents 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 for audit logs. After encryption is enabled, archived log files can be identified by their keys, making it easier to combine with compression, rotation, and cleanup strategies. Follow the steps below as needed.

Enable log encryption

Use the audit_log_encryption parameter to enable or disable audit log encryption. Valid values are:

• NONE: Disable encryption. This is the default value.
• AES: Enable encryption using AES-256-CBC stream encryption during archiving.

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

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

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

Archive logs and generate passwords

Use the following function to archive logs and generate passwords:

• AUDIT_LOG_ENCRYPTION_PASSWORD_SET(): This function has no parameters. It triggers the archiving of the current audit log being written and generates a new encryption key. Subsequent archived files will use this new key for encryption. For more information about this function, see the related documentation at the end of this topic.

Here is an example:

obclient [test]> SELECT AUDIT_LOG_ENCRYPTION_PASSWORD_SET();

Retrieve the encryption password

Use the following function to retrieve the encryption password for a log file:

• AUDIT_LOG_ENCRYPTION_PASSWORD_GET(pwd_id): The pwd_id parameter is an integer that represents the timestamp suffix of the log file. This function returns the encryption password for the specified log file based on its timestamp suffix. If no parameter is provided, it retrieves the password for the latest pwd_id. For more information about this function, see the related documentation at the end of this topic.

Here is an example of retrieving the encryption password for the latest archived log:

obclient [test]> SELECT AUDIT_LOG_ENCRYPTION_PASSWORD_GET();

Here is an example of retrieving the encryption password for a specific log:

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

Decrypt logs

Audit log encryption uses the openssl interface. To encrypt or decrypt a log file using openssl, you must specify the key and iv, or the password. Here is an example command:

# -d indicates decryption
# -aes-256-cbc indicates the use of the AES-256-CBC algorithm
# -pass pass:password indicates the use of the specified password
# -md sha256 indicates the use of the SHA-256 algorithm
# -in encrypted_log_file indicates the input encrypted log file
# -out decrypted_file indicates the output decrypted log file
obclient > openssl enc -d -aes-256-cbc  -pass pass:password -md sha256 -in encrypted_log_file -out decrypted_file

Appendix: Log file naming conventions

The naming conventions for archived audit log files include the timestamp suffix and additional segments based on whether compression and encryption are enabled (pwd_id is the key identifier, corresponding to the view and function parameters):

In this format, timestamp follows the same rules as the log rotation file naming convention described in the "Set Output Path" section. If compression is enabled, the compression extension is determined by the configuration and implementation (.zst for the ZSTD compression algorithm).

Related Documentation

• Set log strategy: audit_log_strategy
• Set the size of the log buffer during asynchronous writes: audit_log_buffer_size
• Set log format: audit_log_format
• Select SQL statements from audit records to set data desensitization: audit_log_query_sql
• Set log output path: audit_log_path
• Set log archive size: audit_log_rotate_on_size
• Set log cleanup size: audit_log_max_size
• Set log cleanup time: audit_log_prune_seconds
• Set log compression: audit_log_compression
• Set log encryption: audit_log_encryption
• Log archive and decryption settings function: AUDIT_LOG_ENCRYPTION_PASSWORD_SET
• Log archive and decryption query function: AUDIT_LOG_ENCRYPTION_PASSWORD_GET
• Set log rotation: audit_log_rotate_on_size