Introduction

Any security policy must maintain a record of system activity to ensure that users are held accountable for their actions. Auditing helps deter unauthorized user behavior which may not otherwise be prevented. It is particularly useful to ensure that authorized system users do not abuse their privileges.

Fine-grained auditing can serve as an "early warning system" of users misusing data access privileges, as well as an intrusion detection system for the database itself.

Fundamental Auditing Requirements

Robust, Comprehensive Auditing

A strong audit facility allows businesses to audit database activity by statement, by use of system privilege, by object, or by user. You can audit activity as general as all user connections to the database, and as specific as a particular user creating a table. You can also audit only successful operations, or unsuccessful operations. For example, auditing unsuccessful SELECT statements may catch users on "fishing expeditions" for data they are not privileged to see. Audit trail records can be stored in an Oracle9i table or combined with operating system audit trails on selected operating systems, for ease of management. Audit trail records stored in an Oracle9i table can be viewed through ad hoc queries or any appropriate application or tool. Storing certain audit trails separately enables an enterprise to audit the actions of even the most privileged users.

Efficient Auditing

Auditing should be implemented efficiently: statements are parsed once for both execution and auditing, not separately. Also, auditing is implemented within the server itself, not in a separate, add-on server which may be remotely situated from the statements which are being executed (thereby incurring network overhead). The granularity and scope of these audit options allow businesses to record and monitor specific database activity without incurring the performance overhead that more general auditing entails. And, by setting just the options of interest, you should be able to avoid catch-all, and throw-away audit methods which intercept and log all statements, and then filter them to retrieve the ones of interest.

Customizable Auditing

To record customized information that is not automatically included in audit records, it is possible to use triggers to further customize auditing conditions and audit record contents. Database triggers are user-defined sets of PL/SQL or Java statements, stored in compiled form. While users explicitly execute stored procedures, database triggers are automatically executed (or "fired") within the data server based on pre-specified events. A trigger is defined to execute either before or after an INSERT, UPDATE or DELETE, so that when that operation is performed on that table, the trigger automatically fires. For example, you could define a trigger on the EMP table to generate an audit record whenever an employee's salary is increased by more than 10 percent and include selected information, such as before and after values of SALARY.

Fine Grained, Extensible Auditing

Standard auditing can monitor privileges and objects, and provides triggers to monitor DML operations such as INSERT, UPDATE, and DELETE. By contrast, monitoring SELECT statements is facilitated by fine-grained auditing, which allows the monitoring of data access based on content. In this way, you can specify auditing conditions, and obtain more specific information about the environment and query result. This additional information helps you reconstruct audited events, and determine whether access rights have been violated. It prevents users from bypassing database auditing.

Auditing in Multitier Application Environments

Many three-tier applications authenticate users to the middle tier, and then the transaction processing monitor or application server connects as super-privileged user, and performs all activity on behalf of all users. But it is important to be able to preserve the identity of the real client over the middle tier and enforce "least privilege" through a middle tier. In addition, you may want to audit actions taken on behalf of the user by the middle tier. Auditing user activity, whether users are connected through a middle tier or directly to the data server, enhances user accountability, and thus the overall security of multitier systems.