Oracle Security

Chapter 11. Developing a Sample Audit Application

In business, you sometimes need to maintain detailed records about people, places, and things. You may feel that the information you are maintaining is so important to your company that more detailed data is needed to track:

• Who created the information

• Who has modified the information

• What was changed

As you saw in Chapter 10, Oracle provides ways to track information about modifications that have been made to a table — at the table level. However, Oracle does not provide an easy way to keep track of actions that have been performed on specific rows within a table. For that information, you need to develop a customized application.

In this chapter, we will show you a simple, but effective, application^[6] you can use for implementing transparent audit trails. Depending on how much auditing you implement, this application may not overly impact your database’s performance, and it does not require a lot of complex maintenance. This sample trigger-based application can give you an idea of how you can implement your own audit trail application, or you can use this application either in part or as a complete application to perform whatever auditing you need.

About the Audit Trail Application

To implement the audit trail application described in this chapter, you need to create the following:

• Three tables

• Two sequences

• One PL/SQL package (with two stored procedures)

• Three database triggers per audited table

• Three additional columns in each ...