Auditing database activity in Oracle 10g

Learning objective

After completing this topic, you should be able to audit database activity in an Oracle 10g database.

Exercise overview

In this exercise, you're required to set up database auditing to capture changes to salary information.

This involves the following tasks:

• auditing unsuccessful attempts to connect to the database
• auditing the exercising of the SELECT statement on the SALARY column

As a database administrator, you suspect that someone in your organization has been viewing employee salary data without proper authorization. You want to detect any unauthorized attempts to access salary data so that you can take appropriate action.

Task 1: Auditing unsuccessful attempts to connect

First you want to collect information on any unauthorized attempts to break into the database.

Step 1 of 1

You want to audit all unsuccessful attempts to connect to the database.

Type the command that enables you to do this.

Result

You type AUDIT SESSION WHENEVER NOT SUCCESSFUL to audit all unsuccessful attempts to connect to the database.

Task 2: Auditing the SALARY column

Next you want to capture information on uses of the SELECT statement on the SALARY column of the EMPLOYEES table.

Step 1 of 4

Which type of auditing should you use to capture audit information on use of the SELECT statement on just the SALARY column?

Options:

1. Fine-grained auditing
2. Standard database auditing
3. Value-based auditing

Result

You use fine-grained auditing (FGA) to capture audit information on use of the SELECT statement on just the SALARY column.

Option 1 is correct. Fine-grained auditing (FGA) enables you to focus the audit on just the SALARY column.

Option 2 is incorrect. Standard database auditing does not allow you to focus the audit on a single column in a table.

Option 3 is incorrect. Value-based auditing collects information on actual values that have been changed, but not on SELECT actions.

Step 2 of 4

Now you need to create the FGA policy to audit SELECT statements on the SALARY column.

Type the name of the procedure that enables you to create an FGA policy.

BEGIN 
dbms_fga.add_policy 

Result

You type dbms_fga.add_policy to complete the statement to create an FGA policy.

Step 3 of 4

You need to complete the list of arguments required by the FGA policy.

Type the name of the argument that specifies that data in the SALARY column is to be audited.

BEGIN 
dbms_fga.add_policy
(object_schema => 'hr' ,
object_name  => 'employees' ,
policy_name  => 'audit_emps_salary' ,
MISSING CODE => 'salary' ,
enable => TRUE , 

Result

You type audit_column to complete the argument.

Step 4 of 4

Finally, you want to specify that the SELECT statement should be audited on the SALARY column.

Complete the statement_types argument to enable the FGA policy to do this.

BEGIN 
dbms_fga.add_policy
(object_schema  => 'hr' ,
object_name => 'employees' ,
policy_name => 'audit_emps_salary' ,
audit_column => 'salary' ,
enable => TRUE ,
statement_types => 'MISSING CODE' ); 

Result

You type select to complete the statement_types argument.

When you execute the procedure, a message confirms that the procedure has been successful. You can now view the audit information in the DBA_FGA_AUDIT_TRAIL view.