RelationalDBDesign RelationalDBDesign

Auditing Features  «Prev  Next»
Lesson 4 Auditing specific SQL statements
Objective Audit the use of a SQL statement.

Auditing Specific SQL Statements in Oracle

One of the three things you can audit in your Oracle database is the use of specific SQL statements.


You can turn on auditing for a SQL statement with the following syntax:

AUDIT statement

where statement is either a specific type of SQL statement or a statement option that groups together multiple related statements. As an example, you could issue an AUDIT command with the statement option of TABLE, which would audit the CREATE TABLE, DROP TABLE, and TRUNCATE TABLE statements.
You could also issue an AUDIT command for specific statements, such as the ALTER TABLE, DELETE TABLE, COMMENT TABLE, INSERT TABLE, SELECT TABLE, or UPDATE TABLE statement. Each of these AUDIT commands would create an audit record for each individual SQL statement.
You can also use the keyword ALL to monitor all statements that can be specified with a statement option, which includes most Data Definition Language (DDL) operations.

AUDIT options

The syntax above is the minimum syntax required for the AUDIT statement. You can also add any of the following optional qualifying clauses to shape the way that the AUDIT operation works:
  1. The BY USER user clause is used to limit the auditing to statements issued by a particular user or list of users, separated by commas. If you do not use this clause, all users' statements will be audited.
  2. You can choose to audit BY SESSION or BY ACCESS. These two options control the number of audit records created. The BY SESSION option, which is the default, writes a single record for all the same type of SQL statements written in a particular database session. The BY ACCESS option creates a record for each individual access. The BY ACCESS option normally creates many more audit records, but provides a finer-grained picture of database operations.
  3. The WHENEVER [NOT] SUCCESSFUL clause limits auditing to either successful SQL statements or SQL statements that fail because of a lack of user privileges or a nonexistent database object in the statement. If a statement fails because of invalid syntax, it is not audited. If you do not specify either of these options, all SQL statements are audited.

Ending auditing

To end auditing, you use the NOAUDIT command. This command has almost the same syntax and options as the AUDIT command, except that the NOAUDIT command does not allow the SESSION/USER option. Each NOAUDIT command turns off its corresponding AUDIT command.
In the next lesson, you will learn how to view the audit trail created by auditing.

Auditing Specific Sql Statements - Exercise

Click the Exercise link below to practice using auditing.
Auditing Specific SQL Statements - Exercise