A properly designed and implemented statement-level auditing policy can help to detect suspicious activity, especially in cases in which we have a small number of statements executed frequently on the same objects. However, if there are thousands of statements being executed per minute, then it may be more difficult to determine if any of those executions are tied to activities we would need to investigate. In those situations it may be more beneficial to implement object-level auditing against the sensitive objects. In this case, it would be easier to audit the sensitive objects separately using object auditing features.
In this recipe we will audit the table EMPLOYEES for all statements, and the emp_details_hr view from the schema HR for the SELECT statements.
- Connect as the
systemuser and start the audit for theEMPLOYEEStable as follows:SQL> conn system Enter password: Connected. SQL> audit all on hr.employees by access; Audit succeeded
- Connect as the user
HRand issue twoSELECTstatements against theemployeestable, one erroneous and one successful as follows:SQL> conn HR Enter password: Connected. SQL> select department_name from employees where email='SKING'; select department_name from employees where email='SKING' * ERROR at line 1: ORA-00904: "DEPARTMENT_NAME": invalid identifier SQL> select first_name,last_name from employees where email='SKING'; FIRST_NAME LAST_NAME -------------------- ------------------------- Steven King SQL>
When an object is involved in a specific audited statement, then a record will be created. The audit records are generated independently of whether the transaction is committed or not. This is the reason why we can audit successful or failed statements, or both.
All objects can be audited, except for functions and procedures created in packages. The auditing scope for the object could be by session and by access. The resulting records can be queried from the dictionary views DBA_AUDIT_COMMON_TRAIL, DBA_AUDIT_STATEMENTS, and USER_AUDIT_STATEMENTS.
If no statement type is specified, the object will be audited by the ALTER, EXECUTE, INSERT, SELECT, AUDIT, GRANT, LOCK, UPDATE, COMMENT, FLASHBACK, READ, DELETE, INDEX, and RENAME statements.
To find out the current audited objects, you can query the DBA_OBJ_AUDIT_OPTS dictionary view.