Oracle PL/SQL for DBAs

Conclusion

Fine-grained auditing provides an excellent tool for logging user accesses to your database by storing information that you can use to track user activities and enforce accountability. You can customize FGA logging to record accesses on specified tables by specified users and under certain conditions (e.g., whether particular columns are referenced or whether the access occurs at a particular time of the day). Although many DBAs think of FGA simply as a security-related tool for ensuring accountabil

FGA in a Nutshell

• FGA can record SELECT accesses to a table (in Oracle9i Database) or all types of DML access (in Oracle Database 10g) into an audit table named FGA_LOG$ in the SYS schema.

• For FGA to work correctly, you must be using the cost-based optimizer (CBO); otherwise, many “false positives” will likely be written to the audit table.

• With FGA, the recording of the trail is done via an autonomous transaction; even if the DML operation fails and issues a rollback, the trail will still exist. This can also lead to false positives.

• The audit trails show the exact SQL statement used by the user, the value of any bind variables, the System Change Number at the time of the query, and various attributes of the session such as the database username, the operating system username, the timestamp, and much more.

ity, you can also use FGA to analyze data access patterns, examine SQL statement usage, and otherwise improve performance. It provides a way to eliminate, or at least ...