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, and DRAPHEAL, and audit only if the statement returns successfully. As the system user execute the following statement:
    SQL> conn HR
Enter password:
Connected.

SQL> audit alter table by HR,SMAVRIS,DRAPHEAL WHENEVER SUCCESS;

Audit succeeded.
  4. To audit all alter table statements at instance level whenever successful or unsuccessful, connect and execute as follows:
    SQL> audit alter table by access;
  5. To audit select table statements by SMAVRIS and DRPAHEAL execute the following:
    SQL> audit select table by HR,SMAVRIS,DRAPHEAL by access;

Audit succeeded.

SQL>
  6. To audit all statements issued at instance level by access, whenever successful, issue the following:
    SQL> audit all statements by access whenever successful;

Audit succeeded.

SQL>
  7. Connect as user DRAPHEAL and alter the column REGION_NAME as follows:
    SQL> Connect DRAPHEAL
Enter password:
Connected.

SQL> alter table hr.hr_emp_details_aud modify region_name varchar2(50);

Table altered.
  8. Connect as user SMAVRIS and alter the column country_name and add a column named additional as follows:
    SQL> conn SMAVRIS
Enter password:
Connected.
SQL> alter table hr.hr_emp_details_aud modify country_name varchar2(50);
Table altered.
SQL> 
SQL> alter table hr.hr_emp_details_aud add additional number;
Table altered.
  9. As SMAVRIS issue a count from hr_emp_details_aud as follows:
    SQL> select count(*) from  hr.hr_emp_details_aud;

  COUNT(*)
----------
       106

SQL>
  10. Drop column additional from the table hr.hr_emp_details_aud as follows:
    SQL> alter table hr.hr_emp_details_aud drop column additional;

Table altered.

SQL>

How it works...

The audit records related to statements can be found in dba_audit_common_trail, dba_audit_trail, dba_audit_statement, and user_audit_statement dictionary views.

There's more...

To find out which statements are currently audited you can query the DBA_STMT_AUDIT_OPTS dictionary view.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
3.15.168.214