Oracle PL/SQL Programming, 5th Edition

Fine-Grained Auditing

Fine-grained auditing (FGA) provides a mechanism to record the fact that some user has issued certain statements against a table and that certain conditions are met. The mechanism records the user’s exact SQL statement as well as other details such as the time, terminal, and so on.

Traditional auditing in Oracle is the mechanism by which Oracle records which schema performed which action: Joe executed procedure X, John selected something from table Y, etc. The records of all these activities—known as the audit trail—go into a single table in the SYS schema, AUD$, which is exposed to users through several data dictionary views—for example, DBA_AUDIT_TRAIL. Audit trails can also be written to operating-system files instead of to database tables. Regardless of where this information is written, the basic problem with regular auditing still remains: it merely keeps track of who executed what statement, not specifically what was done. For example, it shows that Joe selected some records from table ACCOUNTS, but not which particular records. If you want to know the values changed, you can place DML triggers (discussed in Chapter 19) on the tables and capture the values in some table you have defined. But because it’s not possible to define a trigger on SELECT statements, that option will not work either.

This is where Oracle’s FGA comes in. FGA functionality is available via the built-in package DBMS_FGA. FGA was introduced in Oracle9i Database where it applied only ...