Audit design with Oracle fine-grained auditing

With the passage of HIPAA, the importance of fine-grained auditing in Oracle databases cannot be overstated. See how Oracle's most powerful auditing tool can help you meet the steep audit trail requirements you face in your organization.

In my last installment, I examined the different auditing tools provided by Oracle and examined the system-levels triggers for auditing. Now I will explore Oracle’s most powerful auditing tool, the Oracle9i fine-grained auditing (FGA) package.

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

The new HIPAA health care laws have placed a tremendous burden on enterprises using Oracle database systems. According to the law, organizations must provide complete audit trails for all DDL (i.e., schema changes), DML (e.g., updates, insert, deletes), and select audits of confidential patient information. HIPAA also requires that all health care companies (not just hospitals) archive audit trails of anyone who views patient data, and HIPAA provides severe penalties, including jail time, for companies that fail to implement select auditing. Hence, thousands of Oracle shops are rushing to implement Oracle auditing mechanisms.

Starting with Oracle9i you'll see a more sophisticated auditing mechanism using the dbms_fga package. The dbms_fga package allows you to specify the auditing rules for a particular column of a table and report on anyone whose queries match the criteria.

In Listing A, I create a policy called expensive_books that acts as a trigger for any queries against the book where anyone views a book row where book_retail_price is greater than $50.

Note the parameters of the FGA policy function can give you many choices of auditing options. You can specify the table to be audited and the Boolean criteria for triggering a select audit.

All audit information will be written to an Oracle view called dba_fga_audit_trail. Listing B shows the contents of each audit row; note that all details are captured including the user ID and the actual SQL that was used to read the table. Of course, in a busy system this view will become huge very quickly, and mechanisms must be written to periodically offload the audit data.

Now, let’s test the policy and run some SQL queries. In Listing C, I've run a variety of queries against the book table.

In this example, you can see that not all SQL statements will meet the Boolean criteria and only rows where books that cost over $50 will appear in dba_fga_audit_trail.

For example, if you have a 1,000,000 row patient table that is viewed ten million times per day, there will be ten million rows written to dba_fga_audit_trail, and the audit table will become larger than the patient table in just a few hours. So you must develop a mechanism to offload the audit data before the tablespace becomes full. Many shops write a dbms_job routine to transport the table data into a special tablespace, usually using WORM (write once, read many) media such as CD-ROM.

Now let’s look at ways to display the audit information. Listing D shows a script that I used to display the audit table entries. Note that you can roll up select counts by hour of day by aggregating on the timestamp column.

The report is shown in Listing E. I got complete details from the audit, and only those rows specifically in the policy are written to dba_fga_audit_trail.

Additional information
Here is a list of Oracle auditing consultancies: Check out this list of third-party auditing software:

Getting more details
Oracle professionals must overcome obstacles to capture trillions of bytes of audit information and find ways to safely archive the audit trails. There are numerous consulting companies providing auditing services for Oracle shops, and vendors with Oracle auditing tools. While it is impossible to get into all of the auditing details in this short article, you can find some help out there on the Web.

Editor's Picks