Auditing statements

Statement auditing along with session audits is another important tracing method for capturing suspicious operations performed by a user. Statement audits apply both for DML and DDL statements.

In this recipe we will implement statement audit and we will create a new table named HR_EMP_DETAILS_AUD from EMP_DETAILS_VIEW.

Getting ready

All steps from this recipe will be performed on the database HACKDB.

How to do it...

1. Connect as user HR and create table HR_EMP_DETAILS_AUD as follows:

   SQL> conn HR
   Enter password:
   Connected.
   SQL> create table hr_emp_details_aud as select * from emp_details_view;
   
   Table created.
   

2. Grant all privileges to SMAVRIS and DRAPHEAL on the HR_EMP_DETAILS_AUD table as follows:

   SQL> grant alter on hr.hr_emp_details_aud to smavris,drapheal;
   Grant succeeded.
   
   Audit succeeded.
   
   SQL>
   

3. You may want to limit audit scope to specific users. By default both successful and unsuccessful events will be audited. In our case, limit the audit scope to HR, SMAVRIS...