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;