10/15

# 4 Oracle Audit Vault Data Warehouse Schema

This chapter contains:

- About the Oracle Audit Vault Data Warehouse Schema

- Oracle Audit Vault Audit Data Warehouse Architecture

- Design of the Audit Data Warehouse Schema

- How the Fact Table and Dimension Tables Work

- Fact Table Constraints and Indexes

- Relationships Between the Fact and Dimension Tables

- Accessing Data Trace Values

## 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-1 Fact Table Constraints and Indexes

Constraint Name | Column Name | Reference Table
--- | --- | ---
`AV$FACT_ASSOC_TARGET_DIM_FK` | `ASSOC_TARGET_DIM` | `TARGET_DIM (DIMENSION_KEY)`
`AV$FACT_AV_TIME_DIM_FK` | `AV_TIME_DIM` | `TIME_DIM (DIMENSION_KEY)`
`AV$FACT_CLIENT_HOST_DIM_FK` | `CLIENT_HOST_DIM` | `CLIENT_HOST_DIM (DIMENSION_KEY)`
`AV$FACT_CLIENT_TOOL_DIM_FK` | `CLIENT_TOOL_DIM` | `CLIENT_TOOL_DIM (DIMENSION_KEY)`
`AV$FACT_CONTEXT_DIM_FK` | `CONTEXT_DIM` | `CONTEXT_DIM (DIMENSION_KEY)`
`AV$FACT_ENDUSER_DIM_FK` | `ENDUSER_DIM` | `USER_DIM (DIMENSION_KEY)`
`AV$FACT_EVENT_DIM_FK` | `EVENT_DIM` | `EVENT_DIM (DIMENSION_KEY)`
`AV$FACT_GRANTEE_USER_DIM_FK` | `GRANTEE_USER_DIM` | `USER_DIM (DIMENSION_KEY)`
`AV$FACT_NEW_TARGET_DIM_FK` | `NEW_TARGET_DIM` | `TARGET_DIM (DIMENSION_KEY)`
`AV$FACT_OBJPRIVILEGES_DIM_FK` | `OBJPRIVILEGES_DIM` | `PRIVILEGES_DIM (DIMENSION_KEY)`
`AV$FACT_OSUSER_DIM_FK` | `OSUSER_DIM` | `USER_DIM (DIMENSION_KEY)`
`AV$FACT_PRIVILEGES_DIM_FK` | `PRIVILEGES_DIM` | `PRIVILEGES_DIM (DIMENSION_KEY)`
`AV$FACT_SOURCE_DIM_FK` | `SOURCE_DIM` | `SOURCE_DIM (DIMENSION_KEY)`
`AV$FACT_SYSPRIVILEGES_DIM_FK` | `SYSPRIVILEGES_DIM` | `PRIVILEGES_DIM (DIMENSION_KEY)`
`AV$FACT_TARGET_DIM_FK` | `TARGET_DIM` | `TARGET_DIM (DIMENSION_KEY)`
`AV$FACT_TIME_DIM_FK` | `TIME_DIM` | `TIME_DIM (DIMENSION_KEY)`
`AV$FACT_USER_DIM_FK` | `USER_DIM` | `USER_DIM (DIMENSION_KEY)`

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

Table 4-2 Local Bitmap Indexes Defined on the AUDIT_EVENT_FACT Table

Index Name | Column Name
--- | ---
`CLIENT_HOST_DIM_IDX` | `CLIENT_HOST_DIM`
`EVENT_DIM_IDX` | `EVENT_DIM`
`OSUSER_DIM_IDX` | `OSUSER_DIM`
`TARGET_DIM_IDX` | `TARGET_DIM`
`USER_DIM_IDX` | `USER_DIM`

## 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.6.1 AUDIT_EVENT_FACT Fact Table

Table 4-3 lists the contents of the `AUDIT_EVENT_FACT` table. This table stores audit data that the collectors have retrieved from the raw audit data store of the source databases.

Table 4-3 AUDIT_EVENT_FACT Fact Table

Column | Data Type | References | Description
--- | --- | --- | ---
`ACTION_COMMAND_STR` | `VARCHAR2(4000)` | None | The SQL text of the command procedure that was executed that resulted in the audit event being triggered
`ACTION_NAME_STR` | `VARCHAR2(4000)` | None | The name of audit event
`ACTION_OBJECT_ID_NUM` | `NUMBER` | None | Object identifier affected by the triggered audit action
`ACTION_OBJECT_NAME_STR` | `VARCHAR2(4000)` | None | Name of the object affected by the action; also the object name corresponding to the `ACTION_OBJECT_ID_NUM` identifier
`ADMIN_OPTION_NUM` | `NUMBER` | None | When an event includes grants, this field shows if the admin option was included
`ASSOC_TARGET_DIM` | `NUMBER` | `TARGET_DIM` | Dimension key value to the `TARGET_DIM` table, which contains information about the schema object on which an audit event is performed
`AUDIT_OPTION_ID` | `NUMBER` | None | ID links to the `AUDIT_OPTION_TAB` table, which indicates how the audit record was created; for example, the audit record was created when the event failed
`AUTHENTICATION_METHOD_ID` | `NUMBER` | None | ID links to the `AUTHENTICATION_METHOD_TAB` table, which indicates how the database connection was authenticated
`AV_TIME` | `TIMESTAMP WITH LOCAL TIME ZONE` | None | The time in which Oracle Audit Vault receives the audit trail record into the repository
`AV_TIME_DIM` | `NUMBER` | `TIME_DIM` | Dimension key value to the `TIME_DIM` table, which tracks actions over time
`CLIENT_APPINFO_STR` | `VARCHAR2(4000)` | None | Deprecated; will be removed in a future release
`CLIENT_HOST_DIM` | `NUMBER` | `CLIENT_HOST_DIM` | Dimension key value to the `CLIENT_HOST_DIM` table, which contains information about various systems that are used by clients to perform an operation
`CLIENT_ID_ID` | `NUMBER` | None | ID links to the `CLIENT_ID_TAB` table, which displays the client identifier value in an Oracle database updated by an application
`CLIENT_TOOL_DIM` | `NUMBER` | `CLIENT_TOOL_DIM` | Dimension key value to the `CLIENT_TOOL_DIM` table, which contains information about the tools and programs used to connect to an audit source database
`COMMENT_TEXT_ID` | `NUMBER` | None | ID that links to the `COMMENT_TEXT_TAB` table, which contains additional information about the audit event
`CONTEXT_DIM` | `NUMBER` | `CONTEXT_DIM` | Dimension key to the `CONTEXT_DIM` table, which contains context information related to an audit event such as transaction ID
`CREATE_DATE_TS` | `TIMESTAMP(6) WITH LOCAL TIME ZONE` | None | Date the audit trail record was created in the Oracle Database Vault audit trail
`CREATED_BY_STR` | `VARCHAR2(4000)` | None | Database login user name of the user who created the Oracle Database Vault rule
`CURRENT_VALUE_STR` | `VARCHAR2(4000)` | None | If the event resulted in the update of a value, this item contains the value after the update. This may include changes in a target name or audit option.
`DATA_VALUES_CNT` | `NUMBER` | None | Number of columns that have changed due to an insert or update
`DATABASE_ID_NUM` | `NUMBER` | None | ID of the database specified by the `USE` database statement, or the default database if no `USE` database statement is issued for a given connection
`DATABASE_NAME_STR` | `VARCHAR2(4000)` | None | Name of the database specified in the `USE` database statement
`DOMAIN_NAME_STR` | `VARCHAR2(4000)` | None | Domain name of the host system
`DURATION_NUM` | `NUMBER` | None | Amount of elapsed time (in milliseconds) taken by the event
`ENDUSER_DIM` | `NUMBER` | `USER_DIM` | Dimension key to the `USER_DIM` table, which tracks information about the user who is associated with the events that occur in the source database
`END_TIME_TS` | `TIMESTAMP(6) WITH LOCAL TIME ZONE` | None | Time at which the event ended. This column is not populated for starting event classes, such as `SQL:BatchStarting` or `SP:Starting`.
`ERROR_ID_NUM` | `NUMBER` | None | Error message number
`ERROR_MESSAGE_STR` | `VARCHAR2(4000)` | None | Error message text
`EVENT_DIM` | `NUMBER` | `EVENT_DIM` | Dimension key to the `EVENT_DIM` table, which contains information about various events that can be performed in the source databases
`EVENT_STATUS_ID` | `NUMBER` | None | ID of the `EVENT_STATUS_TAB` table, which contains the status of the audit action. If the action was successful, it shows a status of `0 - Action`. If the action was unsuccessful, it shows the error code that the action generates, such as `2004 - Security violation` for an Oracle Database security violation.
`EVENT_SUB_CLASS_NUM` | `NUMBER` | None | Type of event subclass. This data column is not populated for all event classes.
`EVENT_TIME` | `TIMESTAMP WITH LOCAL TIME ZONE` | None | Date and time of the creation of the audit trail entry (date and time of the user login for entries created by `AUDIT SESSION`) in the local database session time zone
`FACTOR_CONTEXT_STR` | `VARCHAR2(4000)` | None | The Oracle Database Vault factor identifiers for the current session at the point when the audit event was triggered
`FGA_POLICYNAME_ID` | `NUMBER` | None | Fine-grained audit policy name; only applies to Oracle Database
`GRANTEE_USER_DIM` | `NUMBER` | `USER_DIM` | Dimension key to the `USER_DIM` table, which tracks information about the user who is associated with the events that occur in the source database
`GUID_NUM` | `NUMBER` | None | Global user identifier value, which is dependent on the event class captured in the trace
`INDEX_ID_NUM` | `NUMBER` | None | Index ID associated with an audit event
`INSTANCE_NUMBER_NUM` | `NUMBER` | None | The database instance number in an Oracle Real Applications Cluster
`IS_SYSTEM_NUM` | `NUMBER` | None | Indicates whether the event occurred on a system process or a user process: `1`: system `0`: user
`LOGOFF_DLOCK_NUM` | `NUMBER` | None | Deadlocks detected during the session
`LOGOFF_LREAD_NUM` | `NUMBER` | None | Logical reads for the session
`LOGOFF_LWRITE_NUM` | `NUMBER` | None | Logical writes for the session
`LOGOFF_PREAD_NUM` | `NUMBER` | None | Logical reads for the session
`MODULE_NAME_STR` | `VARCHAR2(4000)` | None | Program that generated the audit trail record
`NEW_TARGET_DIM` | `NUMBER` | `TARGET_DIM` | Dimension key to the `TARGET_DIM` table, which contains information about the schema object on which an audit event is performed
`OBJECT_ID_NUM` | `NUMBER` | None | Object identifier affected by the triggered audit action
`OBJPRIVILEGES_DIM` | `NUMBER` | `PRIVILEGES_DIM` | Dimension key to the `PRIVILEGES_DIM` table, which contains information about the privileges used during an audit event
`ORIGINAL_CONTENT1_STR` | `VARCHAR2(4000)` | None | Original content of an invalid record
`ORIGINAL_CONTENT2_STR` | `VARCHAR2(4000)` | None | Original content of an invalid record
`ORIGINAL_CONTENT3_STR` | `VARCHAR2(4000)` | None | Original content of an invalid record
`OSUSER_DIM` | `NUMBER` | `USER_DIM` | Dimension key to the `USER_DIM` table, which tracks information about the user who is associated with the events that occur in the source database
`OWNER_ID_NUM` | `NUMBER` | None | Type of the object that owns the lock; for lock events only
`PREVIOUS_VALUE_STR` | `VARCHAR2(4000)` | None | If the event resulted in the update of a value, this column contains the value prior to the update. This value can include changes in a target name or audit option. (Non-Oracle databases only)
`PRIVILEGES_DIM` | `NUMBER` | `PRIVILEGES_DIM` | Dimension key to the `PRIVILEGES_DIM` table, which contains information about the privileges used during an audit event
`PRIV_ID_NUM` | `NUMBER` | None | ID of the privilege used to execute a transaction
`PROCESS#` | `NUMBER` | None | Unique process identifier that generated the audit action
`PROXY_INFORMATION_STR` | `VARCHAR2(4000)` | None | The original login name if the event occurred while a set proxy was in effect
`PROXY_SESSIONID_NUM` | `NUMBER` | None | Session ID of the proxy user
`RECORD_ID` | `NUMBER` | None | Unique identifier of the audit record created when the audit trail is inserted into the Oracle Audit Vault repository
`ROW_ID_STR` | `VARCHAR2(4000)` | None | Row identifier; for example, for the Oracle Database table row that was accessed or modified
`RULE_ID_NUM` | `NUMBER` | None | The unique identifier of the rule that was executing and caused the audit event to trigger in Oracle Database Vault
`RULE_NAME_STR` | `VARCHAR2(4000)` | None | The unique name of the rule that was executing and triggered the audit event in Oracle Database Vault
`RULE_SET_ID_NUM` | `NUMBER` | None | The unique identifier of the rule set that was executing and triggered the audit event in Oracle Database Vault
`RULE_SET_NAME_STR` | `VARCHAR2(4000)` | None | The unique name of the rule set that was executing and triggered the audit event in Oracle Database Vault
`SCN_NUM` | `NUMBER` | None | Oracle system change number at the time of query submission when the audit action was recorded
`SERVER_NAME_STR` | `VARCHAR2(4000)` | None | Name of the instance of SQL Server, either server name or server name and instance name, being traced
`SESSION_ACTIONS_ID` | `NUMBER` | None | ID to the `SESSION_ACTIONS_TAB` table, which contains session information of transactions
`SESSION_CPU_NUM` | `NUMBER` | None | Amount of CPU time used by each session
`SESSION_LOGIN_NAME_STR` | `VARCHAR2(4000)` | None | The login name of the user who originated the session
`SEVERITY_NUM` | `NUMBER` | None | Error severity
`SOURCE_DATABASE_ID_NUM` | `NUMBER` | None | ID of the database in which the source of the object exists
`SOURCE_DIM` | `NUMBER` | `SOURCE_DIM` | Dimension key to the `SOURCE_DIM` table, which contains information about the source databases that send audit data to the data warehouse
`SOURCE_EVENTID` | `VARCHAR2(4000)` | None | Audit event identifier from the source database
`SQL_BIND_STR` | `VARCHAR2(4000)` | None | Bind variable data used by the SQL query statement, if any
`SQL_TEXT_STR` | `VARCHAR2(4000)` | None | SQL statement issued by the user that triggered the audit action
`STATEMENTID_NUM` | `NUMBER` | None | Numeric identifier for each SQL statement executed
`SYSPRIVILEGES_DIM` | `NUMBER` | `PRIVILEGES_DIM` | Dimension key to the `PRIVILEGES_DIM` table, which contains information about the privileges used during an audit event
`TARGET_DIM` | `NUMBER` | `TARGET_DIM` | Dimension key to the `TARGET_DIM` table, which contains information about the schema object on which an audit event is performed
`TARGET_LOGIN_SID_STR` | `VARCHAR2(4000)` | None | SID of the login that is the target of some action
`TARGET_OBJECT_TYPE_STR` | `VARCHAR2(4000)` | None | Type of object, such as table, function, or stored procedure
`THREAD#` | `NUMBER` | None | Unique thread identifier that generated the audit action
`TIME_DIM` | `NUMBER` | `TIME_DIM` | Dimension key to the `TIME_DIM` table, which tracks actions over time
`TRANSACTION_NAME_ID` | `NUMBER` | None | ID to the `TRANSACTION_NAME_TAB` table, which contains the name of the transaction in which the object is accessed or modified
`UNDO_SQL_TEXT_STR` | `VARCHAR2(4000)` | None | Not used
`UPDATE_DATE_TS` | `TIMESTAMP(6) WITH LOCAL TIME ZONE` | None | For Oracle Database Vault, the date on which the command rule or realm information was updated
`UPDATED_BY_STR` | `VARCHAR2(4000)` | None | For Oracle Database Vault, the user who updated the command rule or realm
`USER_DIM` | `NUMBER` | `USER_DIM` | Dimension key to the `USER_DIM` table, which tracks information about the user who is associated with the events that occur in the source database
`USER_GUID_ID` | `NUMBER` | None | Global user identifier for the user, if the user has logged in as an enterprise user; also the global user identifier of Oracle Internet Directory user

### 4.6.2 CLIENT_HOST_DIM Dimension Table

The `CLIENT_HOST_DIM` table contains information about various systems that are used by clients to perform an operation.

Table 4-4 lists the contents of the `CLIENT_HOST_DIM` table.

Table 4-4 CLIENT_HOST_DIM Dimension Table

Column | Data Type | Description
--- | --- | ---
`DIMENSION_KEY` | `NUMBER` | Dimension key to the `AUDIT_EVENT_FACT` fact table
`DOMAIN_ID` | `NUMBER` | ID of the domain
`DOMAIN_NAME` | `VARCHAR2(255)` | Domain name of the host system
`HOST_ID` | `NUMBER` | ID of the host computer
`HOST_IP` | `VARCHAR2(255)` | Host IP address
`HOST_NAME` | `VARCHAR2(255)` | Name of the host
`TERMINAL_ID` | `NUMBER` | Identifier for the user's terminal
`TERMINAL_NAME` | `VARCHAR2(255)` | Name of the user's terminal

### 4.6.3 CLIENT_TOOL_DIM Dimension Table

The `CLIENT_TOOL_DIM` table contains information about the tools used to connect to an audit source database.

Table 4-5 lists the contents of the `CLIENT_TOOL_DIM` table.

Table 4-5 CLIENT_TOOL_DIM Dimension Table

Column | Data Type | Description
--- | --- | ---
`DIMENSION_KEY` | `NUMBER` | Dimension key to the `AUDIT_EVENT_FACT` fact table
`TOOL_ID` | `NUMBER` | ID of the tools and programs used to connect to an audit source database
`TOOL_NAME` | `VARCHAR2(4000)` | The tools and programs used to connect to an audit source database

### 4.6.4 CONTEXT_DIM Dimension Table

The `CONTEXT_DIM` table contains context information related to an audit event.

Table 4-6 lists the contents of the `CONTEXT_DIM` table.

Table 4-6 CONTEXT_DIM Dimension Table

Column | Data Type | Description
--- | --- | ---
`CONTEXT` | `VARCHAR2(4000)` | Session ID of the audit event
`CONTEXT_ID` | `NUMBER` | An internal cross-reference to the `CONTEXT` column.
`DIMENSION_KEY` | `NUMBER` | Dimension key to the `AUDIT_EVENT_FACT` fact table
`PARENT_CONTEXT` | `VARCHAR2(4000)` | Sequence number or identifier of a transaction
`PARENT_CONTEXT_ID` | `NUMBER` | Sequence number or identifier of a transaction
`SUB_CONTEXT` | `VARCHAR2(4000)` | Transaction ID
`SUB_CONTEXT_ID` | `NUMBER` | An internal cross-reference to the `SUB_CONTEXT` column

### 4.6.5 EVENT_DIM Dimension Table

The `EVENT_DIM` table contains information about various events that can be performed in the source databases.

Table 4-7 lists the contents of the `EVENT_DIM` table.

Table 4-7 EVENT_DIM Dimension Table

Column | Data Type | Description
--- | --- | ---
`AVEVENT_ID` | `NUMBER` | Oracle Audit Vault audit event identifier
`CATEGORY_ID` | `NUMBER` | Oracle Audit Vault category identifier
`CATEGORY_NAME` | `VARCHAR2(255)` | Oracle Audit Vault category name
`DIMENSION_KEY` | `NUMBER` | Dimension key to the `AUDIT_EVENT_FACT` fact table
`EVENT_DESCRIPTION` | `VARCHAR2(255)` | Description of the event
`EVENT_ID` | `NUMBER` | Source audit event ID
`EVENT_NAME` | `VARCHAR2(255)` | Source audit event name

### 4.6.6 PRIVILEGES_DIM Dimension Table

The `PRIVILEGES_DIM` table contains information about the privileges used during an audit event.

Table 4-8 lists the contents of the `PRIVILEGES_DIM` table.

Table 4-8 PRIVILEGES_DIM Dimension Table

Column | Data Type | Description
--- | --- | ---
`DIMENSION_KEY` | `NUMBER` | Dimension key to the `AUDIT_EVENT_FACT` fact table
`PRIV_ID` | `NUMBER` | ID of the privilege used to execute a transaction
`PRIV_NAME` | `VARCHAR2(4000)` | Name of the privilege used to execute a transaction

### 4.6.7 SOURCE_DIM Dimension Table

The `SOURCE_DIM` table contains information about the source databases that send audit data to the data warehouse.

Table 4-9 lists the contents of the `SOURCE_DIM` table.

Table 4-9 SOURCE_DIM Dimension Table

Column | Data Type | Description
--- | --- | ---
`DIMENSION_KEY` | `NUMBER` | Dimension key to the `AUDIT_EVENT_FACT` fact table
`SOURCE_DESCRIPTION` | `VARCHAR2(255)` | Description of the source that is defined when the source is added to Oracle Audit Vault
`SOURCE_HOST` | `VARCHAR2(255)` | Name of the host computer on which the audit source database resides
`SOURCE_HOSTIP` | `VARCHAR2(255)` | IP of the host computer on which the audit source database resides
`SOURCE_ID` | `NUMBER` | ID of the audit source database assigned to Oracle Audit Vault
`SOURCE_NAME` | `VARCHAR2(255)` | Name of the source database that is defined when the source is added to Oracle Audit Vault
`SOURCE_POLICY` | `NUMBER` | Deprecated; will be removed in a future release
`SOURCE_STATUS` | `NUMBER` | Indicates if the source database is currently active in Oracle Audit Vault
`SOURCE_VERSION` | `VARCHAR2(30)` | Version number of the source database
`SOURCETYPE_DESCRIPTION` | `VARCHAR2(30)` | Description of the type of source database in which audit trail records are being extracted
`SOURCETYPE_ID` | `NUMBER` | ID of the type of source database in which audit trail records are being extracted
`SOURCETYPE_NAME` | `SOURCETYPE_NAME` | Name of the type of source database in which audit trail records are being extracted

### 4.6.8 TARGET_DIM Dimension Table

The `TARGET_DIM` table contains information about the schema object on which an audit event is performed.

Table 4-10 lists the contents of the `TARGET_DIM` table.

Table 4-10 TARGET_DIM Dimension Table

Column | Data Type | Description
--- | --- | ---
`DIMENSION_KEY` | `NUMBER` | Dimension key to the `AUDIT_EVENT_FACT` fact table
`OWNER_ID` | `NUMBER` | ID of the owner of the target object
`OWNER_NAME` | `VARCHAR2(4000)` | Name of the owner of the target object
`TARGET_ID` | `NUMBER` | ID of the target object that is being audited
`TARGET_NAME` | `VARCHAR2(4000)` | Name of the target object that is being audited

### 4.6.9 TIME_DIM Dimension Table

The `TIME_DIM` table tracks actions over time. This table is the most commonly used by the data warehouse. It implements four levels in the dimension hierarchy (`DAY`, `MONTH`, `QUARTER`, `YEAR`). The `CALENDAR` prefix distinguishes between a fiscal quarter and a fiscal year.

Table 4-11 lists the contents of the `TIME_DIM` table.

Table 4-11 TIME_DIM Dimension Table

Column | Data Type | Description
--- | --- | ---
`CALENDAR_MONTH_CODE` | `NUMBER` | Numeric representation for the `MONTH` level (for example, `200802` for February, 2008)
`CALENDAR_MONTH_DESCRIPTION` | `VARCHAR2(255)` | Text description for level for the `MONTH` level (for example, `Feb 2008`)
`CALENDAR_MONTH_END_DATE` | `DATE` | End date for the `MONTH` level (for example, `29-feb-08`)
`CALENDAR_MONTH_ID` | `NUMBER` | ID for the `MONTH` level
`CALENDAR_MONTH_NAME` | `VARCHAR2(255)` | Same as `CALENDAR_MONTH_DESCRIPTION`
`CALENDAR_MONTH_OF_QUARTER` | `NUMBER` | Numeric representation for the month in this quarter (for example, `2` for February, assuming the quarter begins in January)
`CALENDAR_MONTH_OF_YEAR` | `NUMBER` | Numeric representation for the month in the year (for example, 2 for February)
`CALENDAR_MONTH_START_DATE` | `DATE` | Start date of the `MONTH` level (for example, `1-feb-08`)
`CALENDAR_MONTH_TIME_SPAN` | `NUMBER` | Duration of the `MONTH` level (for example, `29`)
`CALENDAR_QUART_CODE` | `NUMBER` | Numeric representation for the `QUARTER` level (for example, 2 for the second quarter)
`CALENDAR_QUART_DESCRIPTION` | `VARCHAR2(255)` | Text description for the `QUARTER` level (for example, `2` for the second quarter)
`CALENDAR_QUART_END_DATE` | `DATE` | End date for the `QUARTER` level (for example, `29-feb-08`)
`CALENDAR_QUART_ID` | `NUMBER` | ID for the `QUARTER` level
`CALENDAR_QUART_NAME` | `VARCHAR2(255)` | Same as `CALENDAR_QUART_DESCRIPTION`
`CALENDAR_QUART_OF_YEAR` | `NUMBER` | Numeric representation of the calendar quarter (for example, 2 for the second quarter of the year)
`CALENDAR_QUART_START_DATE` | `DATE` | Start date of the `MONTH` level (for example, `1-feb-08`)
`CALENDAR_QUART_TIME_SPAN` | `NUMBER` | Duration of the `QUARTER` level (for example, `90`)
`CALENDAR_YEAR_CODE` | `NUMBER` | Numeric representation for the `YEAR` level (for example, `2008` for the year 2008)
`CALENDAR_YEAR_DESCRIPTION` | `VARCHAR2(255)` | Text description for the `YEAR` level (for example, `2008`)
`CALENDAR_YEAR_END_DATE` | `DATE` | End date for the `YEAR` level (for example, `31-dec-08`)
`CALENDAR_YEAR_ID` | `NUMBER` | ID of the `YEAR` level
`CALENDAR_YEAR_NAME` | `VARCHAR2(255)` | Same as `CALENDAR_YEAR_DESCRIPTION`
`CALENDAR_YEAR_START_DATE` | `DATE` | Start date of the `YEAR` level (for example, `1-jan-08`)
`CALENDAR_YEAR_TIME_SPAN` | `NUMBER` | Duration of the `YEAR` level (for example, `360`)
`DAY` | `DATE` | Numeric representation of the day (for example, `14` for the 14th day)
`DAY_CODE` | `NUMBER` | Numeric representation for the `DAY` level (for example, `20080214` for February 12, 2008)
`DAY_DESCRIPTION` | `VARCHAR2(255)` | Text description of for the `DAY` level (for example, `14` for the 14th day of the month)
`DAY_END_DATE` | `DATE` | End date for the `DAY` level (for example, `29-feb-08`)
`DAY_ID` | `NUMBER` | ID for the `DAY` level
`DAY_NAME` | `VARCHAR2(255)` | Same as `DAY_DESCRIPTION`
`DAY_OF_CAL_MONTH` | `NUMBER` | Numeric representation of the day of the calendar month (for example, `14`)
`DAY_OF_CAL_QUARTER` | `NUMBER` | Numeric representation of the day of the calendar quarter (for example, `14`)
`DAY_OF_CAL_WEEK` | `NUMBER` | Numeric representation of the day of the calendar week (for example, `7`)
`DAY_OF_CAL_YEAR` | `NUMBER` | Numeric representation of the day of the calendar year (for example, `14`)
`DAY_START_DATE` | `DATE` | Start date of the `DAY` level (for example, `1-feb-08`)
`DAY_TIME_SPAN` | `NUMBER` | Duration of the `DAY` level (for example, `1`)
`DIMENSION_KEY` | `NUMBER` | Unique key across all levels

### 4.6.10 USER_DIM Dimension Table

The `USER_DIM` table tracks information about the user who is associated with the events that occur in the source database.

Table 4-12 lists the contents of the `USER_DIM` table.

Table 4-12 USER_DIM Dimension Table

Column | Data Type | Description
--- | --- | ---
`DIMENSION_KEY` | `NUMBER` | Dimension key to the `AUDIT_EVENT_FACT` fact table
`USER_ID` | `NUMBER` | ID of the user assigned by Oracle Audit Vault
`USER_NAME` | `VARCHAR2(255)` | Name of the user that is associated with an audit trail record

## 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."

Example 4-1 Using the DATA_TRACE_ROWS Function to Access Data Trace Values

    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;

See Also:

Section 3.3.2.3 for information about the Data Access Report