Fine-grained auditing

Learning objective

After completing this topic, you should be able to recognize how to implement fine-grained auditing in an Oracle 10g database.

1. Creating an FGA policy

Fine-grained auditing (FGA) enables you to view not only information on the occurrence of an action in the database, but also the actual SQL statement that caused the action.

It also enables you to audit data in a more focused way than either standard or value-based auditing.

With FGA, you can focus audit options on individual columns in a table or view. You can also make FGA audit options conditional, so that records are audited only if certain specifications are met.

Unlike value-based auditing, FGA does not use database triggers to enable auditing. Its impact on system performance is similar to that of standard database auditing.

Question

What are the characteristics of fine-grained auditing?

Options:

1. It allows narrowly focused auditing
2. It allows the capture of SQL statements
3. Audit options can be conditional
4. It requires the use of database triggers

Answer

FGA allows narrowly focused auditing, the capture of SQL statements, and conditional audit options.

Option 1 is correct. The auditing enabled by FGA is more narrowly focused than either standard or value-based auditing.

Option 2 is correct. FGA captures not only information on the occurrence of an action in the database, but also the actual SQL statement that caused the action.

Option 3 is correct. If required, FGA can enable records to be audited only if certain predefined specifications are met.

Option 4 is incorrect. Only value-based auditing requires the use of database triggers.

You create an FGA policy using the DBMS_FGA.ADD_POLICY procedure. Here you've begun the procedure by typing BEGIN.

You type DBMS_FGA.ADD_POLICY and press Enter.

Having typed the DBMS_FGA.ADD_POLICY procedure you are ready to complete the policy.

The DBMS_FGA.ADD_POLICY procedure requires several arguments. Here is an example of a completed FGA policy.

The FGA policy defines the criteria and action for an audit. The DBMS_FGA.ADD_POLICY procedure takes the following arguments.

• object arguments
• policy_name
• audit_condition
• audit_column
• event handler arguments
• enable
• statement_types

object arguments

The object is the table or view that is being audited. It is passed as two arguments: the schema that contains the object, and the name of the object. Here the EMPLOYEES table in the HR schema is being audited.
The highlighted code is
object_schema => 'hr',
object_name => 'employees',

policy_name

You assign each FGA policy a name when you create it. Here the name of the policy is audit_emps_salary.
The highlighted code is
policy_name => 'audit_emps_salary',

audit_condition

The audit condition argument defines when the audit event should occur. Here, the condition specifies that all rows with a department ID of 10 are to be audited.
The highlighted code is
audit_condition=> 'department_id=10',

audit_column

The optional audit column argument specifies the data that is being audited. An audit event occurs only if this column is included in the SELECT statement. If the argument is not specified, only the audit condition argument determines whether an audit event occurs.
The highlighted code is
audit_column => 'salary',

event handler arguments

You can use an optional event handler to define any additional actions to be taken during auditing, such as sending alerts to the DBA. If defined, the event handler is executed once an audit entry is inserted into the audit trail. The event handler is passed as two arguments: the schema containing the PL/SQL program unit, and the name of the PL/SQL program unit.
The highlighted code is
handler_schema => 'secure',
handler_module => 'log_emps_salary',

enable

The enable status argument indicates whether the FGA policy is enabled or not. Here, the policy is enabled.
The highlighted code is
enable => TRUE,

statement_types

The statements that can be audited by FGA are SELECT, INSERT, UPDATE, and DELETE.
The highlighted code is
statement_types=> 'select' );

Question

You want to create an FGA policy.

Type the procedure that enables you to do this.

Answer

To create an FGA policy, you type dbms_fga.add_policy.

2. Using the DBMS_FGA package

The DBMS_FGA package enables you to administer fine-grained audit policies. Because the FGA audit trail can contain sensitive information, only database administrators should have execute privileges on DBMS_FGA.

You can use DBMS_FGA to enable, disable, and drop an FGA policy.

A policy is enabled by default when it is created. However, if you need to enable a policy that has previously been disabled, you use the DBMS_FGA.ENABLE_POLICY procedure.

You type dbms_fga.enable_policy and press Enter.

You can now complete the DBMS_FGA.ENABLE_POLICY procedure.

To use the DBMS_FGA.ENABLE_POLICY procedure, you need to include the object_schema, object_name, and policy_name arguments.

Disabling an FGA policy means that the policy does not generate audit events. To disable a policy, you use the DBMS_FGA.DISABLE_POLICY procedure with all the arguments shown.

You use the DBMS_FGA.DROP_POLICY to drop a policy that you don't use any more. You've begun the statement by typing BEGIN.

You type dbms_fga.drop_policy and press Enter.

You can now complete the DBMS_FGA.DROP_POLICY procedure.

The DBMS_FGA.DROP_POLICY procedure requires the object_schema, object_name, and policy_name arguments.

When you execute the statement, a messsage informs you that it has been successful. The policy has now been dropped.

Question

You want to drop an FGA policy that you no longer use.

Type the procedure that enables you to do this.

Answer

To drop an FGA policy, you type dbms_fga.drop_policy.

If a row returned from a query matches the audited column and the condition specified by the audit, then an audit event occurs. This causes an audit record to be created and stored in the audit trail. If an event handler exists, it is then fired.

In FGA, auditing is focused at the statement level by default, so a SELECT statement that returns thousands of rows generates only one audit record.

The SQL statement shown causes an audit event. This is because the FGA policy's audit_column and audit_condition arguments specify that data in the salary column and all rows with a department ID of 10 are to be audited.

This statement also causes an audit event because, although the department_id is not referenced, the audit column is included in the SELECT statement. The server thus knows that those rows will be accessed.

The statement shown here does not cause an audit event, because the salary column is not specified in the SELECT statement.

However, it would cause an audit event if the FGA policy did not specify the salary column as the audit column.

Question

Suppose, in the ACCOUNTS table, you've specified the column BALANCE as the audit column in your FGA policy. Which SQL statements would trigger an audit event?

Options:

1. SELECT * from ACCOUNTS
2. SELECT BALANCE from ACCOUNTS
3. SELECT cust_id from ACCOUNTS
4. SELECT cust_id from ACCOUNTS WHERE BALANCE <>

Answer

SELECT * from ACCOUNTSSELECT BALANCE from ACCOUNTSSELECT cust_id from ACCOUNTS WHERE BALANCE <>

Option 1 is correct. The statement selects all records from the ACCOUNTS table, so the audit column will be accessed, triggering an audit event.

Option 2 is correct. The statement includes the name of the audit column, BALANCE, in the SELECT statement.

Option 3 is incorrect. The SELECT statement does not include the audit column, so it will not be accessed and an audit event will not be triggered.

Option 4 is correct. The BALANCE column is accessed in this statement, so an audit event will occur.

3. The FGA audit trail

FGA audit entries are logged in a separate table from object and privilege audits. The available FGA data dictionary views are:

• ALL_AUDIT_POLICIES
• DBA_AUDIT_POLICIES
• DBA_FGA_AUDIT_TRAIL
• USER_AUDIT_POLICIES

ALL_AUDIT_POLICIES

The ALL_AUDIT_POLICIES view stores all FGA policies for any objects the current user can access.

DBA_AUDIT_POLICIES

The DBA_AUDIT_POLICIES view stores all FGA policies in the database.

DBA_FGA_AUDIT_TRAIL

The DBA_FGA_AUDIT_TRAIL view stores all FGA events.

USER_AUDIT_POLICIES

The USER_AUDIT_POLICIES view stores all FGA policies for objects in the current user schema.

Suppose you want to complete the code to retrieve timestamp, user, policy name, and SQL information from an FGA audit trail. You've used the SELECT statement to retrieve the information, and you now need to complete the FROM statement.

Type DBA_FGA_AUDIT_TRAIL to complete the code and press Enter.

You can now view

• the timestamp, or date and time of execution
• the database name of the user executing the statement
• the name of the policy that caused the audited event
• the bind variable of the audited event
• the SQL statement that caused the audit event

Question

Suppose you want to view all information from DBA_FGA_AUDIT_TRAIL.

Complete the statement needed to access the relevant information.

Answer

You type SELECT * FROM DBA_FGA_AUDIT_TRAIL.

When creating and implementing an FGA policy, you should look out for these potential issues.

• audit column errors
• audit conditions
• audit condition errors
• audited object errors
• event handler errors
• policy name errors

audit column errors

If you specify an audit column that does not exist in the table, the policy is created as normal but no rows are
audited because the audit column is not accessed.

audit conditions

When creating an FGA policy, the audit condition is null by default. This means all statements will be audited.

audit condition errors

If the syntax of the audit condition is invalid, the policy is created as normal but, when the audited object is accessed, you will receive the error message ORA-28112: failed to execute policy function.

audited object errors

The audited table or view must exist when you create the policy. If it does not, you will receive the error message ORA-00942: table or view does not exist.

event handler errors

When a policy includes a reference to a nonexistent or invalid event handler, the policy is created as normal but no rows are returned when an audit event is triggered.

policy name errors

You must assign policy names that are unique within the database. If the name already exists, the error ORA-28101: policy already exists occurs when you create the policy.

Question

Identify the guidelines for implementing FGA.

Options:

1. By default, a newly created FGA policy audits all statements
2. If you specify a nonexistent audit column in a policy, no rows are audited
3. The audited table or view must exist when you create the policy
4. When a policy refers to a nonexistent event handler, the policy cannot be created
5. You can assign any name to a newly created FGA policy

Answer

New FGA policies audit all statements by default. Specifying a nonexistent audit column in a policy means no rows will be audited. And the audited table or view must exist when you create the policy.

Option 1 is correct. The audit condition is null by default. If you change it, it's important to ensure that the syntax of the condition is correct, otherwise it won't execute.

Option 2 is correct. The policy is created as normal, but no rows are audited, because the audit column is not accessed.

Option 3 is correct. If the table or view does not exist, an error occurs.

Option 4 is incorrect. The policy is created as normal, but no rows are returned when an audit event is triggered.

Option 5 is incorrect. Policy names should be unique within the database. If the name already exists, an error occurs upon policy creation.

Summary

Fine-grained auditing (FGA) enables you to view not only information on the occurrence of an action in the database, but also the actual SQL statement that caused the action. You can focus audit options on individual columns in a table or view, and make FGA audit options conditional.

You can use the DBMS_FGA administration package to enable, disable, and drop an FGA policy. When a policy is enabled and policy conditions are met for a statement, the statement is audited. If an event handler exists, it is then fired.

FGA audit entries can be viewed on four different data dictionary views. You can use the DBA_FGA_AUDIT_TRAIL view to view all FGA events. When creating and implementing an FGA policy, you should look out for potential errors in audit columns, audit conditions, audit objects, event handlers, and policy names.