JDBC Oracle

The purpose of this guide is to provide detailed information regarding the JDBC ORACLE connector. There are a number of things you can learn about this connector here, including information about its configuration and deployment.

About JDBC Oracle Connector

The JDBC connector for Oracle will let you create and onboard JDBC applications (for Oracle) in IDHub.

Connector Version

JDBC ORACLE connector is currently at Version 1.0. More operations and other capabilities will be supported in upcoming updates and releases of the JDBC ORACLE connector.

Component	Version
JDBC ORACLE Connector	Ver 1.0
Target System	SDK version 1.32.1
Connector Server	11.1.2.1.0 or 12.2.1.3.0
Connector Server JDK	JDK 1.6 and later

Connector Components

The components of the connector include Connector Application, Connector Application Configuration, Connector Service Provider Interface, Splice, and Splice configuration.

These connection components contain precise connectivity and setup information for your target system. The connector takes information from these files to allow you to quickly and efficiently onboard your applications using a single, streamlined UI.

Connector Architecture

The connector's architecture is constructed in accordance with the diagram below:

The connector architecture primarily consists of a connector application and a target system component, as seen in the screenshot up above. The native communication with the target system is handled by the target system by leveraging the SPI implementation of the JDBC ORACLE Specific connection. This architecture is implemented because it allows for rapid and straightforward connector deployment as well as precise versioning capabilities.

The connector is configured to run in one of the following modes:

Target Resource reconciliation
- Users are instantly created and changed on IDHub in this situation if you rely on the JDBC ORACLE application as the trusted source. When user records match the reconciliation criteria, the JDBC ORACLE SDK retrieves them and sends them to IDHub. Each user record retrieved from the target system is cross-checked against existing IDHub Users. If there is a match between the target system record and the IDHub User, the User attributes are changed to reflect the changes made to the target system record. If no matches are identified, the target system record is utilized to generate an IDHub User.
Account management
- This entails using IDHub to add, modify, or remove users from the target system.
  - During provisioning, the connector invokes the target system's JDBC ORACLE SDK for provisioning tasks.
  - The target system's SDK accepts provisioning data, does the necessary actions there, and then sends IDHub the result back from the target system. Applications can carry out create, read, update, and delete (CRUD) actions on the target system using the JDBC ORACLE SDK.

note

In developing the connector, we adhere to this fundamental architecture. The IDHub team will handle the connector modification section appropriately based on your unique business requirements if there are any improvements, extra specifications, or variations.

Connector Features

Full Reconciliation and Incremental Reconciliation

Full reconciliation can be performed to bring all existing user data from the target system to IDHub. If the target system has an attribute that stores the timestamp at which an item is created or modified, you can configure your connector for incremental reconciliation once the first full reconciliation operation has been completed.

info

The connector's future release version will incorporate incremental recon, which is not supported by the present version of the connector.

Limited Reconciliation

Records from the target system can be reconciled depending on a defined filter condition. You can define the subset of newly added or updated target system records that must be reconciled in order to restrict or filter the records that are fetched into IDHub during a reconciliation process. You can specify the conditions in which the reconciliation will take place.

Reconciliation of Deleted User Records

The connector can be set up to reconcile user records that have been erased. If a user record is removed on the target system while in target resource mode, the matching User is revoked from IDHub as well.

Transformation and Validation of Account Data

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

Pre-requisites

Create Login using the following query:

CREATE USER serviceuser IDENTIFIED BY password
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp QUOTA UNLIMITED ON users;

Assign the following permissions and roles to the created user:

GRANT CONNECT TO serviceuser;
GRANT SELECT on dba_role_privs TO serviceuser;
GRANT SELECT on dba_sys_privs TO serviceuser;
GRANT SELECT on dba_ts_quotas TO serviceuser;
GRANT SELECT on dba_tablespaces TO serviceuser;
GRANT SELECT on dba_users TO serviceuser;
GRANT CREATE USER TO serviceuser;
GRANT ALTER ANY TABLE TO serviceuser;
GRANT GRANT ANY PRIVILEGE TO serviceuser;
GRANT GRANT ANY ROLE TO serviceuser;
GRANT DROP USER TO serviceuser;
GRANT SELECT on dba_roles TO serviceuser;
GRANT SELECT ON dba_profiles TO serviceuser;
GRANT ALTER USER TO serviceuser;
GRANT CREATE ANY TABLE TO serviceuser;
GRANT DROP ANY TABLE TO serviceuser;
GRANT CREATE ANY PROCEDURE TO serviceuser;
GRANT DROP ANY PROCEDURE TO serviceuser;

Creating an Application by using the Connector

Onboard the Application in IDHub

tip

Click here for the detailed steps for onboarding the application to IDHub

Configuring the Connector

Connectors use connection-related parameters to connect to IDhub with your target system and perform connector operations when creating a connected application. IDHub requires the following connection-related parameters in order to connect to an JDBC ORACLE application.

Basic Configuration of the Connector for Oracle

Parameter	Type	Default Value	Description
host	String	NA	Host name or IP address of the computer hosting the target system.Sample value: `HOST_IP_ADDRESS`
port	String	NA	Enter the number of the port at which the target system database is listening. Sample value: `PORT_NUMBER`
database	String	NA	Name of the target database. Sample value: `DB_NAME`
jdbcDriver	String	NA	JDBC driver class name.Sample value for Oracle database: `oracle.jdbc.driver.OracleDriver`Sample value for MySQL: `com.mysql.jdbc.Driver`Sample value for MS SQL: `com.microsoft.sqlserver.jdbc.SQLServerDriver`Sample value for DB2: `com.ibm.db2.jcc.DB2Driver`
jdbcUrlTemplate	String	NA	JDBC URL template of the target database. The value that you specify depends on the database product that you are using.Sample value for Oracle database with SID: `jdbc:oracle:thin:@mydb.com:PORT:oim`Sample value for Oracle database with Service Name: `jdbc:oracle:thin:@mydb.com:PORT/oim`Sample value for MySQL: `jdbc:mysql://mydb.com:PORT/mysql`Sample value for MS SQL: `jdbc:sqlserver://mydb.com:PORT;Database=acmedb`Sample value for DB2: `jdbc:db2://mydb.com:PORT/mydb`
user	String	NA	User ID of the database user account that IDHub uses to connect to the target system.Sample value: `DB_USERNAME`
password	String	NA	Password of the database user account that IDHub uses to connect to the target system.Sample value: `DB_PASSWORD`
table	String	NA	Name of the parent table or view that contains user records.Sample value: `DB_TABLE_NAME`
keyColumn	String	NA	Name of the column that uniquely identifies each row in the parent table.Sample value: `PRIMARY_KEY_OF_DB_PARENT_TABLE`
passwordColumn	String	NA	Name of the column in the parent table that holds the passwords of the target system records.Sample value: `PASSWORD`
statusColumn	Boolean	NA	Name of the column in the target system that holds the status of a user record. You must specify a value for this attribute only if both the following conditions are true:You want to perform the enable user account or disable user account provisioning operations.There exists a column in the target system that holds the status of a user record.Sample value: `ACTIVE12.3.0`
enableValue	String	NA	Value used on the target system that depicts that a user record is in the enabled status.Sample value: `enable`
disableValue	String	NA	Value used on the target system that depicts that a user record is in the disabled status.Sample value: `disable`
relationTables	String	NA	A comma-separated list of child table names when user data is spread across parent and child tables.Sample value: `CHILD_DB_TABLE_NAME`
Connector Server Name	String	NA	Name of the connector server IT resource.Sample value: `CONNECTOR_SERVER_NAME`
validConnectionQuery	String	NA	If no value is specified for this property, then the connection is validated by switching the auto commit mode. For example, you might have the following query, which might be more efficient for some databases:`SELECT 1 FROM DUMMY`
changeLogColumn	String	NA	Name of the column where the last update-related, non-decreasing, value is stored. Can be a number or a timestamp.The data type of this column can be any of the data types supported by the target system. However, if you are using Oracle Database, then data types such as BLOB, CLOB, and LONG are not supported.
customizedQuery	String	NA	A WHERE clause in a SQL query specifying the subset of newly added or modified records that you want to reconcile. The WHERE clause can contain relations to other tables or views.
allNative	Boolean	false	If value of this property is `false`, then attribute data is converted to Strings by using the JDBC driver.Set the value of this property to `true` to use the appropriate JDBC types and to force the connector to perform the conversion.The new Date format and Timestamps format invalidate this setup.
dateformat	String	dd/MM/yyyy	Allows the user to format how date data is converted to strings.If you want to handle date data as a date editor, then do not enter any value for this parameter.If you want to handle date data as text, then you must enter the date format.Specifying a value for this parameter invalidates the allNative parameter.
timestampFormat	String	dd/MM/yyyy HH:mm:ss:SSS	Allows the user to format how timestamp data is converted to strings. Specifying this property invalidates the nativeTimestamps and allNative properties.
nativeTimestamps	Boolean	false	If the value of this property is set to `false`, then timestamp data is read as Strings, which can cause a loss of time in milliseconds.If the value of this property is set to `true`, then timestamp data is retrieved as java.sql.Timestamp type, and then the connector performs the conversion.
enableEmptyString	Boolean	false	Set to `true` if you want to enable support for writing an empty string instead of a NULL value.Set to `false` if empty strings must be written as NULL values.Note: This property can be applied only to mandatory String attributes
quoting	String	None	Column quoting property (such as `None, Single, Double, Back,` or `Brackets`) that best fits your target system database.Column names are displayed between single quotes, double quotes, back quotes, or brackets in the generated SQL when accessing the database.
jdbcDriver	String	NA	JDBC driver class name.Sample value for SAP HANA DB: `'jdbcDriver': ‘com.sap.db.jdbc.Driver',`
jdbcUrlTemplate	String	NA	JDBC URL template of the target database.Sample value for SAP HANA DB: `'jdbc:sap://acmedb.com:30015',`
rethrowAllSQLExceptions	Boolean	false	Set to `false` if SQL exceptions with a zero (0x00) error code must be considered a success. In other words, SQL exceptions with the zero error code are caught and suppressed by the SQL statement. Otherwise, set to `true`.

Advanced Settings Parameters for Oracle

There are some advanced settings that you can configure in the connector.

info

In the current version of the connector, these advanced settings cannot be configured; however, they will be available in future releases.

Parameter	Default Value	Description
Connector Version	-	This parameter holds the version of the connector bundle class.
Pool Max Idle	10	Maximum number of idle objects in a pool.
Pool Max Size	10	Maximum number of connections that the pool can create.
Pool Max Wait	150000	Maximum time, in milliseconds, the pool must wait for a free object to make itself available to be consumed for an operation.
Pool Min Evict Idle Time	120000	Minimum time, in milliseconds, the connector must wait before evicting an idle object.
Pool Min Idle	1	Minimum number of idle objects in a pool.
datasource	NA	Data source name for the data source naming properties.Sample value: jdbc/operationsDB
jndiProperties	NA	Properties used to establish a connection with the target system by using JDBC drivers, enable additional connection properties, or look up a DataSource using JNDI.Sample value:`"java.naming.factory.initial=weblogic.jndi.WLInitialContextFactory","java.naming.provider.url=t3://example.com:15000","java.naming.security.principal=weblogic","java.naming.security.credentials=WEBLOGIC_PASSWORD"`
createScript	None	This property is present only in the section for target resource configuration.Specify a value for this property only if you want to configure the connector to use custom stored procedures or SQL statements rather than default SQL statements for performing provisioning operations.Enter the Groovy script or the file URL of the Groovy script created for the create user account provisioning operation. When this script is called, the parent form data is added.You must enter the file URL in the following format:`file:///URL`Sample value: `file:///home/jdoe/dbat/scripts/create_user.groovy`
updateSript	None	This property is present only in the section for target resource configuration.Specify a value for this property only if you want to configure the connector to use custom stored procedures or SQL statements rather than default SQL statements for performing provisioning operations.Enter the Groovy script or the file URL of the Groovy script created for the update user account provisioning operation. This script is called when you update the parent form, or enable or disable the user account.You must enter the file URL in the following format:`file:///URL`Sample value: `file:///home/jdoe/dbat/scripts/update_user.groovy`
deleteScript	None	This property is present only in the section for target resource configuration.Specify a value for this property only if you want to configure the connector to use custom stored procedures or SQL statements rather than default SQL statements for performing provisioning operations.Enter the Groovy script or the file URL of the groovy script created for the delete user account provisioning operation. This script is called when you remove or delete an account without child data.You must enter the file URL in the following format:`file:///URL`Sample value: `file:///home/jdoe/dbat/scripts/delete_user.groovy`
executeQueryScript	None	Specify a value for this property only if you want to configure the connector to use custom stored procedures or SQL queries rather than default SQL queries to perform reconciliation.Enter the Groovy script or the file URL of the Groovy script created for reconciliation. The connector delegates the reconciliation operation to the Groovy script, which is responsible for passing the information (connector object) to the callback handler. This script is called while performing an account search (operations such as full and filtered reconciliation).You must enter the file URL in the following format:`file:///URL`Sample value: `file:///home/jdoe/dbat/scripts/recon_user.groovy`
lookupScript	None	This property is present only in the section for target resource configuration.Specify a value for this property only if you want to configure the connector to use custom stored procedures or SQL queries rather than default SQL queries to perform lookup field synchronization.Enter the Groovy script or the file URL of the Groovy script created for lookup field synchronization.You must enter the file URL in the following format:`file:///URL`Sample value: `file:///home/jdoe/dbat/scripts/lookup_field_sync.groovy`
syncScript	None	Specify a value for this property only if you want to configure the connector to use custom stored procedures or SQL queries rather than default SQL queries to perform incremental reconciliation.Enter the Groovy script or the file URL of the Groovy script created for incremental reconciliation.You must enter the file URL in the following format:`file:///URL`Sample value: `file:///home/jdoe/dbat/scripts/increm_recon_user.groovy`
addMultiValuedAttributeScript	None	This property is present only in the section for target resource configuration.Specify a value for this property only if you want to configure the connector to use custom stored procedures or SQL statements rather than default SQL statements for performing provisioning operations.Enter the Groovy script or the file URL of the Groovy script created for the add multivalued attribute provisioning operation. This script is called when you add multivalued child attributes.You must enter the file URL in the following format:`file:///URL`Sample value:`file:///home/jdoe/dbat/scripts/add_mulval_attr.groovy`
removeMultiValuedAttributeScript	None	This property is present only in the section for target resource configuration.Specify a value for this property only if you want to configure the connector to use custom stored procedures or SQL statements rather than default SQL statements for performing provisioning operations.Enter the Groovy script or the file URL of the Groovy script created for lookup field synchronization. This script is called while removing multivalued child attributes.You must enter the file URL in the following format:`file:///URL`Sample value: `file:///home/jdoe/dbat/scripts/remove_mulval_attr.groovy`
sapHanaDb	NA	This property suggests sapHanaDb parameter support, if using for sapHanaDb only.Sample value: `True`

Attribute Mappings for the Connector

JDBC Schema	User Schema in IDHub	Sync Direction	IsVisible	Example	Type of Attribute
organizationName	organizationName	Target system to IDHub	Visible	Sath
department	department	Target system to IDHub	Visible	Marketing
displayName	displayName	Bi-Directional	Visible	John33
firstName	firstName	Target system to IDHub	Visible	John
jobTitle	jobTitle	Target system to IDHub	Visible	Manager
email	email	Target system to IDHub	Visible	John@sath.com	Recon Key
lastName	lastName	Target system to IDHub	Visible	Garret
userLogin	login	Target system to IDHub	Visible	John_g33	Account name Field
phoneNumber	phoneNumber	Target system to IDHub	Visible	355-5656-6666
employeeNumber	employeeNumber	Target system to IDHub	Visible	John8678
location	location	Target system to IDHub	Visible
managerDisplayName	managerDisplayName	Target system to IDHub	Visible
managerLogin	managerLogin	Target system to IDHub	Visible	John8678
status	status	Target system to IDHub	Visible

tip

Sync Direction of the Attributes depends on whether you regard Oracle as your Trusted Source.
You should only synchronize from IDHub to JDBC ORACLE and not the reverse if ORACLE is not a trusted system in your case.

Possible Recon Key	Possible Recon Key Values
email	John@sath.com
userLogin	John33
managerLogin	John8678
employeeNumber	John8678

Connector Application Configuration

Connector application is designed such that it works as the wrapper application to the different scim adapters. This majorly consists of the following:

Authentication

Basic Authentication is required
The encrypted values of username and password will be stored in the properties file

JDBC ORACLE Connector Splice configuration

In order to provision, modify, and revoke two main resources, Accounts and Entitlements, the JDBC ORACLE Connector Splice integrates with the IDHub Connector Application.

Service Provider Configuration

This contains a JSON schema that describes the SCIM Resource Operations Compliance, Authentication Methods, and data models available for a SCIM Service Provider.

{
 ``"schemas"``: [
  ``"urn:ietf:params:scim:schemas:core:2.0:ServiceProviderConfig"
 ``],
 ``"patch"``: {
  ``"supported"``: ``false
 ``},
 ``"bulk"``: {
  ``"supported"``: ``false``,
  ``"maxOperations"``: 0,
  ``"maxPayloadSize"``: 0
 ``},
 ``"filter"``: {
  ``"supported"``: ``false``,
  ``"maxResults"``: 0
 ``},
 ``"changePassword"``: {
  ``"supported"``: ``false
 ``},
 ``"sort"``: {
  ``"supported"``: ``false
 ``},
 ``"etag"``: {
  ``"supported"``: ``false
 ``},
 ``"authenticationSchemes"``: [
  ``{
   ``"name"``: ``"OAuth Bearer Token"``,
   ``"description"``: ``"Authentication scheme using the OAuth Bearer Token Standard"``,
   ``"specUri"``: ``"http://www.rfc-editor.org/info/rfc6750"``,
   ``"documentationUri"``: ``"no documentation"``,
   ``"type"``: ``"oauthbearertoken"``,
   ``"primary"``: ``true
  ``},
  ``{
   ``"name"``: ``"HTTP Basic"``,
   ``"description"``: ``"Authentication scheme using the HTTP Basic Standard"``,
   ``"specUri"``: ``"http://www.rfc-editor.org/info/rfc2617"``,
   ``"documentationUri"``: ``"no documentation"``,
   ``"type"``: ``"httpbasic"
  ``}
 ``],
 ``"meta"``: {
  ``"location"``: ``"scim/v2/ServiceProviderConfig"``,
  ``"resourceType"``: ``"ServiceProviderConfig"``,
  ``"created"``: ``"2019-09-03T00:00:00Z"``,
  ``"lastModified"``: ``"2019-09-03T00:00:00Z"``,
  ``"version"``: ``"W\/\"3694e05e9dff594\""
 ``}
}

Resource Schema Configuration

This is the resource schema configuration as given below:

{
 ``"id"``: ``"urn:sath:params:scim:schemas:core:1.0:Account"``,
 ``"name"``: ``"Account"``,
 ``"description"``: ``"User Account"``,
 ``"attributes"``: [
  ``{
   ``"name"``: ``"Fname"``,
   ``"type"``: ``"string"``,
   ``"required"``: ``true
  ``},
  ``{
   ``"name"``: ``"Lname"``,
   ``"type"``: ``"string"``,
   ``"required"``: ``true
  ``},
  ``{
   ``"name"``: ``"username"``,
   ``"type"``: ``"string"``,
   ``"required"``: ``true
  ``},
  ``{
   ``"name"``: ``"displayName"``,
   ``"type"``: ``"string"``,
   ``"required"``: ``false
  ``},
  ``{
   ``"name"``: ``"emails"``,
   ``"type"``: ``"string"``,
   ``"required"``: ``true
  ``},
  ``{
   ``"name"``: ``"phones"``,
   ``"type"``: ``"string"``,
   ``"required"``: ``true
  ``}
 ``],
 ``"meta"``: {
  ``"resourceType"``: ``"Schema"``,
  ``"location"``: ``"/v2/Schemas/urn:sath:params:scim:schemas:core:1.0:Account"
 ``},
 ``"schemas"``: ``"urn:sath:params:scim:schemas:core:1.0:Account"``,
 ``"matching-attributes"``: [
  ``"username"``,
  ``"email"
 ``],
 ``"attribute-map"``: {
  ``"displayName"``: {
   ``"type"``: ``"string"``,
   ``"attribute"``: ``"displayName"
  ``},
  ``"name"``: {
   ``"type"``: ``"string"``,
   ``"attribute"``: ``"displayName"
  ``},
  ``"username"``: {
   ``"attribute"``: ``"username"``,
   ``"type"``: ``"string"
  ``},
  ``"email"``: {
   ``"type"``: ``"string"``,
   ``"query"``: ``".emails"
  ``},
  ``"phone"``: {
   ``"attribute"``: ``"phones"``,
   ``"type"``: ``"string"
  ``},
  ``"title"``: {
   ``"attribute"``: ``"title"``,
   ``"type"``: ``"string"
  ``},
  ``"department"``: {
   ``"attribute"``: ``"department"``,
   ``"type"``: ``"string"
  ``},
  ``"userType"``: {
   ``"attribute"``: ``"userType"``,
   ``"type"``: ``"string"
  ``},
  ``"organization"``: {
   ``"attribute"``: ``"organization"``,
   ``"type"``: ``"string"
  ``},
  ``"employeeNumber"``: {
   ``"attribute"``: ``"employeeNumber"``,
   ``"type"``: ``"string"
  ``},
  ``"manager"``: {
   ``"attribute"``: ``"manager"``,
   ``"type"``: ``"string"
  ``}
 ``},
 ``"scripts"``: {
  ``"insert"``: [``"INSERT INTO usr (phone,email,username) VALUES('${phone}','${email}','${username}') "``],
  ``"update"``: [``"UPDATE usr set phone='${phone}',email='${email}'"``],
  ``"delete"``: [``"DELETE FROM usr"``],
  ``"select"``: [``"SELECT * FROM usr"``],
  ``"mappingToExistingToResponse"``: [
   ``{
    ``"id"``: ``"${username}"``,
    ``"externalId"``: ``"${username}"``,
    ``"username"``: ``"${username}"``,
    ``"givenName"``: ``"${username}"``,
    ``"displayName"``: ``"${username}"``,
    ``"name"``: ``"${username}"``,
    ``"email"``: ``"${email}"``,
    ``"userType"``: ``"${username}"``,
    ``"title"``: ``"${username}"``,
    ``"department"``: ``"${username}"``,
    ``"organization"``: ``"${username}"``,
    ``"phone"``: ``"${phone}"``,
    ``"employeeNumber"``: ``"${username}"
   ``}
  ``]
 ``},
 ``"configs"``: {
  ``"tableName"``: ``"usr"``,
  ``"uniqueKey"``:``"username"
 ``}
}

Connector Splice Design

Account Schema

The Account Schema configuration of the JDBC connector Splice is as follows:

{
"id": "urn:sath:params:scim:schemas:core:1.0:Account",
"name": "Account",
"description": "User Account",
"attributes": [
{
"name": "displayName",
"type": "string",
"required": true
},
{
"name": "userLogin",
"type": "string",
"required": true
},
{
"name": "phoneNumber",
"type": "string",
"required": true
},
{
"name": "email",
"type": "string",
"required": true
},
{
"name": "entitlements",
"type": "complex"
},
{
"name": "department",
"type": "string"
},
{
"name": "firstName",
"type": "string"
},
{
"name": "employeeNumber",
"type": "string"
},
{
"name": "jobTitle",
"type": "string"
},
{
"name": "lastName",
"type": "string"
},
{
"name": "location",
"type": "string"
},
{
"name": "managerDisplayName",
"type": "string"
},
{
"name": "managerLogin",
"type": "string"
},
{
"name": "organizationName",
"type": "string"
},
{
"name": "status",
"type": "string"
}
],
"meta": {
"resourceType": "Schema",
"location": "/v2/Schemas/urn:sath:params:scim:schemas:core:1.0:Account"
},
"schemas": "urn:sath:params:scim:schemas:core:1.0:Account",
"matching-attributes": [
"userLogin"
],
"entitlement-matching-attributes": [
"userLogin"
],
"attribute-map": {
"displayName": {
"type": "string",
"attribute": "displayName"
},
"userLogin": {
"attribute": "userLogin",
"type": "string"
},
"email": {
"type": "string",
"attribute": "email"
},
"phoneNumber": {
"attribute": "phoneNumber",
"type": "string"
},
"entitlements": {
"attribute": "entitlements",
"type": "complex"
},
"department": {
"attribute": "department",
"type": "string"
},
"firstName": {
"attribute": "firstName",
"type": "string"
},
"employeeNumber": {
"attribute": "employeeNumber",
"type": "string"
},
"jobTitle": {
"attribute": "jobTitle",
"type": "string"
},
"lastName": {
"attribute": "lastName",
"type": "string"
},
"location": {
"attribute": "location",
"type": "string"
},
"managerDisplayName": {
"attribute": "managerDisplayName",
"type": "string"
},
"managerLogin": {
"attribute": "managerLogin",
"type": "string"
},
"organizationName": {
"attribute": "organizationName",
"type": "string"
},
"status": {
"attribute": "status",
"type": "string"
}
},
"scripts": {
"insert": [
"INSERT INTO usr (phoneNumber,email,userLogin,displayName,department,firstName,employeeNumber,jobTitle,lastName,location,managerDisplayName,managerLogin,organizationName,status) VALUES(''${phoneNumber}'',''${email}'',''${userLogin}'',''${displayName}'',''${department}'',''${firstName}'',''${employeeNumber}'',''${jobTitle}'',''${lastName}'',''${location}'',''${managerDisplayName}'',''${managerLogin}'',''${organizationName}'',''${status}'') "
],
"assign": [
"INSERT INTO usr_permission_map (entitlements,userLogin) VALUES(''${entitlements}'',''${userLogin}'') "
],
"update": [
"UPDATE usr set phoneNumber=''${phoneNumber}'',email=''${email}'',displayName=''${displayName}'',department=''${department}'',firstName=''${firstName}'',employeeNumber=''${employeeNumber}'',jobTitle=''${jobTitle}'',lastName=''${lastName}'',location=''${location}'',managerDisplayName=''${managerDisplayName}'',managerLogin=''${managerLogin}'',organizationName=''${organizationName}'',status=''${status}''"
],
"delete": [
"DELETE FROM usr"
],
"deleteEntitle": [
"DELETE FROM usr_permission_map"
],
"select": [
"SELECT * FROM usr"
],
"mappingToExistingToResponse": [
{
"userLogin": "${userLogin}",
"displayName": "${displayName}",
"email": "${email}",
"phoneNumber": "${phoneNumber}",
"entitlements": "${entitlements}",
"department": "${department}",
"firstName": "${firstName}",
"employeeNumber": "${employeeNumber}",
"jobTitle": "${jobTitle}",
"lastName": "${lastName}",
"location": "${location}",
"managerDisplayName": "${managerDisplayName}",
"managerLogin": "${managerLogin}",
"organizationName": "${organizationName}",
"status": "${status}"
}
]
},
"configs": {
"tableName": "usr",
"uniqueKey": "UserLogin"
}
}

Entitlement Schema

The Entitlement Schema configuration of the JDBC connector Splice is as follows:

{
"id": "urn:sath:params:scim:schemas:core:1.0:Entitlement",
"name": "Entitlement",
"description": "Entitlements",
"meta": {
"resourceType": "Schema",
"location": "/v2/Schemas/urn:sath:params:scim:schemas:core:1.0:Entitlement"
},
"attributes": [
{
"name": "id",
"type": "string"
},
{
"name": "name",
"type": "string",
"required": true
},
{
"name": "description",
"type": "string",
"required": true
},
{
"name": "displayName",
"type": "string",
"required": true
},
{
"name": "targetSystem",
"type": "string",
"required": true
}
],
"schemas": "urn:sath:params:scim:schemas:core:1.0:Entitlement",
"matching-attributes": [
"id"
],
"attribute-map": {
"id": {
"attribute": "id",
"type": "string"
},
"name": {
"attribute": "name",
"type": "string"
},
"displayName": {
"attribute": "displayName",
"type": "string"
},
"descriptions": {
"attribute": "descriptions",
"type": "string"
},
"targetSystemDisplayName": {
"attribute": "targetSystemDisplayName",
"type": "string"
}
},
"scripts": {
"insert": [
"INSERT INTO permission (id,name,displayName,descriptions,targetSystemDisplayName) VALUES(''${id}'',''${name}'',''${displayName}'',''${descriptions}'',''${targetSystemDisplayName}'') "
],
"update": [
"UPDATE permission set name=''${name}'',descriptions=''${descriptions}''"
],
"delete": [
"DELETE FROM permission"
],
"select": [
"SELECT * FROM permission"
],
"mappingToExistingToResponse": [
{
"id": "${id}",
"displayName": "${displayName}",
"name": "${name}",
"descriptions": "${descriptions}",
"targetSystemDisplayName": "${targetSystemDisplayName}"
}
]
},
"configs": {
"tableName": "permission",
"uniqueKey": "id"
}
}

Deploying the JDBC ORACLE Connector

Deploying using IDHub Connector Onboarding Wizard

tip

The documentation for deploying the connector using the IDHub Connector Onboarding Wizard is coming soon

Deploy on your own

Deploy on Cloud

tip

The documentation for deploying the connector on your own Cloud Platform is coming soon

Deploy on your Server

Prerequisite

A running IDHub instance and its FQDN/URL
An Ubuntu/Debian Linux VM with Docker engine, Compose plugin, cURL, jq and unzip.
A service URL (with valid certificates) which points to port 7002 on the above VM.

Step 1 — Creating Connector OAuth Client (ID)

Login to IDHub with your admin credentials > Admin Settings > Sign On > Keycloak Administration
- Go to Clients menu.
- Create client and set Client ID to ‘JDBC-connector’ and Save.
- Set Valid Redirect URIs to '*' and Save.

Step 2 — Creating Service Account User in Keycloak in Tenant Realm

In your realm, do the following.
- Go to Users menu.
- Add user.
- Set Username to ‘JDBC-service-account’ and Save.
- Go to its Credentials menu and set a password. (Note: For this tutorial, we have used ‘sapassword1’. We highly recommend using a different one for your service account).

tip

Keycloak configuration is now complete!

Step 3 — Get your tokens

Open Terminal and Install cURL and jq (needs to be installed in the system if not already done).

sudo apt install -y curl jq

Run the following command in your terminal with custom variables. It will generate the KEYCLOAK_ACCESS_TOKEN and KEYCLOAK_REFRESH_TOKEN.

curl --location --request POST 'https://<IDHUB_FQDN>/auth/realms/<YOUR_REALM>/protocol/openid-connect/token' \
--header 'Content-Type: application/x-www-form-urlencoded' \
--data-urlencode 'client_id=<CLIENT_ID>' \
--data-urlencode 'username=<SERVICE_ACCOUNT_USER>' \
--data-urlencode 'password=<SERVICE_ACCOUNT_PASSWORD>' \
--data-urlencode 'scope=offline_access' \
--data-urlencode 'grant_type=password' \
--data-urlencode 'request_token_type=urn:ietf:params:oauth:token-type:access_token' \
| jq

note

<IDHUB_FQDN> is your IDHub FQDN/URL. e.g. example.sath.com
<YOUR_REALM> is your tenant name. e.g. alpha
For <CLIENT_ID>, see Step 1.
For <SERVICE_ACCOUNT_USER> and <SERVICE_ACCOUNT_PASSWORD>, see Step 2.

Example:

curl --location --request POST 'https://example.iamsath.com/auth/realms/alpha/protocol/openid-connect/token' \
--header 'Content-Type: application/x-www-form-urlencoded' \
--data-urlencode 'client_id=JDBC-connector' \
--data-urlencode 'username=JDBC-service-account' \
--data-urlencode 'password=sapassword1' \
--data-urlencode 'scope=offline_access' \
--data-urlencode 'grant_type=password' \
--data-urlencode 'request_token_type=urn:ietf:params:oauth:token-type:access_token' \
| jq

Copy access_token and refresh_token and keep it somewhere safe. You will need it in the next step.

info

We now have our tokens. Let’s deploy the connector.

Step 4 — Prepare the .env file

Download the IDHub’s JDBC Connector package, extract and edit the ‘.env’ file.

wget https://storage.googleapis.com/sath-public-binaries/connectors/idhub-jdbc-oracle-connector.zip
unzip idhub-jdbc-oracle-connector.zip
cd idhub-jdbc-oracle-connector.zip
nano .env

DB_USERNAME=ORACLE_DB_USERNAME
DB_PASSWORD=ORACLE_DB_PASSWORD
DB_DRIVER_CLASS=ORACLE_DB_DRIVER_CLASS
DB_URL=ORACLE_DB_URL
CONNECTOR_DEBUG_LEVEL=DEBUG
IDHUB_HOSTNAME=example.iamsath.com
KEYCLOAK_ACCESS_TOKEN=ACCESS_TOKEN
KEYCLOAK_CLIENT_ID=Entra ID-connector
KEYCLOAK_REFRESH_TOKEN=REFRESH_TOKEN
KEYCLOAK_REALM=TENANT_NAME
SPLICE_DEBUG_LEVEL=DEBUG
PORT=CONNECTOR_PORT
BUSINESS_OWNER=BUSINESS_OWNER
IT_OWNER=IT_OWNER

info

Replace the following:

IDHUB_HOSTNAME: IDHub web hostname. From prerequisites. eg. example.sath.com
KEYCLOAK_ACCESS_TOKEN: From Step 2 output. Put it WITHOUT the quotes.
KEYCLOAK_CLIENT_ID: From Step 1. eg. Entra ID-connector
KEYCLOAK_REFRESH_TOKEN: From Step 2 output. Put it WITHOUT the quotes.
KEYCLOAK_REALM: Tenant created in IDHub. From prerequisites. eg. alpha.
PORT: From prerequisites. eg. 7002
ORACLE_DB_USERNAME: Username of the DB eg. root
ORACLE_DB_PASSWORD: Password of the DB.
ORACLE_DB_DRIVER_CLASS: This is driver class of the DB eg. com.mysql.cj.jdbc.Driver
ORACLE_DB_URL: e.g: jdbc:mysql://35.208.94.181:3306/apple?allowPublicKeyRetrieval=true&useSSL=false
BUSINESS_OWNER: Business owner user namme eg. jerome
IT_OWNER: IT owner user name. eg. jerome

Step 5 — Run the container

In the connector directory, run the following command.

docker compose up -d

JDBC Oracle

About JDBC Oracle Connector​

Connector Version​

Connector Components​

Connector Architecture​

Connector Features​

Full Reconciliation and Incremental Reconciliation​

Limited Reconciliation​

Reconciliation of Deleted User Records​

Transformation and Validation of Account Data​

Pre-requisites​

Creating an Application by using the Connector​

Onboard the Application in IDHub​

Configuring the Connector​

Basic Configuration of the Connector for Oracle​

Advanced Settings Parameters for Oracle​

Attribute Mappings for the Connector​

Connector Application Configuration​

Authentication​

JDBC ORACLE Connector Splice configuration​

Service Provider Configuration​

Resource Schema Configuration​

Connector Splice Design​

Account Schema​

Entitlement Schema​

Deploying the JDBC ORACLE Connector​

Deploying using IDHub Connector Onboarding Wizard​

Deploy on your own​

Deploy on Cloud​

Deploy on your Server​

Prerequisite​

Step 1 — Creating Connector OAuth Client (ID)​

Step 2 — Creating Service Account User in Keycloak in Tenant Realm​

Step 3 — Get your tokens​

Step 4 — Prepare the .env file​

Step 5 — Run the container​

About JDBC Oracle Connector

Connector Version

Connector Components

Connector Architecture

Connector Features

Full Reconciliation and Incremental Reconciliation

Limited Reconciliation

Reconciliation of Deleted User Records

Transformation and Validation of Account Data

Pre-requisites

Creating an Application by using the Connector

Onboard the Application in IDHub

Configuring the Connector

Basic Configuration of the Connector for Oracle

Advanced Settings Parameters for Oracle

Attribute Mappings for the Connector

Connector Application Configuration

Authentication

JDBC ORACLE Connector Splice configuration

Service Provider Configuration

Resource Schema Configuration

Connector Splice Design

Account Schema

Entitlement Schema

Deploying the JDBC ORACLE Connector

Deploying using IDHub Connector Onboarding Wizard

Deploy on your own

Deploy on Cloud

Deploy on your Server

Prerequisite

Step 1 — Creating Connector OAuth Client (ID)

Step 2 — Creating Service Account User in Keycloak in Tenant Realm

Step 3 — Get your tokens

Step 4 — Prepare the .env file

Step 5 — Run the container