Understanding the History Database Schema

This chapter describes the History Database (History DB) schema including OLTP and OLAP database structures. The database schema is designed for extensibility to support customized IAM solutions.

About the History Database OLTP Schema

A history entry consists of the following data:

  • Entry type - indicates the type of entry, such as AccessRight, Account, Activity Definition, Activity Instance, Assignment, Audit Policy, Business Object, Certification Assignment Change, Certification Campaign, Certification Entry, Certification Notification, Configuration Object, Delegation, Domain Object, Group, Permission, Policy, Role, Role Parameter, Target System, Target System Configuration Object, Ticket, User, Workflow Definition and Workflow Instance.

  • Entry - contains identification attributes such as the unique identifiers and the name of the entry.

  • Small attribute values - contain the values of attributes that can be represented with primitive data types like string, number, date and time or boolean.

  • Link attribute values - contain values represented as references to other entries. It is usually a DN in a DirX Identity domain. Link attributes also contain user-to-privilege assignments, including assignment attributes and role parameters values.

  • Large attribute values - contain the values of attributes that can be represented with large object data types like images, XML structures, certificates, and so on.

Values are stored together with their date and time validity.

The following figure illustrates the History Database OLTP schema:

History Database OLTP Schema
Figure 1. History Database OLTP Schema

The following table describes the table columns in the History Database OLTP schema.

Table
Column		 Description

HST_ENTRY_TYPES
TYPE_NAME

Name of the entry type. The entry type is unique for the whole entry’s life-cycle.

HST_ENTRIES
ENT_DXRUID

UID of the entry. The UID is unique for the whole entry’s life-cycle.

HST_ENTRIES
DIRX_ENTRY_UUID

DirX Directory entry UID. The UID is calculated by DirX Server and is unique for the whole entry’s life-cycle.

HST_ENTRIES_IN_TIME
DN

DN of the entry.

HST_ENTRIES_IN_TIME
ENTRY_NAME

Name of the entry.

HST_LDAP_ATTRS
ATTRIBUTE_NAME

Name of the attribute.

HST_LDAP_ATTRS
VISIBLE

Visibility of the attribute.

HST_LDAP_ATTRS
ATTRIBUTE_TYPE

Data type of the attribute.

HST_LDAP_ATTRS
ATTRIBUTE_CATEGORY

Category of the attribute: SMALL, LARGE, LINK, ROLEPARAM, EXCLUDED, ENTRY.

HST_LDAP_ATTRS
TYPE_NAME

Indicates whether the LDAP attribute synchronization is restricted only to a specific entry type.

HST_SMALL_ATTRS_IN_TIME
ATTRIBUTE_NAME

Small attribute’s name.

HST_SMALL_ATTRS_IN_TIME
ATTRIBUTE_VALUE

Small attribute’s value.

HST_LARGE_ATTRS_IN_TIME
ATTRIBUTE_NAME

Large attribute’s name.

HST_LARGE_ATTRS_IN_TIME
ATTRIBUTE_VALUE

Large attribute’s value.

HST_LINK_ATTRS_IN_TIME
ATTRIBUTE_NAME

Link attribute’s name.

HST_LINK_ATTRS_IN_TIME
ATTRIBUTE_VALUE

Link attribute’s value.

HST_ROLEPARAMS_IN_TIME
ROLEPARAM_UID

UID of the role parameter.

HST_ROLEPARAMS_IN_TIME
ROLEPARAM_TYPE

Type of the role parameter.

HST_ROLEPARAMS_IN_TIME
ROLEPARAM_VALUE

Value of the role parameter.

HST_ROLEPARAMS_IN_TIME
ROLEPARAM_KEY

Key of the role parameter.

*
VALID_FROM

Start of the value validity.

*
VALID_TO

End of the value validity.

About the History Database OLAP Schema

The History Database OLAP schema is designed to enhance the performance of queries on history entries. Materialized or indexed views support relationships between all types of entries and their attributes and also of users and their roles, permissions, groups and accounts, of roles and their junior roles and permissions and of permissions and their groups.

The views depend on the calculation of foreign keys which are derived from link attribute values. DirX Audit Server runs the History DB update scheduled job to update the foreign keys of the History OLTP tables.

The following figure illustrates the views of the History Database OLAP schema:

History Database OLAP Schema Views
Figure 2. History Database OLAP Schema Views

For some reports, additional History OLAP tables are created and managed. They keep data on request workflows including their subjects, resources and people activities. The content of these OLAP tables is extended with a regular job run by DirX Audit Server.

The following figure illustrates the History Database OLAP schema tables:

History Database OLAP Schema Tables
Figure 3. History Database OLAP Schema Tables

The following tables describe views and tables and their columns of the History Database OLAP schema.

Table
Column		 Description

HDB_ENTRY_ATTRIBUTES
ATTRIBUTE_NAME

Attribute’s name.

HDB_ENTRY_ATTRIBUTES
ATTRIBUTE_VALUE

Attribute’s value.

HDB_USER_ACCOUNTS
ACC_DN

Account’s distinguished name.

HDB_USER_PRIVILEGES
PRIVILEGE_DN

Privilege’s distinguished name.

HDB_USER_PRIVILEGES
ASSIGNED_BY

Assignment mode: manual, BO, rule, inherited.

HDB_USER_ROLES
ROL_DN

Role’s distinguished name.

HDB_USER_ROLES
ASSIGNED_BY

Assignment mode: manual, BO, rule, inherited.

HDB_USER_PERMISSIONS
PER_DN

Permission’s distinguished name.

HDB_USER_PERMISSIONS
ASSIGNED_BY

Assignment mode: manual, BO, rule, inherited.

HDB_USER_GROUPS
GRP_DN

Group’s distinguished name.

HDB_USER_GROUPS
ASSIGNED_BY

Assignment mode: manual, BO, rule, inherited.

HDB_ROLE_ROLES
ROL_DN

Junior role’s distinguished name.

HDB_ROLE_PERMISSIONS
PER_DN

Permission’s distinguished name.

HDB_PERMISSION_GROUPS
GRP_DN

Group’s distinguished name.
Table
Column		 Description

HDB_REQWFS
REQWF_dxmType

Request workflow type: Request, Certification.

HDB_REQWFS
REQWF_dxmDisplayName

Display name of the request workflow.

HDB_REQWFS
REQWF_dxrApplicationState

Application state of the request workflow.

HDB_REQWFS
REQWF_dxrState

State of the request workflow.

HDB_REQWFS
REQWF_dxrStartDate

Start date of the request workflow.

HDB_REQWFS
REQWF_dxrEndDate

End date of the request workflow.

HDB_REQWFS
REQWF_dxmSA_Path

Path to the request workflow definition.

HDB_REQWFS
SUBJECT_OPERATION

Operation over the request workflow subject.

HDB_REQWF_RESOURCES
RESOURCE_OPERATION

Operation over the request workflow resource.

HDB_REQWF_ACTIVITIES
REQWF_dxmType

Activity type: people.

HDB_REQWF_ACTIVITIES
REQWF_dxmDisplayName

Display name of the request workflow activity.

HDB_REQWF_ACTIVITIES
REQWF_dxrApplicationState

Application state of the request workflow activity.

HDB_REQWF_ACTIVITIES
REQWF_dxrState

State of the request workflow activity.

HDB_REQWF_ACTIVITIES
REQWF_dxrStartDate

Start date of the request workflow activity.

HDB_REQWF_ACTIVITIES
REQWF_dxrEndDate

End date of the request workflow activity.

HDB_REQWF_ACTIVITIES
REQWF_dxmSA_Reason

Reason for the request workflow activity resolution.

HDB_REQWF_ACTIVITIES
REQWF_dxmSA_RetryCount

Retry count of the request workflow activity.

HDB_REQWF_ACTIVITIES
REQWF_dxmEscalationLevel

Escalation level of the request workflow activity.

HDB_SMALL_DATTRS_IN_TIME
ATTRIBUTE_NAME

Derived small attribute’s name.

HDB_SMALL_DATTRS_IN_TIME
ATTRIBUTE_VALUE

Derived small attribute’s value.

HDB_LINK_DATTRS_IN_TIME
ATTRIBUTE_NAME

Derived link attribute’s name.

HDB_LINK_DATTRS_IN_TIME
ATTRIBUTE_VALUE

Derived link attribute’s value.

HDB_IMPORTED_MEMBERSHIPS
MEM_VALID_FROM

Start of the membership validity.

HDB_IMPORTED_MEMBERSHIPS
MEM_VALID_TO

End of the membership validity.

For some Dashboard components and reports on DirX Identity certification campaigns, additional History OLAP tables are created and managed. They keep data on certification campaigns, certification entries and certification assignment changes and their relations to history entries. The content of these OLAP tables is extended with the regular History DB update job run by DirX Audit Server.

The following figure illustrates the History Database OLAP schema tables for certification campaigns:

History Database OLAP Schema Tables for Certification Campaigns and Approvals
Figure 4. History Database OLAP Schema Tables for Certification Campaigns and Approvals

The following table describes the tables and their columns of the History Database OLAP schema for certification campaigns.

Table
Column		 Description

HDB_RSK_USR_CERTIFICATIONS_03
DIM_HST_MONTH

Month when a certification is performed.

HDB_RSK_USR_CERTIFICATIONS_03
DIM_HST_CERRESULT

Whether the user is certified fully, partly, planned only or even unplanned.

HDB_MANUAL_ASSIGNMENTS
PRV_TYPE

Privilege type (Role, Permission or Group).

HDB_ASS_CERTIFICATIONS_12
DIM_HST_MONTH

Month when an assignment is certified.

HDB_ASS_CERTIFICATIONS_12
DIM_HST_ENTRY_TYPES

Entry type (Role, Permission or Group).

HDB_ASS_CERTIFICATIONS_12
DIM_HST_OU

Organizational unit of a certified user.

HDB_ASS_CERTIFICATIONS_12
FCT_HST_CERTIFIED

Whether the assignment is certified (1) or uncertified (0).

HDB_ASS_CERTIFICATIONS_12
FCT_HST_UNCERTIFIED

Whether the assignment is certified (0) or uncertified (1).

HDB_CERTENTRIES_SUCCEEDED
CAM_dxrType

Certification campaign type: Privilege certification (RoleToUser) or User certification (UserToRole).

HDB_CERTENTRIES_SUCCEEDED
SUCCEEDED_VALID_FROM

When certification succeeded.

HDB_CERTENTRIES_SUCCEEDED
ENT_dxrSubjectLink

Reference to the certification entry subject.

HDB_CERTENTRIES_SUCCEEDED
ACCEPTED

Number of accepted assignments.

HDB_CERTENTRIES_SUCCEEDED
REJECTED

Number of rejected assignments.

HDB_CERTENTRIES_SUCCEEDED
UNCERTIFIED

Number of uncertified assignments.

HDB_CERTENTRIES_SUCCEEDED
ENT_CERSTATE

Whether the certification entry is certified fully, partially or only planned.

HDB_USR_CERTIFICATIONS_12
DIM_HST_MONTH

Month when a certification is performed.

HDB_USR_CERTIFICATIONS_12
DIM_HST_CERRESULT

Whether the user is certified fully, partially, planned, or unplanned.

HDB_ASSIGNMENT_CERTIFICATIONS
dxrEndDate

End date of the assignment certification.

HDB_CERTCAMPAIGNS
DIM_HST_DATETIME

Date when a campaign is executed.

HDB_CERTCAMPAIGNS
DIM_HST_MONTH

Month when a campaign is executed.

HDB_CERTCAMPAIGNS
dxrState

Certification campaign state (SUCCEEDED, RUNNING, PREPARING, FAILED.EXPIRED, FAILED.PREPARE).

HDB_CERTCAMPAIGNS
dxrType

Certification campaign type: Privilege certification (RoleToUser) or User certification (UserToRole).

HDB_CERTCAMPAIGNS
dxrRevokePrivType

Certification campaign apply changes type: reject, reject-all, review or simulate.

HDB_APPROVALS
dxrStartDate

Start date of the approval.

HDB_APPROVALS
dxrEndDate

End date of the approval.

HDB_APPROVALS
dxrExpirationDate

Expiration date of the approval.