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
BY SESSION means:
For any type of audit (schema object, statement, or privilege), BY SESSION inserts only one audit record in the audit trail, for each user and schema object, during the session that includes an audited action.
AUDIT_TRAIL=db, extended means
Performs all actions of AUDIT_TRAIL=db, and also populates the SQL bind and SQL text CLOB-type columns of the SYS.AUD$ table, when available. These two columns are populated only when this parameter is specified.
So the answer is D.
in 12c by session no longer inserts only one audit record. It creates one record per successful asuditing conditions.
A
Any one else?
Ans D
BY SESSION Example 1
Assume the following:
The SELECT TABLE statement auditing option is set BY SESSION.
JWARD connects to the database and issues five SELECT statements against the table named departments and then disconnects from the database.
SWILLIAMS connects to the database and issues three SELECT statements against the table employees and then disconnects from the database.
In this case, the audit trail contains two audit records for the eight SELECT statements– one for each session that issued a SELECT statement.
and
AUDIT_TRAIL=db, extended means – sql text + sql bind
A. Ranjit is right, Tested (AUDIT_TRIAL is a typo)
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
A is correct answer.
http://docs.oracle.com/database/121/SQLRF/statements_4007.htm
A
◦Setting audit_trail to DBMS_FGA.DB + DBMS_FGA.EXTENDED sends the audit trail to the SYS.FGA_LOG$ table in the database and includes SQL Text and SQL Bind.
E: Incorrect because, it includes only SQL Text and SQL Bind, and not SQL execution Plan.
D: is Incorrect because it says only select statement
C: Incorrect because of the execution plan. The audit trail will not contain execution plan.
B: Incorrect, again because of execution plan.
A: Correct.
A and B should not be the correct answers because audit will work for “John” Only!
C and E is incorrect because the audit trail will not include the “execution Plan” and the audit trail will contain one audit record for each audited statement.
D is partially correct, it should be “One audit record is created each audited statement”
If you can go through new features of Oracle 12c about Audit
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 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 ACCESS is the default.
Thus the answer is (A)
http://docs.oracle.com/database/121/SQLRF/statements_4007.htm#SQLRF01107
A
D is correct
by session means one per sesiion
db,extended means sql and bind variable
In 12c, By SESSION will create a record for every statement.
I also tested the statement. it is writing for every statement. Hence A is correct.
depends what version db doesn’t it? A would be for 12, D would be for 11. Assuming this question is in first section of the test I’d say A, 2nd section go for D.
I would say “D” as the “BY JOHN” is in the audit command.
That is really interesting, You’re a very professional blogger. I have joined your feed and sit up for searching for extra of your wonderful post. Additionally, I’ve shared your web site in my social networks|