nerdexam
Oracle

1Z0-052 · Question #28

What is the effect of this command? SQL> AUDIT DROP ANY TABLE BY scott BY SESSION WHENEVER SUCCESSFUL;

The correct answer is C. One audit record is created for the whole session if user SCOTT successfully drops one or more tables. The BY SESSION clause in Oracle traditional auditing writes a single audit record for the entire session rather than one record per individual SQL execution.

Implementing Oracle Database Auditing

Question

What is the effect of this command? SQL> AUDIT DROP ANY TABLE BY scott BY SESSION WHENEVER SUCCESSFUL;

Options

  • AOne audit record is created for every successful DROP TABLE command executed in the session
  • BOne audit record is generated for the session when SCOTT grants the DROP ANY TABLE privilege
  • COne audit record is created for the whole session if user SCOTT successfully drops one or more tables
  • DOne audit record is created for every session of any other user in which a table owned by SCOTT is
  • EOne audit record is created for every successful DROP TABLE command executed by any user to

How the community answered

(37 responses)
  • A
    5% (2)
  • B
    3% (1)
  • C
    73% (27)
  • D
    16% (6)
  • E
    3% (1)

Why each option

The BY SESSION clause in Oracle traditional auditing writes a single audit record for the entire session rather than one record per individual SQL execution.

AOne audit record is created for every successful DROP TABLE command executed in the session

Generating one record per individual DROP TABLE statement describes BY ACCESS auditing behavior, not BY SESSION.

BOne audit record is generated for the session when SCOTT grants the DROP ANY TABLE privilege

The AUDIT statement here audits the DROP ANY TABLE system privilege usage (executing DROP TABLE), not the act of granting that privilege to another user.

COne audit record is created for the whole session if user SCOTT successfully drops one or more tablesCorrect

The BY SESSION clause causes Oracle to write at most one audit record per database session for the audited action type. Therefore, if user SCOTT connects and drops multiple tables during that session, only one consolidated audit record is generated for the session, as opposed to BY ACCESS which records each individual statement.

DOne audit record is created for every session of any other user in which a table owned by SCOTT is

The BY scott clause audits actions performed BY the user SCOTT, not actions performed by other users against tables owned by SCOTT.

EOne audit record is created for every successful DROP TABLE command executed by any user to

BY scott restricts the audit scope to SCOTT's own sessions and actions, so drops performed by any other user are not captured by this audit definition.

Concept tested: Oracle traditional auditing BY SESSION vs BY ACCESS

Source: https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/AUDIT-Traditional-Auditing.html

Topics

#statement auditing#BY SESSION#WHENEVER SUCCESSFUL#audit record granularity

Community Discussion

No community discussion yet for this question.

Sign up to join the discussion
Full 1Z0-052 Practice