When you enable security audit, you must set the storage method for the audit records. You can choose to write the audit records into an internal table or a local disk file. You can view the audit records at the specified location.
View the audit records in a disk file
The audit records are written into the <installation directory>/audit/ directory in asynchronous write mode. The default path of the file is /home/admin/oceanbase/audit/observer_xx_xxxxxx.aud.
Here is an 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 excessively long file will be automatically truncated.
View the audit records in an internal table
The audit records are directly written 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.
You can view the audit records in an internal table by using dictionary views compatible with Oracle.
| Dictionary view | 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. |
View the audit records in DBA_AUDIT_TRAIL. Here is an example:
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 |
+----------+-------------+-----------+-----------------------------------------------------------------+