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 Access Right, 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 attribute values that can be represented with primitive data types like string, number, date and time or boolean.
Link attribute values – contain the attribute values that can be 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 parameter values.
Large attribute values – contain the attribute values 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:

Figure 1. History Database OLTP Schema

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

Entry type - Name - (required) name of the entry type. The entry type is unique for the whole entry life cycle.

HST_ENTRY_TYPES.TYPE_NAME

Entry - dxrUID - (optional) UID of the entry. The UID is unique for the whole entry life cycle.

HST_ENTRIES.ENT_DXRUID

Entry - dirxEntryUUID - (required) DirX Directory entry UID. The UID is calculated by DirX Server and is unique for the whole entry life cycle.

HST_ENTRIES.DIRX_ENTRY_UUID

Entry in time - DN - (required) DN of the entry.

HST_ENTRIES_IN_TIME.DN

Entry in time - Name - (optional) name of the entry.

HST_ENTRIES_IN_TIME.ENTRY_NAME

LDAP attribute - Name - (required) name of the LDAP attribute.

HST_LDAP_ATTRS.ATTRIBUTE_NAME

LDAP attribute - Visibility - (required) visibility of the attribute.

HST_LDAP_ATTRS.VISIBLE

LDAP attribute - Type - (required) data type of the attribute.

HST_LDAP_ATTRS.ATTRIBUTE_TYPE

LDAP attribute - Category - (required) category of the attribute: SMALL, LARGE, LINK, ROLEPARAM, EXCLUDED, ENTRY.

HST_LDAP_ATTRS.ATTRIBUTE_CATEGORY

LDAP attribute - Type - (optional) indicates whether the LDAP attribute synchronization is restricted only to a specific entry type.

HST_LDAP_ATTRS.TYPE_NAME

Small atribute in time - Name - (required) name of the small attribute.

HST_SMALL_ATTRS_IN_TIME.ATTRIBUTE_NAME

Small attribute in time - Value - (required) value of the small attribute.

HST_SMALL_ATTRS_IN_TIME.ATTRIBUTE_VALUE

Link atribute in time - Name - (required) name of the link attribute.

HST_LINK_ATTRS_IN_TIME.ATTRIBUTE_NAME

Link attribute in time - Value - (required) value of the link attribute.

HST_LINK_ATTRS_IN_TIME.ATTRIBUTE_VALUE

Large atribute in time - Name - (required) name of the large attribute.

HST_LARGE_ATTRS_IN_TIME.ATTRIBUTE_NAME

Large attribute in time - Value - (required) value of the large attribute.

HST_LARGE_ATTRS_IN_TIME.ATTRIBUTE_VALUE

Role parameter in time - UID - (required) UID of the role parameter.

HST_ROLEPARAMS_IN_TIME.ROLEPARAM_UID

Role parameter in time - Type - (required) type of the role parameter.

HST_ROLEPARAMS_IN_TIME.ROLEPARAM_TYPE

Role parameter in time - Value - (required) value of the role parameter.

HST_ROLEPARAMS_IN_TIME.ROLEPARAM_VALUE

Role parameter in time - Key - (optional) key of the role parameter.

HST_ROLEPARAMS_IN_TIME.ROLEPARAM_KEY

Valid from - (required) start of the entry or attribute value validity. This column is common to multiple tables of the History Database OLTP schema and is used to determine the entry or attribute value validity in time.

VALID_FROM

Valid to - (optional) end of the entry or attribute value validity. This column is common to multiple tables of the History Database OLTP schema and is used to determine the entry or attribute value validity in time.

VALID_TO

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:

Figure 2. History Database OLAP Schema Views

The following section describes the view columns in the History Database OLAP schema.

Entry attributes - Attribute name - (required) name of the attribute.

HDB_ENTRY_ATTRIBUTES.ATTRIBUTE_NAME

Entry attributes - Attribute value - (required) value of the attribute.

HDB_ENTRY_ATTRIBUTES.ATTRIBUTE_VALUE

Entry attributes - Display value - (required) display value of the attribute. Display value is used for attributes with values that are not human readable, such as DNs of users, roles, permissions, groups and accounts. Display value is calculated based on the DN value.

HDB_ENTRY_ATTRIBUTES.DISPLAY_VALUE

User accounts - Account DN - (required) account distinguished name.

HDB_USER_ACCOUNTS.ACC_DN

User privileges - Privilege DN - (required) privilege distinguished name. Privilege can be a role, permission or group.

HDB_USER_PRIVILEGES.PRIVILEGE_DN

User privileges - Assigne by - (required) assignment mode: manual, BO, rule, inherited.

HDB_USER_PRIVILEGES.ASSIGNED_BY

User roles - Role DN - (required) role distinguished name.

HDB_USER_ROLES.ROL_DN

User roles - Assigned by - (required) assignment mode: manual, BO, rule, inherited.

HDB_USER_ROLES.ASSIGNED_BY

User permissions - Permission DN - (required) permission distinguished name.

HDB_USER_PERMISSIONS.PER_DN

User permissions - Assigned by - (required) assignment mode: manual, BO, rule, inherited.

HDB_USER_PERMISSIONS.ASSIGNED_BY

User groups - Group DN - (required) group distinguished name.

HDB_USER_GROUPS.GRP_DN

User groups - Assigned by - (required) assignment mode: manual, BO, rule, inherited.

HDB_USER_GROUPS.ASSIGNED_BY

Role roles - Role DN - (required) junior role distinguished name.

HDB_ROLE_ROLES.ROL_DN

Role permissions - Permission DN - (required) permission distinguished name.

HDB_ROLE_PERMISSIONS.PER_DN

Permission groups - Group DN - (required) group distinguished name.

HDB_PERMISSION_GROUPS.GRP_DN

Valid from - (required) start of the attribute value or assignment validity. This column is common to multiple views of the History Database OLAP schema and is used to determine the attribute value or assignment validity in time.

VALID_FROM

Valid to - (optional) end of the attribute value or assignment validity. This column is common to multiple views of the History Database OLAP schema and is used to determine the attribute value or assignment validity in time.

VALID_TO

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:

Figure 3. History Database OLAP Schema Tables

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

Request workflow - dxmType - (optional) request workflow type: Request, Certification.

HDB_REQWFS.REQWF_dxmType

Request workflow - dxmDisplayName - (optional) display name of the request workflow.

HDB_REQWFS.REQWF_dxmDisplayName

Request workflow - dxrApplicationState - (optional) application state of the request workflow.

HDB_REQWFS.REQWF_dxrApplicationState

Request workflow - dxrState - (optional) state of the request workflow.

HDB_REQWFS.REQWF_dxrState

Request workflow - dxrStartDate - (optional) start date of the request workflow.

HDB_REQWFS.REQWF_dxrStartDate

Request workflow - dxrEndDate - (optional) end date of the request workflow.

HDB_REQWFS.REQWF_dxrEndDate

Request workflow - dxmSA_Path - (optional) path to the request workflow definition.

HDB_REQWFS.REQWF_dxmSA_Path

Request workflow - Subject operation - (optional) operation over the request workflow subject.

HDB_REQWFS.SUBJECT_OPERATION

Request workflow - Resource operation - (optional) operation over the request workflow resource.

HDB_REQWF_RESOURCES.RESOURCE_OPERATION

Request workflow - dxmType - (optional) activity type: people.

HDB_REQWF_ACTIVITIES.REQWF_dxmType

Request workflow - dxmDisplayName - (optional) display name of the request workflow activity.

HDB_REQWF_ACTIVITIES.REQWF_dxmDisplayName

Request workflow - dxrApplicationState - (optional) application state of the request workflow activity.

HDB_REQWF_ACTIVITIES.REQWF_dxrApplicationState

Request workflow - dxrState - (optional) state of the request workflow activity.

HDB_REQWF_ACTIVITIES.REQWF_dxrState

Request workflow - dxrStartDate - (optional) start date of the request workflow activity.

HDB_REQWF_ACTIVITIES.REQWF_dxrStartDate

Request workflow - dxrEndDate - (optional) end date of the request workflow activity.

HDB_REQWF_ACTIVITIES.REQWF_dxrEndDate

Request workflow - dxmSA_Reason - (optional) reason for the request workflow activity resolution.

HDB_REQWF_ACTIVITIES.REQWF_dxmSA_Reason

Request workflow - _dxmSA_RetryCount - (optional) retry count of the request workflow activity.

HDB_REQWF_ACTIVITIES.REQWF_dxmSA_RetryCount

Request workflow - dxmEscalationLevel - (optional) escalation level of the request workflow activity.

HDB_REQWF_ACTIVITIES.REQWF_dxmEscalationLevel

Small derived atribute in time - Name - (required) name of the small derived attribute.

HDB_SMALL_DATTRS_IN_TIME.ATTRIBUTE_NAME

Small derived atribute in time - Value - (required) value of the small derived attribute.

HDB_SMALL_DATTRS_IN_TIME.ATTRIBUTE_VALUE

Link derived atribute in time - Name - (required) name of the link derived attribute.

HDB_LINK_DATTRS_IN_TIME.ATTRIBUTE_NAME

Link derived atribute in time - Value - (required) value of the link derived attribute.

HDB_LINK_DATTRS_IN_TIME.ATTRIBUTE_VALUE

Imported membership - Valid from - (required) start of the imported membership validity.

HDB_IMPORTED_MEMBERSHIPS.MEM_VALID_FROM

Imported membership - Valid to - (optional) end of the imported membership validity.

HDB_IMPORTED_MEMBERSHIPS.MEM_VALID_TO

For some dashboards 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:

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

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

Risk user certification - Month - (required) month when a certification is performed.

HDB_RSK_USR_CERTIFICATIONS_03.DIM_HST_MONTH

Risk user certification - Certification result - (required) whether the user is certified fully, partially, planned only or even unplanned.

HDB_RSK_USR_CERTIFICATIONS_03.DIM_HST_CERRESULT

Manual assignment - Privilege type - (required) privilege type: Role, Permission or Group.

HDB_MANUAL_ASSIGNMENTS.PRV_TYPE

Assignment certification - Month - (required) month when an assignment is certified.

HDB_ASS_CERTIFICATIONS_12.DIM_HST_MONTH

Assignment certification - Entry type - (required) entry type: Role, Permission or Group.

HDB_ASS_CERTIFICATIONS_12.DIM_HST_ENTRY_TYPES

Assignment certification - Organizational unit - (required) organizational unit of a certified user.

HDB_ASS_CERTIFICATIONS_12.DIM_HST_OU

Assignment certification - Is certified - (required) whether the assignment is certified (1) or uncertified (0).

HDB_ASS_CERTIFICATIONS_12.FCT_HST_CERTIFIED

Assignment certification - Is uncertified - (required) whether the assignment is certified (0) or uncertified (1).

HDB_ASS_CERTIFICATIONS_12.FCT_HST_UNCERTIFIED

Succeeded certification entry - Certification campaign type - (required) certification campaign type: Privilege certification (RoleToUser) or User certification (UserToRole).

HDB_CERTENTRIES_SUCCEEDED.CAM_dxrType

Succeeded certification entry - Valid from - (required) when certification succeeded.

HDB_CERTENTRIES_SUCCEEDED.SUCCEEDED_VALID_FROM

Succeeded certification entry - Subject - (required) reference to the certification entry subject.

HDB_CERTENTRIES_SUCCEEDED.ENT_dxrSubjectLink

Succeeded certification entry - Number of accepted - (required) number of accepted assignments.

HDB_CERTENTRIES_SUCCEEDED.ACCEPTED

Succeeded certification entry - Number of rejected - (required) number of rejected assignments.

HDB_CERTENTRIES_SUCCEEDED.REJECTED

Succeeded certification entry - Number of uncertified - (required) number of uncertified assignments.

HDB_CERTENTRIES_SUCCEEDED.UNCERTIFIED

Succeeded certification entry - Certification state - (required) whether the certification entry is certified fully, partially or only planned.

HDB_CERTENTRIES_SUCCEEDED.ENT_CERSTATE

User certification - Month - (required) month when a certification is performed.

HDB_USR_CERTIFICATIONS_12.DIM_HST_MONTH

User certification - Certification result - (required) whether the user is certified fully, partially, planned, or unplanned.

HDB_USR_CERTIFICATIONS_12.DIM_HST_CERRESULT

Assignment certification - End date - (required) end date of the assignment certification.

HDB_ASSIGNMENT_CERTIFICATIONS.dxrEndDate

Certification campaign - Date - (required) date when a campaign is executed.

HDB_CERTCAMPAIGNS.DIM_HST_DATETIME

Certification campaign - Month - (required) month when a campaign is executed.

HDB_CERTCAMPAIGNS.DIM_HST_MONTH

Certification campaign - dxrState - (required) certification campaign state (SUCCEEDED, RUNNING, PREPARING, FAILED.EXPIRED, FAILED.PREPARE).

HDB_CERTCAMPAIGNS.dxrState

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

HDB_CERTCAMPAIGNS.dxrType

Certification campaign - dxrRevokePrivType - (required) certification campaign apply changes type: reject, reject-all, review or simulate.

HDB_CERTCAMPAIGNS.dxrRevokePrivType

Approval - dxrStartDate - (required) start date of the approval.

HDB_APPROVALS.dxrStartDate

Approval - dxrEndDate - (required) end date of the approval.

HDB_APPROVALS.dxrEndDate

Approval - dxrExpirationDate - (required) expiration date of the approval.

HDB_APPROVALS.dxrExpirationDate