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.

• 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.

Set a log format

You can set the auditing log format using the audit_log_format parameter. Currently, only the CSV format is supported.

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.

• 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.

• 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://$OSS_ACCESS_ID:$OSS_ACCESS_KEY@$OSS_HOST/test/audit/';
  

  Here, $OSS_ACCESS_ID, $OSS_ACCESS_KEY, and $OSS_HOST should be replaced with the actual access key ID, access key, and host address, 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 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;

(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 example, to set the compression algorithm for auditing logs to ZSTD:

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