Choose the proper Oracle design for auditing

Not only is auditing an Oracle database a good administrative practice, but in many cases it is now the law. Auditing methods should be decided during the design stages--not as an afterthought. Find out now what options Oracle provides for auditing.

More than ever, companies are demanding complete audit trails for everything that happens within an Oracle database. Managers want to see audits of all aspects of the database, and government regulations are spurring this interest.

The most important regulation is HIPAA, the Health Insurance Portability and Accountability Act of 1996. Under HIPAA, all Oracle databases in the health care industry must meet rigorous auditing standards, including auditing of the viewing of confidential patient information.

Auditing is not something that is strapped on to the Oracle database, and Oracle designers must carefully choose from among the auditing methods available in Oracle and implement the most appropriate method for their shops.

Oracle auditing falls into these areas:
  • Auditing errors: Audits all server errors, audits of alert log messages, and trace file generation. This is usually done with shell scripts and the Oracle system-level servererror trigger.
  • Auditing database changes: Oracle provides a DDL trigger to audit all schema changes and can report the exact change, when it was made, and by which user.
  • Auditing data updates: U.S. government regulations for banks and financial institutions require that all changes to the database be recorded and kept for a period of several years. Oracle provides the archived redo logs for this purpose and the Oracle9i LogMiner for extraction of database change details.
  • Auditing data viewing: With confidential information being commonplace within Oracle, many companies are requiring audit trails of who views confidential information, such as personnel files, student grades, and hospital patient information. Oracle provides the SQL audit command and Oracle9i fine-grained auditing to track viewing of Oracle rows.

Miss parts of this series?
Get up to speed with these past articles:

Auditing tools
Oracle is arguably the world's most flexible database and offers the Oracle designer many auditing choices. Regardless of the choices, the Oracle designer must carefully plan the auditing scheme. There are several ways to audit within Oracle and the following auditing tools are provided:
  • SQL audit command (for DML)
  • Auditing with object triggers (DML auditing)
  • Auditing with system-level triggers (DML and DDL)
  • Auditing with LogMiner (DML and DDL)
  • Fine-grained auditing (select auditing)

Additional information
There are entire books devoted to Oracle security and auditing. A good example is HIPAA Auditing for Oracle Database Security, written by Donald Burleson & Arup Nanda (2003, Rampant TechPress).

While it is impossible to review all the possible auditing tools in this short article, let’s briefly explore the Oracle system-level triggers and see how they are used for Oracle auditing:
  • Database startup triggers: Database startup triggers can be used to invoke the dbms_shared_pool.keep procedure to pin all frequently referenced PL/SQL packages into RAM.
  • Logon triggers: The logon triggers can be used to store logon information directly inside Oracle tables, thereby providing a complete audit of all those times when users entered your system.
  • Logoff triggers: Oracle logoff triggers can automatically capture elapsed session duration times for all users who have accessed your Oracle database.
  • Servererror triggers: With the servererror trigger, the Oracle administrator can automatically log all serious PL/SQL errors into an Oracle STATSPACK extension table. This table, in turn, has an insert trigger that e-mails the server error directly to the Oracle professional for immediate notification.
  • DDL triggers: Using the Data Definition Language (DDL) trigger, the Oracle DBA can automatically track all changes to the database, including changes to tables, indexes, and constraints. The data from this trigger is especially useful for change control for the Oracle DBA.

Unlike traditional triggers that invoke a procedure when a specific schema object has changed, system-level triggers are global and are not tied to any schema objects.

Because the Oracle system-level triggers can interface with Oracle tables, the logical approach is to create Oracle tables to hold the data. Let's take a close look at how these triggers work as auditing mechanisms.

Auditing DDL
Auditing database changes is easy with the DDL trigger. You start by defining a table to hold the changes, as shown in Listing A.

The DDL trigger executes every time a DDL statement is executed, and adds new entries to your new table, as shown in Listing B.

Now whenever a change is made, the details will be written to your table, and you can create audit reports for management. This report is critical for the Oracle DBA who must track changes to his or her production database. This report uses the DDL trigger and produces a complete audit log of all Oracle databases changes, as shown in Listing C.

Auditing server errors with the servererror trigger
The code snippet in Listing D creates an Oracle table that will capture all information relating to PL/SQL errors.

The servererror trigger takes whatever server error was generated from Oracle PL/SQL and places it into an Oracle table. Note in Listing E that by capturing the user ID and the time of the error, the Oracle administrator can build an insert trigger on the stats$servererror log table and immediately be notified via e-mail whenever a server error occurs.

Now that you have the data inside an Oracle table, you can easily query the table to provide a server error summary report. The report in Listing F shows daily details, but you can also create a weekly rollup of server errors.

Editor's Picks

Free Newsletters, In your Inbox