Synopsis. Oracle 10g extends the original fine-grained auditing (FGA) features that Oracle 9iR2 introduced, including expanded capacity to audit for specific events based on statements issued, the columns that a SQL statement has accessed, and even the subset of data that the statement is affecting. This article – the first in an ongoing series on Oracle 10g Security – demonstrates how to implement FGA in Oracle 10g and illustrates how to take advantage of the newest Oracle 10g Release 2 (10gR2) FGA features.
Imagine this scenario: Your CIO calls you into a confidential meeting with the Director of Accounting at your company and informs you that a hitherto highly-trusted employee is suspected of stealing vast sums of money from the company. “He has been quite ingenious,” says the Director. “He edited a Vendor’s credit card number, changed it to match that of his personal credit card number, then created a series of fake invoices for the vendor. Once the invoices were created, he issued credit memos for the invoices, thus generating large credits for his credit card account. And then he simply deleted the invoices and changed the credit card numbers back to the original values.”
The lines of concern thicken on the Director’s face as she continues. “And we think he’s still trying to do this, but at maddeningly infrequent intervals! Can you help us catch him? We need solid proof of what they’ve done so we can prosecute him and retrieve the funds that he embezzled.” Fortunately, Oracle 10g provides you with several tricks up your sleeve. You tell the Director and CIO about your plan to catch a thief … and they smile broadly. “Congratulations, “ says the CIO, “you’ve just earned your pay for the week.”
Basic Auditing: An Overview
All unintended drama aside, this situation is probably not as infrequent as we might imagine. Data continues to become more voluminous and the need to keep sensitive data secured will continue unabated for the foreseeable future. Fortunately, while Oracle 10g provides us with several excellent tools to keep sensitive data secure – a topic for the next article in this series – DBAs also need to know when that sensitive data is being touched by queries and manipulated by DML statements.
Oracle has long provided a set of standard auditing tools for observing and tracking the activity within a database’s tables, sessions, and objects with the AUDIT command. For example, if I wanted to audit for any activity against the HR.EMPLOYEES table, I would first have to change the value for the AUDIT_TRAIL initialization parameter to DB and bounce the database to activate auditing. Then I could simply issue the following command to perform the standard audit:
AUDIT ALL ON HR.EMPLOYEES BY ACCESS;
I could then query the DBA_AUDIT_TRAIL view to see if any user session had issued a SELECT, INSERT, UPDATE, or DELETE statement against that table, as well as view what SQL statements had been issued.
This approach, however, does leave a lot to be desired. For one thing, I can only limit my auditing activities to one type of SQL statement, or all statements. Also, with AUDIT, I have no choice but to audit each and every statement that’s been applied against a table. In our scenario, I really need to focus on only a few columns in the database tables that make up the Accounts Payable system – for example, the Vendor’s credit card number — but standard auditing will return all SQL statements regardless of whether this column was accessed.
Fine-Grained Auditing (FGA) Policies and DBMS_FGA
Oracle 9i Release 0 provided us with capabilities to perform fine-grained auditing (FGA) through a new package named DBMS_FGA. This package allows me to implement auditing at an extremely low level of granularity against any table in the database through a special database object called an FGA policy.
Just as with standard auditing, I can implement an FGA policy to tell Oracle which table(s) I wish to audit for unexpected activity, and it also tells Oracle which type(s) of SQL statements (SELECT, INSERT, UPDATE, or DELETE) should be audited. Oracle 10g further improves fine-grained auditing via FGA, and offers significant upgrades to those features introduced in Oracle 9i:
Tighter Column References. An FGA policy insures that auditing only is performed when one or more specific column(s) in a table or view are referenced. For example, I can tell Oracle to audit a SELECT statement only when it references one or more specified columns. I can also tell Oracle that a statement should only be audited when any one of the columns listed is found in the statement, or only when all of the columns are found.
Conditional Auditing. I can configure an FGA policy so that auditing is only triggered when a specific data subset has been affected. For example, I can instruct Oracle to trigger an audit only when a row of data is changed via an UPDATE statement that meets the conditional criteria specified. Oracle 10g also allows specification of a NULL condition if there are no conditions to apply.
Combined Audit Trails. In Oracle 10gR2, the standard and fine-grained auditing views have been combined for easier viewing in DBA_COMMON_AUDIT_TRAIL. Also, it’s now possible to write out FGA audit trail information in either XML or extended XML formats to external files. A new Oracle 10g view, V$XML_AUDIT_TRAIL, can be queried directly to view the contents of the generated XML audit trail files. I can use this feature to map out specific operating system directories for storage of the XML audit trail logs, thus providing an even more secure place to which the audit trails can be written.
Event Handling. Finally, I can instruct Oracle to trigger a call to an event handler when a specific event occurs. For example, if a particularly sensitive audit event is raised, I might want to send an e-mail or page to someone in my IT shop’s data security division so that when the event happens we can be prepared to take immediate action against the perpetrator of the violation.
Implementing Fine-Grained Auditing: A Demonstration
To simulate the security scenarios I mentioned at the start of this article, I’ll first construct a new Accounts Payable (AP) schema and three new tables (AP.VENDORS, AP.INVOICES, and AP.INVOICE_DETAILS) within that schema. To show that Oracle 10g now allows views to be audited as well, I’ll also build a reporting view, AP.RV_INVOICE_DETAILS, that joins together these three tables in READ ONLY mode. See Listing 1.1 for links to the corresponding code to construct the schema, including an example of a stored procedure that functions as a handler package for any FGA-triggered event.
Now that the new schema is built and some sample data has been populated, I’ll construct new FGA policies that reference that table. Listing 1.2 shows how to build the new policies and then interrogate the DBA_POLICIES data dictionary view to see the results. Note that by default Oracle 10g will not enable the FGA policies unless specifically told to do so; I’ve purposely left one policy in DISABLED status to illustrate how to achieve this.
Now that the test data is loaded and the FGA policies are in place, I can demonstrate how FGA works. I’ll issue a series of SQL statements against the tables and view in the AP schema to demonstrate how Oracle 10g tracks the execution of the statements. See Listing 1.3 for the end results of these demonstrations, and notice that the statements in the second FGA policy are simply ignored because the policy is not yet enabled.
In Listing 1.4, I’ve constructed some queries against the FGA audit trail data dictionary view, DBA_FGA_AUDIT_TRAIL, that contains the results of any FGA policy that Oracle applied to the statements and data within the AP schema during the prior set of unit tests.
Finally, it’s extremely simple to disable and drop any existing FGA policy, as shown in Listing 1.5. This code shows how to disable the FGA policy against table AP.INVOICES, as well as how to drop the existing FGA policy against the AP.RV_INVOICE_DETAILS reporting view.
Conclusion
Fine-grained auditing is an excellent tool for tracking changes to individual rows and columns of data within database tables and views. Oracle 10g has significantly improved these features by combining the FGA audit trail view with the standard audit trail view, increasing the granularity of the filtering that can be applied against the audited data, and allowing for audit data to be written out to XML-format files for additional security.
In the next article, I’ll ramp up our discussion of Oracle 10g Security features by demonstrating how to prevent access to data before it can be viewed or changed with Oracle 10gR2’s enhanced row-level security features.
References and Additional Reading
Even though I’ve hopefully provided enough technical information in this article to encourage you to explore with these features, I also strongly suggest that you first review the corresponding detailed Oracle documentation before proceeding with any experiments. Actual implementation of these features should commence only after a crystal-clear understanding exists. Please note that I’ve drawn upon the following Oracle 10gR2 documentation for the deeper technical details of this article:
B14214-01 Oracle Database New Features Guide
B14231-01 Oracle Database Administrator’s Guide
B14258-01 PL/SQL Packages and Types Reference
B14266-01 Oracle Database Security Guide
