Skip Headers
Oracle® Audit Vault Auditor's Guide
Release 10.3

Part Number E16813-02
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
PDF · Mobi · ePub

4 Oracle Audit Vault Data Warehouse Schema

This chapter contains:

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 follows
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 follows
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 follows
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:

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 follows
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:

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 avsys.event_dim e, avsys.target_dim t, avsys.audit_event_fact f,
table(avsys.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