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