Status

Provisioning works well.
Synchronization works well.

Description

The OpenICF DatabaseTable connector can be used for any table in JDBC-supported database. The following steps describe setup for MySQL database table.

Recommended Connectors

Type	Description	Comments
OpenICF Generic DatabaseTable connector	DatabaseTable Connector

Resource Configuration

MySQL Installation

Standard MySQL installation is expected.

Example Table Definition

The database needs to be created and populated. The following example is available in samples/resources/databasetable/create-simple-idm-table.mysql.

SQL Simple Table Definition

/*!40101 SET NAMES utf8 */;
/*!40101 SET character_set_client = utf8 */;

CREATE DATABASE IF NOT EXISTS midpoint_tests CHARACTER SET utf8 COLLATE utf8_bin;

CREATE TABLE midpoint_tests.idrepo (
userId VARCHAR(16) NOT NULL,
password VARCHAR(16) NOT NULL,
firstName VARCHAR(16),
lastName VARCHAR(16),
fullName VARCHAR(32),
PRIMARY KEY (userId)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

GRANT ALL PRIVILEGES on midpoint_tests.* TO midpoint_tests@'%' IDENTIFIED BY 'secret';
GRANT ALL PRIVILEGES on midpoint_tests.* TO midpoint_tests@localhost IDENTIFIED BY 'secret';

Sample resource can be imported from samples/resources/databasetable/*.xml.

Connector Configuration

See DatabaseTable Connector documentation. (currently, no published documentation).

JDBC Driver

The connector requires appropriate JDBC driver. The driver needs to be available to the web server. It usually has to be placed on on web server classpath. E.g. this means copying the driver to $TOMCAT_HOME/lib directory if tomcat server is used and restarting the server.

Connector Configuration Example

<c:connectorConfiguration>

    <!-- Configuration specific for the DBTable connector -->
    <icfc:configurationProperties
                        xmlns:icscdbtable="http://midpoint.evolveum.com/xml/ns/public/connector/icf-1/bundle/org.forgerock.openicf.connectors.databasetable-connector/org.identityconnectors.databasetable.DatabaseTableConnector">

        <icscdbtable:port>3306</icscdbtable:port>
        <icscdbtable:quoting></icscdbtable:quoting>
        <icscdbtable:host>localhost</icscdbtable:host>
        <icscdbtable:user>midpoint_tests</icscdbtable:user>
        <icscdbtable:password>
            <clearValue>secret</clearValue>
        </icscdbtable:password>
        <icscdbtable:database>midpoint_tests</icscdbtable:database>
        <icscdbtable:table>idrepo</icscdbtable:table>
        <icscdbtable:keyColumn>userId</icscdbtable:keyColumn>
        <icscdbtable:passwordColumn>password</icscdbtable:passwordColumn>
        <icscdbtable:jdbcDriver>com.mysql.jdbc.Driver</icscdbtable:jdbcDriver>
        <icscdbtable:jdbcUrlTemplate>jdbc:mysql://%h:%p/%d</icscdbtable:jdbcUrlTemplate>
        <icscdbtable:enableEmptyString>false</icscdbtable:enableEmptyString>
        <icscdbtable:rethrowAllSQLExceptions>true</icscdbtable:rethrowAllSQLExceptions>
        <icscdbtable:nativeTimestamps>false</icscdbtable:nativeTimestamps>
        <icscdbtable:allNative>false</icscdbtable:allNative>
        <icscdbtable:changeLogColumn></icscdbtable:changeLogColumn>
        <icscdbtable:datasource></icscdbtable:datasource>
    </icfc:configurationProperties>
</c:connectorConfiguration>

Configuration with Datasource

To avoid using JDBC connection properties such as host, port, username, password and database name, you can also use datasource.

To configure datasource in Tomcat, you can use the step 1 as when configuring midPoint repository (modification of server.xml and context.xml).

After Tomcat restart, change your resource configurationProperties (jdbc/testds1 is used as an example datasource):

. . .
<c:connectorConfiguration>

    <!-- Configuration specific for the DBTable connector -->
    <icfc:configurationProperties
                        xmlns:icscdbtable="http://midpoint.evolveum.com/xml/ns/public/connector/icf-1/bundle/org.forgerock.openicf.connectors.databasetable-connector/org.identityconnectors.databasetable.DatabaseTableConnector">
        <icscdbtable:table>idrepo</icscdbtable:table>
        <icscdbtable:keyColumn>userId</icscdbtable:keyColumn>
        <icscdbtable:passwordColumn>password</icscdbtable:passwordColumn>
        <icscdbtable:jdbcDriver>com.mysql.jdbc.Driver</icscdbtable:jdbcDriver>
        <icscdbtable:jdbcUrlTemplate>jdbc:mysql://%h:%p/%d?useUnicode=true&amp;characterEncoding=utf8&amp;connectionCollation=utf8_bin</icscdbtable:jdbcUrlTemplate>
        <icscdbtable:enableEmptyString>false</icscdbtable:enableEmptyString>
        <icscdbtable:rethrowAllSQLExceptions>true</icscdbtable:rethrowAllSQLExceptions>
        <icscdbtable:nativeTimestamps>false</icscdbtable:nativeTimestamps>
        <icscdbtable:allNative>false</icscdbtable:allNative>
        <icscdbtable:changeLogColumn></icscdbtable:changeLogColumn>
        <icscdbtable:datasource>java:comp/env/jdbc/testds1</icscdbtable:datasource>
    </icfc:configurationProperties>
</c:connectorConfiguration>
. . .

Resource Sample

See resource samples in Git samples directory for DBTable connector (master).

Page tree

DatabaseTable