1z-052-41

QUESTION 41
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 every successful drop table command executed in the session of SCOTT

B. One audit record is generated for the session when SCOTT grants the DROP ANY TABLE privilege to other users

C. One audit record is created for the whole session if user SCOTT successfully drops one or more tables in his session

D. One audit record is created for every session of any other user in which a table owned by SCOTT is dropped successfully

E. One audit record is created for every successful drop table command executed by any user to drop tables owned by SCOTT

 

What is audit?=>Records all operations on data objects. When And when users perform operations on objects. Audit is disabled by default.

Audit-related table Installation

SQLPLUS> connect/AS SYSDBA

SQLPLUS> select * from sys. aud $; -- no record is returned

SQLPLUS> select * from dba_audit_trail;-no record is returned

If the table does not exist during the above query, it indicates that the audit-related table has not been installed and needs to be installed.

 

SQLPLUS> connect/as sysdba

SQLPLUS> @ $ ORACLE_HOME/rdbms/admin/cataudit. SQL

The audit table is installed in the SYSTEM tablespace. Therefore, make sure that the SYSTEM tablespace has enough space to store audit information.

Restart the database after installation

 

There are three types of audits:
1. Statement Audit(Statement Auditing) => audits specified SQL statements.
For example, audit create table => indicates the Review Record create table statement.

2. Permission audit (Privilege Auditing) => audits operations involving certain permissions.
For example, GRANT user a query permission: grant select any table to a; audit select any table;
Then, when user a executes the query statement: SELECT * FROM B. T uses the SELECT permission, so this statement will be recorded.
Sometimes statements are both Statement Audit and permission audit content. As mentioned above, create table.

3. Object audit(Object Auditing) => the action of the audit on the specified Object.
Example: audit alter, DELETE, drop on a. T by B;
Statement specifies to authenticate the table of user a, and by specifies to only authenticate operations initiated by user B.

 

Audit statement Parameters

1. By [Session | Access]
Session => record the repeated operations in each Session only once.
Access => each operation is recorded.

Example:

Audit select, UPDATE, INSERT, DELETE
ON a. T
BY SESSION
All independent operations on table T under User a will be recorded.

2. Whenever [not] sucessful
Sucessful => the operation is successfully recorded.
Not Sucessful => operation failure is recorded.
NULL (not set) => this statement is recorded no matter whether it is set successfully or not.

Example:

AUDIT ADELETE ANY TABLE
Whenever not sucessful;
Records all actions that failed to be deleted.

Two important parameters for enabling Audit
Audit_SYS_Operations=> Specifies whether operations of all system (sysdba, sysoper) users are recorded. The default value is false.
The content is recorded in the file specified by audit_file_dest.
Audit Trail=> Enable and disable audit by using AUDIT_TRAIL In the init. ora parameter of the initial database file.
There are three values:
None => default, no audit
DB => record audit_trail in the Database Audit table, aud $
OS => record audit_trail in the operating system file. The file directory address is specified by audit_file_dest.

 

Audit results are saved in the database
SYS. AUD $ is the only table with audit results retained. All others are views.

STMT_AUDIT_OPTION_MAP contains information about the audit option type code, which is created by the SQL. BSQ script at CREATEDATABASE.
AUDIT_ACTIONS: Description of the audit trail action type code
ALL_DEF_AUDIT_OPTS includes the default Object audit option. These options are applied when an object is created.

DBA_STMT_AUDIT_OPTS describes the cross-system Current System Audit options set by the user
DBA_PRIV_AUDIT_OPTS describes the cross-system current system permissions being audited by the user.
DBA_OBJ_AUDIT_OPTS describes Audit options on all objects
The USER_OBJ_AUDIT_OPTS USER view describes Audit options for all objects owned by the current USER.

The following are audit records
DBA_AUDIT_TRAIL list all audit trail entries
The USER_AUDIT_TRAIL USER view displays audit trail entries related to the current USER.

DBA_AUDIT_OBJECT contains audit trail records of all objects in the system
The USER_AUDIT _ object user view lists some audit trail records that involve statements on the objects accessible to the current USER.

DBA_AUDIT_SESSION lists all audit trail records involving CONNECT and DISCONNECT
The USER_AUDIT _ session user view lists all audit trail records related to CONNECT and DISCONNECT of the current USER.

DBA_AUDIT_STATEMENT lists AUDIT trail records of all grant revoke audit noaudit and alter system statements involving the database
USER _ AUDIT _ STATEMENT for the USER view, these statements should be published by the USER.

DBA_AUDIT_EXISTS lists AUDIT trail entries generated BY AUDIT NOT EXISTS

The following view is used for fine-grained Audit
DBA_AUDIT_POLICIES: displays all audit policies on the system.
DBA_FGA_AUDIT_TRAIL lists audit trail records based on value

What is audit?=>Records all operations on data objects. When And when users perform operations on objects. Audit is disabled by default.

Audit-related table Installation

SQLPLUS> connect/AS SYSDBA

SQLPLUS> select * from sys. aud $; -- no record is returned

SQLPLUS> select * from dba_audit_trail;-no record is returned

If the table does not exist during the above query, it indicates that the audit-related table has not been installed and needs to be installed.

 

SQLPLUS> connect/as sysdba

SQLPLUS> @ $ ORACLE_HOME/rdbms/admin/cataudit. SQL

The audit table is installed in the SYSTEM tablespace. Therefore, make sure that the SYSTEM tablespace has enough space to store audit information.

Restart the database after installation

 

There are three types of audits:
1. Statement Audit(Statement Auditing) => audits specified SQL statements.
For example, audit create table => indicates the Review Record create table statement.

2. Permission audit (Privilege Auditing) => audits operations involving certain permissions.
For example, GRANT user a query permission: grant select any table to a; audit select any table;
Then, when user a executes the query statement: SELECT * FROM B. T uses the SELECT permission, so this statement will be recorded.
Sometimes statements are both Statement Audit and permission audit content. As mentioned above, create table.

3. Object audit(Object Auditing) => the action of the audit on the specified Object.
Example: audit alter, DELETE, drop on a. T by B;
Statement specifies to authenticate the table of user a, and by specifies to only authenticate operations initiated by user B.

 

Audit statement Parameters

1. By [Session | Access]
Session => record the repeated operations in each Session only once.
Access => each operation is recorded.

Example:

Audit select, UPDATE, INSERT, DELETE
ON a. T
BY SESSION
All independent operations on table T under User a will be recorded.

2. Whenever [not] sucessful
Sucessful => the operation is successfully recorded.
Not Sucessful => operation failure is recorded.
NULL (not set) => this statement is recorded no matter whether it is set successfully or not.

Example:

AUDIT ADELETE ANY TABLE
Whenever not sucessful;
Records all actions that failed to be deleted.

Two important parameters for enabling Audit
Audit_SYS_Operations=> Specifies whether operations of all system (sysdba, sysoper) users are recorded. The default value is false.
The content is recorded in the file specified by audit_file_dest.
Audit Trail=> Enable and disable audit by using AUDIT_TRAIL In the init. ora parameter of the initial database file.
There are three values:
None => default, no audit
DB => record audit_trail in the Database Audit table, aud $
OS => record audit_trail in the operating system file. The file directory address is specified by audit_file_dest.

 

Audit results are saved in the database
SYS. AUD $ is the only table with audit results retained. All others are views.

STMT_AUDIT_OPTION_MAP contains information about the audit option type code, which is created by the SQL. BSQ script at CREATEDATABASE.
AUDIT_ACTIONS: Description of the audit trail action type code
ALL_DEF_AUDIT_OPTS includes the default Object audit option. These options are applied when an object is created.

DBA_STMT_AUDIT_OPTS describes the cross-system Current System Audit options set by the user
DBA_PRIV_AUDIT_OPTS describes the cross-system current system permissions being audited by the user.
DBA_OBJ_AUDIT_OPTS describes Audit options on all objects
The USER_OBJ_AUDIT_OPTS USER view describes Audit options for all objects owned by the current USER.

The following are audit records
DBA_AUDIT_TRAIL list all audit trail entries
The USER_AUDIT_TRAIL USER view displays audit trail entries related to the current USER.

DBA_AUDIT_OBJECT contains audit trail records of all objects in the system
The USER_AUDIT _ object user view lists some audit trail records that involve statements on the objects accessible to the current USER.

DBA_AUDIT_SESSION lists all audit trail records involving CONNECT and DISCONNECT
The USER_AUDIT _ session user view lists all audit trail records related to CONNECT and DISCONNECT of the current USER.

DBA_AUDIT_STATEMENT lists AUDIT trail records of all grant revoke audit noaudit and alter system statements involving the database
USER _ AUDIT _ STATEMENT for the USER view, these statements should be published by the USER.

DBA_AUDIT_EXISTS lists AUDIT trail entries generated BY AUDIT NOT EXISTS

The following view is used for fine-grained Audit
DBA_AUDIT_POLICIES: displays all audit policies on the system.
DBA_FGA_AUDIT_TRAIL lists audit trail records based on value