Security audit

Data encryption and access control greatly reduce security risks. However, OceanBase Database still needs to record the behaviors of authorized users to prevent risks due to the leakage of user logon information or the abuse of access permissions. Security audit is an effective tool for tracking user behaviors. It allows enterprises to meet higher data security and compliance requirements.

Enable security audit

To enable security audit, you can set the audit_trail parameter as needed. The parameter takes effect immediately after you specify the value. Valid values are the following ones:

• NONE: disables security audit.

• OS: writes audit records to local files.

• DB: writes audit records to internal tables.

• DB,EXTENDED: writes records to internal tables and the records include the executed SQL statements.

The administrator has the highest authority and can perform many operations. Therefore, OceanBase Database provides separate audit configurations for the administrator. The tenant-level audit_sys_operations parameter determines whether to record the behaviors of the administrator.

Set audit rules

You must log on as the built-in user ORAAUDITOR to set audit rules, which are divided into the following two types:

• Statement audit: the audit of specific operations. You do not specify the objects, but you can specify whether the audit rules apply to specific or all users.

• Object audit: the audit of the operations that are performed on specified objects. You can specify whether the audit rules apply to specific or all users.

An audit rule is also a schema object. To configure each rule, AUDIT and NOAUDIT statements are executed.

Audit process

Security audit works after an SQL statement is executed but before the return of data packets. The audit process is divided into the following steps:

1. Check whether the current user is auditable based on the tenant, username, and parameters.

2. Parse auditable operations specified in SQL statements. An SQL statement can contain multiple operations. For example, insert into t1 select * from t2, t3 contains the following three operations: (insert, t1), (select, t2), and (select t3).

3. Check whether each operation hits an audit rule.

4. Generate an audit record for each operation that hit an audit rule. Save the audit records in internal tables or files based on the parameters.

Audit records

Audit files are stored in the audit/ directory, and the file name follows the observer_${pid}_${timestamp}.aud format. The API provided by ObLogger is used to write audit files. Other features are the same as other system logs. For example, an audit file is split when its size reaches 256 MB.

Writing audit records to internal tables involves the following characteristics:

• When the audit_trail parameter is set to DB, SQL statements executed by the user are not recorded. To record SQL statements executed by the user, set the audit_trail parameter to DB,EXTENDED.

• The insertion of audit records is independent of user transactions. This means that audit records are retained even if user transactions are rolled back.

• If security audit fails to write audit records to internal tables, it saves audit records to audit files.