SkillBuilders | Oracle 9i Administration | Appendix D: Fine Grained Auditing (FGA)

Instructor's Notes

(If applicable)

Appendix D: Fine Grained Auditing
This lesson is a brief introduction to the Fine Grained Auditing (FGA) feature, new in Oracle 9i. FGA allows you to conditionally audit SELECT statements, and is useful for monitoring access to sensitive columns.

Other Text:
(Examples or comments displayed on slide, if any).

Instructor's Notes

(If applicable)

Fine Grained auditing (FGA) is a new feature in Oracle9i that allows you to conditionally audit SELECT statements.
This feature is useful for monitoring the access to sensitive columns. Sensitive information could be salary data, tax information, company financial data, etc.
With Oracle8i, we are able to uncover the access to an object, but not see the SQL statement used nor see the data that the user saw. With 9i FGA, we can see the SELECT statement executed, the user (database and OS), timestamp and SCN.
By using the SCN of the query with the flashback query feature, we have the possibility of seeing the actual data that the original query saw.

Supplemental Notes
- To enable auditing set the INIT.ORA parameter AUDIT_TRAIL=DB or OS.

Other Text:
(Examples or comments displayed on slide, if any).

Instructor's Notes

(If applicable)

Fine grained auditing is done through audit policies that define the criteria by which a specific column should be audited.
The example in the slide sets up auditing in the event that a query is executed which returns the SALARY column of the DAVE.EMPLOYEE table for and Sales Managers.
See the supplied script AUDIT_EMPLOYEE.SQL for a working example of this code.
Supplemental Notes
- Other relevant DBMS_FGA procedures include DISABLE_POLICY, ENABLE_POLICY and DROP_POLICY.
- This feature is only available with the cost-based optimizer.

Other Text:
(Examples or comments displayed on slide, if any).

execute DBMS_FGA.ADD_POLICY( object_schema => 'dave',
object_name => 'employee',
policy_name => 'employee_salary',
audit_condition => 'title = ''Sales Manager'' ' ,
audit_column => 'salary')

Conditional auditing of SELECT

Instructor's Notes

(If applicable)

To view audited activity query the DBA_FGA_AUDIT_TRAIL view.
From the results displayed in the example we can tell that user DAVE performed a SELECT against the EMPLOYEE at SCN 5039872 (note that the TIMESTAMP is also available).
See the supplied script AUDIT_EMPLOYEE.SQL for a working example of this code.
Supplemental Notes
- Other relevant views include DBA_AUDIT_POLICIES, which contains information about the policies that have been defined.
- If you use the SCN with the Oracle9i flashback query feature, you could re-execute the query and see the same data that the user saw.

Other Text:
(Examples or comments displayed on slide, if any).