Tables in the Identity Certification Module
This chapter describes the tables that make up the Oracle Identity Analytics Identity
Certification module.
ID_CERTS Table
Structure
|
|
|---|
PK |
ID |
|
NAME PERIOD
BUSINESSUNIT_ID STATE INCREMENTAL REPORT_ID CREATEUSER UPDATEUSER
CREATEDATE UPDATEDATE TYPE END_DATE CERTIFIER_ID REMEDIATION_STATUS
REMEDIATION_START_DATE REMEDIATION_END_DATE SUMMARY |
|
Indexes
Description
Each certification created in the Oracle Identity Analytics identity certification module is stored
in the ID_CERTS table. A certification has a period of validity. The start
date of the certification is saved in the PERIOD field, and the end
date is stored in the END_DATE field. A business unit can also be
associated with a certification and this information is saved in the table's BUSINESSUNIT_ID
field. The remediation status of a supported certification can be tracked using the
REMEDIATION_STATUS, REMEDIATION_START_DATE, and REMEDIATION_END_DATE fields. Finally, the following fields are provided for audit purposes:
CREATEDATE, CREATEUSER, UPDATEDATE, and UPDATEUSER.
Primary Keys
PK_ID_CERTS - primary key on column ID
Foreign Keys
None
Indexes
None
IDC_CONFIGURATIONS Table
Structure
|
|
|---|
|
IDC_ID CONFIGURATION_XML CREATEDATE CREATEUSER UPDATEDATE
UPDATEUSER |
|
Indexes
Description
The IDC_CONFIGURATIONS table contains information about certification view configuration changes made by
the administrator prior to launching the certification process. The CONFIGURATIONS_XML column holds each
certification's configuration information in XML format. The CREATE_USER and CREATE_DATE fields store information about
the user creating the certification and a timestamp marking when the certification was
created.
Primary Keys
None
Foreign Keys
None
Indexes
IX_IDC_ID - non-unique index on column IDC_ID
IDC_USERS TABLE
Structure
|
|
|---|
PK |
ID |
|
IAM_USER_ID USERNAME FIRSTNAME
LASTNAME MIDDLENAME STREET CITY STATEORPROVINCE ZIPORPOSTALCODE
COUNTRYORREGION FAX PHONE EXTENSION MOBILE PAGER
TITLE PRIMARYEMAIL SECONDARYEMAIL OFFICENAME DESCRIPTION COMMENTS
SUSPENDEDDATE ENABLEDDATE DISABLEDDATE DELETEDDATE USERDATA EMPLOYEEID
CUSTOMPROPERTY1 CUSTOMPROPERTY2 CUSTOMPROPERTY3 |
|
CUSTOMPROPERTY4 CUSTOMPROPERTY5 CUSTOMPROPERTY6
CUSTOMPROPERTY7 CUSTOMPROPERTY8 CUSTOMPROPERTY9 CUSTOMPROPERTY10 CUSTOMPROPERTY11 CUSTOMPROPERTY12
CUSTOMPROPERTY13 CUSTOMPROPERTY14 CUSTOMPROPERTY15 CUSTOMPROPERTY16 CUSTOMPROPERTY17 CUSTOMPROPERTY18
CUSTOMPROPERTY19 CUSTOMPROPERTY20 CREATEUSER UPDATEUSER CREATEDATE UPDATEDATE
EMPLOYEETYPE SERVICEDESKTICKETNUMBER STARTDATE ENDDATE MANAGER |
|
BUSINESSAPPROVER
TECHNICALAPPROVER DELEGATE LOCATION JOBCODES |
|
Indexes
Description
For entitlement certifications, users who require certification are defined in the IDC_USERS table.
Each user record has a unique ID, a username, a firstname, a lastname,
and a middlename. There are also fields that can store each user's street
address, email address, and phone number. Custom fields are provided to capture special
information. Because the user can be associated with a workflow, it is possible
to save the user's manager, businessapprover, and technicalapprover. A delegate field is also
present that a user can use to specify a delegated user. A statuskey
specifies the user's status. For audit purposes the table includes fields for capturing
the users's Creation Date and Update Date.
Primary Keys
PK_IDC_USERS - primary key on column ID
Foreign Keys
None
Indexes
None
ID_CERT_USERS Table
Structure
|
|
|---|
|
|
|
COMMENTS WORKS_FOR_ME REPORTS_TO CERTIFIED_BY |
|
Indexes
|
|
|
|
|---|
PK_ID_CERT_USERS |
✓
|
Yes |
CERT_ID, USER_ID |
|
Description
Every user who is certified in the certification process appears in the ID_CERT_USERS
table. This table includes a CERT_ID column that maps to the ID field
of the ID_CERTS table. A USER_ID is also assigned to the user. The
REPORTS_TO field associates a user with the user's manager, and the CERTIFIED_BY field
captures the username of the person updating the certification.
Primary Keys
PK_ID_CERT_USERS - composite primary key on columns CERT_ID and USER_ID
Foreign Keys
None
Indexes
None
IDC_ACCOUNTS Table
Structure
|
|
|---|
PK |
ID |
|
IAM_ACCOUNT_ID NAMESPACEKEY NAME
DESCRIPTION DOMAIN COMMENTS SUSPENDED LOCKED CREATEUSER
UPDATEUSER CREATEDATE UPDATEDATE ENDPOINT_ID ENDPOINT_NAME ACCOUNTTYPE_ID |
|
Index
Description
Each account for a user in the certification process is listed under the
IDC_ACCOUNTS table. Each account is also associated with the IACCOUNTS and NAMESPACES tables
by way of their respective reference keys. The table also stores the following
information: account name, endpoint (resource), description, domain, and account status. ACCOUNTTYPE_ID correlates an
account type with the certified account.
Primary Keys
PK_IDC_ACCOUNTS - primary key on column ID
Foreign Keys
None
Indexes
None
IDC_ACCOUNT_ATTRIBUTES Table
Structure
|
|
|---|
PK |
ID |
|
PARENT_ID ACCOUNT_ID ATTRIBUTE_ID
ATTRIBUTE_VALUE HIGH_PRIVILEGED UPDATEDATE |
|
Indexes
|
|
|
|
|---|
PK_IDC_ACCOUNT_ATTRIBUTES |
✓ |
|
ID |
|
Description
The IDC_ACCOUNT_ATTRIBUTES table saves information about the value of the attribute in a
certification. Each account in Oracle Identity Analytics has a list of attributes that
need to be certified. These attributes are referenced from the ATTRIBUTES table, and
the accounts are referenced from the ACCOUNTS table. The attribute value is also
stored in this table.
Primary Keys
PK_IDC_ACCOUNT_ATTRIBUTES - primary key on column ID
Foreign Keys
None
Indexes
None
IDC_ATTR_VALUES Table
Structure
|
|
|---|
PK |
ID |
|
NAMESPACE_ID ENDPOINT_ID ATTRIBUTE_ID
VALUE IAM_ATTR_VAL_ID CREATUSER UPDATEUSER CREATEDATE UPDATEDATE
|
|
Indexes
|
|
|
|
|---|
PK_IDC_ATTR_VALUES |
✓
|
Yes |
ID |
|
Description
For Data Owner certifications, a list of attribute values can be specified for
the certifications that needs to be performed. These attribute values are defined in
the IDC_ATTR_VALUES table. Each value has a unique ID field, along with an
ATTRIBUTE_ID field, an ENDPOINT_ID field, and a NAMESPACE_ID field that refer to the
respective tables in the Identity Warehouse. Note - In Oracle Identity Analytics, the
terminology "Namespace" and "Resource Type" mean the same thing.
Primary Keys
PK_IDC_ATTR_VALUES - primary key on column ID
Foreign Keys
None
Indexes
None
IDC_USER_ACCOUNTS Table
Structure
|
|
|---|
|
CERT_ID USER_ID ACCOUNT_ID |
|
CERTIFIED COMMENTS
CERTIFIED_BY CERTIFICATION_DATE STATUS_END_DATE REMEDIATION_STATUS REMEDIATION_DATE REMEDIATION_COMMENTS
|
|
Indexes
|
|
|
|
|---|
PK_IDC_USER_ACCOUNTS |
✓
|
Yes |
CERT_ID, USER_ID, ACCOUNT_ID |
|
Description
The IDC_USER_ACCOUNTS table is a derived table that associates user accounts with users
who are subject to certification. This table includes the CERT_ID, the USER_ID, and
the ACCOUNT_ID fields that reference the IDC_CERT_USERS and IDC_ACCOUNTS tables. Also associated with
the IDC_USER_ACCOUNTS table is a certified flag that tracks if the account has
been certified, as well as a CERTIFICATION_DATE field that stores the date/time of
the certification, thus allowing for updates. In addition, the CERTIFIED_BY field contains the username
of the person performing updates on the certification, which is required for auditing
purposes. Remediation details of the certification process can be tracked using the REMEDIATION_STATUS,
REMEDIATION_DATE, and REMEDIATION_COMMENTS fields.
Primary Keys
PK_IDC_USER_ACCOUNTS - composite primary key on columns CERT_ID, USER_ID and ACCOUNT_ID
Foreign Keys
None
Indexes
None
IDC_USER_ACCT_ATTRS Table
Structure
|
|
|---|
|
CERT_ID USER_ID ACCOUNT_ID
ACCOUNT_ATTRIBUTE_ID |
|
CERTIFIED COMMENTS CERTIFIED_BY CHANGED_SINCE_LAST CERTIFICATION_DATE
STATUS_END_DATE REMEDIATION_STATUS REMEDIATION_DATE REMEDIATION_COMMENTS |
|
Indexes
|
|
|
|
|---|
PK_USER_IDC_ACCT_ATTRS |
✓
|
Yes |
CERT_ID, USER_ID, ACCOUNT_ID, ACCOUNT_ATTRIBUTE_ID |
|
Description
The IDC_USER_ACCT_ATTRS table is a derived table that associates user account attributes
with users in a certification. This table includes the CERT_ID, USER_ID, ACCOUNT_ID,
and ACCOUNT_ATTRIBUTE_ID fields that reference the IDC_CERT_USERS, IDC_ACCOUNTS, and IDC_USER_ACCOUNTS tables. A certified flag
tracks if accounts have been certified, and the CERTIFICATION_DATE field stores the date/time
of the certification, thus allowing for updates. In addition, the CERTIFIED_BY field contains
the username of the person that makes changes to the certification, which is
required for auditing purposes. Remediation details of the certification process can be tracked
using the REMEDIATION_STATUS, REMEDIATION_DATE, and REMEDIATION_COMMENTS fields.
Primary Keys
PK_USER_IDC_ACCT_ATTR - composite primary key on columns CERT_ID, USER_ID, ACCOUNT_ID, ACCOUNT_ATTRIBUTE_ID
Foreign Keys
None
Indexes
None
ID_ATTR_VAL_USERS Table
Structure
|
|
|---|
|
CERT_ID
USER_ID ATTR_VALUE_ID |
|
NAMESPACE_ID ENDPOINT_ID ACCOUNT_ID
ATTRIBUTE_ID CERTIFIED COMMENTS CERTIFIED_BY CERTIFICATION_DATE STATUS_END_DATE
REMEDIATION_STATUS REMEDIATION_DATE REMEDIATION_COMMENTS ACCOUNT_NAME ENDPOINT_NAME NAMESPACE_NAME
ATTRIBUTE_NAME |
|
Indexes
|
|
|
|
|---|
PK_ID_ATTR_VAL_USERS |
✓
|
Yes |
CERT_ID, USER_ID, ATTR_VALUE_ID |
|
Description
The ID_ATTR_VAL_USERS table is a derived table that associates users and attribute values
with a certification. This table has CERT_ID, ATTR_VALUE_ID, and USER_ID fields that reference
the ID_CERTS, IDC_ATTR_VALUES, and IDC_USERS tables. A certified flag records whether the
association between the user and the attribute value has been certified, and the
CERTIFICATION_DATE field stores the date/time of the certification, thus providing for updates. In addition,
the CERTIFIED_BY field contains the username of the person who updated the certification,
which is required for audit purposes. Remediation details of the certification process can
be tracked using the REMEDIATION_STATUS, REMEDIATION_DATE, and REMEDIATION_COMMENTS fields.
Primary Keys
PK_ID_ATTR_VAL_USERS - composite primary key on columns CERT_ID, USER_ID, ATTR_VALUE_ID
Foreign Keys
None
Indexes
None
ID_CERT_ATTR_VALUES Table
Structure
|
|
|---|
|
|
|
GLOSSARY_DEF COMMENTS TECH_DESCRIPTION HIGH_PRIVILEGED CERTIFIED BELONGS_TO_ME
CERTIFIED_BY CERTIFICATION_DATE STATUS_END_DATE |
|
Indexes
|
|
|
|
|---|
PK_ID_CERT_ATTR_VALUES |
✓ |
Yes |
CERT_ID, ATTR_VALUE_ID
|
|
Description
The ID_CERT_ATTR_VALUES table is a derived table that associates attribute values with a
certification. This table has CERT_ID and ATTR_VALUE_ID fields that reference the ID_CERTS and
IDC_ATTR_VALUES tables. A certified flag records if the association between the attribute value
and the certification has been certified, and the CERTIFICATION_DATE field stores the date/time
of the certification, thus providing for updates. In addition, the CERTIFIED_BY field
contains the username of the person who updated the certification, which is required for
audit purposes. Remediation details of the certification process can be tracked using the
REMEDIATION_STATUS, REMEDIATION_DATE, and REMEDIATION_COMMENTS fields.
Primary Keys
PK_ID_CERT_ATTR_VALUES - composite primary key on columns CERT_ID, ATTR_VALUE_ID
Foreign Keys
None
Indexes
None
IDC_ROLES Table
Structure
|
|
|---|
PK |
ID |
|
IAM_ROLE_ID ROLENAME ROLEDESCRIPTION
ROLECOMMENTS DEPARTMENT STARTDATE ENDDATE CUSTOMPROPERTY1 CUSTOMPROPERTY2
CUSTOMPROPERTY3 CUSTOMPROPERTY4 CUSTOMPROPERTY5 CUSTOMPROPERTY6 CUSTOMPROPERTY7 CUSTOMPROPERTY8
CUSTOMPROPERTY9 CUSTOMPROPERTY10 HIGHPRIVELEGED JOBCODE SERVICEDESKTICKETNUMBER BUSINESSAPPROVER
TECHNICALAPPROVER USERASSOCIATIONBUAPPROVER USERASSOCIATIONTECHAPPROVER CREATEUSER UPDATEUSER CREATEDATE
UPDATEDATE |
|
Indexes
Description
The IDC_ROLES table lists each role associated with the user in the certification
process. Each role is associated with the ROLES table by way of the
reference rolekey, and custom fields are provided to capture custom role information. For
audit purposes, the table includes fields for capturing createuser and createtime information, and
updateuser and updatetime information for a role. The BusinessApprover, TechnicalApprover, UserAssociationBusinessApprover, and UserAssociationTechnicalApprover fields
are provided for the Workflow process. Each role is associated with an Identity
Certificate through the ID field.
Primary Keys
PK_IDC_ROLES - primary key on column ID
Foreign Keys
None
Indexes
None
ID_CERT_ROLES Table
Structure
|
|
|---|
|
|
|
CERTIFIED COMMENTS CERTIFIED_BY CERTIFICATION_DATE REMEDIATION_STATUS
REMEDIATION_DATE REMEDIATION_COMMENTS |
|
Indexes
|
|
|
|
|---|
PK_ID_CERT_ROLES |
✓ |
Yes
|
CERT_ID, ROLE_ID |
|
Description
The ID_CERT_ROLES table records every role that is certified in the certification process.
The certification ID maps to the ID field in the ID_CERTS table, and
a ROLE_ID is assigned to capture the role ID. A CERTIFIED flag records
if the roles have been certified, and the CERTIFICATION_DATE field stores the date/time
of the certification, thus providing for updates. In addition, the CERTIFIED_BY field contains
the username of the person updating the certification, which is required for auditing
purposes. Remediation details of the certification process can be tracked using the REMEDIATION_STATUS, REMEDIATION_DATE,
and REMEDIATION_COMMENTS fields.
Primary Keys
PK_ID_CERT_ROLES - composite primary key on column CERT ID, ROLE_ID
Foreign Keys
None
Indexes
None
IDC_USER_ROLES Table
Structure
|
|
|---|
|
|
|
CERTIFIED COMMENTS
CERTIFIED_BY CERTIFICATION_DATE REMEDIATION_STATUS REMEDIATION_DATE REMEDIATION_COMMENTS |
|
Indexes
|
|
|
|
|---|
PK_IDC_USER_ROLES |
✓
|
Yes |
CERT_ID, USER_ID, ROLE_ID |
|
Description
The IDC_USER_ROLES table is a composite table that associates roles with user certifications.
The USER_ID, ROLE_ID, and CERT_ID fields reference the IDC_CERT_USERS and IDC_ROLES tables. A
CERTIFIED flag records if the account has been certified, and the CERTIFICATION_DATE field
stores the date/time of the certification, thus providing for updates. In addition, the
CERTIFIED_BY field contains the username of the person updating the certification, which is required
for auditing purposes. Remediation details of the certification process can be tracked using
the REMEDIATION_STATUS, REMEDIATION_DATE, and REMEDIATION_COMMENTS fields.
Primary Keys
PK_IDC_USER_ROLES - composite primary key on columns USER_ID, ROLE_ID and CERT_ID
Foreign Keys
None
Indexes
None
IDC_POLICIES Table
Structure
|
|
|---|
PK |
ID |
|
IAM_POLICIES_ID NAMESPACE_ID
ENDPOINT_ID POLICY_NAME DESCRIPTION COMMENTS CREATE_USER UPDATE_USER
CREATE_DATE UPDATE_DATE |
|
Indexes
Description
The IDC_POLICIES table saves the policy information of a certification with roles. A
policy is assigned a POLICY_NAME, a DESCRIPTION, and COMMENTS. An association with a
Namespace (Resource Type) and an Endpoint (Resource) is made by utlilizing NAMESPACE_ID and
ENDPOINT_ID respectively.
Primary Keys
PK_IDC_POLICIES - composite primary key on columns ID
Foreign Keys
None
Indexes
None
IDC_POLICY_ATTRIBUTES Table
Structure
|
|
|---|
PK |
ID |
|
PARENT_ID POLICY_ID ATTRIBUTE_ID ATTRIBUTE_VALUE
HIGH_PRIVILEGED UPDATE_DATE |
|
Indexes
|
|
|
|
|---|
PK_IDC_POLICY_ATTRIBUTES |
✓ |
Yes
|
ID |
|
Description
A snapshot of various policy attributes are captured at the moment of certification
and saved in the IDC_POLICY_ATTRIBUTES table. These attributes are similar to the account
attributes defined. The ATTRIBUTE_VALUE field holds the actual value of the policies, whereas
the POLICIES and ATTRIBUTES tables are referenced by way of the POLICY_ID and
ATTRIBUTE_ID fields. The PARENT_ID field associates the policy with its parent.
Primary Keys
PK_IDC_POLICY_ATTRIBUTES - primary key on column ID
Foreign Keys
None
Indexes
None
IDC_ROLE_POLICIES Table
Structure
|
|
|---|
|
CERT_ID ROLE_ID POLICY_ID |
|
CERTIFIED COMMENTS
CERTIFICATION_DATE STATUS_END_DATE REMEDIATION_STATUS REMEDIATION_DATE REMEDIATION_COMMENTS |
|
Indexes
|
|
|
|
|---|
PK_IDC_ROLE_POLICIES |
✓
|
Yes |
CERT_ID, ROLE_ID, POLICY_ID |
|
Description
The IDC_ROLE_POLICIES table is a derived table that associates roles and policies with
certifications. The CERT_ID, ROLE_ID, and POLICY_ID fields reference the IDC_POLICIES, IDC_ROLES, and ID_CERT_ROLES
tables. A CERTIFIED flag records if the role to policy association has been
certified, and the CERTIFICATION_DATE field stores the date/time of the certification, thus providing for
updates. In addition, the CERTIFIED_BY field contains the username of the person updating
the certification, which is required for auditing purposes. Remediation details of the certification
process can be tracked using the REMEDIATION_STATUS, REMEDIATION_DATE, and REMEDIATION_COMMENTS fields.
Primary Keys
PK_IDC_ROLE_POLICIES - composite primary key on column CERT_ID, ROLE_ID, POLICY_ID
Foreign Keys
None
Indexes
None
IDC_ROLE_POLICY_ATTRS Table
Structure
|
|
|---|
|
CERT_ID ROLE_ID POLICY_ID
POLICY_ATTRIBUTE_ID |
|
CERTIFIED COMMENTS CERTIFIED_BY CHANGED_SINCE_LAST CERTIFICATION_DATE
STATUS_END_DATE REMEDIATION_STATUS REMEDIATION_DATE REMEDIATION_COMMENTS |
|
Indexes
|
|
|
|
|---|
PK_IDC_ROLE_POLICY_ATTRS |
✓
|
Yes |
CERT_ID, ROLE_ID, POLICY_ID, POLICY_ATTRIBUTE_ID |
|
Description
The IDC_ROLE_POLICY_ATTR table is a derived table that associates roles, policies, and role
and policy attributes with certifications. The CERT_ID, ROLE_ID, POLICY_ID, and POLICY_ATTRIBUTE_ID fields reference
the following tables: IDC_POLICIES, IDC_ROLES, IDC_ROLES_POLICIES and ID_CERT_ROLES. A CERTIFIED flag records if the
association between roles, policies, and role and policy attributes have been certified,
and the CERTIFICATION_DATE field stores the date/time of the certification, thus providing for
updates. In addition, the CERTIFIED_BY field contains the username of the person
updating the certification, which is required for auditing purposes. Remediation details of the
certification process can be tracked using the REMEDIATION_STATUS, REMEDIATION_DATE, and REMEDIATION_COMMENTS fields.
Primary Keys
PK_IDC_ROLE_POLICIES - composite primary key on column CERT_ID, ROLE_ID, POLICY_ID, POLICY_ATTRIBUTE_ID
Foreign Keys
None
Indexes
None
REMEDIATION_CONFIG Table
Structure
|
|
|---|
PK |
ENDPOINT_ID |
|
REMEDIATION_MODE REMEDIATION_STEPS
IAM_CONNECTION_ID CREATEUSER UPDATEUSER CREATEDATE UPDATEDATE |
|
Indexes
|
|
|
|
|---|
PK_REMEDIATION_CONFIG |
✓
|
Yes |
ENDPOINT_ID |
|
Description
The REMEDIAITON_CONFIG table stores details that define how closed loop remediation of certification
data should occur for each resource (endpoint). This process can happen either automatically
or manually. Fields are provided to record the configured remediation mode (that is,
auto or manual), the reference to the IAM connection (if the mode is
auto), the textual description of the remediation steps (if the mode is
manual), and the unique ID.
Primary Keys
PK_REMEDIATION_CONFIG - primary key on column ENDPOINT_ID
Foreign Keys
None
Indexes
None
