Auditing to Secure Oracle Database XE

Statement Auditing

No matter what type of auditing you wish to do, you use the AUDIT command to turn on auditing and NOAUDIT to turn off auditing. For statement auditing, the format of the AUDIT command looks something like the following:

AUDIT sql_statement_clause BY {SESSION | ACCESS}
     WHENEVER [NOT] SUCCESSFUL;

Thesql_statement_clause contains a number of different pieces of information, such as the type of SQL statement you want to audit and who you are auditing. In addition, you want to either audit the action every time it happens (BY ACCESS) or only once
(BY SESSION). The default isBY SESSION.

Sometimes you want to audit successful actions—statements that did not generate an error message. For these statements, you addWHENEVER SUCCESSFUL. Other times you only care if the commands using the audited statements fail, either due to privilege violations, running out of space in the tablespace, or syntax errors. For these you useWHENEVER NOT SUCCESSFUL.

For most categories of auditing methods, you can specifyALLinstead of individual statement types or objects if you truly want all types of access to a table or any privileges by a certain user to be audited.

The types of statements you can audit, with the statements that are covered in each category, are listed in Table 31-13. IfALL is specified, any statement in this list is audited. However, the types of statements in Table 31-14 do not fall into theALLcategory when enabling auditing; they must be explicitly specified in anyAUDITcommands.

Table 31-13. Auditable Statements Included in the ALL Category  

Table 31-14. Explicitly Specified Statement Types

Some examples will help make all these options a lot clearer. In our sample database, the userKELLYChas all privileges on the tables in theHRschema and other schemas.KELLYCis allowed to create indexes on some of these tables, but we want to know when the indexes are created in case we have some performance issues related to execution plans changing. We can audit index creation byKELLYCwith the following command:

SQL> audit index by kellyc whenever successful;
Audit succeeded.

Later that day,KELLYCcreates an index on theHR.JOBStable:

SQL> create index job_title_idx on hr.jobs(job_title);
Index created.

Checking the audit trail in the data dictionary viewDBA_AUDIT_TRAIL, we see thatKELLYCdid indeed create an index at 9:21 p.m. on September 17:

SQL> select username, to_char(timestamp,'MM/DD/YY HH24:MI') Timestamp,
  2      obj_name, action_name, sql_text from dba_audit_trail
  3  where username = 'KELLYC';

1 row selected.

To turn off auditing forKELLYCfor indexing operations, use theNOAUDIT command, as follows:

SQL> noaudit index by kellyc;
Noaudit succeeded.

You also may wish to routinely audit both successful and unsuccessful logins. This requires twoAUDITcommands:

SQL> audit session whenever successful; Audit succeeded.
SQL> audit session whenever not successful; Audit succeeded.

Statement auditing also includes startup and shutdown operations. Although you can audit the commandSHUTDOWN IMMEDIATEin theSYS.AUD$table, it is not possible to audit theSTARTUPcommand inSYS.AUD$because the database has to be started before rows can be added to this table. For these cases, you can look in the directory$ORACLE_HOME/rdbms/auditto see a record of a startup operation performed by a system administrator.