Audit user operations

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

Limitations

Currently, only the Oracle mode of OceanBase Database supports the security audit feature.

Audit successful DDL operations on all tables by a specific user

1. Log in to the database as the ORAAUDITOR user and audit user1's successful DDL operations on all tables.

   obclient> AUDIT TABLE BY user1 WHENEVER SUCCESSFUL;
   

2. View the audit records of user1's successful DDL operations in the DBA_AUDIT_TRAIL view.

   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 by a specific user

1. Log in to the database as the ORAAUDITOR user and audit user1's all operations, including the login and logoff operations.

   obclient> AUDIT ALL STATEMENTS BY user1;
   

2. View the audit records of user1's all operations in the DBA_AUDIT_TRAIL view.

   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 login operations
• Audit specified database objects