Database Table Connector

The Database Table connector enables provisioning to a single table in a JDBC database.

Configuring the Database Table Connector

A sample connector configuration for the Database Table connector is provided in samples/provisioners/provisioner.openicf-contractordb.json. 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 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.

OpenICF Interfaces Implemented by the Database Table Connector

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.

Database Table Connector Configuration

The Database Table Connector has the following configurable properties.

Configuration Properties

Property Type Default Encrypted Required

quoting

String

No

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.

host

String

No

Enter the name of the host on which the database is running.

port

String

No

Enter the port number on which the database server is listening..

user

String

No

Enter the name of the mandatory Database user with permission to access the accounts table.

password

GuardedString

null

Yes

No

Enter a user account that has permission to access the accounts table.

database

String

No

Enter the name of the database on the database server that contains the table.

table

String

Yes

Enter the name of the table in the database that contains the accounts.

keyColumn

String

Yes

This mandatory column value will be used as the unique identifier for rows in the table.

passwordColumn

String

No

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.

jdbcDriver

String

oracle.jdbc.driver.OracleDriver

No

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.

jdbcUrlTemplate

String

jdbc:oracle:thin:@%h:%p:%d

No

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

enableEmptyString

boolean

false

No

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.

rethrowAllSQLExceptions

boolean

true

No

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.

nativeTimestamps

boolean

false

No

Select to retrieve Timestamp data type of the columns in java.sql.Timestamp format from the database table.

allNative

boolean

false

No

Select to retrieve all data types of columns in native format from the database table.

validConnectionQuery

String

null

No

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.

changeLogColumn

String

The change log column stores the latest change time. Providing this value the Sync capabilities are activated.

datasource

String

No

If specified, the connector will attempt to connect using only this data source, and will ignore other specified resource parameters. For example: jdbc/SampleDataSourceName

jndiProperties

String[]

null

No

Could be empty or enter the JDBC JNDI Initial context factory, context provider in a format: key = value.

suppressPassword

boolean

true

No

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.