Skip to main content

Data import

Connection details​

ParameterDescription
Connection typeChoose whether the connector should employ Standard security or a Trusted connection. We recommend selecting Advanced configuration.

When selecting the Advanced configuration option the password will be encrypted and masked with asterisks (*****). When making changes in the configuration the password must be reentered.
Database platformYou can choose from IBM DB2, Microsoft SQL Server, Oracle, MySQL, MariaDB, and PostgreSQL.

Bear in mind that using IBM DB2 as a database platform requires a valid IBM license. If the license is not available, select Microsoft SQL Server platform.

NOTE: If you select MySQL and your database uses the utf8mb3 character set, you may encounter the "Error executing web service" error. This affects the automatic detection of tables and columns. Upgrade the character set to utf8mb4 to resolve this issue. See the MySQL documentation for details.
OLEDB ProviderThe provider of the data access technology (for example, OraOLEDB.Oracle). Leave empty to use the default one (only for IBM DB2, Microsoft SQL Server, Oracle).

If you want to use the DB2OLEDB provider then the IBM DB2OLEDB driver must be installed on the Omada Identity server.
Database AddressThe address of the database.
Name of the databaseThe name of the database.
UserThe name of the user in the database (Standard security only)
PasswordThe user’s password (Standard security only)
Connection stringOnly available for Advanced configuration. Directly specified full connection string, including the initial catalog, the hostname, the security type and the OLE DB provider.

For example:
IBM platform: Provider=DB2OLEDB,portNumber;Network Transport Library=TCPIP;Network Address=db500dev.chsinc.ds;Initial Catalog=DB500DEV;User ID=myUsername;Password=myPassword;

Microsoft platform: Provider=MSOLEDBSQL19, portNumber;Data Source=example.com;Initial Catalog=SqlCollectorDB;Uid=myUsername;Pwd=myPassword;

Oracle platform: Provider=ORAOLEDB.ORACLE,portNumber;User ID=myUsername;password=myPassword; Data Source=ordbmar-001;Persist Security Info=False;

MySQL platform: Server=example.com;Port=portNumber;Database=mysqldb;Uid=myusername;Pwd=mypassword;

MariaDB platform: Server=example.com;Port=3316;Database=mariadb;Uid=myusername;Pwd=mypassword;SSL Mode=None;

PostgreSQL platform: Server=example.com;Port=portNumber;Database=postgres;User Id=myusername;Password=mypassword;

where portNumber, myUsername and myPassword are correct port number, username and password values.
ProtocolThe connection protocol for IBMDB2.
Port numberThe number of the port used for the connection.
Test connectionSelect this checkbox to perform a test of the connection before using the collector.
note

If you use the Multiple GenDB data import (collector) and change the schema in the existing instance (in the connection details), ensure that you open Queries and mappings, and then click Save. This allows detecting the database tables/columns and creating an updated SQL statement. This action is only required for the primary system, the change is inherited by the derived systems.

Advanced configuration​

The Advanced dialog consists of only one setting, the Request timeout. This setting allows you to define the timeout the SQL queries. The default value is 600 seconds.

advanced-dialog

Queries and mappings​

For the SQL data import, you must define SQL data to be mapped for a given object type. The SQL data import (collector) can read data from a table, view, or stored procedure.

While defining a new query in queries and mappings, you can select the following options:

  • Query build type

    • Build with UI -- Two additional fields are available for picking a table/view and columns. Based on the connection settings, the SQL Collector connects to Database and imports schema information. This schema is used to populate the fields with available tables, views, and columns. Using the Pick table or view field, users can select which table/view they want to query. Similarly, using Pick column(s), users can select which table/view columns they should include when doing the import.
      • The format for the SQL database is: [schema].[tableName]
      • The format for the Oracle and DB2 databases is: "schema"."tableName"
      • The format for the MySQL and MariaDB database is: schema.tableName
      • The format for the PostgreSQL is: schema."tableName"
    • Advanced -- Additional SQL Statement field is displayed where users need to enter a complete SQL statement used to perform an import.
    note

    Table names cannot contain SQL keywords as:

    • UPDATE
    • INSERT
    • DELETE

  • Rows processed per one call -- this parameter indicates how many rows the SQL Collector should take in one call. In other words, it indicates the size of the Result set to be processed in one pass. If there are 1000 Resources in the external Database and this parameter is set to 100, the Collector will perform 10 calls with 100 rows each. This parameter is used for performance reasons only. When empty or set to 0, the collector will perform only one call with the entire set of Rows.

  • Use delta - A field indicating if delta is used when importing data.

  • Modification date column name - Field where name of the column from table or view indicating when was the last time the record was modified is stated. It is used to determine records for delta import.

  • Filter -- This field features a filtering functionality with a Dynamic Expresso expression that is used for filtering the data imported into Omada Identity. It returns a TRUE/FALSE result for each imported data row. If the expression returns "FALSE" for the given row that row is skipped during import.

additional considerations

While mapping for queries, remember about the following:

  1. Microsoft SQL Server - Microsoft SQL Server returns the column names exactly as they are spelled in the database. For example, if there is a column roleName, it will be also returned as roleName.
  2. Oracle - Oracle returns column names in capital letters. For example, the column roleName will be returned as ROLENAME.
  3. IBM DB2 - The same naming rules apply as for Oracle. DB2 providers must be installed on the machine. Ensure that you use proper DB2 syntax when calling stored procedures, for example, CALL {name_of_procedure}.

Mapping of resource owners​

If you create a query to import resource owners, it is possible to specify the resource's owner in two ways. You can do it either by directly importing the UID of the identity or by specifying the account from which the resolved owner is imported as a resource owner.

When mapping directly to the UID of identity, ensure that identities are already imported to Omada Identity.

When mapping to an owned account, it is possible to either specify the business key of the account or the composed business key. The former should be used if the account is in the same system as the resource; the latter should be used if the account is imported into any of the trusted systems.

When the account stems from another system, you should use a Lookup mapping.

Using delta mode​

During the first import with delta mode enabled, all of the data is queried from source system. Consecutive queries with the Use delta option set to Yes, include only records with Modification date column value equal to NULL or greater than the last import date.

To work properly, delta mode requires that all queries defined for the same Object Type have the same import mode setting, either the Use delta option is set to Yes or No.

The same rule applies to following pairs of Object Types:

  • Identity and Account
  • Resource and Account
  • Resource Assignment and Resource parent child