Set audit rules

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

Set the log strategy

You can set the audit log strategy by using the audit_log_strategy parameter. The supported write strategies are ASYNCHRONOUS, PERFORMANCE, and SYNCHRONOUS. You can select a write strategy based on your business requirements to achieve a balance between performance and log integrity.

• ASYNCHRONOUS: indicates asynchronous log writing. When the buffer is full, the system will wait synchronously. This is the default strategy.

• PERFORMANCE: indicates asynchronous log writing. When the buffer is full, data will be discarded.

• SYNCHRONOUS: indicates synchronous log writing.

For example, you can set the audit log writing strategy to asynchronous writing, and set the system to wait synchronously when the buffer is full.

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

When asynchronous log writing is used, you can use the audit_log_buffer_size parameter to specify the buffer size.

Set the log format

You can set the audit log format by using the audit_log_format parameter. Only the CSV format is supported.

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

Set data desensitization

You can set the SQL statements to be recorded in audit logs by using the audit_log_query_sql parameter. The value can be ALL or NONE.

• ALL: records all Query SQL statements.

• NONE: does not record Query SQL statements.

For example, you can set the SQL statements to be recorded in audit logs to NONE.

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

When Query SQL statements are recorded, the audit log file desensitizes the following statements:

• CMD: For statements that contain sensitive information such as passwords and AccessKeys/Secrets, such as CREATE USER, initiate backup and restore, and modify parameters, the system records them as ***. For other CMD statements, the system records the original SQL.
• Statements whose types cannot be identified: For example, if the Parser fails, the system records them as ***.

Set the output path

You can set the audit log output path by using the audit_log_path parameter. The system supports local and cloud storage. If you do not specify the value of audit_log_path, the system uses the current working path ${WORK_DIR}/audit by default.

• Local storage scenario

  In the same tenant, audit logs of different OBServer nodes are written to different directories. In the same cluster, users may set the output path of different tenants to the same directory. In this case, the directory structure includes a tenant_id layer. The local output directory structure is shown in the following figure:

  - ${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 configuring the audit log output directory to local storage.

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

• Cloud storage scenario

  OceanBase Database supports multiple OBServer nodes writing to the same oss/nfs directory. Considering the performance of concurrent appending writes and the need for subsequent compression and encryption operations, only archived files are stored in cloud storage. Appending writes are performed to files in the local audit directory. When downstream consumers access audit logs in cloud storage, some of the latest records may only exist on the local OBServer nodes and are not visible to downstream consumers.

  Here is an example of configuring the audit log output directory 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';
  

  In this example, $OSS_HOST, $OSS_ACCESS_ID, and $OSS_ACCESS_KEY need to be replaced with the actual HOST address, AccessKey ID, and AccessKey, respectively. The AccessKey is stored in an encrypted manner.

Set log archiving

Audit logs are continuously appended to the audit.log file. You can set 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. Archiving only controls the size of individual log files and does not involve file cleanup.

For example, you can set the size of a single audit log file to 256 MB.

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

Configure log cleanup

You can configure the audit_log_max_size and audit_log_prune_seconds parameters to control the capacity and retention period of archived audit logs. Both parameters can be configured simultaneously. When a new log file is generated during log archiving, the system checks whether to clean up the logs. The total log size is calculated separately in each OBServer directory.

Set the audit_log_max_size parameter to control the maximum size of audit logs saved on a single OBServer node. The default value is 0M, indicating that audit logs are not saved.

For example, set the maximum size of audit logs saved on a single OBServer node to 1G.

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

Set the audit_log_prune_seconds parameter to control the maximum retention period of audit logs. The unit is seconds, and you do not need to specify the unit when configuring the parameter. The default value is 0, indicating that audit logs are not saved.

For example, set the maximum retention period of audit logs to 2h.

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

Configure log compression (optional)

You can configure the audit_log_compression parameter to enable log compression. If compression is enabled, compressed files are generated during log archiving. The supported compression algorithm is ZSTD, and streaming compression is supported. You can use the external tool zstd to decompress the compressed files.

For example, set the compression algorithm for audit logs to ZSTD.

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