Set audit rules

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

Specify the log strategy

You can use the audit_log_strategy parameter to specify the log audit strategy. Three log write strategies are supported: ASYNCHRONOUS, PERFORMANCE, and SYNCHRONOUS. You can select a log write strategy based on your business needs to achieve a balance between performance and log integrity.

• ASYNCHRONOUS: specifies to asynchronously write logs. The write operation will wait when the buffer is full. This strategy is used by default.

• PERFORMANCE: specifies to asynchronously write logs. Data will be discarded when the buffer is full.

• SYNCHRONOUS: specifies to synchronously write logs.

The following example specifies to asynchronously write logs for audit. In this strategy, the write operation will wait when the buffer is full.

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

When you specify to asynchronously write logs, you can use the audit_log_buffer_size parameter to specify the buffer size.

Specify the log format

You can use the audit_log_format parameter to specify the format of audit logs. At present, only the CSV format is supported.

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

Specify data desensitization

You can use the audit_log_query_sql parameter to specify the SQL statements to be included in audit records. Valid values are as follows:

• ALL: specifies to record all SQL query statements.

• NONE: specifies not to record SQL query statements.

The following example specifies not to include SQL query statements in audit records.

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

When SQL query statements are recorded, the following types of statements are desensitized:

• CMD statements: User creation, backup/restore initiation, and parameter modification statements that contain passwords, AccessKey pairs, and other sensitive information are recorded as ***. Other CMD statements can be directly recorded.
• Unknown statements: Statements with parser errors are also recorded as ***.

Specify the output path

You can use the audit_log_path parameter to specify the output path for audit logs. Local storage and cloud storage are supported. If this parameter is not specified, the ${WORK_DIR}/audit path is used by default.

• Local storage

  Audit logs of different OBServer nodes in a tenant are stored to different directories. You can specify the same output path for different tenants in a cluster. In this case, a level that specifies the tenant ID is added in the directory structure. The directory structure of a local output path 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
  

  You can execute the following statement to specify a local output path for audit logs:

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

• Cloud storage

  OceanBase Database allows you to store audit logs of multiple OBServer nodes to the same directory in Alibaba Cloud Object Storage Service (OSS) or Network File System (NFS). Logs will be concurrently appended to files in the local 'audit' directory, and the local files will be accessed in subsequent compression and encryption. Therefore, only archived files are moved to cloud storage, thus ensuring the access performance. When audit logs in cloud storage are consumed downstream, a part of the latest audit logs locally stored on OBServer nodes are unavailable for the downstream consumers.

  You can execute the following statement to specify an OSS directory as the output path for audit logs:

  obclient [test]> ALTER SYSTEM SET audit_log_path='oss://$OSS_ACCESS_ID:$OSS_ACCESS_KEY@$OSS_HOST/test/audit/';
  

  You must replace $OSS_ACCESS_ID, $OSS_ACCESS_KEY, and $OSS_HOST with the actual access key ID, access key, and host address. The access key is used to encrypt the storage.

Specify log archiving

Audit logs are continuously appended to the audit.log file. You can use the audit_log_rotate_on_size parameter to enable automatic log archiving. When the size of the audit.log file reaches the specified value, a new file is automatically generated. The log archiving feature controls the size of a single log file without clearing files.

You can execute the following statement to set the maximum size allowed for a single file to 256 MB.

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

Specify log cleanup

You can use the audit_log_max_size and audit_log_prune_seconds parameters to specify the maximum size and retention period of archived audit logs. The parameters can take effect at the same time. Each time a new archive log file is generated, the system checks whether logs need to be cleared. The total size of logs is calculated separately in the directory of each OBServer node.

audit_log_max_size specifies the maximum size of audit logs that can be retained on a single OBServer node. The default value is 0M, indicating that audit logs are not to be retained.

The following example sets the maximum size of audit logs that can be retained on a single OBServer node to 1 GB.

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

audit_log_prune_seconds specifies the maximum retention period in seconds of audit logs. You do not need to specify the unit of the value. The default value is 0, indicating that audit logs are not to be retained.

The following example sets the maximum retention period of audit logs to 2 hours.

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

(Optional) Specify log compression

You can use the audit_log_compression parameter to specify whether to enable log compression. If log compression is enabled, compressed files are generated during log archiving. The Zstandard (zstd) and streaming compression algorithms are supported. You can decompress the files by using the external tool zstd.

The following example sets the compression algorithm for audit logs to ZSTD.

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