Audit user operations

The auditing feature can be used to audit operations performed by specified users.

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

1. Log in as the ORAAUDITOR user and enable auditing for all successful DDL operations performed by user1 on tables.

   obclient> AUDIT TABLE BY user1 WHENEVER SUCCESSFUL;
   

2. View auditing records for successful operations performed by user1 in the DBA_AUDIT_TRAIL table.

   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, including login and logout

1. Log in as the ORAAUDITOR user and enable auditing for all operations performed by user1.

   obclient> AUDIT ALL STATEMENTS BY user1;
   

2. View auditing records for user1 in the DBA_AUDIT_TRAIL table.

   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 security auditing, see:

• Enable auditing
• Set up and view auditing rules
• View auditing records
• Audit user login
• Audit a specified database object