1 About the Database User Management Connector

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

The following sections provide a high-level overview of the connector:

• Introduction to the Database User Management Connector

• Certified Components

• Usage Recommendation

• Certified Languages

• Supported Connector Operations

• Connector Architecture

• Supported Connector Features Matrix

• Connector Features

1.1 Introduction to the Database User Management Connector

Oracle Identity Governance is a centralized identity management solution that provides self service, compliance, provisioning and password management services for applications residing on-premise or on the Cloud. Oracle Identity Governance connectors are used to integrate Oracle identity Governance with the external identity-aware applications.

The Database User Management connector lets you onboard applications in Oracle Identity Governance for target systems such as Oracle Database and MySQL.

Note:

In this guide, the connector that is deployed using the Applications option on the Manage tab of Identity Self Service is referred to as an AOB application. The connector that is deployed using the Manage Connector option in Oracle Identity System Administration is referred to as a CI-based connector (Connector Installer-based connector).

From Oracle Identity Governance release 12.2.1.3.0 onward, connector deployment is handled using the application onboarding capability of Oracle Identity Self Service. This capability lets business users to onboard applications with minimum details and effort. The connector installation package includes a collection of predefined templates (XML files) that contain all the information required for provisioning and reconciling data from a given application or target system. These templates also include basic connectivity and configuration details specific to your target system. The connector uses information from these predefined templates allowing you to onboard your applications quickly and easily using only a single and simplified UI.

Application onboarding is the process of registering or associating an application with Oracle Identity Governance and making that application available for provisioning and reconciliation of user information.

You can configure and use the Database User Management connector with 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.

• MySQL

Note:

In this guide, database resources such as Oracle and MySQL are referred to as the target system.

1.2 Certified Components

These are the software components and their versions required for installing and using the connector.

Note:

If you are using Oracle Identity Manager release 11.1.x, then you can install and use the connector only in the CI-based mode. If you want to use the AOB application, then you must upgrade to Oracle Identity Governance release 12.2.1.3.0 or later.

Table 1-1 Certified Components

Component	Requirement for AOB Application	Requirement for CI-Based Connector
Oracle Identity Governance or Oracle Identity Manager	You can use one of the following releases: Oracle Identity Governance 12c (12.2.1.4.0) Oracle Identity Governance 12c (12.2.1.3.0)	You can use one of the following releases: Oracle Identity Governance 12c (12.2.1.4.0) Oracle Identity Governance 12c (12.2.1.3.0) Note: If you are using Oracle Identity Governance 12c (12.2.1.3.0), then download and apply the patch 26616250 from My Oracle Support. Failing to apply this patch causes target resource user reconciliation runs to fail. Oracle Identity Manager 11g Release 2 PS3 (11.1.2.3.0)
Target systems	The target system can be any one of the following: Oracle Database 12c as single database, pluggable database (PDB), or Oracle RAC implementation Oracle Database 18c as single database, pluggable database (PDB), or Oracle RAC implementation Oracle Database 19c as single database, pluggable database (PDB), or Oracle RAC implementation MySQL 5.x	The target system can be any one of the following: Exadata V2 Oracle Database 12c as single database, pluggable database (PDB), or Oracle RAC implementation Oracle Database 18c as single database, pluggable database (PDB), or Oracle RAC implementation Oracle Database 19c as single database, pluggable database (PDB), or Oracle RAC implementation Microsoft SQL Server 2005, 2008, 2012, 2014, 2016 MySQL 5.x IBM DB2 UDB 9.x Sybase 15.x
Connector Server	12.2.1.3.0	12.2.1.3.0
Connector Server JDK	JDK 1.8 or later.	JDK 1.8 or later.

1.3 Usage Recommendation

These are the recommendations for the Database User Management connector versions that you can deploy and use depending on the Oracle Identity Governance or Oracle Identity Manager version that you are using.

• If you are using Oracle Identity Governance 12c (12.2.1.3.0) and want to integrate it with Oracle Database or MySQL, then use the latest 12.2.1.x version of this connector and deploy it using the Applications option on the Manage tab of Identity Self Service.

• If you are using Oracle Identity Governance 12c (12.2.1.3.0) and want to integrate it with IBM DB2, Microsoft SQL Server, or Sybase, then use the latest 12.2.1.x version of this connector and deploy it using the Manage Connector option in Oracle Identity System Administration.

• If you are using any of the Oracle Identity Manager releases listed in the “Requirement for CI-Based Connector” column of Certified Components, then use the 11.1.1.x version of the Database User Management connector. If you want to use the 12.1.x version of this connector, then you can install and use it only in the CI-based mode. If you want to use the AOB application, then you must upgrade to Oracle Identity Governance release 12.2.1.3.0.

• If you are using a release earlier than Oracle Identity Manager 11g Release 1 (11.1.1.5.3) and no later than Oracle Identity Manager release 9.1.0.2, then use the 9.1.x version of the Database User Management connector.

1.4 Certified Languages

These are the languages that the connector supports.

• Arabic

• Chinese (Simplified)

• Chinese (Traditional)

• Czech

• Danish

• Dutch

• English

• Finnish

• French

• French (Canadian)

• German

• Greek

• Hebrew

• Hungarian

• Italian

• Japanese

• Korean

• Norwegian

• Polish

• Portuguese

• Portuguese (Brazilian)

• Romanian

• Russian

• Slovak

• Spanish

• Swedish

• Thai

• Turkish

1.5 Supported Connector Operations

These are the list of operations that the connector supports for your target system.

Table 1-2 Supported Connector Operations

Operation	Supported for IBM DB2?	Supported for MSSQL?	Supported for MySQL?	Supported for Oracle Database?	Supported for Sybase?
User Management
Create user	Yes	Yes	Yes	Yes	Yes
Update user	No	No	No	Yes	Yes
Delete User	Yes	Yes	Yes	Yes	Yes
Enable user	Yes	No	No	Yes	Yes
Disable user	Yes	No	No	Yes	Yes
Reset password	Yes	Yes	Yes	Yes	Yes
Create UserLogin	Not applicable	Yes	Not applicable	Not applicable	Yes
Update UserLogin	Not applicable	Yes	Not applicable	Not applicable	Yes
Delete UserLogin	Not applicable	Yes	Not applicable	Not applicable	Yes
Entitlement Grant Management
Add roles	Not applicable	Yes	No	Yes	Not applicable
Revoke Roles	Not applicable	Yes	No	Yes	Not applicable
Add privileges	Not applicable	Not applicable	Yes	Yes	Not applicable
Revoke privileges	Not applicable	Not applicable	Yes	Yes	Not applicable
Add schema	Yes	Not applicable	Not applicable	Not applicable	Not applicable
Revoke schema	Yes	Not applicable	Not applicable	Not applicable	Not applicable
Add tablespace	Yes	Not applicable	Not applicable	Not applicable	Not applicable
Revoke tablespace	Yes	Not applicable	Not applicable	Not applicable	Not applicable
Add roles list	Not applicable	Not applicable	Not applicable	Not applicable	Yes
Revoke roles list	Not applicable	Not applicable	Not applicable	Not applicable	Yes

1.6 Connector Architecture

The Database User Management connector enables management of database accounts through Oracle Identity Governance, and is implemented using the Identity Connector Framework (ICF).

Figure 1-1 shows the architecture of the connector.

Figure 1-1 Architecture of the Connector

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

The Database User Management 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 Governance 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 Governance. 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 Governance for more information about the ICF

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

The connector performs all DBUM operations by executing SQL Scripts or by calling Stored Procedures (Procs).

For example, during a provisioning operation, the process tasks invoke an ICF operation, ICF inturn invokes an operation on the connector bundle which runs corresponding SQL statements. These SQL statements carry out the required operation on the target system, and return the response from the target system back to the bundle, which passes it to the adapters.

Similarly, during reconciliation, a scheduled task invokes ICF operation, ICF inturn invokes a search operation on the connector bundle which runs the corresponding query or stored procedure on the target system. Target system records that meet the query or stored procedure criteria are fetched into Oracle Identity Governance.

The scripts and stored procs in the connector bundle are externalized in different files in the connector bundle and can also be customized. The 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, the corresponding ExecutionHandler is invoked. StoredProcExecutionHandler is used for operations in MSSQL. The following is an 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 an example for the 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 an example for the 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 which is required to identity a connector bundle:

org.identityconnectors.dbum.12.3.0.jar

In this example:

org.identityconnectors: refers to connector bundle framework

dbum: refers to the connector bundle name

12.3.0 jar: refers to the connector bundle version

1.7 Supported Connector Features Matrix

Provides the list of features supported by the AOB application and CI-based connector.

Table 1-3 Supported Connector Features Matrix

Feature	AOB Application	CI-Based Connector	Supported Target Systems
Add new standard and custom attributes for reconciliation and provisioning	Yes	Yes	All
Customize the predefined queries for reconciliation	Yes	Yes	All
Customize the predefined queries for provisioning	Yes	Yes	All
Full reconciliation	Yes	Yes	All
Incremental reconciliation	Yes	Yes	For AOB Application: Oracle Database For CI-based connector: Oracle Database and MSSQL
Limited reconciliation	Yes	Yes	All
Batched reconciliation	Yes	Yes	All
Exclude accounts from reconciliation and provisioning operations	Yes	Yes	All
Connection pooling	Yes	Yes	All
Use connector server	Yes	Yes	All
Clone applications or create new application instances	Yes	Yes	All
Transformation and validation of account data	Yes	Yes	All
Reconcile deleted entities	Yes	Yes	All
Scheduled jobs for reconciliation of users, logins, and deleted login entities	Yes	Yes	All
SSL communication between the target system and Oracle Identity Manager	Yes	Yes	All
Add pre or post action scripts	Yes	Yes	All
Manage authorizations to Oracle Database Vault realms	Yes	Yes	Oracle Database
Configure Enterprise User Security	Yes	Yes	Oracle Database

1.8 Connector Features

The features of the connector include support for connector server, predefined and custom queries for performing provisioning and reconciliation operations, reconciliation of all existing or modified account data, support for limited and batched reconciliation, and so on.

The following are features of the connector:

• Mapping Standard and Custom Attributes for Reconciliation and Provisioning

• Predefined and Custom Provisioning and Reconciliation Queries

• Full and Incremental Reconciliation

• Limited (Filtered) Reconciliation

• Batched Reconciliation

• Specifying Accounts to Be Excluded from Reconciliation and Provisioning Operations

• Connection Pooling

• Support for Connector Server

• Support for Cloning Applications and Creating Instance Applications

• Transformation and Validation of Account Data

• Support for Reconciling Data About Deleted Entities

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

• Support for SSL Communication Between the Target System and Oracle Identity Governance

• Support for Running Pre and Post Action Scripts

• Support for Managing Authorization to Oracle Database Vault Realms

• Support for Configuring the Connector for Enterprise User Security

1.8.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.

For more information about adding new attributes, see Providing Schema Information for Target Application or Providing Schema Information for Authoritative Application in Oracle Fusion Middleware Performing Self Service Tasks with Oracle Identity Governance.

1.8.2 Predefined and Custom Provisioning and Reconciliation 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 Governance. Reconciliation involves running a SQL query or stored procedure on the target system database to fetch the required user account records to Oracle Identity Governance. The connector provides predefined SQL queries and stored procedures that enable you to reconcile user data from the target system and perform provisioning operations such as create, enable, and update target system accounts. You can modify these SQL queries or stored procedures. In addition, you can add your own SQL queries or stored procedures for provisioning and reconciliation.

The predefined SQL queries and stored procedures for reconciliation and provisioning are stored in the Search.queries and Provisioning.queries files, respectively, in the connector bundle.

For more information about modifying predefined SQL queries and stored procedures, see Modifying the Predefined Queries or Creating New Queries.

1.8.3 Full and Incremental Reconciliation

Full reconciliation involves reconciling all existing user records from the target system into Oracle Identity Governance. In incremental reconciliation, only records that are added or modified after the last reconciliation run are fetched into Oracle Identity Governance.

After you create the application, you can first perform full reconciliation. After the first full reconciliation run, incremental reconciliation is automatically enabled.

The following sections provide more information:

• Performing Full and Incremental Reconciliation from Oracle Database

• Performing Full Reconciliation from MySQL

1.8.4 Limited (Filtered) Reconciliation

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

The following sections provide more information:

• Performing Limited Reconciliation from Oracle Database

• Performing Limited Reconciliation from MySQL

1.8.5 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.

The following sections provide more information:

• Performing Batched Reconciliation from Oracle Database

• Performing Batched Reconciliation from MySQL

1.8.6 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. The accounts for which you specify users attributes in the exclusion list are not affected by reconciliation and provisioning operations.

You can write a Groovy-based validation script that specifies a list of accounts that must be excluded from connector operations. For more information about the Validation Groovy Script for Resource Exclusion, see About Customizing Groovy Scripts in Oracle Fusion Middleware Performing Self Service Tasks with Oracle Identity Governance.

1.8.7 Connection Pooling

A connection pool is a cache of objects that represent physical connections to the target. Oracle Identity Governance 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 set of basic configuration parameters that you provide while creating an application. For example, if you have three applications for three installations of the target system, then three connection pools will be created, one for each target system installation.

For more information about configuring connection pool, see:

• For Oracle Database: Advanced Settings Parameters for Oracle Database

• For MySQL: Advanced Settings Parameters for MySQL

1.8.8 Support for Connector Server

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

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.

For information about installing, configuring, and running the Connector Server, and then installing the connector in a Connector Server, see Using an Identity Connector Server in Oracle Fusion Middleware Developing and Customizing Applications for Oracle Identity Governance.

1.8.9 Support for Cloning Applications and Creating Instance Applications

You can configure this connector for multiple installations of the target system by cloning applications or by creating instance applications.

When you clone an application, all the configurations of the base application are copied into the cloned application. When you create an instance application, it shares all configurations as the base application.

For more information about these configurations, see Cloning Applications and Creating Instance Applications in Oracle Fusion Middleware Performing Self Service Tasks with Oracle Identity Governance.

1.8.10 Transformation and Validation of Account Data

You can configure transformation and validation of account data that is brought into or sent from Oracle Identity Governance during reconciliation and provisioning operations by writing Groovy scripts while creating your application.

For more information, see Validation and Transformation of Provisioning and Reconciliation Attributes in Oracle Fusion Middleware Performing Self Service Tasks with Oracle Identity Governance.

1.8.11 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 into Oracle Identity Governance, 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 Governance.

1.8.12 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.

For information about the scheduled jobs, see one of the following topics:

• Reconciliation Jobs for Oracle Database

• Reconciliation Jobs for MySQL

1.8.13 Support for SSL Communication Between the Target System and Oracle Identity Governance

You can configure SSL to secure communication between Oracle Identity Governance and the target system.

The following sections provide more information:

• Configuring Secure Communication Between Oracle Database and Oracle Identity Governance

• Configuring Secure Communication Between MySQL and Oracle Identity Governance

1.8.14 Support for Running Pre and Post Action Scripts

You can run pre and post action scripts on a computer where the connector is deployed. These scripts can be of type SQL/StoredProc/Groovy. You can configure the scripts to run before or after the create, update, or delete an account provisioning operations.

For more information, see Updating the Provisioning Configuration in Oracle® Fusion Middleware Performing Self Service Tasks with Oracle Identity Governance.

1.8.15 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 Governance 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.8.16 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, and globally authenticated users for a target system account (login or user). Depending on the authentication type, you need to select the corresponding authentication type at the time of provisioning. 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 global authentication.

In addition, while performing the provisioning operation, you must:

• Set authentication type to Global.

• Provide the unique ID in Global DN.

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