4 Oracle Audit Vault Data Warehouse Schema

4.1 About the Oracle Audit Vault Data Warehouse Schema

The Oracle Audit Vault repository has an internal data warehouse schema that manages the audit data collected from the source databases. The data warehouse collects the data from the Oracle Audit Vault collection agents, organizes it, and then provides it in report format for the reports described in Chapter 3, "Using Oracle Audit Vault Reports."

If you plan to design custom reports using tools such as Oracle Business Intelligence Publisher and the Oracle Business Intelligence Suite, you must understand the structure of the Oracle Audit Vault data warehouse schema. This appendix describes the schema in detail. You must also understand the structure of the audit events provided by the source database products; Oracle Database, Microsoft SQL Server, Sybase Adaptive Server Enterprise, and IBM DB2. Appendix A through Appendix D describe the structure of these audit events.

4.2 Oracle Audit Vault Audit Data Warehouse Architecture

Figure 4-1 illustrates the Oracle Audit Vault audit data warehouse architecture. Audit Vault stores the audit records in the raw audit data table, which is typical of a traditional online transaction processing (OLTP) system that is optimized for insert performance for the records arriving from their audit sources.

Figure 4-1 Architecture of the Oracle Audit Vault Audit Data Warehouse

Description of "Figure 4-1 Architecture of the Oracle Audit Vault Audit Data Warehouse"

Audit records, stored in the raw audit data table go through an extraction and transformation process before the data loading process (ETL). The ETL operation takes place in the staging area. Oracle Audit Vault optimizes data in the data warehouse for data analysis, and includes the metadata and summaries that aid in these data analysis.

If you have been granted the AV_AUDITOR role, then you can directly access audit data in the audit data warehouse to analyze data, generate reports, and perform data mining. See Oracle Database Data Warehousing Guide for more information about Oracle data warehouses.

4.3 Design of the Audit Data Warehouse Schema

The audit data warehouse uses a logical design to model the logical relationships among the entities (tables) and their attributes (columns) as entity-relationship modeling. The audit record is the most important information, and it contains attributes or columns that describe it. Other information about the audit record is linked by foreign key to other tables that store this related information. This related information includes items such as its source information, its event information, its description of the objects in the source on which users performed actions, the client computer information from which these events originated, and the time when these events occurred. In data warehouse terminology, the audit record forms the fact table and its most important attributes form the dimension tables.

Figure 4-2 shows how Oracle Audit Vault uses a star schema to model the audit data warehouse. A star schema optimizes performance by keeping queries simple and providing fast response time. All the information about each level is stored in one row. For this star schema, the audit record is an entity (the fact table, AUDIT_EVENT_FACT) in the center of the star. The surrounding dimension tables describe the attributes of the AUDIT_EVENT_FACT fact table. If the audit records in the AUDIT_EVENT_FACT table have before and after values, then they can be accessed using the procedure described in "Accessing Data Trace Values".

Figure 4-2 Structure of the Oracle Audit Data Warehouse

Description of "Figure 4-2 Structure of the Oracle Audit Data Warehouse"

The audit data warehouse involves a fact (the entity), which is an action, and dimensions (the attributes), which are details about the action. For example, a login attempt is a fact (an audit record). Who logged on, onto what system, at what time, using what authentication system, using what user name and password, and from what system are all dimensions (the attributes) about this fact. In the audit data warehouse, each fact represents an audit record and each dimension represents unique information about that audit record that further describes the audit record.

4.4 How the Fact Table and Dimension Tables Work

The fact table, AUDIT_EVENT_FACT, is linked to each dimension table by its foreign key. The fact table in the audit data warehouse contains the audit record ID, some attributes of the audit record for report generation, and the foreign keys to these dimensions. The main measure of the fact table is the result, whether a particular event was a success or failure.

A dimension is a structure, often composed of one or more hierarchies, that categorizes data to enable proper analysis of the data. Dimensions represent natural 1:n relationships between columns or column groups (the levels of a hierarchy) that cannot be represented with constraint conditions. Going up a level in the hierarchy is called rolling up the data, and going down a level in the hierarchy is called drilling down the data.

Level relationships specify top-to-bottom ordering of levels from most general (the root) to most specific information. They define the parent-child relationship between the levels in a hierarchy. A dimension hierarchy shows these level relationships. For example, the source dimension consists of two levels, source type and source, with the source being the child of its parent source type, as shown in Figure 4-3.

Figure 4-3 Source Dimension Hierarchy

Description of "Figure 4-3 Source Dimension Hierarchy"

The primary key in the dimension tables is a unique identifier. Primary keys are represented with the characters PK. Foreign keys are represented by the characters FK.

The audit data warehouse includes the following dimensions:

• Client Host. This dimension consists of various systems that are used by clients to perform the operation. The basic hierarchy is IP address, subnet, and domain. The CLIENT_HOST_DIM dimension table, described in Section 4.6.2, stores this information. Oracle Audit Vault populates this table dynamically, as the audit records are entered into the raw audit data table.

• Client Tools. This dimension represents the information about the tools used to connect to the audit source database. The CLIENT_TOOL_DIM dimension table, described in Section 4.6.3, stores this information.

• User. This dimension tracks the user information that is associated with the events occurring at the source database. There is no hierarchy associated with the user information. The USER_DIM dimension table, described in Section 4.6.10, stores this information.

• Target. This dimension contains the information about the object on which the event is performed. The target is the object of the event. For example, if a user is granted a privilege, then the user becomes the target. If there is a query on the table, then the table is a target. The hierarchy is based on ownership of the target objects.

  The TARGET_DIM dimension table, described in Section 4.6.8, stores this information. Oracle Audit Vault updates the TARGET_DIM table dynamically as audit records are entered into the raw audit data table. The target name is stored with the owner name appended to the target name (for example, SCOTT.EMP to represent the EMP table in the SCOTT schema).

• Event. This dimension is built on the various events that can be performed in any of the source databases. Oracle Audit Vault uses a category of events to group events, and this forms the hierarchy used by this dimension. The EVENT_DIM dimension table, described in Section 4.6.5, stores this information.

• Time. This dimension tracks actions over time. It is the most common use of the Oracle Audit Vault data warehouse. The hierarchy for time is based on calendar year.

  The TIME_DIM dimension table, described in Section 4.6.9, stores this information. The time dimension tracks event time as well as for the time when the record was received into the raw audit data table. The granularity of the time dimension is one day, and the actual time of the event and recording of the event are stored as measures in the fact table. Oracle Audit Vault uses this time measurement to filter events to granularity smaller than a day.

• Context. This dimension is used to represent the context information related to the audit event. This dimension has three levels: sub_context, context, and parent_context. You can use these levels to group events based on the context during analysis. The CONTEXT_DIM dimension table, described in Section 4.6.4, stores this information.

• Source. This dimension consists of the list of source databases that send audit data to the data warehouse. The SOURCE_DIM dimension table, described in Section 4.6.7, stores this information.

• Privileges. This dimension represents the information about the privileges used during the event. There is no hierarchy for this dimension. The PRIVILEGES_DIM dimension table, described in Section 4.6.6, stores this information.

4.5 Fact Table Constraints and Indexes

Table 4-1 lists the constraints in the AUDIT_EVENT_FACT table. Each constraint references the primary key of a dimension. All constraints are in RELY DISABLE NOVALIDATE mode. The constraints are guaranteed to be validated by the extract, transform, load (ETL) process. RELY is specified to take advantage of query rewrites based on the constraint even though they are disabled.

Table 4-2 lists the local bitmap indexes in the AUDIT_EVENT_FACT table.

4.6 Relationships Between the Fact and Dimension Tables

Figure 4-4 shows the relationships between the tables of the Oracle Audit Vault data warehouse.

Figure 4-4 Tables in the Oracle Audit Vault Data Warehouse

Description of "Figure 4-4 Tables in the Oracle Audit Vault Data Warehouse"

Table 4-3 through Table 4-12 contain information about the individual tables, including their columns, the data types for those columns, and whether the columns are allowed to be null. When the column is actually a reference to a dimension table, the referenced table is also listed. The asterisk and 0...1 show a one-to-many relationship that exists between the fact table and the dimension table.

4.7 Accessing Data Trace Values

You can include before and after values that have been collected from the Oracle redo logs in your data warehouse queries. To do so, use the AVSYS.AV$DW_BEFORE_AFTER PL/SQL package. Only users who have been granted the AV_AUDITOR role can invoke this package. This package contains one function, DATA_TRACE_ROWS, which is a pipelined table function that can create a virtual table listing before and after values from the redo log.

The syntax for the DATA_TRACE_ROWS function is as follows:

AV$DW_BEFORE_AFTER.DATA_TRACE_ROWS (
 rec_id   IN NUMBER,
 src_dim  IN NUMBER,
 rec_time IN TIMESTAMP WITH LOCAL TIME ZONE)
RETURN before_after_tab PIPELINED; 

In this specification:

• rec_id refers to the record ID number from in the RECORD_ID column in the AUDIT_EVENT_FACT table.

• src_dim refers to the source dimension table number from the SOURCE_DIM column in the AUDIT_EVENT_FACT table.

• rec_time refers to the time that the record was created in the Audit Vault repository, based on the local time zone. It comes from the AV_TIME column in the AUDIT_EVENT_FACT table.

Example 4-1 shows a query that uses the DATA_TRACE_ROWS function to include before and after values in a report similar to the Data Access Report you can access through the user interface."

select t.owner_name||'.'||t.target_name table_name, e.event_name, 
f.event_time, x.column_name, x.old_value, x.new_value 
from event_dim e, target_dim t, audit_event_fact f, 
table(av$dw_before_after.data_trace_rows(f.record_id, f.source_dim, 
f.av_time)) x 
where f.event_dim = e.dimension_key 
and f.target_dim = t.dimension_key 
and f.data_values_cnt > 0;