Databases Reference
In-Depth Information
The following week, Janice turns off the CREATE TABLE auditing with the fol-
lowing command:
noaudit create table by scrawford;
Noaudit succeeded.
Rows in the SYS.AUD$ table (and as a result, the DBA_AUDIT_TRAIL view)
remain there until they are removed by the DBA.
Object Auditing
Object auditing allows the DBA to monitor access to specific objects in the data-
base, along with the operations performed on those objects. For example, the DBA
may want to see how often SELECT statements occur on a particular table in a cer-
tain period of time versus how many UPDATE statements occur against that same
table. As with statement auditing, object auditing can also be further refined to
audit only successful or only unsuccessful statements against the object.
Janice, the DBA, wants to find out how often the EMPLOYEES table in the HR
schema is being accessed by SELECT, INSERT, UPDATE, and DELETE statements and
by whom. She decides that auditing the table for a few hours one day would give
her the information that she needs. The AUDIT statement she runs looks like this:
audit select, insert, update, delete
on hr.employees;
Audit succeeded.
After a few hours, she reviews the data dictionary view DBA_AUDIT_TRAIL to
see what kind of activity has been performed against the EMPLOYEES table:
select username, obj_name,
to_char(timestamp,'dd-mon-yy hh:miPM') "Date/Time" from
dba_audit_trail where obj_name = 'EMPLOYEES';
USERNAME OBJ_NAME Date/Time
--------------- --------------- ------------------
HR EMPLOYEES 27-oct-02 08:53AM
HR EMPLOYEES 27-oct-02 08:59AM
HR EMPLOYEES 27-oct-02 10:23AM
HR EMPLOYEES 27-oct-02 10:56AM
OE EMPLOYEES 27-oct-02 11:59AM
5 rows selected.
