1 About the Connector

The Database User Management connector integrates Oracle Identity Manager with database user management tables in Oracle Database, Microsoft SQL Server, MySQL, IBM DB2, and Sybase.

This chapter contains the following sections:

1.1 Introduction to the Database User Management Connector

Oracle Identity Manager automates access rights management, security, and provisioning of IT resources. Oracle Identity Manager connectors are used to integrate Oracle Identity Manager with third-party applications. This guide discusses the procedure to deploy the connector that is used to integrate Oracle Identity Manager with database user management tables in the following target systems:

  • Oracle Database

    In Oracle Database, the Login and User entities are treated as a single entity. In this guide, that entity is referred to as the Login entity.

  • Microsoft SQL Server

  • MySQL

  • DB2

  • Sybase

In Microsoft SQL Server and Sybase, access entities can be divided into the following types:

  • UserLogin: A login entity is used for authentication purposes.

  • User: A user entity is used for authorization or access control purposes.

Microsoft SQL Server and Sybase treat these entities as parent (Login) and child (User) elements. In Oracle Identity Manager, these entities are treated as separate, independent entities. In other words, the connector provides login provisioning as well as user provisioning features in Microsoft SQL Server and Sybase.

Note:

At some places in this guide, database resources such as Oracle, MSSQL, MySQL, DB2, or Sybase have been referred to as the target system.

1.2 Certified Components

connector.htm#GUID-C2D995F1-879C-4568-A002-394B33262D5B__BABEJCJF lists the certified components for the connector.

Table 1-1 Certified Components

Component Requirement

Oracle Identity Manager

You can use one of the following releases of Oracle Identity Manager:

  • Oracle Identity Governance 12c (12.2.1.4.0)

  • Oracle Identity Governance 12c (12.2.1.3.0)

  • Oracle Identity Manager release 11g PS1 (11.1.1.5.3) BP03 and any later BP in this release track

    If you are using Oracle Identity Manager 11.1.1.5.3, then you must download and apply the patches 14163597 and 13897038. To download a patch, sign in to My Oracle Support and search for the patch number on the Patches and Updates page at:

    https://support.oracle.com/

    Note: In this guide, Oracle Identity Manager release 11.1.1 has been used to denote Oracle Identity Manager release 11.1.1.5 BP03, and future releases in the 11.1.1 series that the connector will support.

  • Oracle Identity Manager 11g Release 2 (11.1.2.0.4) and any later BP in this release track

  • Oracle Identity Manager 11g Release 2 PS3 (11.1.2.3.0)

Target systems

The target system can be any one of the following:

  • Exadata V2

  • Oracle9i Database

  • Oracle Database 10g and 11g as either single database or Oracle RAC implementation

  • Oracle Database 12c as single database, pluggable database (PDB), or Oracle RAC implementation

  • Oracle Database 19c or 18c as a single database, pluggable database (PDB), or Oracle RAC implementation

  • Microsoft SQL Server 2005, 2008, 2012, 2014, 2016, 2017, 2019

  • MySQL 5.x

  • IBM DB2 UDB 9.x

  • Sybase 15.x

Connector Server

11.1.2.1.0

Connector Server JDK

JDK 1.6 or later, or JRockit JDK 1.6 or later.

Target system user account

Depending on the target system, one of the following user accounts is used by Oracle Identity Manager to perform reconciliation and provisioning operations on the target system:

  • For Oracle Database: sys as sysdba, or system

  • For Microsoft SQL Server: sa (administrator)

  • For MySQL: root

  • For DB2: db2admin

  • For Sybase: sa (administrator)

1.3 Usage Recommendation

Depending on the Oracle Identity Manager version that you are using, you must deploy and use one of the following connectors:

  • If you are using an Oracle Identity Manager release that is later than release 9.1.0.2 and earlier than Oracle Identity Manager 11g Release 1 (11.1.1.5.3), then you must use the 9.1.x version of this connector.

  • If you are using Oracle Identity Manager 11g Release 1 (11.1.1.5.3) and any later BP in this release track, Oracle Identity Manager 11g Release 2 (11.1.2.0.4) and any later BP in this release track, or Oracle Identity Manager 11g Release 2 PS3 (11.1.2.3.0) and any later BP in this release track, then you must use the latest 11.1.1.x version of this connector.

  • If you are using Microsoft SQL Server 2000 as the target system, then you must use the 9.1.x version of this connector, irrespective of the Oracle Identity Manager release you are using.

1.4 Certified Languages

The connector supports the following languages:

  • Arabic

  • Chinese (Simplified)

  • Chinese (Traditional)

  • Czech

  • Danish

  • Dutch

  • English (UK)

  • English (US)

  • Finnish

  • French

  • German

  • Greek

  • Hebrew

  • Hungarian

  • Italian

  • Japanese

  • Korean

  • Norwegian

  • Polish

  • Portuguese

  • Portuguese (Brazilian)

  • Romanian

  • Russian

  • Slovak

  • Spanish

  • Swedish

  • Thai

  • Turkish

Note:

However, the connector does not support the entry of multibyte characters in some of the fields.

1.5 Connector Architecture

The connector enables management of database accounts through Oracle Identity Manager. connector.htm#GUID-9DFE5B66-B8D9-4D3C-9A99-53254C1EB4A0__BABHIECG shows the architecture of the connector.

Figure 1-1 Architecture of the Connector

Description of Figure 1-1 follows
Description of "Figure 1-1 Architecture of the Connector"

The Database User Management (DBUM) connector is implemented by using the Identity Connector Framework (ICF). The ICF is a component that provides basic reconciliation and provisioning operations that are common to all Oracle Identity Manager connectors. In addition, ICF provides common features that developers would otherwise need to implement on their own, such as connection pooling, buffering, time outs, and filtering. The ICF is shipped along with Oracle Identity Manager. Therefore, you need not configure or modify the ICF.

See Also:

Understanding the Identity Connector Framework in Oracle Fusion Middleware Developing and Customizing Applications for Oracle Identity Manager for more information about the ICF

The out of the box (OOB) connectors are provided with the certified scripts for the certified targets such as Oracle, MSSQL, MySQL, DB2, and Sybase. If the connector is customized for a database other than the certified ones, then you need to manually add scripts for the new database.

All DBUM operations are performed by executing SQL Scripts or by calling Stored Procedures (Procs). These scripts and stored procs are externalized in different files in the connector bundle and can also be customized. IT resource, that has the configuration lookup parameter contains the bundle key. This bundle key is made of bundle name, bundle version, connector name, and is used for loading the bundle.

The following are the three categories of scripts that are stored in the connector bundle:

Script Description

Provisioning.queries

This script is used for Create, Update, or Delete operations.

LoVSearch.queries

This script is used for lookup reconciliation. It contains the set of possible values for certain fields such as profile, privileges, roles, and tablespaces.

Search.queries

This script is used for full or incremental or delete reconciliation. You can also perform account and group search with various conditions using this script.

Depending on the query invoked, ExecutionHandler executes the queries. There are two different handlers SQLExecutionHandler and StoredProcExecutionHandler which extends ExecutionHandler.

Depending on the type of Query, corresponding ExecutionHandler is invoked. StoredProcExecutionHandler is used for operations in MSSQL. The following is the example used for searching users:

USER_DATA_QUERY {
    Query="CALL sp_helpuser({__UID__})"
    QueryType="StoredProc"
    Parameters=["__UID__":"Type:String,Direction:IN",
            "defaultDatabase":"Type:String,Direction:OUT,ColName:DefDBName",
            "loginName":"Type:String,Direction:OUT,ColName:LoginName",
            "roles~DBRole~__NAME__":"Type:String,Direction:OUT,ColName:RoleName"]           
    QueryExtensions=[]
}

SQL queries are categorized into Data Definition Language (DDL) and Data Manipulation Language (DML) queries. DDL queries are used for CREATE, REVOKE, GRANT, ALTER, and so on, where as DML queries are used for UPDATE, INSERT, and so on.

The DDL queries are executed as regular statements. The following is the example for DDL statement used for the create operation:

Statement stmt = null;
                try {
                        stmt = _dbConnection.getConnection().createStatement();
                        stmt.execute(sqlScript);
                }

The DML queries are executed as prepared statements. The following is the example for DML statement used for the update operation:

PreparedStatement st = null;
            try {
                st = conn.prepareStatement(sqlScript);
                setParams(st, Arrays.asList(params));
                return st.executeUpdate();
                }

The information about the connector bundle is stored in the manifest file. This file contains the connector definition, which gives the information about the connector bundle framework version, connector bundle name, and connector bundle version. The following is the example of the connector definition:

org.identityconnectors.dbum.1.0.1116.jar

In this example:

org.identityconnectors: refers to connector bundle framework

dbum: refers to the connector bundle name

1.0.1116.jar: refers to the connector bundle version

The connector definition is required to identify the connector bundle. If you are deploying the multiple versions of the target, then you need to change the connector version and redeploy it. You can do it by changing the connector version in the connector bundle present in the manifest file.

See Also:

The following sections for more details about lookup definitions for each database:

1.6 Features of the Connector

The following are features of the connector:

1.6.1 Mapping Standard and Custom Attributes for Reconciliation and Provisioning

You can create mappings for single-valued and multivalued target system attributes that are not included in the list of default attribute mappings. These attributes can be part of the standard set of attributes provided by the target system or custom attributes that you add on the target system.

See the following sections for more information:

1.6.2 Predefined and Custom Reconciliation Queries

Reconciliation involves running a SQL query or stored procedure on the target system database to fetch the required user account records to Oracle Identity Manager.

The connector provides predefined SQL queries and stored procedures that enable you to reconcile user data from the target system. These predefined SQL queries and stored procedures are stored in the Search.queries file in the connector bundle.

You can modify these SQL queries or stored procedures. In addition, you can add your own SQL queries or stored procedures for reconciliation.

See the following sections for more information:

1.6.3 Predefined and Custom Provisioning Queries

Provisioning involves running a SQL query or stored procedure such as CREATE USER, ALTER USER, and DROP USER to perform Create User and Update user operations on the target system through Oracle Identity Manager.

The connector provides predefined queries that enable you to perform provisioning operations such as create, enable, and update target system accounts. These predefined SQL queries and stored procedures are stored in the Provisioning.queries file in the connector bundle.

You can modify and use any of the predefined provisioning queries. In addition, you can create your own provisioning queries. See the following sections for more information:

1.6.4 Support for Creating Global and External Users In Oracle Database

A local database user is a user who can be authenticated using a password stored in the database. In addition to support for local database users, the connector can also be used to work with the following types of users in Oracle Database:

  • Global users: These are database users who must be authorized by an enterprise directory service such as Oracle Internet Directory.

  • External users: These are database users who must be authenticated by an external service, such as an operating system or a third-party service such as Kerberos.

See the following sections for more information about local, global, and external user authentication types:

1.6.5 Full and Incremental Reconciliation

After you deploy the connector, you can perform full reconciliation to bring all existing user data from the target system to Oracle Identity Manager. After the first full reconciliation run, you can configure your connector for incremental reconciliation. In incremental reconciliation, only records that are added or modified after the last reconciliation run are fetched into Oracle Identity Manager.

See the following sections for more information:

1.6.6 Limited (Filtered) Reconciliation

ICF filter performs the limited reconciliation and the records are fetched into Oracle Identity Manager during a reconciliation run. The ICF filters are translated to WHERE clause and applied in the Search query.

See the following sections for more information:

1.6.7 Batched Reconciliation

You can break down a reconciliation run into batches by specifying the number of records that must be included in each batch and the query that must be used to perform batched reconciliation.

Note:

Microsoft SQL Server uses stored procedures to perform reconciliation. Therefore, the connector does not support batched reconciliation.

See the following sections for more information:

1.6.8 Specifying Accounts to Be Excluded from Reconciliation and Provisioning Operations

You can specify a list of target system accounts that must be excluded from all reconciliation and provisioning operations. Accounts whose users attributes you specify in the exclusion list are not affected by reconciliation and provisioning operations.

See the following sections for more information:

1.6.9 Connection Pooling

A connection pool is a cache of objects that represent physical connections to the target. Oracle Identity Manager connectors can use these connections to communicate with target systems. At run time, the application requests a connection from the pool. If a connection is available, then the connector uses it and then returns it to the pool. A connection returned to the pool can again be requested for and used by the connector for another operation. By enabling the reuse of connections, the connection pool helps reduce connection creation overheads like network latency, memory allocation, and authentication.

One connection pool is created for each IT resource. For example, if you have three IT resources for three installations of the target system, then three connection pools will be created, one for each target system installation.

The configuration properties of the connection pool are part of the lookup configuration. Setting up the Lookup Definition for Connection Pooling provides information about setting up the connection pool.

1.6.10 Support for Connector Server

Connector Server is a component provided by ICF. By using one or more connector servers, the connector architecture permits your application to communicate with externally deployed bundles. In other words, a connector server enables remote execution of an Oracle Identity Manager connector.

A Java connector server is useful when you do not wish to execute a Java connector bundle in the same VM as your application. It can be beneficial to run a Java connector on a different host for performance improvements.

See Installing and Configuring the Connector Server for more information.

1.6.11 Support for Creating Connector Copies

You can configure this connector for multiple installations of your target system by creating copies of connector objects such as lookup definitions, resource objects, and process forms.

See the following sections for more information:

1.6.12 Transformation and Validation of Account Data

You can configure validation of account data that is brought into or sent from Oracle Identity Manager during reconciliation and provisioning. In addition, you can configure transformation of account data that is brought into Oracle Identity Manager during reconciliation. The following sections provide more information:

For Oracle Database:

For MSSQL:

For MySQL:

For DB2:

For Sybase:

1.6.13 Support for Reconciling Data About Deleted Entities

You can reconcile data about login entities that have been deleted on the target system that has been configured as a trusted source or target resource.

After the records are fetched in to Oracle Identity Manager, depending on whether you have configured your target system as a target resource or trusted source, the records are compared with existing OIM Users or database resources provisioned to existing OIM Users. The unmatched accounts are revoked/removed from Oracle Identity Manager.

1.6.14 Separate Scheduled Jobs for Reconciliation of Users, Logins, and Deleted Login Entities

You can reconcile data about users, logins, or deleted login entities from a target system that is configured as a trusted source or target resource. Depending on the target system that you are using, the mode in which it is configured, and the type of data that you want to reconcile, separate scheduled jobs have been created.

See the following sections for more information:

1.6.15 Support for SSL Communication Between the Target System and Oracle Identity Manager

You can configure SSL to secure communication between Oracle Identity Manager and the target system. See the following sections for more information:

1.6.16 Support for Managing Authorization to Oracle Database Vault Realms

Oracle Database Vault restricts access to specific areas in an Oracle Database from any user, including users who have administrative access. For example, you can restrict administrative access to employee salaries, customer medical records, or other sensitive information. This enables you to apply fine-grained access control to your sensitive data in a variety of ways. It hardens your Oracle Database instance and enforces industry standard best practices in terms of separating duties from users with administrative access. Most importantly, it protects data from super-privileged users but still allows them to manage the Oracle Database installation.

With Oracle Database Vault, you can address business requirements such as protecting against insider threats, meeting regulatory compliance requirements, and enforcing separation of duty.

You configure Oracle Database Vault to manage the security of an individual Oracle Database instance. You can install Oracle Database Vault on standalone Oracle Database installations, in multiple Oracle homes, and in Oracle Real Application Clusters (Oracle RAC) environments.

In Oracle Database installations on which Oracle Database Vault is installed, the connector can be used to grant and manage authorization to Oracle Database Vault realms. The connector treats access to Oracle Database Vault realms as an entitlement. You can use the connector to provision database users with access to multiple realms with different levels of access.

Because Oracle Identity Manager is an enterprise application for managing user accounts and access to entitlements, the connector does not support management of the following:

  • Realms

  • Command rules and rule sets

  • Factors

  • Secure Application Roles

See Creating the Administrator Account on Oracle Database Vault for more information.

1.6.17 Support for Configuring the Connector for Enterprise User Security

Oracle Enterprise User Security addresses user, administrative, and security challenges by using the identity management services supplied by Oracle Internet Directory, an LDAP-compliant directory service. You must use either Oracle Identity Manager LDAP connectors or some other means to create the user in the LDAP-compliant directory. Enterprise users are provisioned and managed centrally in an LDAP-compliant directory, such as Oracle Internet Directory, for database access. Enterprise users have a unique identity in the directory called the distinguished name (DN). When enterprise users log on to a database, the database authenticates those users by using their DN.

In Oracle Database installations configured with Oracle Enterprise User Security, the connector supports the creation of password, global, and external authenticated users for a target system account (login or user). Depending on the authentication type, you need to make some changes in the process form. If the authentication type is password or external, then you must remove the other authentication fields from the process form. If the authentication type is global, then you must make the following changes in the process form:

  • Remove the password field as it is not required for the global authentication.

  • Set authentication type from default to Global and make it read-only. This will prevent user from choosing the other authentication type.

  • In Global DN, provide the unique ID.

You can use the connector to create and manage accounts of these enterprise users on the target database.

1.7 Roadmap for Deploying and Using the Connector

The following is the organization of information in the rest of this guide: