Database Table Connector
The Database Table connector enables provisioning to a single table in a JDBC database.
A sample connector configuration for the Database Table connector is provided in samples/provisioners/provisioner.openicf-contractordb.json.
"connectorRef" : {
"connectorHostRef": "#LOCAL",
"bundleName" : "org.openidentityplatform.openicf.connectors.databasetable-connector",
"bundleVersion" : "[1.1.0.2,2)",
"connectorName" : "org.identityconnectors.databasetable.DatabaseTableConnector"
}The corresponding data definition language file is provided in samples/provisioners/provisioner.openicf-contractordb.sql.
The following excerpt shows the settings for the connector configuration properties in the sample Database Table connector:
"configurationProperties":
{
"quoting" : "",
"host" : "localhost",
"port" : "3306",
"user" : "root",
"password" : "",
"database" : "contractordb",
"table" : "people",
"keyColumn" : "UNIQUE_ID",
"passwordColumn" : "",
"jdbcDriver" : "com.mysql.jdbc.Driver",
"jdbcUrlTemplate" : "jdbc:mysql://%h:%p/%d",
"enableEmptyString" : false,
"rethrowAllSQLExceptions" : true,
"nativeTimestamps" : true,
"allNative" : false,
"validConnectionQuery" : null, "changeLogColumn" : "CHANGE_TIMESTEMP", "datasource" : "",
"jndiProperties" : null
}The Database Table Connector enables provisioning to a single table in a JDBC database. Before you set up the Database Table Connector, your JDBC database must be up and running, and the required JDBC driver must be available in the openidm/bundle directory.
The mandatory configurable properties are as follows:
database
The JDBC database that contains the table to which you are provisioning.
table
The name of the table in the JDBC database that contains the user accounts.
keyColumn
The column value that is used as the unique identifier for rows in the table.
The Database Table Connector implements the following OpenICF interfaces.
Authenticate
Provides simple authentication with two parameters, presumed to be a user name and password.
Create
Creates an object and its uid.
Delete
Deletes an object, referenced by its uid.
Resolve Username
Resolves an object by its username and returns the uid of the object.
Schema
Describes the object types, operations, and options that the connector supports.
Script on Connector
Enables an application to run a script in the context of the connector. Any script that runs on the connector has the following characteristics:
- The script runs in the same execution environment as the connector and has access to all the classes to which the connector has access.
- The script has access to a connector variable that is equivalent to an initialized instance of the connector. At a minimum, the script can access the connector configuration.
- The script has access to any script-arguments passed in by the application.
Search
Searches the target resource for all objects that match the specified object class and filter.
Sync
Polls the target resource for synchronization events, that is, native changes to objects on the target resource.
Test
Tests the connector configuration. Testing a configuration checks all elements of the environment that are referred to by the configuration are available. For example, the connector might make a physical connection to a host that is specified in the configuration to verify that it exists and that the credentials that are specified in the configuration are valid.
This operation might need to connect to a resource, and, as such, might take some time. Do not invoke this operation too often, such as before every provisioning operation. The test operation is not intended to check that the connector is alive (that is, that its physical connection to the resource has not timed out).
You can invoke the test operation before a connector configuration has been validated.
Update
Updates (modifies or replaces) objects on a target resource.
| Property | Description | Type | Default | Encrypted | Required |
|---|---|---|---|---|---|
| quoting | Select whether database column names for this resource should be quoted, and the quoting characters. By default, database column names are not quoted (None). For other selections (Single, Double, Back, or Brackets), column names will appear between single quotes, double quotes, back quotes, or brackets in the SQL generated to access the database. | String | null | No | |
| host | Enter the name of the host on which the database is running. | String | null | No | |
| port | Enter the port number on which the database server is listening. | String | null | No | |
| user | Enter the name of the mandatory Database user with permission to access the accounts table. | String | null | No | |
| password | Enter a user account that has permission to access the accounts table. | GuardedString | null | No | |
| database | Enter the name of the database on the database server that contains the table. | String | null | No | |
| table | Enter the name of the table in the database that contains the accounts. | String | null | Yes | |
| keyColumn | This mandatory column value will be used as the unique identifier for rows in the table. | String | null | Yes | |
| passwordColumn | Enter the name of the column in the table that will hold the password values. If empty, no validation is done on resources and passwords. | String | null | No | |
| jdbcDriver | Specify the JDBC Driver class name. For Oracle: oracle.jdbc.driver.OracleDriver. For MySQL: org.gjt.mm.mysql.Driver. Can be empty if datasource is provided. | String | oracle.jdbc.driver.OracleDriver | No | |
| jdbcUrlTemplate | Specify the JDBC Driver Connection URL. Oracle template is jdbc:oracle:thin:@[host]:[port(1521)]:[DB]. MySQL template is jdbc:mysql://[host]:[port(3306)]/[db], for more info, read the JDBC driver documentation. Could be empty if datasource is provided. | String | jdbc:oracle:thin:@%h:%p:%d | No | |
| enableEmptyString | Select to enable support for writing an empty string, instead of a NULL value, in character based columns defined as not-null in the table schema. This option does not influence the way strings are written for Oracle based tables. By default empty strings are written as a NULL value. | boolean | false | No | |
| rethrowAllSQLExceptions | If this is not checked, SQL statements which throw SQLExceptions with a 0 ErrorCode will be have the exception caught and suppressed. Check it to have exceptions with 0 ErrorCodes rethrown. | boolean | true | No | |
| nativeTimestamps | Select to retrieve Timestamp data type of the columns in java.sql.Timestamp format from the database table. | boolean | false | No | |
| allNative | Select to retrieve all data types of columns in native format from the database table. | boolean | false | No | |
| validConnectionQuery | Specify whether the check connection alive query should be used. If empty, the default implementation checks the connection by switching autocommit on/off. It might be more efficient to test the connection by selecting 1 from a dummy table. | String | null | No | |
| changeLogColumn | The change log column stores the latest change time. Providing this value the Sync capabilities are activated. | String | null | Sync | |
| datasource | If specified, the connector will attempt to connect using only this data source, and will ignore other specified resource parameters. For example: jdbc/SampleDataSourceName | String | null | No | |
| jndiProperties | Could be empty or enter the JDBC JNDI Initial context factory, context provider in a format: key = value. | String[] | [] | No | |
| suppressPassword | If set to true then the password will not be returned. Never. Even though it is explicitly requested. If set to false then the password will be returned if it is explicitly requested. | boolean | true | No |
