You execute the following commands to audit database activities:
SQL > ALTER SYSTEM SET AUDIT_TRIAL=DB, EXTENDED SCOPE=SPFILE;
SQL > AUDIT SELECT TABLE, INSERT TABLE, DELETE TABLE BY JOHN By SESSION
WHENEVER SUCCESSFUL;
Which statement is true about the audit record that generated when auditing after instance
restarts?
A.
One audit record is created for every successful execution of a SELECT, INSERT OR DELETE
command on a table, and contains the SQL text for the SQL Statements.
B.
One audit record is created for every successful execution of a SELECT, INSERT OR DELETE
command, and contains the execution plan for the SQL statements.
C.
One audit record is created for the whole session if john successfully executes a SELECT,
INSERT, or DELETE command, and contains the execution plan for the SQL statements.
D.
One audit record is created for the whole session if JOHN successfully executes a select
command, and contains the SQL text and bind variables used.
E.
One audit record is created for the whole session if john successfully executes a SELECT,
INSERT, or DELETE command on a table, and contains the execution plan, SQL text, and bind
variables used.
Explanation:
Note:
* BY SESSION
In earlier releases, BY SESSION caused the database to write a single record for all SQL
statements or operations of the same type executed on the same schema objects in the same
session. Beginning with this release (11g) of Oracle Database, both BY SESSION and BY
ACCESS cause Oracle Database to write one audit record for each audited statement and
operation.
* BY ACCESS
Specify BY ACCESS if you want Oracle Database to write one record for each audited statement
and operation.Note:
If you specify either a SQL statement shortcut or a system privilege that audits a data definition
language (DDL) statement, then the database always audits by access. In all other cases, the
database honors the BY SESSION or BY ACCESS specification.
* 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
Reference: Oracle Database SQL Language Reference 12c
I would say that the right choice is somewhere between A and D.
– The audit trail provides both SQL Text and Bind variables for the statement, and the BY JOHN clause means that only JOHN’s operations are audited => D not A
– Starting with Oracle 11g, BY SESSION acts as BY ACCESS and a record is generated for each successfull operation => A not C
So, the response would be:
One audit record is created for every successful execution by John of a SELECT, INSERT OR DELETE command, and contains the SQL text and bind variables used.
Oracle recommends that the audit trail be written to the operating system files as this configuration imposes the least amount of overhead on the source database system. To enable database auditing, the initialization parameter, AUDIT_TRAIL, should be set to one of these values:
Parameter Value = DB,EXTENDED
Does all actions of AUDIT_TRAIL=DB and also populates the SQL bind and SQL text columns of the SYS.AUD$ table
Corret is D
A is correct
BY SESSION
http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_4007.htm
In earlier releases, BY SESSION caused the database to write a single record for all SQL statements or operations of the same type executed on the same schema objects in the same session. Beginning with this release of Oracle Database, both BY SESSION and BY ACCESS cause Oracle Database to write one audit record for each audited statement and operation. BY SESSION continues to populate different values to the audit trail compared with BY ACCESS. Oracle recommends that you include the BY ACCESS clause for all AUDIT statements, which results in a more detailed audit record. If you specify neither clause, then BY SESSION is the default.
Note:
A is correct. Important thing to note here is that with 11g the AUDIT by SESSION behavior changed and now it also operates on all the statements executed in a session same like AUDIT by ACCESS.
What about BY JOHN? A doesn’t include that. A can’t be correct in my opinion.
It’s D.
You have to think of it in set-wise fashion.
A & B are minimally wrong because it doesn’t work for any non-John user.(B also is wrong about execution plans)
C & E are wrong because the reference to execution plans.
That leaves D. Now D isn’t inclusive of all that might be audited w/John, but it isn’t false, therefore true.
A is the only right answer. Most important – the record for every successful execution and no execution plan is written to audit trail.
“D is correct answer.
”A” is wrong – says ‘one record’ for EVERY successful execution. When “BY SESSION” is mentioned, only ONE row for whole session is created with SQL and bind variables/values (no execution plan).
“B” is wrong – no execution plan is recorded in audit trail
“C” is wrong – no execution plan is recorded in audit trail
“E” is wrong – no execution plan is recorded in audit trail
correct observation. D is the answer.
AUDIT SELECT TABLE, INSERT TABLE, DELETE TABLE BY SCOTT By SESSION
WHENEVER SUCCESSFUL;
After Executing the above script I connect with the scott user and select different table in different time for every time new row inserted in SYS.AUD$ table.
So A would be correct answer though DUMP said D is the correct answer.
Tested. Oracle write audit for every statement:
SQL> SELECT username, sql_text FROM cdb_audit_trail WHERE obj_name = ‘A’;
USERNAME SQL_TEXT
——— ——————————
JOHN insert into sys.a values(1)
JOHN insert into sys.a values(2)
JOHN update sys.a set a = a+1
JOHN update sys.a set a = a+2
JOHN select * from sys.a
JOHN delete from sys.a where rownum
= 1
JOHN delete from sys.a
JOHN select * from sys.a
8 rows selected.
So the right answer is A