Skip Headers

Oracle® Audit Vault Auditor's Guide 10g Release 2 (10.2.2) Part Number B28853-02 |
Home | Book List | Contents | Index | Contact Us

Previous | Next
| View PDF

# A Data Warehouse Schema Reference

Oracle Audit Vault provides an audit data warehouse to facilitate data analysis of audit data combined with a report generation feature that lets you generate reports on the results of the data analysis. The audit data warehouse is a relational database optimized for query and analysis; it differs from the raw audit data table in that this table is optimized for transaction processing or insert performance. For more information about data warehouses, see Oracle Database Data Warehousing Guide.

This appendix includes the following sections:

- Oracle Audit Vault Audit Data Warehouse Architecture

- Design of the Audit Data Warehouse

- Fact Table and Dimension Tables

- Table Details

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

Figure A-1 shows the Oracle Audit Vault audit data warehouse architecture. Audit records are stored in the raw audit data table, which is typical of a traditional online transaction processing (OLTP) system that is optimized for insert performance as these records originate from a variety of 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.

## A.2 Design of the Audit Data Warehouse

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 other related information includes such items 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 dimensions tables.

Oracle Audit Vault uses a star schema to model the audit data warehouse, as shown in Figure A-2, where 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.

Figure A-2 Structure of the Audit Data Warehouse

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

The audit data warehouse involves a fact (the entity), which is an are action, and dimensions (the attributes), which are details about the action. For example, a logon 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.3 Fact Table and Dimension Tables

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, while 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. At the lowest level, the event names as defined at the source are used.

- 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. These levels can be used 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. The basic hierarchy for sources is based on source types. The source dimension is a Type-2 SCD. It keeps track of changes to the dimension attributes.

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

## A.4 Fact Table Constraints and Indexes

The AUDIT_EVENT_FACT table has the constraints listed in Table A-1. 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 Constraints Defined for the AUDIT_EVENT_FACT Table

Constraint Name | Column Name | Reference Table
--- | --- | ---
AV$FACT_SOURCE_DIM_FK | SOURCE_DIM | SOURCE_DIM (DIMENSION_KEY)
AV$FACT_EVENT_DIM_FK | EVENT_DIM | EVENT_DIM (DIMENSION_KEY)
AV$FACT_TIME_DIM_FK | TIME_DIM | TIME_DIM (DIMENSION_KEY)
AV$FACT_AV_TIME_DIM_FK | AV_TIME_DIM | TIME_DIM (DIMENSION_KEY)
AV$FACT_TARGET_DIM_FK | TARGET_DIM | TARGET_DIM (DIMENSION_KEY)
AV$FACT_USER_DIM_FK | USER_DIM | USER_DIM (DIMENSION_KEY)
AV$FACT_OSUSER_DIM_FK | OSUSER_DIM | USER_DIM (DIMENSION_KEY)
AV$FACT_ENDUSER_DIM_FK | ENDUSER_DIM | USER_DIM (DIMENSION_KEY)
AV$FACT_CONTEXT_DIM_FK | CONTEXT_DIM | CONTEXT_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_PRIVILEGES_DIM_FK | PRIVILEGES_DIM | PRIVILEGES_DIM (DIMENSION_KEY)
AV$FACT_ASSOC_TARGET_DIM_FK | ASSOC_TARGET_DIM | TARGET_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_SYSPRIVILEGES_DIM_FK | SYSPRIVILEGES_DIM | PRIVILEGES_DIM (DIMENSION_KEY)
AV$FACT_GRANTEE_USER_DIM_FK | GRANTEE_USER_DIM | USER_DIM (DIMENSION_KEY)

The AUDIT_EVENT_FACT has local bitmap indexes defined as shown in Table A-2.

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

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

In addition to these indexes, two local bitmap join indexes are defined for SOURCE_DIM and EVENT_DIM columns as shown in Table A-3.

Table A-3 Bitmap Join Indexes Defined for the SOURCE_DIM and EVENT_DIM Columns

Index Name | Dimension Name | Join Condition
--- | --- | ---
SOURCE_DIM_IDX | SOURCE_DIM | SOURCE_DIM = DIMENSION_KEY
EVENT_DIM_IDX | EVENT_DIM | EVENT_DIM = DIMENSION_KEY

## A.5 Table Details

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"

Tables (UNKNOWN STEP NUMBER) through (UNKNOWN STEP NUMBER) 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.5.1 AUDIT_EVENT_FACT Table

The AUDIT_EVENT_FACT table consists of the columns shown in Table A-4.

Table A-4 AUDIT_EVENT_FACT Table

Column | Data Type | Can Be Null | References
--- | --- | --- | ---
ADMIN_OPTION_NUM | NUMBER | Y | None
ASSOC_TARGET_DIM | NUMBER | Y | TARGET_DIM
AUDIT_OPTION_ID | NUMBER | Y | None
AUTHENTICATION_METHOD_ID | NUMBER | Y | None
AV_TIME | TIMESTAMP WITH LOCAL TIME ZONE | Y | None
AV_TIME_DIM | NUMBER | Y | TIME_DIM
CLIENT_APPINFO_STR | VARCHAR2(4000) | Y | None
CLIENT_HOST_DIM | NUMBER | Y | CLIENT_HOST_DIM
CLIENT_ID | NUMBER | Y | None
CLIENT_TOOL_DIM | NUMBER | Y | CLIENT_TOOL_DIM
COMMENT_TEXT_ID | NUMBER | Y | None
CONTEXT_DIM | NUMBER | Y | CONTEXT_DIM
DATA_VALUES_CNT | NUMBER | Y | None
ENDUSER_DIM | NUMBER | Y | USER_DIM
ERROR_ID_NUM | NUMBER | Y | None
ERROR_MESSAGE_STR | VARCHAR2(4000) | Y | None
EVENT_DIM | NUMBER | Y | EVENT_DIM
EVENT_STATUS_ID | NUMBER | Y | None
EVENT_TIME | TIMESTAMP WITH LOCAL TIME ZONE | Y | None
FGA_POLICYNAME_ID | NUMBER | Y | None
GRANTEE_USER_DIM | NUMBER | Y | USER_DIM
INSTANCE_NUMBER_NUM | NUMBER | Y | None
LOGOFF_DLOCK_NUM | NUMBER | Y | None
LOGOFF_LREAD_NUM | NUMBER | Y | None
LOGOFF_LWRITE_NUM | NUMBER | Y | None
LOGOFF_PREAD_NUM | NUMBER | Y | None
MODULE_NAME_STR | VARCHAR2(4000) | Y | None
NEW_TARGET_DIM | NUMBER | Y | TARGET_DIM
OBJECT_ID_NUM | NUMBER | Y | None
OBJPRIVILEGES_DIM | NUMBER | Y | PRIVILEGES_DIM
ORIGINAL_CONTENT1_STR | VARCHAR2(4000) | Y | None
ORIGINAL_CONTENT2_STR | VARCHAR2(4000) | Y | None
ORIGINAL_CONTENT3_STR | VARCHAR2(4000) | Y | None
OSUSER_DIM | NUMBER | Y | USER_DIM
PRIVILEGES_DIM | NUMBER | Y | PRIVILEGES_DIM
PROCESS# | NUMBER | Y | None
PROXY_SESSIONID_NUM | NUMBER | Y | None
RECORD_ID | NUMBER | Y | None
ROW_ID_STR | VARCHAR2(4000) | Y | None
SCN_NUM | NUMBER | Y | None
SESSION_ACTIONS_ID | NUMBER | Y | None
SESSION_CPU_NUM | NUMBER | Y | None
SEVERITY_NUM | NUMBER | Y | None
SOURCE_DIM | NUMBER | Y | None
SQL_BIND_STR | VARCHAR2(4000) | Y | None
SQL_TEXT_STR | VARCHAR2(4000) | Y | None
STATEMENTID_NUM | NUMBER | Y | None
SYSPRIVILEGES_DIM | NUMBER | Y | PRIVILEGES_DIM
TARGET_DIM | NUMBER | Y | TARGET_DIM
THREAD# | NUMBER | Y | None
TIME_DIM | NUMBER | Y | TIME_DIM
TRANSACTION_NAME_ID | NUMBER | Y | None
UNDO_SQL_TEXT_STR | VARCHAR2(4000) | Y | None
USER_DIM | NUMBER | Y | USER_DIM
USER_GUID_ID | NUMBER | Y | None

### A.5.2 CLIENT_HOST_DIM Table

The CLIENT_HOST_DIM table consists of the columns shown in Table A-5.

Table A-5 CLIENT_HOST_DIM Table

Column | Data Type | Can Be Null
--- | --- | ---
DIMENSION_KEY | NUMBER | N
DOMAIN_DESCRIPTION | VARCHAR2(255) | Y
DOMAIN_ID | NUMBER | Y
DOMAIN_NAME | VARCHAR2(255) | Y
HOST_DESCRIPTION | VARCHAR2(255) | Y
HOST_ID | NUMBER | Y
HOST_IP | VARCHAR2(255) | Y
HOST_NAME | VARCHAR2(255) | Y
SUBNET_DESCRIPTION | VARCHAR2(255) | Y
SUBNET_DOMAIN | VARCHAR2(255) | Y
SUBNET_ID | NUMBER | Y
SUBNET_NAME | VARCHAR2(255) | Y
TERMINAL_DESCRIPTION | VARCHAR2(255) | Y
TERMINAL_HOST | VARCHAR2(255) | Y
TERMINAL_ID | NUMBER | Y
TERMINAL_IP | VARCHAR2(255) | Y
TERMINAL_NAME | VARCHAR2(255) | Y
DIMENSION_KEY | NUMBER | N
TOOL_DESCRIPTION | VARCHAR2(255) | Y
TOOL_ID | NUMBER | Y
TOOL_NAME | VARCHAR2(4000) | Y

### A.5.3 CONTEXT_DIM Table

The CONTEXT_DIM table consists of the columns shown in Table A-6.

Table A-6 CONTEXT_DIM Table

Column | Data Type | Can Be Null
--- | --- | ---
CONTEXT | VARCHAR2(4000) | Y
CONTEXT_DESCRIPTION | VARCHAR2(4000) | Y
CONTEXT_ID | NUMBER | Y
DIMENSION_KEY | NUMBER | N
PARENT_CONTEXT | VARCHAR2(4000) | Y
PARENT_CONTEXT_DESCRIPTION | VARCHAR2(4000) | Y
PARENT_CONTEXT_ID | NUMBER | Y
SUB_CONTEXT | VARCHAR2(4000) | Y
SUB_CONTEXT_DESCRIPTION | VARCHAR2(4000) | Y
SUB_CONTEXT_ID | NUMBER | Y

### A.5.4 EVENT_DIM Table

The EVENT_DIM table consists of the columns shown in Table A-7.

Table A-7 EVENT_DIM Table

Column | Data Type | Can Be Null
--- | --- | ---
CATEGORY_DESCRIPTION | VARCHAR2(255) | Y
CATEGORY_ID | NUMBER | Y
CATEGORY_NAME | VARCHAR2(255) | Y
DIMENSION_KEY | NUMBER | Y
EVENT_DESCRIPTION | VARCHAR2(255) | Y
EVENT_ID | NUMBER | Y
EVENT_NAME | VARCHAR2(255) | Y
SOURCETYPE_EVENT_DESC | VARCHAR2(255) | Y
SOURCETYPE_EVENT_ID | NUMBER | Y
SOURCETYPE_EVENT_NAME | VARCHAR2(255) | Y
SOURCETYPE_ID | NUMBER | Y

### A.5.5 PRIVILEGES_DIM Table

The PRIVILEGES_DIM table consists of the columns shown in Table A-8.

Table A-8 PRIVILEGES_DIM Table

Column | Data Type | Can Be Null
--- | --- | ---
DIMENSION_KEY | NUMBER | N
PRIV_DESCRIPTION | VARCHAR2(4000) | Y
PRIV_ID | NUMBER | Y
PRIV_NAME | VARCHAR2(4000) | Y

### A.5.6 SOURCE_DIM Table

The SOURCE_DIM table consists of the columns shown in Table A-9.

Table A-9 SOURCE_DIM Table

Column | Data Type | Can Be Null
--- | --- | ---
DIMENSION_KEY | NUMBER | N
EFFECTIVE_DATE | TIMESTAMP WITH LOCAL TIME ZONE | Y
EXPIRATION_DATE | TIMESTAMP WITH LOCAL TIME ZONE | Y
SOURCE_DESCRIPTION | VARCHAR2(255) | Y
SOURCE_HOST | VARCHAR2(255) | Y
SOURCE_HOSTIP | VARCHAR2(255) | Y
SOURCE_ID | NUMBER | Y
SOURCE_NAME | VARCHAR2(255) | Y
SOURCE_POLICY | NUMBER | Y
SOURCE_STATUS | NUMBER | Y
SOURCE_VERSION | VARCHAR2(30) | Y
SOURCETYPE_DESCRIPTION | VARCHAR2(30) | Y
SOURCETYPE_ID | NUMBER | Y
SOURCETYPE_NAME | SOURCETYPE_NAME | Y

### A.5.7 TARGET_DIM Table

The TARGET_DIM table consists of the columns shown in Table A-10.

Table A-10 TARGET_DIM Table

Column | Data Type | Can Be Null
--- | --- | ---
DIMENSION_KEY | NUMBER | N
OWNER_DESCRIPTION | VARCHAR2(4000) | Y
OWNER_ID | NUMBER | Y
OWNER_NAME | VARCHAR2(4000) | Y
TARGET_DESCRIPTION | VARCHAR2(4000) | Y
TARGET_ID | NUMBER | Y
TARGET_NAME | VARCHAR2(4000) | Y

### A.5.8 TIME_DIM Table

The TIME_DIM table consists of the columns shown in Table A-11.

Table A-11 TIME_DIM Table

Column | Data Type | Can Be Null
--- | --- | ---
CALENDAR_MONTH_CODE | NUMBER | Y
CALENDAR_MONTH_DESCRIPTION | VARCHAR2(255) | Y
CALENDAR_MONTH_END_DATE | DATE | Y
CALENDAR_MONTH_ID | NUMBER | Y
CALENDAR_MONTH_NAME | VARCHAR2(255) | Y
CALENDAR_MONTH_OF_QUARTER | NUMBER | Y
CALENDAR_MONTH_OF_YEAR | NUMBER | Y
CALENDAR_MONTH_START_DATE | DATE | Y
CALENDAR_MONTH_TIME_SPAN | NUMBER | Y
CALENDAR_QUART_CODE | NUMBER | Y
CALENDAR_QUART_DESCRIPTION | VARCHAR2(255) | Y
CALENDAR_QUART_END_DATE | DATE | Y
CALENDAR_QUART_ID | NUMBER | Y
CALENDAR_QUART_NAME | VARCHAR2(255) | Y
CALENDAR_QUART_OF_YEAR | NUMBER | Y
CALENDAR_QUART_START_DATE | DATE | Y
CALENDAR_QUART_TIME_SPAN | NUMBER | Y
CALENDAR_YEAR_CODE | NUMBER | Y
CALENDAR_YEAR_DESCRIPTION | VARCHAR2(255) | Y
CALENDAR_YEAR_END_DATE | DATE | Y
CALENDAR_YEAR_ID | NUMBER | Y
CALENDAR_YEAR_NAME | VARCHAR2(255) | Y
CALENDAR_YEAR_START_DATE | DATE | Y
CALENDAR_YEAR_TIME_SPAN | NUMBER | Y
DAY | DATE | Y
DAY_CODE | NUMBER | Y
DAY_DESCRIPTION | VARCHAR2(255) | Y
DAY_END_DATE | DATE | Y
DAY_ID | NUMBER | Y
DAY_NAME | VARCHAR2(255) | Y
DAY_OF_CAL_MONTH | NUMBER | Y
DAY_OF_CAL_QUARTER | NUMBER | Y
DAY_OF_CAL_WEEK | NUMBER | Y
DAY_OF_CAL_YEAR | NUMBER | Y
DAY_START_DATE | DATE | Y
DAY_TIME_SPAN | NUMBER | Y
DIMENSION_KEY | NUMBER | N

### A.5.9 USER_DIM Table

The USER_DIM table consists of the columns shown in Table A-12.

Table A-12 USER_DIM Table

Column | Data Type | Can Be Null
--- | --- | ---
DIMENSION_KEY | NUMBER | N
USER_DESCRIPTION | VARCHAR2(255) | Y
USER_ID | NUMBER | Y
USER_NAME | VARCHAR2(255) | Y