JDBC

The Scripted-JDBC connector enables complex data synchronization between RAC/M Identity and relational databases using the JDBC driver.

The connector has been tested with SQL Server (2017, 2019, 2020), Azure SQL, Oracle, MySQL solutions.

Note

For Azure SQL, the only available driver is com.microsoft.sqlserver.jdbc.SQLServerDriver.

Jar

This configuration is based on the ICF connector Scripted SQL - 1.1.1.0.

Configuration

Configuration	Description	Example
Name Quoting	Specifies the quoting style used for entity names in scripts.	`"`, `'`, or none
Create Script Filename	Path to the script file used to create records in the database.	C:\Program Files\Okiok Data\RACM Identity\ICF\scripts 1.1.0.0\racm\Update.groovy
Reload Script on Execution	Specifies whether the script should be reloaded on every execution.	true or false
Authenticate Script Filename	Path to the script file used to authenticate users or sessions.
Delete Script Filename	Path to the script file used to delete records from the database.	C:\Program Files\Okiok Data\RACM Identity\ICF\scripts 1.1.0.0\racm\Update.groovy
Clear Text Password to Script	Specifies whether plaintext passwords should be passed to the script.	true or false
autoCommit	Option to enable or disable auto-commit of transactions.	true or false
Search Script Filename	Path to the script file used to search for records in the database.	C:\Program Files\Okiok Data\RACM Identity\ICF\scripts 1.1.0.0\racm\Search.groovy
Update Script Filename	Path to the script file used to update records in the database.	C:\Program Files\Okiok Data\RACM Identity\ICF\scripts 1.1.0.0\racm\Update.groovy
Scripting Language	Language used for the scripts.	Groovy
Schema Script Filename	Path to the script file used to initialize or validate the database schema.	C:\Program Files\Okiok Data\RACM Identity\ICF\scripts 1.1.0.0\racm\Schema.groovy
Test Script Filename	Path to the script file used to test connectivity or specific functionality.	C:\Program Files\Okiok Data\RACM Identity\ICF\scripts 1.1.0.0\racm\TestSecurity.groovy
Sync Script Filename	Path to the script file used to synchronize data between systems.
Host	Address or domain name of the database server.	localhost
Port	Port number used to connect to the database server.	1433
User	Username for connecting to the server. Use a dedicated account restricted to the LOGIN and LOGIN_ROLE tables, not a built-in administrator such as `sa`.	racm_connector
User Password	Password for connecting to the server.	Password123
Database	Name of the database to connect to.	racm_demo_config
JDBC Driver	Name of the Java class implementing the JDBC driver.	com.microsoft.sqlserver.jdbc.SQLServerDriver
JDBC Connection URL	URL used for the connection.	jdbc:sqlserver://ip:1433;databaseName=RACM_PROD_DATA
Enable Writing Empty String	Option to allow or disallow writing empty strings to the database.	true or false
Rethrow All SQLExceptions	Option to rethrow all captured SQL exceptions for debugging or logging.	true or false
Native Timestamps	Option to use native database types for timestamps instead of application-level conversions.	true or false
All Native	Option to enforce the exclusive use of native database types for all supported fields.	true or false
Validate Connection Query	SQL query used to validate the database connection.	SELECT 1
Datasource Path	JNDI path or direct path to the data source configuration.
Initial JNDI Properties	Specific properties required to initialize the JNDI context for the data source.

Note

For Azure SQL: The only available driver is com.microsoft.sqlserver.jdbc.SQLServerDriver.

Certificate

A certificate may be required for SSL mode.

Note

In the JDBC URL for Microsoft Azure, the encrypt parameter must be included to handle SQL connection encryption:

encrypt=false: Disables encryption, which excludes the need for a certificate. This configuration is not recommended for production environments.
encrypt=true: Enables encryption, which includes the use of a certificate. In this case, the corresponding SQL certificate must be present in the ICF (Identity Connector Framework) truststore.

Addition

Add the certificate to the ICF truststore.

Restart

When changes are made to the default ICF truststore, restarting the RAC/M service is mandatory.

Testing

You can test the Script SQL connector by clicking the TEST button. However, an implementation of the Test Script must be carried out.

Import

The schemas defined in Schema Script Filename will be available for the Source Table Column with the ModuleICFImportData primitive in the object classes under the chosen ICF connector.

_ACCOUNT_ is the default ICF variable to represent accounts defined in the schema.
_GROUP_ is the default ICF variable to represent groups defined in the schema.

Account Mapping

ICF Connector -> Source table _ACCOUNT_ -> Target table APPLICATION_ACCOUNT_IMPORT.

Target Table Column	Source Table Column	Formatting (Constant)
ACCOUNT_NAME	`_NAME_`
EIN	GIA_ID
EMAIL	EMAIL
EXT_APPLICATION_ID		RACM
EXTRA1	LANGUAGE
EXTRA2
FULL_NAME	FULL_NAME
GUID	`_GUID_`
STATUS		Active

Group Mapping

Groups are loaded using ModuleICFImportData.

ICF Connector -> Source table _GROUP_ -> Target table PROFILE_IMPORT.

RACM Column	Target System Attribute	Formatting (Constant)
GUID	`_UID_`
EXT_PROFILE_ID	`_UID_`
PROFIL_NAME	DESCRIPTION
EXT_APPLICATION_ID		RACM

Member Mapping

Members are loaded using ModuleICFImportData.

ICF Connector -> Source table _ROLE_MEMBERS_ -> Target table PROFILE_IMPORT.

RACM Column	Target System Attribute	Formatting (Constant)
ACCOUNT_NAME	LOGIN
EXT_PROFILE_ID	LOGIN_ROLE_ID
GUID	LOGIN_ROLE_ID
IDENTIFIER1	`_UID_`
EXT_APPLICATION_ID		RACM

Materialization

To enable provisioning, configuration is required within the asset:

Select an ICF Connector.
Select an account strategy to create the account name and terminate the account(s).
Define the password policies. In our example, there is no password.
Define the username policies. They will be assigned to this variable during the mapping: ${ACCOUNT_NAME}.

Next, modules must be configured to handle activations, modifications, creations, terminations, and deactivations.
The processing of these modules corresponds to materialization, which involves creating the necessary data for provisioning to be processed.

Materialization is performed in the APPLICATION_ACCOUNT_IMPORT table.
SYNC_STATUS will be set to 0 when the account is materialized and awaiting provisioning.

Note

If the information is not available in the base mapping or if a join is required to retrieve the data, an SQL module should be created to update our materialization.

Activate

Configure a ModuleModifyAccountFromAccountMng primitive:

Materialize an ACTIVATE.
Source table APPLICATION_ACCOUNT -> Target table APPLICATION_ACCOUNT_IMPORT.

Target Table Column	Source Table Column	Formatting (Constant)	Unique
ACCOUNT_NAME	ACCOUNT_NAME		X
EIN	EIN
EMAIL	EMAIL
EXT_APPLICATION_ID		RACM
EXTRA1	EXTRA1
EXTRA2		update
FULL_NAME	FULL_NAME
STATUS		Active

Modify

Configure a ModuleModifyAccountFromAccountMng primitive:

Materialize a MODIFY.
Source table APPLICATION_ACCOUNT -> Target table APPLICATION_ACCOUNT_IMPORT.

Target Table Column	Source Table Column	Formatting (Constant)	Unique
ACCOUNT_NAME	ACCOUNT_NAME		X
EIN	EIN
EMAIL	EMAIL
EXT_APPLICATION_ID		RACM
EXTRA1	EXTRA1
EXTRA2		update
FULL_NAME	FULL_NAME
STATUS		Active

Create

Configure a ModuleCreateAccountFromAccountMng primitive

Source table APPLICATION_ACCOUNT -> Target table APPLICATION_ACCOUNT_IMPORT
${ACCOUNT_NAME} will be resolved by the account creation policy.
- EIN will represent the GIA_ID in our provisioning.
- If a password policy has been created:
  - A password will be generated even if the mapping is not present in the mappings.

Target Table Column	Source Table Column	Formatting (Constant)	Unique
ACCOUNT_NAME		`${ACCOUNT_NAME}`	X
EIN	EIN
EMAIL	EMAIL
EXT_APPLICATION_ID		RACM
EXTRA1	EXTRA1
EXTRA2		create
FULL_NAME	FULL_NAME
STATUS			Active

Terminate

Configure a ModuleModifyAccountFromAccountMng primitive:

Materialize a TERMINATE.
Source table APPLICATION_ACCOUNT -> Target table APPLICATION_ACCOUNT_IMPORT.

Target Table Column	Source Table Column	Formatting (Constant)	Unique
ACCOUNT_NAME	ACCOUNT_NAME		X
EXT_APPLICATION_ID		RACM
FULL_NAME	FULL_NAME
EXTRA2		terminate
STATUS		Terminated

Deactivate

Configure a ModuleModifyAccountFromAccountMng primitive:

Materialize a DEACTIVATE.
Source table APPLICATION_ACCOUNT -> Target table APPLICATION_ACCOUNT_IMPORT.

Target Table Column	Source Table Column	Formatting (Constant)	Unique
ACCOUNT_NAME	ACCOUNT_NAME		X
EIN	EIN
EXT_APPLICATION_ID		RACM
EXTRA1	EXTRA1
EXTRA2		deactivate
FULL_NAME	FULL_NAME
STATUS		Deactivated

Provisioning

Variables available are defined within the schema:

SYNC_STATUS will be set to 1 once the materialized account is provisioned.
_ACCOUNT_ is the default account name assigned by the ICF implementation.
The target system attribute is defined in the target application schema.

Note

The action variable in the Groovy script does not always seem to be properly initialized. An ACTION_PATCH variable could be created to address the issue and pass the actions to be processed to the Groovy script.

Account Provisioning

Configure a ModuleICFAccountProvisioning primitive to send materialized accounts to the target application:

Source table APPLICATION_ACCOUNT_IMPORT -> ICF Connector --> Target system.

RACM Column	Target System Attribute	Unique
ACCOUNT_NAME	LOGIN	X
EIN	GIA_ID
EXTRA1	LANGUAGE
EXTRA2	ACTION_PATCH
FULL_NAME	FULL_NAME
PASSWORD	PASSWORD
STATUS	STATUS

Group Provisioning

Configure a ModuleICFProfileProvisioning primitive to send materialized members to the target application.

Source table PROFILE_IMPORT -> ICF Connector --> _GROUP_.

Note

Groups do not need to be materialized as only members are managed by RAC/M.

RACM Column	Target System Attribute	Unique
ACCOUNT_NAME	LOGIN	X
EXT_PROFILE_ID	LOGIN_ROLE_ID

Groovy

A Groovy file will be called by the ICF connector when it executes a specific action defined in the ICF connector configuration.
All mappings will be available in the Groovy script under the names defined in the Groovy schema.
We will be able to access these variables and execute an SQL query that will be sent to the target database.

Search Script Filename

When performing a read to retrieve information from a table, this Groovy script is executed.

groovy

import groovy.sql.Sql

import java.util.logging.Logger

Logger logger = Logger.getLogger("")
logger.info("Entering Search script")


def sql = new Sql(connection)
def result = []
switch (objectClass) {
case "__ACCOUNT__":
query = """
SELECT  login_id, login, full_name, language, email  from LOGIN 
"""
logger.info("Executing query : " + query)
    result.add(__UID__: it.USER_ID, __NAME__: it.USER_NAME, LOGIN: it.LOGIN, FULL_NAME: it.FULL_NAME,
        LANGUAGE: it.LANGUAGE, EMAIL: it.EMAIL)
    
break
case "__GROUP__":
query = """
SELECT LOGIN_ROLE_ID, DESCRIPTION,LOGIN_ROLE_ID, DESCRIPTION from LOGIN_ROLE  
"""
logger.info("Executing query : " + query)
sql.eachRow(query, { result.add(__UID__: it.ROLE_ID, __NAME__: it.ROLE_NAME, ROLE_ID: it.ROLE_ID, ROLE_NAME: it.ROLE_NAME) })
break
case "ROLE_MEMBERS":
query = """
SELECT  login_id ,login, LOGIN_ROLE_ID from LOGIN
"""
logger.info("Executing query : " + query)
sql.eachRow(query, { result.add(__UID__: it.LOGIN_ID, __NAME__: it.LOGIN, LOGIN_ROLE_ID: it.LOGIN_ROLE_ID) })
break
default:
result
}

return result

Update Script Filename

Definition of the ICF connector schema versus the database schema. The mapping will be done in each Groovy script implementation.

Note

The hashPassword function at the end of this script is left as pseudocode on purpose. Password storage is specific to each target application: implement it with the exact scheme that application uses to verify passwords, and rely on a slow, adaptive password hashing function (Argon2id, scrypt, bcrypt, or PBKDF2-HMAC-SHA-256) rather than a plain digest such as SHA-256 or SHA-512.

groovy

package racm

import org.identityconnectors.common.logging.Log
import org.identityconnectors.framework.common.objects.Uid
import groovy.sql.Sql
import java.security.SecureRandom
import org.identityconnectors.common.security.SecurityUtil
import org.apache.commons.codec.binary.Base64

def log = log as Log

def LOGIN = attributes.get('LOGIN')
def LOGIN_ROLE_ID = attributes.get('LOGIN_ROLE_ID')
def FULL_NAME = attributes.get('FULL_NAME')
def PASSWORD = attributes.get('PASSWORD')
def LANGUAGE = attributes.get('LANGUAGE')
def EMAIL = attributes.get('EMAIL')
def GIA_ID = attributes.get('GIA_ID')
def STATUS = attributes.get('STATUS')
def ACTION_PATCH = attributes.get('ACTION_PATCH')

log.info("----- Update - login - Entering {0} script -----", action)
log.info("Entering {0} LOGIN", LOGIN)
log.info("Entering {0} LOGIN_ROLE_ID", LOGIN_ROLE_ID)
log.info("Entering {0} FULL_NAME", FULL_NAME)
log.info("Entering {0} LANGUAGE", LANGUAGE)
log.info("Entering {0} EMAIL", EMAIL)
log.info("Entering {0} GIA_ID", GIA_ID)
log.info("Entering {0} STATUS", STATUS)
log.info("Entering {0} ACTION_PATCH", ACTION_PATCH)
log.info("Entering {0} object class", objectClass)

def sql = new Sql(connection)
log.info("Connected to database, script update racm login")

if (toString(LOGIN).trim().equals('')) {
    log.info("LOGIN cannot be empty")
    sql.close()
    return new Uid("")
}

switch (objectClass) {
    case "__GROUP__":
        log.info("Update login group")

        if ('ADD_ATTRIBUTE_VALUES'.equalsIgnoreCase(action)) {
            log.info("Add group member LOGIN_ROLE")

            // Always bind values as parameters: identity attributes are untrusted
            // input and may legitimately contain a quote (e.g. O'Brien).
            updated = sql.executeUpdate(
                "UPDATE LOGIN SET LOGIN_ROLE_ID = ? WHERE LOGIN = ?",
                [toString(LOGIN_ROLE_ID), toString(LOGIN)])
        } else if ('REMOVE_ATTRIBUTE_VALUES'.equalsIgnoreCase(action)) {
            log.info("Remove group member LOGIN_ROLE")

            updated = sql.executeUpdate(
                "UPDATE LOGIN SET LOGIN_ROLE_ID = 3 WHERE LOGIN = ?",
                [toString(LOGIN)])
        } else {
            throw new Exception("Script not implemented for action: " + action + " LOGIN: " + toString(LOGIN_ROLE_ID).trim())
        }
        sql.commit()
        sql.close()
        return new Uid(LOGIN_ROLE_ID[0])

    case "__ACCOUNT__":
        log.info("Modify account")

        if ('TERMINATE'.equalsIgnoreCase(action) || "terminate".equalsIgnoreCase(ACTION_PATCH)) {
            log.info("Terminating login")

            sql.executeUpdate("DELETE FROM LOGIN WHERE LOGIN = ?", [toString(LOGIN)])

        } else if ('ACTIVATE'.equalsIgnoreCase(action) || "activate".equalsIgnoreCase(ACTION_PATCH)) {
            log.info("Activate login")

            def decryptPassword = SecurityUtil.decrypt(PASSWORD[0])
            def salt = generateSalt(16) // Generates a random 128-bit (16-byte) salt
            def password = hashPassword(salt, decryptPassword)

            // Do not log this statement or its parameters: they carry the salt and
            // the password hash, which must not end up in the connector logs.
            def count = sql.executeUpdate("""
                UPDATE LOGIN SET
                    PASSWORD_SALT = ?,
                    PASSWORD = ?,
                    FULL_NAME = ?,
                    GIA_ID = ?,
                    LANGUAGE = ?,
                    EMAIL = ?
                WHERE LOGIN = ?
            """, [salt, password, toString(FULL_NAME), toString(GIA_ID),
                  toString(LANGUAGE), toString(EMAIL), toString(LOGIN)])

            if (count == 0) {
                sql.executeUpdate("""
                    INSERT INTO [dbo].[LOGIN]
                    ([LOGIN], [PASSWORD_SALT], [PASSWORD], [LOGIN_ROLE_ID], [FULL_NAME], [GIA_ID], [LANGUAGE], [EMAIL])
                    VALUES (?, ?, ?, ?, ?, ?, ?, ?)
                """, [toString(LOGIN), salt, password, 3, toString(FULL_NAME),
                      toString(GIA_ID), toString(LANGUAGE), toString(EMAIL)])
            }

        } else if ('UPDATE'.equalsIgnoreCase(action) || "update".equalsIgnoreCase(ACTION_PATCH)) {
            log.info("Update login")

            sql.executeUpdate("""
                UPDATE LOGIN SET
                    FULL_NAME = ?,
                    GIA_ID = ?,
                    LANGUAGE = ?,
                    EMAIL = ?
                WHERE LOGIN = ?
            """, [toString(FULL_NAME), toString(GIA_ID), toString(LANGUAGE),
                  toString(EMAIL), toString(LOGIN)])

        } else {
            log.info("Updated login " + action)
        }
        sql.commit()
        sql.close()
        return new Uid(LOGIN[0])

    default:
        log.info("Script not implemented for {0}", objectClass)
        break
}

/**
 * Generates a random salt of the given length (e.g. 16 bytes for 128 bits).
 */
def generateSalt(int length) {
    SecureRandom random = new SecureRandom()
    byte[] saltBytes = new byte[length]
    random.nextBytes(saltBytes)
    return Base64.encodeBase64String(saltBytes) // Base64-encoded for easy storage
}

/**
 * Hashes a password with the given salt.
 *
 * PSEUDOCODE: this example deliberately ships no hashing implementation, because
 * the algorithm must match the one the target application uses to verify LOGIN
 * passwords. Implement it with a slow, adaptive password hashing function
 * (Argon2id, scrypt, bcrypt, or PBKDF2-HMAC-SHA-256) and a work factor tuned for
 * your hardware. Never use a plain, fast digest (MD5, SHA-1, or a single
 * SHA-256/SHA-512 pass): even salted, those can be cracked at high speed on GPUs
 * if the LOGIN table is ever exfiltrated.
 */
def hashPassword(String salt, String password) {
    // Call the password hashing library bundled with the target application, e.g.:
    //
    //   byte[] hash = Argon2id(password, salt,
    //                          memory: 64 MiB, iterations: 3, parallelism: 1, length: 32 bytes)
    //   -- or --
    //   byte[] hash = PBKDF2_HMAC_SHA256(password, salt,
    //                                    iterations: 600000, length: 32 bytes)
    //
    //   return Base64.encodeBase64String(hash) // Base64-encoded for storage

    throw new UnsupportedOperationException(
        "Implement hashPassword() with the adaptive password hashing function expected by the target application")
}

Schema Script Filename

Definition of the ICF connector schema versus the database schema. The mapping will be done in each Groovy script implementation. This schema will also be used in collector interfaces.

groovy

package racm

import org.identityconnectors.framework.common.objects.AttributeInfo
import org.identityconnectors.framework.common.objects.AttributeInfoBuilder
import org.identityconnectors.framework.common.objects.ObjectClassInfo
import org.identityconnectors.framework.common.objects.ObjectClassInfoBuilder
import org.identityconnectors.common.security.GuardedString

import java.util.logging.Logger

Logger logger = Logger.getLogger("Initilazing logger")

// Declare the __ACCOUNT__ attributes
accAttrsInfo = new HashSet<AttributeInfo>()

accAttrsInfo.add(AttributeInfoBuilder.build("__UID__", String.class))
accAttrsInfo.add(AttributeInfoBuilder.build("__NAME__", String.class))
accAttrsInfo.add(AttributeInfoBuilder.build("LOGIN", String.class))
accAttrsInfo.add(AttributeInfoBuilder.build("LOGIN_ROLE_ID", String.class))
accAttrsInfo.add(AttributeInfoBuilder.build("FULL_NAME", String.class))
accAttrsInfo.add(AttributeInfoBuilder.build("LANGUAGE", String.class))
accAttrsInfo.add(AttributeInfoBuilder.build("PASSWORD", GuardedString.class))
accAttrsInfo.add(AttributeInfoBuilder.build("LANGUAGE", String.class))
accAttrsInfo.add(AttributeInfoBuilder.build("EMAIL", String.class))
accAttrsInfo.add(AttributeInfoBuilder.build("GIA_ID", String.class))
accAttrsInfo.add(AttributeInfoBuilder.build("STATUS", String.class))
accAttrsInfo.add(AttributeInfoBuilder.build("ACTION_PATCH", String.class))

// Create the __ACCOUNT__ Object class
final ObjectClassInfo ociAccount = new ObjectClassInfoBuilder().setType("__ACCOUNT__").addAllAttributeInfo(accAttrsInfo).build()
builder.defineObjectClass(ociAccount)

// Declare the __GROUP__ attributes
groupAttrsInfo = new HashSet<AttributeInfo>()

groupAttrsInfo.add(AttributeInfoBuilder.build("__UID__", String.class))
groupAttrsInfo.add(AttributeInfoBuilder.build("__NAME__", String.class))
groupAttrsInfo.add(AttributeInfoBuilder.build("LOGIN", String.class))
groupAttrsInfo.add(AttributeInfoBuilder.build("LOGIN_ROLE_ID", String.class))

// Create the __GROUP__ Object class
final ObjectClassInfo ociGroup = new ObjectClassInfoBuilder().setType("__GROUP__").addAllAttributeInfo(groupAttrsInfo).build()
builder.defineObjectClass(ociGroup)

// Declare the __ROLE_MEMBERS__ attributes
memberAttrsInfo = new HashSet<AttributeInfo>()

memberAttrsInfo.add(AttributeInfoBuilder.build("__UID__", String.class))
memberAttrsInfo.add(AttributeInfoBuilder.build("__NAME__", String.class))
memberAttrsInfo.add(AttributeInfoBuilder.build("LOGIN", String.class))
memberAttrsInfo.add(AttributeInfoBuilder.build("LOGIN_ROLE_ID", String.class))

// Create the __ROLE_MEMBERS__ Object class
final ObjectClassInfo ociMember = new ObjectClassInfoBuilder().setType("__ROLE_MEMBERS__").addAllAttributeInfo(memberAttrsInfo).build()
builder.defineObjectClass(ociMember)

JDBC ​

Jar ​

Configuration ​

Certificate ​

Addition ​

Restart ​

Testing ​

Import ​

Account Mapping ​

Group Mapping ​

Member Mapping ​

Materialization ​

Activate ​

Modify ​

Create ​

Terminate ​

Deactivate ​

Provisioning ​

Account Provisioning ​

Group Provisioning ​

Groovy ​

Search Script Filename ​

Update Script Filename ​

Schema Script Filename ​

JDBC

Jar

Configuration

Certificate

Addition

Restart

Testing

Import

Account Mapping

Group Mapping

Member Mapping

Materialization

Activate

Modify

Create

Terminate

Deactivate

Provisioning

Account Provisioning

Group Provisioning

Groovy

Search Script Filename

Update Script Filename

Schema Script Filename