Auditing in the Oracle database stores information about database activities. The activities to be audited are specified by the DBA. Once enabled, auditing records the activity in the AUD$ table, owned by SYS.
auditing
Storing information about activities in the database in the SYS.AUD$ table. Auditing is controlled by the DBA.
Auditing can be fine-tuned in a number of ways. It can be restricted to particular objects or to specific users or based on whether the action is successful or unsuccessful. In other words, you might not care if users who are granted rights to a table access the table, but you might want to know when users without rights to a table try to access that table.
The types of auditing can be divided into two broad categories: statement auditing and object auditing. The general syntax for AUDIT is as follows:
AUDIT {statement_clause | object_clause} [BY SESSION | BY ACCESS] [WHENEVER [NOT] SUCCESSFUL];
The statement_clause allows you to specify not only the SQL statement to audit but also, optionally, the username that will be running the SQL statement. The object_clause allows you to specify a particular object to audit.
The BY SESSION clause means that an audit record is written to SYS.AUD$ only once in the session that triggered the audit, regardless of how many times the action was performed. BY ACCESS will record all occurrences of the specified action.
The NOAUDIT command turns off auditing and has the same syntax as AUDIT, except that BY SESSION or BY ACCESS is not specified when using NOAUDIT.
Statement auditing allows the DBA to trigger audit records in SYS.AUD$ when a given SQL statement is executed, either for all users or a particular group of users.
Recently, Janice, the DBA, created a new user SCRAWFORD and granted the CREATE TABLE privilege to SCRAWFORD. Janice is concerned that the new user is having trouble creating tables, so she decides to turn on auditing to see how often the new user's CREATE TABLE statements are failing:
audit create table by scrawford whenever not successful; Audit succeeded.
In the next few days, the user SCRAWFORD runs a variety of CREATE TABLE statements, such as the following:
create table temp_emp (employee_id number(6), email varchar2(25)); Table created. create table temp_emp (employee_id number(6), email varchar2(25)); ERROR at line 1: ORA-00955: name is already used by an existing object
The user's second attempt failed because the table already exists.
Janice could review the SYS.AUD$ table, but she knows that the data dictionary view called DBA_AUDIT_TRAIL formats the records from SYS.AUD$ into a more readable format. She checks that view:
select username, obj_name, timestamp, action_name from dba_audit_trail; USERNAME OBJ_NAME TIMESTAMP ACTION_NAME ------------- ------------ --------- ------------ SCRAWFORD TEMP_EMP 26-OCT-02 CREATE TABLE 1 row selected.
The OBJ_NAME column contains the name of the object affected by the statement, and the ACTION_NAME column contains the type of statement executed. Because Janice is auditing only unsuccessful uses of the CREATE TABLE statement, there is only one row inserted into SYS.AUD$, even though two CREATE TABLE statements were executed.
The following week, Janice turns off the CREATE TABLE auditing with the following 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 allows the DBA to monitor access to specific objects in the database, 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 certain 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.
From this query, she sees that the activity so far has been very light, with four accesses by HR and one by OE, all in the morning. Janice turns off the EMPLOYEE table auditing using the NOAUDIT command:
noaudit select, insert, update, delete on hr.employees; Noaudit succeeded.
As with statement auditing, the records in SYS.AUD$ remain there until they are removed by the DBA.