Skip Headers

Oracle® Database SQL Reference
10g Release 1 (10.1)

Part Number B10759-01
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Feedback

Go to previous page
Previous
Go to next page
Next
View PDF

NOAUDIT

Purpose

Use the NOAUDIT statement to stop auditing operations previously enabled by the AUDIT statement.

The NOAUDIT statement must have the same syntax as the previous AUDIT statement. Further, it reverses the effects only of that particular statement. For example, suppose one AUDIT statement A enables auditing for a specific user. A second statement B enables auditing for all users. A NOAUDIT statement C to disable auditing for all users reverses statement B. However, statement C leaves statement A in effect and continues to audit the user that statement A specified.


See Also:

AUDIT for more information on auditing

Prerequisites

To stop auditing of SQL statements, you must have the AUDIT SYSTEM system privilege.

To stop auditing of schema objects, you must be the owner of the object on which you stop auditing or you must have the AUDIT ANY system privilege. In addition, if the object you chose for auditing is a directory, then even if you created it, you must have the AUDIT ANY system privilege.

Syntax


noaudit::=
Description of noaudit.gif follows
Description of the illustration noaudit.gif

(sql_statement_clause ::=, schema_object_clause ::=)


sql_statement_clause ::=
Description of sql_statement_clause.gif follows
Description of the illustration sql_statement_clause.gif


auditing_by_clause::=
Description of auditing_by_clause.gif follows
Description of the illustration auditing_by_clause.gif


schema_object_clause ::=
Description of schema_object_clause.gif follows
Description of the illustration schema_object_clause.gif


auditing_on_clause::=
Description of auditing_on_clause.gif follows
Description of the illustration auditing_on_clause.gif

Semantics


sql_statement_clause

Use the sql_statement_clause to stop auditing of a particular SQL statement.


statement_option

For statement_option, specify the statement option for which auditing is to be stopped. Please refer to Table 13-1 and Table 13-2 for a list of the statement options and the SQL statements they audit.


ALL

Specify ALL to stop auditing of all statement options currently being audited.


system_privilege

For system_privilege, specify the system privilege for which auditing is to be stopped. Please refer to Table 18-1 for a list of the system privileges and the statements they authorize.


ALL PRIVILEGES

Specify ALL PRIVILEGES to stop auditing of all system privileges currently being audited.


auditing_by_clause

Use the auditing_by_clause to stop auditing only those SQL statements issued by particular users. If you omit this clause, then Oracle Database stops auditing all users' statements.


schema_object_clause

Use the schema_object_clause to stop auditing of a particular database object.


object_option

For object_option, specify the type of operation for which auditing is to be stopped on the object specified in the ON clause. Please refer to Table 13-3 for a list of these options.


ALL

Specify ALL as a shortcut equivalent to specifying all object options applicable for the type of object.


auditing_on_clause

The auditing_on_clause lets you specify the particular schema object for which auditing is to be stopped.


WHENEVER [NOT] SUCCESSFUL

Specify WHENEVER SUCCESSFUL to stop auditing only for SQL statements and operations on schema objects that complete successfully.

Specify WHENEVER NOT SUCCESSFUL to stop auditing only for statements and operations that result in Oracle Database errors.

If you omit this clause, then the database stops auditing for all statements or operations, regardless of success or failure.

Examples


Stop Auditing of SQL Statements Related to Roles: Example

If you have chosen auditing for every SQL statement that creates or drops a role, then you can stop auditing of such statements by issuing the following statement:

NOAUDIT ROLE; 

Stop Auditing of Updates or Queries on Objects Owned by a Particular User: Example

If you have chosen auditing for any statement that queries or updates any table issued by the users hr and oe, then you can stop auditing for queries by hr by issuing the following statement:

NOAUDIT SELECT TABLE BY hr; 

The preceding statement stops auditing only queries by hr, so the database continues to audit queries and updates by oe as well as updates by hr.


Stop Auditing of Statements Authorized by a Particular Object Privilege: Example

To stop auditing on all statements that are authorized by DELETE ANY TABLE system privilege, issue the following statement:

NOAUDIT DELETE ANY TABLE;

Stop Auditing of Queries on a Particular Object: Example

If you have chosen auditing for every SQL statement that queries the employees table in the schema hr, then you can stop auditing for such queries by issuing the following statement:

NOAUDIT SELECT 
   ON hr.employees; 

Stop Auditing of Queries that Complete Successfully: Example

You can stop auditing for queries that complete successfully by issuing the following statement:

NOAUDIT SELECT 
   ON hr.employees
   WHENEVER SUCCESSFUL; 

This statement stops auditing only for successful queries. Oracle Database continues to audit queries resulting in Oracle Database errors.