OCP-042 AUDIT BY SESSION

12. You execute the following command to audit the database activities:

SQL> AUDIT DROP ANY TABLE BY scott BY SESSION WHENEVER SUCCESSFUL;
What is the effect of this command?
A. One audit record is created for the whole session if user SCOTT successfully drops one or more tables
in his session.
B. One audit record is created for every session when any user successfully drops a table owned by
SCOTT.
C. One audit record is created for each successful DROP TABLE command executed by any user to drop
tables owned by SCOTT.
D. One audit record is generated for the session when SCOTT grants the DROP ANY TABLE privilege to
other users in his session.
E. One audit record is created for each successful DROP TABLE command executed in the session of
SCOTT.
Answer: A

AUDIT

Purpose

Use the AUDIT statement to:

• Track the occurrence of SQL statements in subsequent user sessions. You can track the occurrence of a specific SQL statement or of all SQL statements authorized by a particular system privilege. Auditing operations on SQL statements apply only to subsequent sessions, not to current sessions.

• Track operations on a specific schema object. Auditing operations on schema objects apply to current sessions as well as to subsequent sessions.

  See Also:

  • Oracle Database Security Guide for general information about auditing

  • PL/SQL Packages and Types Reference for information on the DBMS_FGA package, which lets you create and administer value-based auditing policies

  • NOAUDIT for information on disabling auditing

Prerequisites

To audit occurrences of a SQL statement, you must have AUDIT SYSTEM system privilege.

To audit operations on a schema object, the object you choose for auditing must be in your own schema or you must have AUDIT ANY system privilege. In addition, if the object you choose for auditing is a directory object, even if you created it, then you must have AUDIT ANY system privilege.

To collect auditing results, you must set the initialization parameter AUDIT_TRAIL to DB. You can specify auditing options regardless of whether auditing is enabled. However, Oracle Database does not generate audit records until you enable auditing.

See Also:

Oracle Database Reference for information on the  AUDIT_TRAIL parameter

Syntax

audit::=

Description of the illustration audit.gif

sql_statement_clause::=

Description of the illustration sql_statement_clause.gif

auditing_by_clause::=

Description of the illustration auditing_by_clause.gif

schema_object_clause::=

Description of the illustration schema_object_clause.gif

auditing_on_clause::=

Description of the illustration auditing_on_clause.gif

Semantics

sql_statement_clause

Use the sql_statement_clause to audit SQL statements.

statement_option

Specify a statement option to audit specific SQL statements.

For each audited operation, Oracle Database produces an audit record containing this information:

• The user performing the operation

• The type of operation

• The object involved in the operation

• The date and time of the operation

Oracle Database writes audit records to the audit trail, which is a database table containing audit records. You can review database activity by examining the audit trail through data dictionary views.

See Also:

• Table 13-1 and Table 13-2 for a list of statement options and the SQL statements they audit

• Oracle Database Security Guide for a listing of the audit trail data dictionary views

• Oracle Database Reference for detailed descriptions of the data dictionary views

• "Auditing SQL Statements Relating to Roles: Example"

system_privilege

Specify a system privilege to audit SQL statements that are authorized by the specified system privilege.

Rather than specifying many individual system privileges, you can specify the roles CONNECT, RESOURCE, and DBA. Doing so is equivalent to auditing all of the system privileges granted to those roles.

Oracle Database also provides two shortcuts for specifying groups of system privileges and statement options at once:

ALL Specify ALL to audit all statements options shown in Table 13-1 but not the additional statement options shown in Table 13-2.

ALL PRIVILEGES Specify ALL PRIVILEGES to audit system privileges.

Note:

Oracle recommends that you specify individual system privileges and statement options for auditing rather than roles or shortcuts. The specific system privileges and statement options encompassed by roles and shortcuts change from one release to the next and may not be supported in future versions of Oracle Database.