Oracle PL/SQL for DBAs

Auditing Differences Between Oracle Database Versions

There are a few differences between how both auditing and fine-grained auditing work in Oracle9i Database and Oracle Database 10g. The major differences are listed here. Later sections, in particular "FGA in Oracle Database 10g ,” provide more-detailed information on using these features.

• In Oracle Database 10g, the AUDIT statement , which is used in regular auditing to record the fact that someone selected from a particular table, has been extended to capture information about the SQL statement, something that was not available in earlier releases. (Although it may seem that this enhancement makes FGA and regular auditing in Oracle Database 10g nearly identical, the functionalities are quite different, as we’ll explore later in this chapter.)

• In Oracle9i Database, only SELECT statements are audited by the FGA feature, not the DML statements INSERT, UPDATE, and DELETE. In that release, the only way to capture what has changed is to build triggers on those statements and write to a log table. In Oracle Database 10g, on the other hand, FGA can capture these DML statements as well. Although triggers are needed less often in Oracle Database 10g, it sometimes makes sense to use them in place of FGA. Both approaches have their advantages and disadvantages, as we’ll explore later in this chapter.

• In Oracle9i Database, if any column of the list of columns you provide when you add a policy is referenced, FGA is invoked. In Oracle ...