Audit user operations

This topic describes how to use the security audit feature to audit operations of a specified user.

Limitations

OceanBase Database supports security audit only in Oracle mode.

Audit successful DDL operations performed on all tables by a specified user

1. Log on to the database as the ORAAUDITOR user and specify to audit successful DDL operations performed by user1 on all tables.

   obclient> AUDIT TABLE BY user1 WHENEVER SUCCESSFUL;
   

2. Query the DBA_AUDIT_TRAIL table to view the audit result of the successful DDL operations of user1.

   obclient> SELECT USERNAME,USERHOST,TIMESTAMP,SQL_TEXT FROM SYS.DBA_AUDIT_TRAIL WHERE USERNAME = 'USER1';
   +----------+----------------+-----------+--------------------------------------+
   | USERNAME | USERHOST       | TIMESTAMP | SQL_TEXT                             |
   +----------+----------------+-----------+--------------------------------------+
   
   | USER1    | 100.10.10.10   | 23-DEC-21 | create table tbl1(col1 int,col2 int) |
   | USER1    | 100.10.10.10   | 23-DEC-21 | DROP TABLE tbl1                      |
   +----------+----------------+-----------+--------------------------------------+
   2 rows in set
   

Audit all operations of a specified user

1. Log on to the database as the ORAAUDITOR user and specify to audit all operations of user1, including the logon and logoff operations.

   obclient> AUDIT ALL STATEMENTS BY user1;
   

2. Query the DBA_AUDIT_TRAIL table to view the audit result of operations of user1.

   obclient> SELECT USERNAME,USERHOST,TIMESTAMP,SQL_TEXT FROM SYS.DBA_AUDIT_TRAIL WHERE USERNAME = 'USER1';
   +----------+----------------+-----------+--------------------------------------+
   | USERNAME | USERHOST       | TIMESTAMP | SQL_TEXT                             |
   +----------+----------------+-----------+--------------------------------------+
   | USER1    | 100.10.10.10   | 22-DEC-21 | CONNECT                              |
   | USER1    | 100.10.10.10   | 23-DEC-21 | DISCONNECT                           |
   | USER1    | 100.10.10.10   | 23-DEC-21 | CONNECT                              |
   | USER1    | 100.10.10.10   | 23-DEC-21 | create table tbl1(col1 int,col2 int) |
   | USER1    | 100.10.10.10   | 23-DEC-21 | DROP TABLE tbl1                      |
   | USER1    | 100.10.10.10   | 23-DEC-21 | GRANT CREATE SESSION TO user2        |
   | USER1    | 100.10.10.10   | 23-DEC-21 | CREATE ROLE role1                    |
   | USER1    | 100.10.10.10   | 23-DEC-21 | DROP ROLE role1                      |
   +----------+----------------+-----------+--------------------------------------+
   8 rows in set
   

References

For more information about audit operations, see the following topics:

• Enable security audit
• Set and view audit rules
• View audit records
• Audit user logon operations
• Audit specified database objects