Skip to main content

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.

ComponentVersion
JDBC ORACLE ConnectorVer 1.0
Target SystemSDK version 1.32.1
Connector Server11.1.2.1.0 or 12.2.1.3.0
Connector Server JDKJDK 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

  1. Create Login using the following query:
CREATE USER serviceuser IDENTIFIED BY password
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp QUOTA UNLIMITED ON users;
  1. 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

ParameterTypeDefault ValueDescription
hostStringNAHost name or IP address of the computer hosting the target system.Sample value: HOST_IP_ADDRESS
portStringNAEnter the number of the port at which the target system database is listening. Sample value: PORT_NUMBER
databaseStringNAName of the target database. Sample value: DB_NAME
jdbcDriverStringNAJDBC driver class name.Sample value for Oracle database: oracle.jdbc.driver.OracleDriverSample value for MySQL: com.mysql.jdbc.DriverSample value for MS SQL: com.microsoft.sqlserver.jdbc.SQLServerDriverSample value for DB2: com.ibm.db2.jcc.DB2Driver
jdbcUrlTemplateStringNAJDBC 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:oimSample value for Oracle database with Service Name: jdbc:oracle:thin:@mydb.com:PORT/oimSample value for MySQL: jdbc:mysql://mydb.com:PORT/mysqlSample value for MS SQL: jdbc:sqlserver://mydb.com:PORT;Database=acmedbSample value for DB2: jdbc:db2://mydb.com:PORT/mydb
userStringNAUser ID of the database user account that IDHub uses to connect to the target system.Sample value: DB_USERNAME
passwordStringNAPassword of the database user account that IDHub uses to connect to the target system.Sample value: DB_PASSWORD
tableStringNAName of the parent table or view that contains user records.Sample value: DB_TABLE_NAME
keyColumnStringNAName of the column that uniquely identifies each row in the parent table.Sample value: PRIMARY_KEY_OF_DB_PARENT_TABLE
passwordColumnStringNAName of the column in the parent table that holds the passwords of the target system records.Sample value: PASSWORD
statusColumnBooleanNAName 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
enableValueStringNAValue used on the target system that depicts that a user record is in the enabled status.Sample value: enable
disableValueStringNAValue used on the target system that depicts that a user record is in the disabled status.Sample value: disable
relationTablesStringNAA 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 NameStringNAName of the connector server IT resource.Sample value: CONNECTOR_SERVER_NAME
validConnectionQueryStringNAIf 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
changeLogColumnStringNAName 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.
customizedQueryStringNAA 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.
allNativeBooleanfalseIf 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.
dateformatStringdd/MM/yyyyAllows 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.
timestampFormatStringdd/MM/yyyy HH:mm:ss:SSSAllows the user to format how timestamp data is converted to strings. Specifying this property invalidates the nativeTimestamps and allNative properties.
nativeTimestampsBooleanfalseIf 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.
enableEmptyStringBooleanfalseSet 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
quotingStringNoneColumn 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.
jdbcDriverStringNAJDBC driver class name.Sample value for SAP HANA DB: 'jdbcDriver': ‘com.sap.db.jdbc.Driver',
jdbcUrlTemplateStringNAJDBC URL template of the target database.Sample value for SAP HANA DB: 'jdbc:sap://acmedb.com:30015',
rethrowAllSQLExceptionsBooleanfalseSet 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.

ParameterDefault ValueDescription
Connector Version-This parameter holds the version of the connector bundle class.
Pool Max Idle10Maximum number of idle objects in a pool.
Pool Max Size10Maximum number of connections that the pool can create.
Pool Max Wait150000Maximum 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 Time120000Minimum time, in milliseconds, the connector must wait before evicting an idle object.
Pool Min Idle1Minimum number of idle objects in a pool.
datasourceNAData source name for the data source naming properties.Sample value: jdbc/operationsDB
jndiPropertiesNAProperties 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"
createScriptNoneThis 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:///URLSample value: file:///home/jdoe/dbat/scripts/create_user.groovy
updateSriptNoneThis 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:///URLSample value: file:///home/jdoe/dbat/scripts/update_user.groovy
deleteScriptNoneThis 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:///URLSample value: file:///home/jdoe/dbat/scripts/delete_user.groovy
executeQueryScriptNoneSpecify 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:///URLSample value: file:///home/jdoe/dbat/scripts/recon_user.groovy
lookupScriptNoneThis 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:///URLSample value: file:///home/jdoe/dbat/scripts/lookup_field_sync.groovy
syncScriptNoneSpecify 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:///URLSample value: file:///home/jdoe/dbat/scripts/increm_recon_user.groovy
addMultiValuedAttributeScriptNoneThis 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:///URLSample value:file:///home/jdoe/dbat/scripts/add_mulval_attr.groovy
removeMultiValuedAttributeScriptNoneThis 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:///URLSample value: file:///home/jdoe/dbat/scripts/remove_mulval_attr.groovy
sapHanaDbNAThis property suggests sapHanaDb parameter support, if using for sapHanaDb only.Sample value: True

Attribute Mappings for the Connector

JDBC SchemaUser Schema in IDHubSync DirectionIsVisibleExampleType of Attribute
organizationNameorganizationNameTarget system to IDHubVisibleSath
departmentdepartmentTarget system to IDHubVisibleMarketing
displayNamedisplayNameBi-DirectionalVisibleJohn33
firstNamefirstNameTarget system to IDHubVisibleJohn
jobTitlejobTitleTarget system to IDHubVisibleManager
emailemailTarget system to IDHubVisible[email protected]Recon Key
lastNamelastNameTarget system to IDHubVisibleGarret
userLoginloginTarget system to IDHubVisibleJohn_g33Account name Field
phoneNumberphoneNumberTarget system to IDHubVisible355-5656-6666
employeeNumberemployeeNumberTarget system to IDHubVisibleJohn8678
locationlocationTarget system to IDHubVisible
managerDisplayNamemanagerDisplayNameTarget system to IDHubVisible
managerLoginmanagerLoginTarget system to IDHubVisibleJohn8678
statusstatusTarget system to IDHubVisible
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 KeyPossible Recon Key Values
email[email protected]
userLoginJohn33
managerLoginJohn8678
employeeNumberJohn8678

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