By Janet Wakeley
Edited by Gary Gordhamer

Oracle Database has an extremely robust and granular audit capability. It does little good, though, if not properly secured or if there are so many audit records generated that it is impossible to review. Oracle Audit Vault provides a solution to securely capture your audit records, alert on potential incidents and attest to the periodic review of the audits, ultimately resulting in a more secure and regulatory compliant environment.

Introduction to Oracle Audit Vault

Regulatory Compliance

The myriad regulations one must comply with these days can be quite daunting. Among the various requirements contained within these regulations are directives to audit. Auditing affords the opportunity to prove compliance and can also help the overall security of your systems by bringing anomalies into focus that can then be acted upon. Although most database technologies provide some level of native auditing, determining how to map the audit statements to the regulatory requirements can be quite challenging. Once auditing is enabled and records are being generated, they need to be secured so they cannot be tampered with, and they also need to be reviewed for suspicious activity. Finally, you will need a way to record the results of reviewing potentially thousands of records and then clean up all those audit records. You may be starting to get a headache and think you will need an army to do this, but, alas, there is a glimmer of hope: Oracle Audit Vault (OAV).

OAV provides a solution for these issues. It comes preconfigured for reporting on common criteria among several regulated industries such as PCI (Payment Card Industry), HIPAA (Health Insurance Portability and Accountability Act) and SOX (Sarbanes-Oxley). OAV also provides the ability to purge records, attest to reports and alert on custom configured criteria.

Architecture

The architecture of OAV consists of two major components that work together to store and secure the audit data. First is the Audit Vault Server, a standalone application that contains a data warehouse built on a customized installation of Oracle Database 10g, Oracle Database Vault, and OC4J components to support the Audit Vault Console and Enterprise Manager Database Control. Second is the Audit Vault Collection Agent that manages the collectors and maintains the Audit Vault Wallet. The collector acts as the middleman between the Oracle Audit Vault Server and the target system to pull audit trail data. The wallet is used to store and maintain the passwords needed for the collectors to connect to the target source. Figure 1 is a diagram from the Oracle Audit Vault Administrator’s Guide that provides an overview of the basic architecture.

The Audit Vault Server consists of an audit data store, the Audit Vault Console and services to manage data collection, alerts, monitoring, and reporting. Oracle Database Vault protects the audit data by restricting administrative access and is included with the Oracle Audit Vault Server. The OAV Collection Agent Components consist of the Audit Vault Collector Manager, Oracle Wallet to hold the credentials used to authenticate Audit Vault users, and utilities used to configure and manage the Audit Vault including AVCA, AVCTL, AVORCLDB, AVMSSQLDB, AVSYSDB, and AVDB2DB command-line tools.

Installation and Configuration

Supported Platforms

Oracle Audit Vault can be installed and configured several different ways depending on your environment. The software can be installed on a variety of server operating systems including AIX, HP-UX, Linux, Windows, and Solaris. The database source targets that can be monitored include Oracle Database, Microsoft SQL Server, Sybase Adaptive Server Enterprise and IBM DB2. This document is based on limited experience with the vault software installed on a Linux x86-64 architecture and only used to monitor Oracle Database targets. The options for installation are basic for a single server and advanced if using a RAC configuration. Perhaps the biggest difference between Oracle Audit Vault and other Oracle software installations are the options available for securing the users and the communications between the components.

Secure Communication

Management communication between the Oracle Audit Vault Server and collection agent is secured by using the HTTPS protocol to encrypt data. Keystores are used to enable the Audit Vault Console to communicate securely with the Audit Vault Server.

Roles

Often when dealing with audit records it is important to prove that separation of duties exists between auditors and those being audited, as well as those with access to those records. Oracle Audit Vault uses Database Vault realms to achieve this separation by limiting the privileges of users, including super users such as SYS. It is important to configure the users appropriately and to have an understanding of the impact of this configuration on normal maintenance activities.

The installation prompts you to add Audit Vault users, including an Oracle Audit Vault administrator account assigned the AV_ADMIN role. You can optionally create an OAV auditor account assigned the AV_AUDITOR role to facilitate the separation of duties. The administrator account is used to administer, configure and manage the system. This includes registering audit sources and configuring parameters used to populate the OAV data warehouse. The auditor account is used for reporting, analysis and the creation of alerts to determine intrusions, anomalies and other security trends. In addition, two Oracle Database Vault users are created and assigned the DV_OWNER and DV_ACCTMGR roles, respectively. The DV_ACCTMGR role is used to create and maintain users, while the DV_OWNER role is used to grant roles and administer the database. Users are managed on the Audit Vault Server through SQL*Plus. Neither the Oracle documentation nor a quick Google search describes any provision for centralizing the management of OAV users.

Collection Agents and Collectors

OAV uses a collection agent to manage collectors that retrieve audit trail records from source databases and send them to the OAV server for storage, alerting and reporting. Collection agents are configured for each host and one or more collectors for each source database. For example, if five hosts contain 10 databases, then you would configure one collection agent for each host and one or more collectors for each of the 10 databases. Alternatively, one collection agent can be configured to remotely manage collectors across several hosts. The latter configuration is advantageous for maintenance activities such as patching because only one agent will need to be maintained.

The collector configuration for each source database is based on the type of audit trail the source database is configured to use. There are three collector types available for use with Oracle databases (i.e., DBAUD, OSAUD and REDO) and one collector type for each of the other supported database technologies (i.e., MS SQL SERVER, IBM DB2 and Sybase ASE.) To determine which Oracle collector type to use depends both on what audit trail records need to be captured and where they are stored. The DBAUD collector type is used when audit trail records are written to SYS.AUD$, SYS.FGA_LOG$ or DVSYS. AUDIT_TRAIL$ dictionary tables. The OSAUD collector type is used when database audit files are written to the operating system. In some organizations, writing database audit records to the operating system may be preferred for security requirements and/or to maintain a separation of duties. Lastly, the REDO collector type is used to collect audit data from the logical change records (LCRs) from the REDO logs. This collector type is best if there is a need to track before and after value changes of DDL and DML using the database redo log files.

Capacity and Performance

Storage Requirements

According to Oracle, 1 million audit records use between 600 and 900 MB of disk space or approximately 750 bytes per audit record. The minimum audit record retention setting within the Oracle Audit Vault warehouse is one year, and the default installation is set at 10 years. It is important to configure enough storage to meet the policies for record retention defined by your organization and/or the regulatory mandates that govern your industry. Depending on how many systems are being managed by OAV and the audit policies enforced on those systems, the storage requirements could easily be in the terabyte range. However, once the source audit records are secured and moved to the OAV warehouse, they can be purged automatically from the source systems, freeing up capacity for the target databases. If the Audit Vault collectors are down, records will be retained on the source system until the collectors are brought up and the audit data is moved to the raw data store. An ETL process is used to move the audit records to the data warehouse where they are optimized for reporting. Oracle Audit Vault can be configured to store the records from one to 99 years based on the time the record is collected, not the time the audited event occurred. After the retention period, the records are automatically purged.

Performance

Oracle Audit Vault architecture is optimized for the storage and reporting of audit records. Typically, performance is not as critical in this environment as, say, an online transaction processing system, as it is mainly used for reporting. One exception may be if you are using the alerting capability to detect security events and need near real-time reporting of these events.

Perhaps the bigger performance consideration is that of the source systems. This is true whether you are using OAV or not, provided you have native auditing capability enabled and audit statements issued. According to Oracle Database Auditing Performance Guidelines, the additional throughput and CPU usage during a test of approximately 250 audit records per second using standard audit commands was 4.57 percent and 8.77 percent, respectively, for records written to the database.

In addition, the collectors associated with each database run on the source and will impact the load. Oracle states that additional overhead for 100 audit records per second is as follows:

• 2 percent CPU overhead for DBAUD
• 3 percent CPU overhead for OSAUD
• 6 percent CPU overhead for REDO

Remember that the time invested up front understanding your auditing requirements will go a long way in properly sizing the OAV environment.

Auditor Tasks

Reports and Attestation

Oracle Audit Vault comes seeded with a wide selection of industry-related reports for entitlements, financial, health care, credit cards, and database activity. Also, you can create user-defined reports within the tool based on existing reports and categorize the reports into logical groups such as the associated regulation. The caveat is that the reports will only be as good as the data you are auditing and collecting from the source systems. There is a rich set of features associated with OAV reports, including the ability to highlight, filter, chart, compare and hide data. Reports can be scheduled to run periodically and can be emailed to a list of predefined recipients. Oracle Audit Vault has an open data warehouse schema, which can be used to build custom reports using Oracle Application Express, business intelligence tools such as Oracle BI Publisher or third-party tools. Another great feature is the ability to annotate and attest to the generated reports. Notes can be added to a report and saved or saved and attested, which adds the username and timestamp to the report. Some policies may require just the attestation and not the actual report to be saved, which could significantly reduce the storage requirements.

Unfortunately, in OAV, if the report is deleted, so are the annotation and attestation fields. Figure 2 provides an example of the annotation and attestation input screen.

Alerts

Alerts can be configured for Oracle Database, Microsoft SQL Server, Sybase ASE, and IBM DB2 source databases. An alert is raised when the incoming audit data violates specific audit policies. Templates can be used to configure notification emails that describe the alert and automate the distribution of alert notification. Remember that alerts are raised when the audit data reaches the Oracle Audit Vault database, not when the actual action occurs and depends on several factors, including network latency, collector availability, the volume of audit records, and how frequently the audit data collectors are set to collect the audit records.

Since OAV collects audit trail records from source databases only, it is not possible to automate log correlation with other components of the tech stack. This means that if an alert signals a potential security breach, the investigation of that alert may involve manually gathering log data from other sources, such as network devices and servers, to formulate a more comprehensive snapshot of the event.

Admin Tasks

Maintenance

Common Oracle Audit Vault administrative tasks can be performed using the Audit Vault Console GUI or the command-line interface equivalent commands in the AVCA and AVCTL utilities. Tasks such as starting, stopping and checking the status of Audit Vault Server as well as managing the collector properties, enabling/disabling alerts, managing the data warehouse, creating notifications, configuring source database attributes and removing source databases are all part of normal maintenance activities.

Another equally important maintenance task is purging audit records once they are secured in the Audit Vault. This includes the source databases as well as the Audit Vault itself. Oracle provides the DBMS_AUDIT_MGMT PL/SQL package to perform audit trail cleanup tasks, including scheduling purge jobs, moving the audit trail to a different tablespace, setting archive timestamps in the audit trail, etc. If your source database is Oracle release 10.2.0.3 through 11.1.0.7, you can download the DBMS_AUDIT_MGMT package and data dictionary views from My Oracle Support. For database versions 11.2 or above, the DBMS_AUDIT_MGMT package is installed by default. The package is not available for Oracle9i Database and older versions of the Oracle Database.

Lastly, it is important to stay current on patches to get the latest security and bug fixes. Oracle Audit Vault requires that the same patchsets and bundles be applied to the AV Server and Agents because some patches may change the way agents interact with the server. As noted earlier, this may impact your configuration choice as whether to install one remote agent to manage many collectors, rather than installing multiple agents for every host. Besides installing the patches for Audit Vault, the underlying repository database needs to be patched as well. AV Server 10.2.3.0 uses a 10.2.0.3 database, 10.2.3.2 AV Server uses a 10.2.0.4 database and the current AV Server release 10.3 uses an 11.2.0.3 database. Oracle recommends that the latest PSU patches be applied to the repository database.

Summary

Oracle Audit Vault provides a solution to securely capture your audit records, alert on potential incidents and attest to the periodic review of the audits. Although there are some limitations and challenges — such as correlating suspicious activities and maintaining patch levels — the tool can be an effective means to a more secure and regulatory compliant environment if proper consideration is given to designing the system appropriately for the audit data requirements

References

• Master Note for Oracle Audit Vault [ID 1199033.1]
• Oracle Database Auditing: Performance Guidelines
• Ategrity Solutions – Oracle Audit Vault
• Oracle Audit Vault Documentation Release 10.2.3.2
• Oracle Audit Vault Administrator’s Guide Release 10.2.3.2
• Oracle White Paper August 2010: Oracle Database Auditing Performance Guidelines

About the Author

Janet Wakeley, CISSP, works for GE Healthcare as the data management security leader with more than 14 years of experience on various Oracle technologies and more than half that time focused on security-related concerns.