Auditing

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, 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

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 JSMITH and granted the CREATE TABLE privilege to JSMITH. 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 jsmith    whenever not successful; Audit succeeded.

In the next few days, the user JSMITH 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 ------------- ------------ --------- ------------ JSMITH        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 jsmith; 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 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.