Oracle PL/SQL for DBAs

Chapter 6. Fine-Grained Auditing

Auditing is a mechanism for logging the activity of database users. By supplying a way to associate specific actions with specific users, auditing provides accountability , a cornerstone of security. Traditional Oracle auditing logs information when users make changes to the database, but not when they merely query the data. Fine-grained auditing (FGA ), introduced in Oracle9i Database, extends logging to capture both changing and querying data. FGA is crucial for security, but it also provides an excellent way to analyze SQL usage and the performance of both individual statements and the overall application. It gives you a method for analyzing patterns of data access, which can be a powerful tool in improving your database performance.

This chapter describes how to use fine-grained auditing to your best advantage. By allowing you to choose which actions are to be audited and what information is to be collected, FGA lets you customize its features to suit your own database and application requirements.

FGA functionality is provided via the Oracle built-in package DBMS_FGA. In this chapter, I’ll describe the DBMS_FGA programs that allow you to establish and use FGA policies for your database and how the FGA features available in Oracle9i Database compare with those available in Oracle Database 10g. I’ll also describe how FGA interacts with another new Oracle Database 10g feature, flashback query, which allows you to see exactly what users saw when ...