Enable audit

This topic describes how to enable AuditTrail, set and view audit rules, and query audit records. You can perform audit operations of OceanBase Database as the ORAAUDITOR user. The default password for the user is ORAAUDITOR.

Enable AuditTrail and set the audit record storage location

1. Log on to the Oracle tenant with the sys account and unlock the ORAAUDITOR account.

   obclient> ALTER USER ORAAUDITOR ACCOUNT UNLOCK;
   

   Note

   The ORAAUDITOR user is created in OceanBase Database by default. By default, this user is locked. You must unlock this user before you enable AuditTrail.

   To modify the related parameters as the ORAAUDITOR user, you can execute the GRANT statement to grant the ALTER SYSTEM privilege to the ORAAUDITOR user.

   obclient> GRANT ALTER SYSTEM TO ORAAUDITOR;
   

2. Log on to the Oracle tenant as the ORAAUDITOR user.

3. Enable AuditTrail and set the storage location for the audit records.

   You can set the audit_trail parameter to enable AuditTrail. After it is set, the configuration immediately takes effect. Valid values of this parameter are:

   • NONE: disables AuditTrail. This is the default value.

   • OS: writes audit records into a local disk file.

   • DB: writes audit records into an internal table.

   • DB,EXTENDED/DB_EXTENDED: writes audit records and executed SQL statements into an internal table.

   For example:

   obclient> ALTER SYSTEM SET audit_trail = 'DB,EXTENDED';
   

   For more information about the audit_trailparameter, see audit_trail.

   Note

   You can choose an internal table or a local disk file to store the audit records.

   • If you choose to write audit records into a local disk file, the audit records are written into the <installation directory>/audit/ directory in asynchronous write mode. The default installation directory is /home/admin/oceanbase/audit/observer_xx_xxxxxx.aud.
   • If you choose to write audit records into an internal table, OceanBase Database automatically writes audit records into the system table. The internal table __all_tenant_security_audit_record stores the audit records. The internal table __all_virtual_audit_operation or __all_virtual_audit_action stores mappings of IDs and names related to the audit.

Set audit rules

Set audit rules by using the AUDIT statement. You can set audit rules of the statement and object operation types. For more information about the audit rules, see AUDIT.

Audit the INSERT, UPDATE, and DELETE operations of user1 on the tbl1 table.

obclient> AUDIT INSERT,UPDATE,DELETE ON user1.tbl1;

View audit rules

After you set the audit rules, you can view them by using a series of data dictionary views that are compatible with Oracle.

For example, you can execute the following statement to view the audit rules in the ALL_DEF_AUDIT_OPTS view:

obclient> SELECT * FROM SYS.ALL_DEF_AUDIT_OPTS;

View audit records

• View the audit records in a disk file.

  For example:

  $ pwd
  /home/admin/oceanbase/audit
  $ cat observer_47454_20200924144931.aud
  2020-10-04 13:48:10.635533
  LENGTH : '408'
  ACTION :[23] 'create table t1(c0 int)'
  ACTION_NUMBER:[1] '0'
  STATUS:[1] '0'
  ENTRYID:[1] '5'
  STATEMENTID:[1] '3'
  DBID:[16] '1100611139404836'
  SESSIONID:[10] '3221505515'
  PROXY SESSIONID:[4] '2816'
  TENANTID:[4] '1001'
  USERID:[16] '1100611139404786'
  DATABASE USER:[9] 'AUDITTEST'
  CLIENT TENANT:[3] 'ora'
  CLIENT USER:[9] 'AUDITTEST'
  CLIENT ADDRESS:[13] 'xx.xx.xx.xx'
  PROXY CLIENT ADDRESS:[13] 'xx.xx.xx.xx'
  

  Note

  An over-long file will be automatically truncated.

• View the audit records in an internal table.

  You can view the audit records in an internal table by using dictionary views compatible with Oracle.

  Dictionary views	Description
  DBA_AUDIT_EXISTS	Displays audit trail entries produced by AUDIT EXISTS and AUDIT NOT EXISTS.
  DBA_AUDIT_OBJECT	Displays audit trail entries for all objects in the database.
  DBA_AUDIT_SESSION	Displays all audit trail entries concerning CONNECT and DISCONNECT.
  DBA_AUDIT_STATEMENT	Displays audit trail entries for the GRANT, REVOKE, AUDIT, NOAUDIT, and ALTER SYSTEM statements in the database.
  DBA_AUDIT_TRAIL	Displays all audit trail entries.
  USER_AUDIT_OBJECT	Displays audit trail entries for objects accessible to the current user.
  USER_AUDIT_SESSION	Displays audit trail entries for connections and disconnections of the current user.
  USER_AUDIT_STATEMENT	Displays audit trail entries for the GRANT, REVOKE, AUDIT, NOAUDIT, and ALTER SYSTEM statements issued by the current user.
  USER_AUDIT_TRAIL	Displays all audit trail entries concerning the current user.

  For example, you can execute the following statement to view the audit records in the DBA_AUDIT_TRAIL table:

  obclient> SELECT USERNAME,USERHOST,TIMESTAMP,SQL_TEXT FROM SYS.DBA_AUDIT_TRAIL;
  +----------+-------------+-----------+-----------------------------------------------------------------+
  | USERNAME |   USERHOST  | TIMESTAMP | SQL_TEXT   |
  +----------+-------------+-----------+-----------------------------------------------------------------+
  |   AUD    | xx.xx.xx.xx | 29-OCT-20 | DISCONNECT |
  |   AUD    | xx.xx.xx.xx | 29-OCT-20 | CONNECT    |
  +----------+-------------+-----------+-----------------------------------------------------------------+