Generic database
Some types of systems must be registered and onboarded to Omada Identity through a generic database instead of a direct connection to the system. Omada Identity supports import of data from a GenericDB database schema hosted in databases on either Microsoft SQL Server or Oracle. The GenericDB database schema is the Omada-provided predefined schema which matches the data model of the Omada Data Warehouse.
When registering a new system in Omada, in the Technology drop-down list, select the SQL Query Collector for Generic Database or SQL Query Collector for Generic Database (multiple systems) option.
Supported data types and modes
The following objects can be imported:
Identity Systems: Identities, Contexts, Identity Owners, Context Owners, Context Assignments
Access Systems: Accounts, Resources, Resource Owners, Resource Assignments
The following modes are supported:
Full import: The database must contain the full set of objects.
Delta import: The database only contains added, changed, and deleted objects. The high-water mark used to generate the delta should be stored in the ODW database by implementing an EventHandler extension based on the Omada ODW GenericDB EventHandler Template.
Prerequisites
You need to prepare a database with populated tables before the Omada Identity import. The following requirements need to be met for the target systems:
- Oracle
- Microsoft SQL Server
- PostreSQL
The servers running the Omada Identity Data Warehouse import must have Oracle’s OLE DB provider installed. This is part of the ODAC (Oracle Data Access Components) and is available for download from Oracle’s website. ODAC is available in both 32-bit and 64-bit versions (both 32-bit and 64-bit versions ODAC can be installed on the same machine at the same time - for some actions the 32-bit version is the only supported version).
To access an Oracle-based generic database when running an Omada Identity Data Warehouse import by invoking the dtexec executable directly or through the SQL Agent, the 64-bit version of ODAC must be installed on the machine running the SSIS packages.
The Microsoft SQL Server must be in one of the versions supported by Omada Identity. For Omada Identity 15.0.2, see Server software requirements.
No additional requirements. If you select PostgreSQL, provide a connection string.
Data model
The maximum value length is 400 characters.
System
The system should only be populated for the Generic database (multiple systems) categories.
| Column | Data type | Mandatory | Description |
|---|---|---|---|
| Action | char | In delta mode only | Fill in for delta import only: D for deleted data and C for created/changed data in the source system. In the full import mode, this column must be null. In the delta import mode, this column must not be null. |
| BusinessKey | string | Yes | The system’s key for the object. Unique value required. |
| Name | string | Yes | The name of the resource. |
| Shortname | string | If you do not specify anything here, the BusinessKey is used as ShortName if you have specified this. If you have not specified BusinessKey or do not specify a value for ShortName, Name is used as ShortName. Unique value required. | |
| Description | string | A description of the system. |
Context
| Column | Data type | Mandatory | Description |
|---|---|---|---|
| Action | char | In delta mode only | Fill in for delta import only: D for deleted data and C for created/changed data in the source system. In the full import mode, this column must be null. In the delta import mode, this column must not be null. |
| BusinessKey | string | Yes | The system’s key for the object. Unique value required. |
| Name | string | Yes | The name of the context. |
| Type | string | Yes | The context type, for example OrgUnit, Company, or Cost Center. For organizational units, the type must be equal to OrgUnit. If you do not specify any values here, OrgUnit is used. |
| SubType | string | A more detailed type of context. | |
| Parent_BusinessKey | string | This value must correspond to the value that you specified in the BusinessKey field for the parent context. | |
| Parent_Composed BusinessKey | string | The composed business key for the parent context. Use this if the parent context comes from a different system category. Note that the parent context that you specify must be present in the Source System Data database. |
Identity
| Column | Data type | Mandatory | Description |
|---|---|---|---|
| Action | char | In delta mode only | Fill in for delta import only: D for deleted data and C for created/changed data in the source system. In the full import mode, this column must be null. In the delta import mode, this column must not be null. |
| BusinessKey | string | Yes | The system’s key for the object. Unique value required. |
| UID | string | Yes | The company-wide identifier of the identity (employee ID from an HR system). UID is normally used to identify the individual employee across systems. If the person exists in multiple systems, this is the common ID. Starting with v14, you can customize this behavior using identity join rules. When the identity is exported from the Omada Identity Data Warehouse to the Omada Identity Enterprise Server, UID becomes IDENTITYID. |
| Name | string | Yes | The name of the identity. |
| FirstName | string | Yes | The first name of the identity. |
| LastName | string | Yes | The last name of the identity. |
| string | The identity’s email address. | ||
| JobTitle | string | The identity’s job title. | |
| Country | string | The country in which the identity is employed. | |
| Company | string | The company in which the identity is employed. | |
| EmployeeID | string | The employee ID from an HR system. EmployeeID is only used for presentation purposes, so you do not need to specify this. | |
| Status | string | Yes | Omada recommends that you only use the following values: - Active - Disabled - Terminated |
| ValidFrom | datetime | The identity’s first day of work. | |
| ValidTo | datetime | The identity’s last day of work. | |
| Category | string | The category must correspond to the English name of a value of the property Identity. IDENTITYCATEGORY - Employee - Contractor - Business partner - Other If not specified, it defaults to Employee. | |
| Type | string | Omada recommends that you only use the following values: - Primary - Machine If not specified, it defaults to Primary. |
ContextOwner
| Column | Data type | Mandatory | Description |
|---|---|---|---|
| Action | char | In delta mode only | Fill in for delta import only: D for deleted data and C for created/changed data in the source system. In the full import mode, this column must be null. In the delta import mode, this column must not be null. |
| BusinessKey | string | The system’s key for the object. If not specified, it will be automatically generated from OwnerUID+Context_BusinessKey. Unique value required. | |
| OwnerUID | string | yes | This value must correspond to what you specified in the UID field for the identity. |
| Context_BusinessKey | string | yes | This value must correspond to the value that you specified in the BusinessKey field for the context. |
| Type | string | Ownership type (for example: manager or substitute). |
ContextAssignment
| Column | Data type | Mandatory | Description |
|---|---|---|---|
| Action | char | In delta mode only | Fill in for delta import only: D for deleted data and C for created/changed data in the source system. In the full import mode, this column must be null. In the delta import mode, this column must not be null. |
| BusinessKey | string | The system’s key for the object. If not specified, it will be automatically generated from ContextBusinessKey+IdentityUID. Unique value required. | |
| ContextBusinessKey | string | yes | This value must correspond to what you specified in the BusinessKey field for the context. |
| IdentityUID | string | yes | This value must correspond to what you specifiedin the UID field for the identity. |
| ValidFrom | datetime | The date from which the context assignment is valid. | |
| ValidTo | datetime | The date to which the context assignment is valid. |
Account
| Column | Data type | Mandatory | Description |
|---|---|---|---|
| Action | char | In delta mode only | Fill in for delta import only: D for deleted data and C for created/ changed data in the source system. In the full import mode, this column must be null. In the delta import mode, this column must not be null. |
| BusinessKey | string | yes | The system’s key for the object. Unique value required. |
| UID | string | yes | The UID is used to establish the relation between the account and the identity UID. |
| AccountName | string | yes | The name of the account. The name must have a unique value within a file/system. |
| Description | string | A description of the account. | |
| Category | string | If you select Group as the category, the resource is also imported as an account in the Omada Identity Data Warehouse. This is done to map resource assignments assigned to users via a group. | |
| Status | string | Status, for example, active or disabled. | |
| ValidFrom | datetime | The date from which the account is valid. | |
| ValidTo | datetime | The date until the account is valid. | |
| System_BusinessKey | string | yes | This value must correspond to the value that you specified in the BusinessKey field for the system. |
| Domain | string | Only relevant for an AD account. | |
| DisplayName | string | If you do not specify anything, Name is used as DisplayName. | |
| DistinguishedName | string | The distinguished name of the account. | |
| Path | string | The path to the account. | |
| StatusMask | bigint | Integral representation of status, for example, as a bit mask. | |
| LastLogon | datetime | The last time that the account logged on. | |
| LastPasswordChange | datetime | The last time that the password was changed. |
Resource
| Column | Data type | Mandatory | Description |
|---|---|---|---|
| Action | char | In delta mode only | Fill in for delta import only: D for deleted data and C for created/changed data in the source system. In the full import mode, this column must be null. In the delta import mode, this column must not be null. |
| BusinessKey | string | yes | The system’s key for the object. Unique value required. |
| Name | string | yes | The name of the resource. |
| Domain | string | Use this if the resource belongs to an AD or LDAP system. | |
| ShortName | string | A short name for the resource. Unique value required. | |
| DisplayName | string | If you do not specify anything, Name is used as DisplayName. | |
| DistinguishedName | string | The distinguished name of the resource. Use this if the resource belongs to an AD/LDAP system. | |
| Path | string | The path to the resource. Use this if the resource belongs to an AD/LDAP system. | |
| Description | string | A description of the resource. | |
| Type | string | Details the category, for example Security Group or Distribution Group. | |
| Category | string | If you select Group as the category, the resource is also imported as an account in the Omada Identity Data Warehouse. This is done to map resource assignments assigned to users via a group. | |
| System_BusinessKey | string | yes | This value must correspond to the value that you specified in the BusinessKey field for the system. |
| SecurityResource_BusinessKey | string | yes | The business key for the resource that defines the security for this resource. Usually, it has the same value as the BusinessKey of the Resource. |
| LogicKey | string | The logical key is a configurable key that can be used to match resource objects. For more details, see Import and onboarding. |
IdentityOwner
| Column | Data type | Mandatory | Description |
|---|---|---|---|
| Action | char | In delta mode only | Fill in for delta import only: D for deleted data and C for created/changed data in the source system. In the full import mode, this column must be null. In the delta import mode, this column must not be null. |
| BusinessKey | string | The system’s key for the object. If not specified, it will be automatically generated from IdentityUID.+OwnerUID. Unique value required. | |
| IdentityUID | string | yes | The value must correspond to the value that you specified in the UID field for the identity. |
| OwnerUID | string | yes | The value must correspond to the value that you specified in the UID field for the identity owner. |
| Type | string | Ownership type, for example: manager. |
ResourceParentChild
| Column | Data type | Mandatory | Description |
|---|---|---|---|
| Action | char | In delta mode only | Fill in for delta import only: D for deleted data and C for created/changed data in the source system. In the full import mode, this column must be null. In the delta import mode, this column must not be null. |
| ParentBusinessKey | string | yes | This value must correspond to the value that you specified in the BusinessKey of the parent Resource. |
| Child_BusinessKey | string | yes | This value must correspond to the value that you specified in the BusinessKey of the child Resource. It is only mandatory if Child_ComposedBusinessKey has not been specified (one of these two needs to be always specified). |
| Child_ComposedBusinessKey | string | yes | The composed business key for the child resource. Use this if the child resource comes from a different system category. Note that the child resource that you specify must be present in the Source System Data database. It is only mandatory if Child_BusinessKey has not been specified (one of these two needs to be always specified). |
| Indirect | boolean | yes | You must set this value to True if the resource parent child relationship is indirect, for example between a children resource and one of its grandparents or greatgrandparents. If the resource parent-child relationship is not indirect, you should not set this value to True. If it is not specified, it defaults to False. Use indirect resource parent-child relationships to generate indirect resource assignments between resources that have the category Group. |
ResourceOwner
| Column | Data type | Mandatory | Description |
|---|---|---|---|
| Action | char | In delta mode only | Fill in for delta import only: D for deleted data and C for created/changed data in the source system. In the full import mode, this column must be null. In the delta import mode, this column must not be null. |
| BusinessKey | string | The system’s key for the object. If not specified, it will be automatically generated from IdentityUID.+OwnerUID. Unique value required. | |
| OwnerUID | string | yes | The value must correspond to what you specified in the UID field for the identity. |
| Resource_BusinessKey | string | yes | This value must correspond to the value that you specified in the BusinessKey of the Resource. |
| Type | string | Ownership type, for example, substitute. |
ResourceAssignment
| Column | Data type | Mandatory | Description |
|---|---|---|---|
| Action | char | In delta mode only | Fill in for delta import only: D for deleted data and C for created/changed data in the source system. In the full import mode, this column must be null. In the delta import mode, this column must not be null. |
| Resource_BusinessKey | string | yes | This value must correspond to the value that you specified in the BusinessKey of the Resource. |
| Account_BusinessKey | string | yes | Must correspond to what you specified in the BusinessKey field for the Account. It is only mandatory if Account_ComposedBusinessKey has not been specified (one of these two needs to be always specified). |
| Account_ComposedBusinessKey | string | yes | The composed business key for the account. Use this if the account comes from a different system category. The account that you specify must be present in the Source System Data database. It is only mandatory if Account_BusinessKey has not been specified (one of these two needs to be always specified). |
| ValidFrom | datetime | The date from which the resource assignment is valid. | |
| ValidTo | datetime | The date to which the resource assignment is valid. | |
| Status | string | Status, for example, active or disabled. | |
| Description | string | A description of the resource assignment. | |
| PermissionLevel | string | The level of permission. For example, Full Control or Read Only. | |
| PermissionMask | integer | The integral representation of the permission level, for example as a bit mask. | |
| AccountIsGroup | boolean | yes | If the account belongs to the category Group, you must set this field to True. If not specified, defaults to False. |
| BusinessKey | string | The system’s key for the object. If not specified, it will be automatically generated from Resource_BusinessKey+Account_ComposedBusinessKey or Resource_BusinessKey+Account_BusinessKey. Unique value required. |
ImportSystemFilter
| Column | Data type | Mandatory | Description |
|---|---|---|---|
| BusinessKey | string | yes | The system’s key for the object. Unique value required. |
| ExtHighWaterMark | string | yes | The high-water mark for the current system. In the delta mode, it is used to provide a point of reference for delta creation. |
| NewExtHighWaterMark | string | yes | The new high-water mark for the system. In the delta mode, it is used to provide a point of reference for delta creation. |
Decision
| Column | Data type | Mandatory | Description |
|---|---|---|---|
| BusinessKey | string | The system’s key for the object. Unique value required. | |
| ProcessType | string | Shows which type of process the decision was made in. | |
| Time | string | The time of the decision. | |
| ApproverRole | string | The role of the approver identity, for example, manager. | |
| ApproverName | string | The name of the approver identity. | |
| ApproverOISID | integer | Unique identifier of the approver identity within Omada Identity. | |
| ExpirationTime | datetime |
DecisionResourceAssignment
| Column | Data type | Mandatory | Description |
|---|---|---|---|
| Decision_BusinessKey | string | yes | Must correspond to what you specified in the BusinessKey field for the decision. |
| ResourceAssignment_ComposedBusinessKey | string | The composed business key for the resource assignment. Use this if the resource assignment comes from a different system category. The resource assignment that you specify must be present in the Source System Data database. | |
| ResourceAssignment_BusinessKey | string | Must correspond to what you specified in the BusinessKey field for the resource assignment. | |
| Resource_BusinessKey | string | Must correspond to what you specified in the BusinessKey field for the resource. | |
| Account_ComposedBusinessKey | string | The composed business key for the account. Use this if the account comes from a different system category. The account that you specify must be present in the Source System Data database. | |
| Account_BusinessKey | string | Must correspond to what you specified in the BusinessKey field for the Account. | |
| AccountIsGroup | boolean | If the account belongs to the category Group, you must set this field to True. | |
| Answer | string | The actual decided action (for example: approve, reject, or dispensate). | |
| Justification | string | Shows the comment given for the decision. | |
| Constraints | string | Information on the combinations of resources or business processes that represent a violation of established policies. | |
| CompensationControl | string | Information on the describing the plan for compensating for the violation of a constraint. |
Collector configuration (data import)
Connection details
| Setting | Description |
|---|---|
| Database platform | The platform to use for the generic database. Select either Microsoft SQL Server or Oracle. |
| Connection string | Define a connection string to connect Omada Identity to the generic database. This is a mandatory setting. Enter a connection string following the OLE DB syntax, referencing the provider selected for the database platform. For Microsoft SQL Server, see Microsoft OLE DB Provider for SQL Server Overview. For Oracle, find more information on the Oracle homepage. |
| Database schema | Specify a database schema for the generic database connector to use when reading from the generic database. |
| Test connection | Enable this setting to test the specified connection details. To use this functionality, install Omada Provisioning Service and make sure it has the necessary permissions to communicate with the target system. |
Extension attributes
For all extension attributes, enter a comma-separated list of the relevant attributes. Extension attribute categories appear twice - the first option records the history, the other doesn't:
- Accounts
- Contexts
- Identities
- Resources
- Resource assignments
Advanced configuration
| Setting | Description |
|---|---|
| Perform unfolding | Enable the setting to unfold a resource parent-child hierarchy. |
| Event handler extension | Enter the name of a custom event handler extension package (for example, to load data from files or other sources into the generic database). The event handler extension packages should be deployed to the Omada\ODW\Event Handler folder on the SSIS server. Use the following naming conventions for Event Handler Packages: <system category name>Event Handler (example: SAP EventHandler) |
| Import mode for identities, accounts, and resources Import mode for contexts Import mode for context assignments Import mode for context owners Import mode for identity owners Import mode for resource assignments Import mode for resource owners Import mode for systems Import mode for system owners | Select Full to perform a full database import or Delta to only import data that has changed since the last import. |
Configure thresholds
The Configure thresholds function allows you to set the amount of changes that cannot be exceeded, relevant to the last import. In the Configure import thresholds view, enter a number (integer) in percentage for new objects, modified objects, and deleted objects to enable thresholds for the import of objects from this system. For more information, see the Threshold section.
::: note
Thresholds only apply to Access data (accounts, resources, and resource assignments) even if the Both category is selected. If the system is configured as Access Data, the Configure thresholds functionality is not shown in the menu list.
:::
Connector configuration (data provisioning)
| Setting | Description |
|---|---|
| Database provider | Select OLE DB or PostreSQL. |
| Connection string | Define a connection string to connect Omada Identity to the generic database. This is a mandatory setting. |
| Disable schema validation | Optionally, select the Disable schema validation checkbox to disable the function where the connector validates the database schema. |
| Test connection | Enable this setting to test the specified connection details. To use this functionality, install Omada Provisioning Service and make sure it has the necessary permissions to communicate with the target system. |
Provisioning
| Setting | Description |
|---|---|
| Method for accounts | Select the provisioning method to use to provision assignments. You can select the following options: - None (no provisioning of account assignments) - Omada Provisioning Service - Manual provisioning - Microsoft Identity Manager (MIM) |
| Method for assignments | Select the provisioning method to use for provisioning assignments. You can select the following options: - None (no provisioning of permission assignments) - Omada Provisioning Service - Manual provisioning - Microsoft Identity Manager (MIM) |
| Provisioning connector | If you select Omada Provisioning Service as the provisioning method, you must also select a provisioning connector (for example, a database connector created from the Generic database (Template), PowerShell, or a custom connector). |
| Use default configuration | Select the checkbox to use the default configuration. If you enable this option, it overwrites any existing configuration. |
| Name | Enter a name of the connector to use. |
Data model
Enter or paste code related to the connector’s data model. The data model is adapted individually to each database to which you want to write. You must describe each database table as an <object> in the model. Define each column to write to in the table as a <property>.
Example: GWGUserProfile table
The corresponding connector data model:
<connectorDataModel xmlns="http://schemas.omada.net/ops/2015/ConnectorDataModelML"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<properties>
<property name="UserName" dataType="stringType"/>
<property name="UserDisplayName" dataType="stringType"/>
</properties>
<objects>
<object name="GWGUserProfile">
<objectProperties>
<objectProperty isKey="true">UserName</objectProperty>
<objectProperty>UserDisplayName</objectProperty>
</objectProperties>
</object>
</objects>
</connectorDataModel>
The UserId is left out as it is an identity column that should not be used. To perform update/delete operations, specify key column(s) in the table. The column does not have to be the actual primary key, but it must contain a unique value in the table. To indicate the key columns, add the isKey attribute to the objectProperty element:
<objectProperty isKey="true">UserName</objectProperty>
When the connector performs an update and deletes operations, all key properties are used form the where-clause of the SQL.
Foreign key columns
In some cases, you must locate foreign-key values for insert and update statements.
Example: GWGResourceAssigment table
The Account_UserId column is a foreign key to the UserId column in table GWGUserProfile mentioned in the data model example. Often, Omada Identity does not have this value available anywhere, but only the Username value. In this case, the OPS Database connector must perform a lookup to find the UserId. The referenceObject, referenceKeyProperty, and referenceLookupProperty are
used for this purpose. To support the lookup, use the following data model:
<connectorDataModel xmlns="http://schemas.omada.net/ops/2015/ConnectorDataModelML"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<properties>
<property name="Account_UserId" dataType="referenceType"/>
<property name="Resource_ResourceId" dataType="referenceType"/>
</properties>
<objects>
<object name="GWGResourceAssignment">
<objectProperties>
<objectProperty isKey="true" referenceObject="GWGUserProfile"
referenceKeyProperty="UserId" referenceLookupProperty="UserName">Account_
UserId</objectProperty>
<objectProperty isKey="true" referenceObject="GWGResource"
referenceKeyProperty="ResourceId" referenceLookupProperty="ResourceName">Resource_
ResourceId</objectProperty>
</objectProperties>
</object>
</objects>
</connectorDataModel>
You must specify the Account_UserId as a reference type property dataType="referenceType:
<property name="Account_UserId" dataType="referenceType"/>
The objectProperty is specified:
<objectProperty isKey="true"
referenceObject="GWGUserProfile"
referenceKeyProperty="UserId"
referenceLookupProperty="UserName">Account_UserId</objectProperty>
This instructs the database connector to make a lookup against the database table named GWGUserProfile where the filtering is done on the UserName column and the value retrieved
from column UserId. If your database is defined with proper foreign-key information, the database connector can look up the information automatically. In this case, all you need to specify is the
referenceLookupProperty property. The rest is read automatically out of the schema’s information in the database.
Call stored procedures
The database connector is designed to perform simple insert, update and delete statements against database tables. It is often necessary with multiple inserts or lookup to perform the wanted operation (in a stored procedure on the database which the connector can then call). When you create the stored procedure, it must follow the naming convention:
<Operation>_<ObjectName>
<Operation> can be Create, Update, Delete, CreateOrUpdate, or DeleteIfExists.
Specify objects as stored procedures
If the DisableSchemaValidation parameter is set to True, you must manually specify if an object is a stored procedure (in the object details):
<objects>
<object name=”User”>
<objectDetails>
<objectDetail name=”isStoredProcedure” value=”true” />
</objectDetails>
<objectProperties/>
</object>
</objects>
</connectorDataModel>
Database schemas
You can provide a database schema for every single object defined in the configuration. By default, no schema type is used in the query against the target databases. The schema has to be specified in the objectDetail section of the configuration. To use the schema OPS for the User objects, use the following configuration:
<objects>
<object name=”User”>
<objectDetails>
<objectDetail name=”schema” value=”OPS” />
</objectDetails>
<objectProperties/>
</object>
</objects>
</connectorDataModel>
Task mappings
For the Generic Database connector, no template task mappings are provided out of the box. You must define the data model and task mapping objects that are configured for the particular system that you want to connect to. The system will add any required fields to all the new task mappings, but you need to add mappings for all other relevant fields.
If a custom connector is used, task mappings must be created manually. See Software Development Kit for details.