JDBC MySQL

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

About JDBC MySQL Connector

In IDHub, you can create and onboard JDBC MYSQL applications using the JDBC MYSQL connector.

Connector Version

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

Component	Version
JDBC MYSQL 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 MYSQL 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 MYSQL application as the trusted source. When user records match the reconciliation criteria, the JDBC MYSQL 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 MYSQL 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 MYSQL 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

STEP 1: The 1st setup is to install mysqlworkbench into you environment.

STEP 2: The setup in mysqlworkbench, the configurations looks like this

• Give your connection name, hostname as localhost/127.0.0.1, port as 3306
• Give the username and password you set up while installing in your env.
• Then test connection. It should be successful if all credentials are correct.

STEP 3: Next you have to create 3tables named “usr“,”usr_permission_map”,”permission”, first 2 tables will be for Account.json and the 3rd one for Entitlement.json.

STEP 4: For table 1 “usr”, following columns we need to make inside our mymysqlworkbench

phoneNumber,email,userLogin,displayName,department,firstName,employeeNumber,jobTitle,lastName,location,managerDisplayName,managerLogin,organizationName,status

For table “usr”, Unique key will be userLogin.

STEP 5: For table 1 “usr_permission_map”, following columns we need to make inside our mymysqlworkbench

entitlements,userLogin

note

userLogin for usr_permission_map and userLogin for usr will act as a foreign key so we need to make it as foreign key.

Till this your setup for Account has been completed now lets move to Entitlement.

STEP 6: For table 1 “permission”, following columns we need to make inside our mymysqlworkbench

id,name,displayName,description,targetSystemDisplayName

For table “permission”, Unique key will be id, which will be auto generated.

Till now, all the setup which is required to run is done in mysqlworkbench,

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 MYSQL application.

Basic Configuration of the Connector for MySQL

Parameter	Default Value	Description
user	NA	User ID of the mySQL database user account that IDHub uses to connect to the target system.
password	NA	Password of the database user account that IDHub uses to connect to the target system.
connectionAttributes	NA	A comma-delimited list of user-defined "key:value" pairs, in addition to standard MySQL-defined "key:value" pairs, to be passed to MySQL Server for display as connection attributes in the 'PERFORMANCE_SCHEMA' tables 'session_account_connect_attrs' and 'session_connect_attrs'. Example usage: "connectionAttributes=key1:value1,key2:value2". This functionality is available for use with MySQL Server version 5.6 or later only.
sslMode	PREFERRED	By default, network connections are SSL encrypted; this property permits secure connections to be turned off, or a different levels of security to be chosen. The following values are allowed: "DISABLED" - Establish unencrypted connections; "PREFERRED" - Establish encrypted connections if the server enabled them, otherwise fall back to unencrypted connections; "REQUIRED" - Establish secure connections if the server enabled them, fail otherwise; "VERIFY_CA" - Like "REQUIRED" but additionally verify the server TLS certificate against the configured Certificate Authority (CA) certificates; "VERIFY_IDENTITY" - Like "VERIFY_CA", but additionally verify that the server certificate matches the host to which the connection is attempted.
maxRows	-1	The maximum number of rows to return. The default "0" means return all rows.
autoReconnect	false	Should the driver try to re-establish stale and/or dead connections? If enabled the driver will throw an exception for queries issued on a stale or dead connection, which belong to the current transaction, but will attempt reconnect before the next query issued on the connection in a new transaction. The use of this feature is not recommended, because it has side effects related to session state and data consistency when applications don't handle SQLExceptions properly, and is only designed to be used when you are unable to configure your application to handle SQLExceptions resulting from dead and stale connections properly. Alternatively, as a last option, investigate setting the MySQL server variable 'wait_timeout' to a high value, rather than the default of 8 hours.
connectionTimeZone	LOCAL	Configures the connection time zone which is used by Connector/J if conversion between the JVM default and a target time zone is needed when preserving instant temporal values.

Advanced Settings Parameters for MySQL

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.

Name	Default Value	Description
authenticationPlugins	-	Comma-delimited list of classes that implement the interface 'com.mysql.cj.protocol.AuthenticationPlugin'. These plugins will be loaded at connection initialization and can be used together with their sever-side counterparts for authenticating users, unless they are also disabled in the connection property 'disabledAuthenticationPlugins'.
disabledAuthenticationPlugins	-	Comma-delimited list of authentication plugins client-side protocol names or classes implementing the interface 'com.mysql.cj.protocol.AuthenticationPlugin'. The authentication plugins listed will not be used for authenticating users and, if anyone of them is required during the authentication exchange, the connection fails. The default authentication plugin specified in the property 'defaultAuthenticationPlugin' cannot be disabled.
ociConfigFile	-	The location of the OCI configuration file as required by the OCI SDK for Java. Default value is "~/.oci/config" for Unix-like systems and "%HOMEDRIVE%%HOMEPATH%.oci\config" for Windows.
defaultAuthenticationPlugin	mysql_native_password	The default authentication plugin client-side protocol name or a fully qualified name of a class that implements the interface 'com.mysql.cj.protocol.AuthenticationPlugin'. The specified authentication plugin must be either one of the built-in authentication plugins or one of the plugins listed in the property 'authenticationPlugins'. Additionally, the default authentication plugin cannot be disabled with the property 'disabledAuthenticationPlugins'. Neither an empty nor unknown plugin name or class can be set for this property.
ociConfigProfile	DEFAULT	The profile in the OCI configuration file specified in 'ociConfigFile', from where the configuration to use in the 'authentication_oci_client' authentication plugin is to be read.
authenticationFidoCallbackHandler	-	Fully-qualified class name of a class implementing the interface 'com.mysql.cj.callback.MysqlCallbackHandler'. This class will be used by the FIDO authentication plugin to obtain the authenticator data and signature required for the FIDO authentication process.

Advanced Connection Configuration

Name	Default Value	Description
connectionLifecycleInterceptors	-	A comma-delimited list of classes that implement 'com.mysql.cj.jdbc.interceptors.ConnectionLifecycleInterceptor' that should be notified of connection lifecycle events (creation, destruction, commit, rollback, setting the current database and changing the autocommit mode) and potentially alter the execution of these commands. 'ConnectionLifecycleInterceptors' are stackable, more than one interceptor may be specified via the configuration property as a comma-delimited list, with the interceptors executed in order from left to right.
useConfigs	-	Load the comma-delimited list of configuration properties for specifying combinations of options for particular scenarios. These properties are loaded before parsing the URL or applying user-specified properties. Allowed values are "3-0-Compat", "clusterBase", "coldFusion", "fullDebug", "maxPerformance", "maxPerformance-8-0" and "solarisMaxPerformance", and they correspond to properties files shipped within the Connector/J jar file, under "com/mysql/cj/configurations".
clientInfoProvider		The name of a class that implements the 'com.mysql.cj.jdbc.ClientInfoProvider' interface in order to support JDBC-4.0's 'Connection.get/setClientInfo()' methods.
createDatabaseIfNotExist	false	Creates the database given in the URL if it doesn't yet exist. Assumes the configured user has permissions to create databases.
databaseTerm	CATALOG	MySQL uses the term "schema" as a synonym of the term "database," while Connector/J historically takes the JDBC term "catalog" as synonymous to "database". This property sets for Connector/J which of the JDBC terms "catalog" and "schema" is used in an application to refer to a database. The property takes one of the two values "CATALOG" or "SCHEMA" and uses it to determine (1) which Connection methods can be used to set/get the current database (e.g. 'setCatalog()' or 'setSchema()'?), (2) which arguments can be used within the various 'DatabaseMetaData' methods to filter results (e.g. the catalog or 'schemaPattern' argument of 'getColumns()'?), and (3) which fields in the result sets returned by 'DatabaseMetaData' methods contain the database identification information (i.e., the 'TABLE_CAT' or 'TABLE_SCHEM' field in the result set returned by 'getTables()'?).
detectCustomCollations	false	Should the driver detect custom charsets/collations installed on server? If this option set to "true" the driver gets actual charsets/collations from the server each time a connection establishes. This could slow down connection initialization significantly.
disconnectOnExpiredPasswords	true	If 'disconnectOnExpiredPasswords' is set to "false" and password is expired then server enters sandbox mode and sends 'ERR(08001, ER_MUST_CHANGE_PASSWORD)' for all commands that are not needed to set a new password until a new password is set.
interactiveClient	false	Set the 'CLIENT_INTERACTIVE' flag, which tells MySQL to timeout connections based on 'interactive_timeout' instead of 'wait_timeout'.
passwordCharacterEncoding	-	Instructs the server to use the default character set for the specified Java encoding during the authentication phase. If this property is not set, Connector/J falls back to the collation name specified in the property 'connectionCollation' or to the Java encoding specified in the property 'characterEncoding', in that order of priority. The default collation of the character set utf8mb4 is used if none of the properties is set.
propertiesTransform	-	An implementation of 'com.mysql.cj.conf.ConnectionPropertiesTransform' that the driver will use to modify connection string properties passed to the driver before attempting a connection.
rollbackOnPooledClose	true	Should the driver issue a 'rollback()' when the logical connection in a pool is closed?
useAffectedRows	false	Don't set the 'CLIENT_FOUND_ROWS' flag when connecting to the server. Note that this is not JDBC-compliant and it will break most applications that rely on "found" rows vs. "affected rows" for DML statements, but does cause correct update counts from "INSERT ... ON DUPLICATE KEY UPDATE" statements to be returned by the server.

Advance Networking Configuration

Name	Default Value	Description
socksProxyHost	-	Name or IP address of a SOCKS host to connect through.
socksProxyPort	1080	Port of the SOCKS server.
socketFactory	com.mysql.cj.protocol.StandardSocketFactory	The name of the class that the driver should use for creating socket connections to the server. This class must implement the interface 'com.mysql.cj.protocol.SocketFactory' and have a public no-args constructor.
connectTimeout	0	Timeout for socket connect (in milliseconds), with 0 being no timeout.
socketTimeout	0	Timeout, specified in milliseconds, on network socket operations. Value "0" means no timeout.
dnsSrv	false	Should the driver use the given host name to lookup for DNS SRV records and use the resulting list of hosts in a multi-host failover connection? Note that a single host name and no port must be provided when this option is enabled.
localSocketAddress	-	Hostname or IP address given to explicitly configure the interface that the driver will bind the client side of the TCP/IP connection to when connecting.
maxAllowedPacket	65535	Maximum allowed packet size to send to server. If not set, the value of system variable 'max_allowed_packet' will be used to initialize this upon connecting. This value will not take effect if set larger than the value of 'max_allowed_packet'. Also, due to an internal dependency with the property 'blobSendChunkSize', this setting has a minimum value of "8203" if 'useServerPrepStmts' is set to "true".
socksProxyRemoteDns	false	When using a SOCKS proxy, whether the DNS lookup for the database host should be performed locally or through the SOCKS proxy.
tcpKeepAlive	true	If connecting using TCP/IP, should the driver set 'SO_KEEPALIVE'?
tcpNoDelay	true	If connecting using TCP/IP, should the driver set 'SO_TCP_NODELAY', disabling the Nagle Algorithm?
tcpRcvBuf	0	If connecting using TCP/IP, should the driver set 'SO_RCV_BUF' to the given value? The default value of "0", means use the platform default value for this property.
tcpSndBuf	0	If connecting using TCP/IP, should the driver set 'SO_SND_BUF' to the given value? The default value of "0", means use the platform default value for this property.
tcpTrafficClass	0	If connecting using TCP/IP, should the driver set traffic class or type-of-service fields? See the documentation for 'java.net.Socket.setTrafficClass()' for more information.
useCompression	false	Use zlib compression when communicating with the server?
useUnbufferedInput	true	Don't use 'BufferedInputStream' for reading data from the server.

Advance Security Configuration

Name	Default Value	Description
paranoid	false	Take measures to prevent exposure sensitive information in error messages and clear data structures holding sensitive data when possible
serverRSAPublicKeyFile	-	File path to the server RSA public key file for 'sha256_password' authentication. If not specified, the public key will be retrieved from the server.
allowPublicKeyRetrieval	false	Allows special handshake round-trip to get an RSA public key directly from server.
trustCertificateKeyStoreUrl	-	URL for the trusted root certificates key store. If not specified, the property 'fallbackToSystemTrustStore' determines if system-wide trust store is used.
trustCertificateKeyStoreType	JKS	Key store type for trusted root certificates. Null or empty means use the default, which is "JKS". Standard key store types supported by the JVM are "JKS" and "PKCS12", your environment may have more available depending on what security providers are installed and available to the JVM.
trustCertificateKeyStorePassword	-	Password for the trusted root certificates key store.
fallbackToSystemTrustStore	true	Whether the absence of setting a value for 'trustCertificateKeyStoreUrl' falls back to using the system-wide default trust store or one defined through the system properties 'javax.net.ssl.trustStore*'.
clientCertificateKeyStoreUrl	-	URL for the client certificate KeyStore. If not specified, the property 'fallbackToSystemKeyStore' determines if system-wide key store is used.
clientCertificateKeyStoreType	JKS	Key store type for client certificates. Null or empty means use the default, which is "JKS". Standard key store types supported by the JVM are "JKS" and "PKCS12", your environment may have more available depending on what security providers are installed and available to the JVM.
clientCertificateKeyStorePassword	-	Password for the client certificates key store.
fallbackToSystemKeyStore	true	Whether the absence of setting a value for 'clientCertificateKeyStoreUrl' falls back to using the system-wide key store defined through the system properties 'javax.net.ssl.keyStore*'.
tlsCiphersuites	-	When establishing secure connections, overrides the cipher suites enabled for use on the underlying SSL sockets. This may be required when using external JSSE providers or to specify cipher suites compatible with both MySQL server and used JVM. Prior to version 8.0.28, this property was named 'enabledSSLCipherSuites', which remains as an alias.
tlsVersions	-	List of TLS protocols to allow when establishing secure connections. Overrides the TLS protocols enabled in the underlying SSL sockets. This can be used to restrict connections to specific TLS versions and, by doing that, avoid TLS negotiation fallback. Allowed and default values are "TLSv1.2" and "TLSv1.3". Prior to version 8.0.28, this property was named 'enabledTLSProtocols', which remains as an alias.
fipsCompliantJsse	false	Enables Connector to be compatible to JSSE operating in FIPS mode. Should be set to "true" if the JSSE is configured to operate in FIPS mode and Connector/J receives the error "FIPS mode: only SunJSSE TrustManagers may be used" when creating secure connections. If set to "true" then, when establishing secure connections, the driver operates as if the 'sslMode' was set to "VERIFY_CA" or "VERIFY_IDENTITY", i.e., all secure connections require at least server certificate validation, for which a trust store must be configured or fall back to the system-wide trust store must be enabled.
KeyManagerFactoryProvider	-	The name of the a Java Security Provider that provides a 'javax.net.ssl.KeyManagerFactory' implementation. If none is specified then the default one is used.
trustManagerFactoryProvider	-	The name of the a Java Security Provider that provides a 'javax.net.ssl.TrustManagerFactory' implementation. If none is specified then the default one is used.
keyStoreProvider	-	The name of the a Java Security Provider that provides a 'java.security.KeyStore' implementation that supports the key stores types specified with 'clientCertificateKeyStoreType' and 'trustCertificateKeyStoreType'. If none is specified then the default one is used.
sslContextProvider	-	The name of the a Java Security Provider that provides a 'javax.net.ssl.SSLContext' implementation. If none is specified then the default one is used.
allowLoadLocalInfile	false	Should the driver allow use of "LOAD DATA LOCAL INFILE ..."? Setting to "true" overrides whatever path is set in 'allowLoadLocalInfileInPath', allowing uploading files from any location.
allowLoadLocalInfileInPath	-	Enables "LOAD DATA LOCAL INFILE ..." statements, but only allows loading files from the specified path. Files within sub-directories are also allowed, but relative paths or symlinks that fall outside this path are forbidden.
allowMultiQueries	false	Allow the use of ";" to delimit multiple queries during one statement. This option does not affect the 'addBatch()' and 'executeBatch()' methods, which rely on 'rewriteBatchStatements' instead.
allowUrlInLocalInfile	false	Should the driver allow URLs in "LOAD DATA LOCAL INFILE ..." statements?

Advanced High Availability and Clustering Configuration

Name	Default Value	Description
failOverReadOnly	true	When failing over in 'autoReconnect' mode, should the connection be set to 'read-only'?
maxReconnects	3	Maximum number of reconnects to attempt if 'autoReconnect' is "true".
reconnectAtTxEnd	false	If 'autoReconnect' is set to "true", should the driver attempt reconnections at the end of every transaction?
retriesAllDown	120	When using load balancing or failover, the number of times the driver should cycle through available hosts, attempting to connect. Between cycles, the driver will pause for 250 ms if no servers are available.
initialTimeout	2	If 'autoReconnect' is enabled, the initial time to wait between re-connect attempts (in seconds, defaults to "2").
queriesBeforeRetrySource	50	When using multi-host failover, the number of queries to issue before falling back to the primary host when failed over. Whichever condition is met first, 'queriesBeforeRetrySource' or 'secondsBeforeRetrySource' will cause an attempt to be made to reconnect to the primary host. Setting both properties to "0" disables the automatic fall back to the primary host at transaction boundaries.
secondsBeforeRetrySource	30	How long, in seconds, should the driver wait when failed over, before attempting to reconnect to the primary host? Whichever condition is met first, 'queriesBeforeRetrySource' or 'secondsBeforeRetrySource' will cause an attempt to be made to reconnect to the source host. Setting both properties to "0" disables the automatic fall back to the primary host at transaction boundaries
allowReplicaDownConnections	false	By default, a replication-aware connection will fail to connect when configured replica hosts are all unavailable at initial connection. Setting this property to "true" allows to establish the initial connection. It won't prevent failures when switching to replicas i.e. by setting the replication connection to read-only state. The property 'readFromSourceWhenNoReplicas' should be used for this purpose.
allowSourceDownConnectionsProperties	false	By default, a replication-aware connection will fail to connect when configured source hosts are all unavailable at initial connection. Setting this property to "true" allows to establish the initial connection, by failing over to the replica servers, in read-only state. It won't prevent subsequent failures when switching back to the source hosts i.e. by setting the replication connection to read/write state.
ha.enableJMX	false	Enables JMX-based management of load-balanced connection groups, including live addition/removal of hosts from load-balancing pool. Enables JMX-based management of replication connection groups, including live replica promotion, addition of new replicas and removal of source or replica hosts from load-balanced source and replica connection pools.
loadBalanceHostRemovalGracePeriod	15000	Sets the grace period to wait for a host being removed from a load-balanced connection, to be released when it is currently the active host.
readFromSourceWhenNoReplicas	false	Replication-aware connections distribute load by using the source hosts when in read/write state and by using the replica hosts when in read-only state. If, when setting the connection to read-only state, none of the replica hosts are available, an 'SQLException' is thrown back. Setting this property to "true" allows to fail over to the source hosts, while setting the connection state to read-only, when no replica hosts are available at switch instant.
selfDestructOnPingMaxOperations	0	If set to a non-zero value, the driver will report close the connection and report failure when 'com.mysql.cj.jdbc.JdbcConnection.ping()' or 'java.sql.Connection.isValid(int)' is called if the connection's count of commands sent to the server exceeds this value.
selfDestructOnPingSecondsLifetime	0	If set to a non-zero value, the driver will close the connection and report failure when 'com.mysql.cj.jdbc.JdbcConnection.ping()' or 'java.sql.Connection.isValid(int)' is called if the connection's lifetime exceeds this value, specified in milliseconds.
ha.loadBalanceStrategy	random	If using a load-balanced connection to connect to SQL servers in a MySQL Cluster configuration (by using the URL prefix "jdbc:mysql:loadbalance://"), which load balancing algorithm should the driver use: (1) "random" - the driver will pick a random host for each request.
loadBalanceAutoCommitStatementRegex	-	When load-balancing is enabled for auto-commit statements (via 'loadBalanceAutoCommitStatementThreshold'), the statement counter will only increment when the SQL matches the regular expression. By default, every statement issued matches.
loadBalanceAutoCommitStatementThreshold	0	When auto-commit is enabled, the number of statements which should be executed before triggering load-balancing to rebalance. Default value of "0" causes load-balanced connections to only rebalance when exceptions are encountered, or auto-commit is disabled and transactions are explicitly committed or rolled back.
loadBalanceBlocklistTimeout	0	Time in milliseconds between checks of servers which are unavailable, by controlling how long a server lives in the global blocklist.
loadBalanceConnectionGroup	-	Logical group of load-balanced connections within a class loader, used to manage different groups independently. If not specified, live management of load-balanced connections is disabled.
loadBalanceExceptionChecker		Fully-qualified class name of custom exception checker. The class must implement 'com.mysql.cj.jdbc.ha.LoadBalanceExceptionChecker' interface, and is used to inspect 'SQLException' exceptions and determine whether they should trigger fail-over to another host in a load-balanced deployment.
loadBalancePingTimeout	0	Time in milliseconds to wait for ping responses from each of load-balanced physical connections when using a load-balanced connection.
loadBalanceSQLExceptionSubclassFailover	-	Comma-delimited list of classes/interfaces used by default load-balanced exception checker to determine whether a given 'SQLException' should trigger a failover. The comparison is done using 'Class.isInstance(SQLException)' using the 'SQLException' thrown.
loadBalanceSQLStateFailover	-	Comma-delimited list of 'SQLState' codes used by the default load-balanced exception checker to determine whether a given 'SQLException' should trigger a failover. The 'SQLState' of a given 'SQLException' is evaluated to determine whether it begins with any of the values specified in the comma-delimited list.
loadBalanceValidateConnectionOnSwapServer	false	Should the load-balanced connection explicitly check whether the connection is live when swapping to a new physical connection at commit/rollback?
pinGlobalTxToPhysicalConnection	false	When using XA connections, should the driver ensure that operations on a given XID are always routed to the same physical connection? This allows the 'XAConnection' to support "XA START ... JOIN" after "XA END" has been called.
replicationConnectionGroup	-	Logical group of replication connections within a class loader, used to manage different groups independently. If not specified, live management of replication connections is disabled.
resourceId	-	A globally unique name that identifies the resource that this data source or connection is connected to, used for 'XAResource.isSameRM()' when the driver can't determine this value based on hostnames used in the URL.
serverAffinityOrder	-	A comma separated list containing the host/port pairs that are to be used in load-balancing "serverAffinity" strategy. Only the sub-set of the hosts enumerated in the main hosts section in this URL will be used and they must be identical in case and type, i.e., can't use an IP address in one place and the corresponding host name in the other.

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 MySQL as your Trusted Source.
• You should only synchronize from IDHub to JDBC MYSQL and not the reverse if MYSQL 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 MYSQL Connector Splice configuration

In order to provision, modify, and revoke two main resources, Accounts and Entitlements, the JDBC MYSQL 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"
 ``],
 ``"documentationUri"``: ``"https://www.sath.com/idhub/documentation"``,
 ``"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 MYSQL Connector

Deploying using IDHub Connector Onboarding Wizard

Click Here to view more details about how to use IDHub's Cloud Connector Onboarding wizard to deploy the connector.

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-mysql-connector.zip
unzip idhub-jdbc-mysql-connector.zip
cd idhub-jdbc-mysql-connector.zip
nano .env

DB_USERNAME=DB_USERNAME
DB_PASSWORD=DB_PASSWORD
DB_DRIVER_CLASS=DB_DRIVER_CLASS
DB_URL=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
• DB_USERNAME: Username of the DB eg. root
• DB_PASSWORD: Password of the DB.
• DB_DRIVER_CLASS: This is driver class of the DB eg. com.mysql.cj.jdbc.Driver
• 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