Skip Headers

Oracle® Audit Vault Auditor's Guide Release 10.2.3 Part Number E11058-01 |
Home | Book List | Contents | Index | Contact Us

Previous | Next
| View PDF

# A Oracle Audit Vault Data Warehouse Schema

This appendix 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

- Relationships Between the Fact and Dimension Tables

## A.1 About the Oracle Audit Vault Data Warehouse Schema

Oracle Audit Vault has an internal data warehouse schema that manages the audit data collected from the source databases. The data warehouse collects the data from the 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 third-party tools, such as Oracle Business Intelligence Publisher and the Oracle Business Intelligence Suite, you must understand the structure of the 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 and Microsoft SQL Server. Appendix B and Appendix C describe the structure of these audit events.

## A.2 Oracle Audit Vault Audit Data Warehouse Architecture

Figure A-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 A-1 Architecture of the Oracle Audit Vault Audit Data Warehouse

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

Audit Vault administrators with the `AV_AUDITOR` role can directly access audit data in the audit data warehouse for data analysis, generating reports, and performing data mining. See Oracle Database Data Warehousing Guide for more information.

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

Oracle Audit Vault uses a star schema to model the audit data warehouse, as shown in Figure A-2. The audit record is an entity (the fact table, `AUDIT_EVENT_FACT`) in the center of the star that is further described by its attributes (the dimensions) that form its points. 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.

Figure A-2 Structure of the Audit Data Warehouse

Description of "Figure A-2 Structure of the 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.

## A.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 A-3.

Figure A-3 Source Dimension Hierarchy

Description of "Figure A-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 dimension uses the `CLIENT_HOST_DIM` table as the dimension table. This table is populated 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. The `CLIENT_TOOL_DIM` table is used to store this information.

- User. This dimension is used to track the users information associated with the events occurring at the source. There is no hierarchy associated with the user information. The user dimension uses the `USER_DIM` table as the dimension table.

- 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 dimension uses the `TARGET_DIM` table as the dimension table. The `TARGET_DIM` table is updated 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 sources. A category of events is used by Oracle Audit Vault to group events, and this forms the hierarchy used by this dimension.

- Time. This dimension is used to track actions over time and it is expected to be the most common use of this data warehouse. The hierarchy for time is based on calendar year.

  The hierarchical information for time is stored in the `TIME_DIM` table. The time dimension is used for 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. This is used 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.

- Source. This dimension consists of the list of sources that send audit data to the data warehouse.

- Privileges. This dimension represents the information about the privileges used during the event. There is no hierarchy for this dimension.

## A.5 Fact Table Constraints and Indexes

Table A-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 A-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 A-2 lists the local bitmap indexes in the `AUDIT_EVENT_FACT` table.

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

Index Name | Column Name
--- | ---
`ASSOC_TARGET_DIM_IDX` | `ASSOC_TARGET_DIM`
`AV_TIME_DIM_IDX` | `AV_TIME_DIM`
`CLIENT_HOST_DIM_IDX` | `CLIENT_HOST_DIM`
`CLIENT_TOOL_DIM_IDX` | `CLIENT_TOOL_DIM`
`CONTEXT_DIM_IDX` | `CONTEXT_DIM`
`ENDUSER_DIM_IDX` | `ENDUSER_DIM`
`EVENT_DIM_IDX` | `EVENT_DIM`
`GRANTEE_USER_DIM_IDX` | `GRANTEE_USER_DIM`
`NEW_TARGET_DIM_IDX` | `NEW_TARGET_DIM`
`OBJPRIVILEGES_DIM_IDX` | `OBJPRIVILEGES_DIM`
`OSUSER_DIM_IDX` | `OSUSER_DIM`
`PRIVILEGES_DIM_IDX` | `PRIVILEGES_DIM`
`SOURCE_DIM_IDX` | `SOURCE_DIM`
`SYSPRIVILEGES_DIM_IDX` | `SYSPRIVILEGES_DIM`
`TARGET_DIM_IDX` | `TARGET_DIM`
`TIME_DIM_IDX` | `TIME_DIM`
`USER_DIM_IDX` | `USER_DIM`

## A.6 Relationships Between the Fact and Dimension Tables

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

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

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

Table A-3 through Table A-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.

### A.6.1 AUDIT_EVENT_FACT Fact Table

Table A-3 lists the contents `AUDIT_EVENT_FACT` table.

Table A-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 on 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
`CREATED_BY_STR` | `VARCHAR2(4000)` | None | Database login user name of the user who created the Oracle Database Vault rule
`CREATE_DATE_TS` | `TIMESTAMP(6) WITH LOCAL TIME ZONE` | None | Date the audit trail record was created in the Oracle Database Vault audit trail
`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
`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
`DATA_VALUES_CNT` | `NUMBER` | None | Number of columns that have changed due to an insert or update
`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 database sources
`EVENT_STATUS_ID` | `NUMBER` | None | ID of the `EVENT_STATUS_TAB` table, which contains the status of the audit action, `0 - Action` succeeded; otherwise, for example, the Oracle error code generated by the action, such as `2004 - Security violation`, or the error code generated for other sources
`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 | An invalid record's original content
`ORIGINAL_CONTENT2_STR` | `VARCHAR2(4000)` | None | An invalid record's original content
`ORIGINAL_CONTENT3_STR` | `VARCHAR2(4000)` | None | An invalid record's original content
`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 contains information about the privileges used during an audit event
`PRIV_ID_NUM` | `NUMBER` | None | ID of the privileged 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 | Proxy user's session identifier
`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 caused the audit event to trigger in Oracle Database Vault
`RULE_SET_ID_NUM` | `NUMBER` | None | The unique identifier of the rule set that was executing and caused the audit event to trigger in Oracle Database Vault
`RULE_SET_NAME_STR` | `VARCHAR2(4000)` | None | The unique name of the rule set that was executing and caused the audit event to trigger 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 database sources 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 `TRANSCATION_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
`UPDATED_BY_STR` | `VARCHAR2(4000)` | None | For Oracle Database Vault, the user who updated the command rule or realm
`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
`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 OID user

### A.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 A-4 lists the contents of the `CLIENT_HOST_DIM` table.

Table A-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

### A.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 A-5 lists the contents of the `CLIENT_TOOL_DIM` table.

Table A-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/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

### A.6.4 CONTEXT_DIM Dimension Table

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

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

Table A-6 CONTEXT_DIM Dimension Table

Column | Data Type | Description
--- | --- | ---
`CONTEXT` | `VARCHAR2(4000)` | Session ID of the audit event
`CONTEXT_ID` | `NUMBER` | Session ID of the audit event
`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` | Transaction ID

### A.6.5 EVENT_DIM Dimension Table

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

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

Table A-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

### A.6.6 PRIVILEGES_DIM Dimension Table

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

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

Table A-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 privileged used to execute a transaction
`PRIV_NAME` | `VARCHAR2(4000)` | Name of the privileged used to execute a transaction

### A.6.7 SOURCE_DIM Dimension Table

The `SOURCE_DIM` table contains information about the database sources that send audit data to the data warehouse.

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

Table A-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 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` | Oracle Audit Vault-assigned identifier of the audit source database
`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 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

### A.6.8 TARGET_DIM Dimension Table

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

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

Table A-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

### A.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 or fiscal year.

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

Table A-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

### A.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 A-12 lists the contents of the `USER_DIM` table.

Table A-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