You are viewing an old version of this page. View the current version.

Compare with Current View Page History

« Previous Version 4 Next »

Introduction

The Scripted SQL connector provides great flexibility in the interaction with a database resource. A different Groovy script can be configured for each operation.
This wiki page shows all the steps needed to configure a Scripted SQL resource identified by the following connection parameters:

Host: localhost
Port: 3306
Database: HR
JDBC Driver: com.mysql.jdbc.Driver
JDBC Connection URL: jdbc:mysql://%h:%p/%d
User: root
User Password: password

In order to link a Scripted SQL resource there are pre-requisites and configuration steps to be satisfied.

Pre-Requisites

  1. Download JDBC driver (MySQL JDBC driver in this case) and include it into your JEE container classpath
  2. Install OpenICF ScriptedSQL connector bundle
  3. Implement custom Groovy SQL scripts

Install OpenICF ScriptedSQL connector bundle

Warning

A bug in the current stable version of this connector bundle makes sync() not working: a patch for this was already provided but not yet part of any standard release.
A temporary workaround is to perform full reconciliation on the related synchronization task(s).

Scripted SQL connector bundles can be added at run-time just performing the following two steps.

  1. Download Scripted SQL connector bundle JAR file from OpenICF site
  2. Copy downloaded JAR file into the connector bundles directory specified into the configuration parameter bundles.directory

Implementation of custom Groovy SQL scripts

Every action script receives a set of parameters and an attribute map. The set of parameters varies for each action.

Configuration steps

Configure Scripted SQL connector instance

Use this page as example for your configuration.

  1. Open your browser and access to the Syncope administration console (http://host:port/syncope-console).
  2. Log into the interface by providing the following credentials:
    1. Login: admin
    2. Password: password
  3. Click on the Resources tab.
  4. Click on the Connectors sub-tab.
  5. Click on Create new Connector button (configuration modal page appears).
  6. Click on tab General.
    1. Provide Display name (e.g. 'Sql Scripted Connector').
    2. Select the correct Bundle name (org.forgerock.openicf.connectors.scriptedsql.ScriptedSQLConnector).
    3. Select the correct Bundle version (1.1.0.1).
  7. Click on tab Configurations and provide connector instance configuration.
    User: root
    Password: password
    Host: localhost
    TCP Port: 3306
    Database: HR
    JDBC Driver: com.mysql.jdbc.Driver
    JDBC Connection URL: jdbc:mysql://%h:%p/%d
    clearTextPasswordToScript: true
    reloadScriptOnExecution: true
    createScriptFileName: /opt/scriptsql/groovy/CreateScript.groovy
    updateScriptFileName: /opt/scriptsql/groovy/UpdateScript.groovy
    deleteScriptFileName: /opt/scriptsql/groovy/DeleteScript.groovy
    searchScriptFileName: /opt/scriptsql/groovy/SearchScript.groovy
    syncScriptFileName: /opt/scriptsql/groovy/SyncScript.groovy
  8. Click on tab Capabilities.
  9. Check needed capabilities as shown at Connector instance configuration.
  10. Click on Save button.

Configure Scripted SQL resource

  1. Click on the Resources tab.
  2. Click on the Resources sub-tab.
  3. Click on Create new Resource button (configuration modal page appears).
  4. Click on Resource Details tab.
  5. Choose from the list the correct Connector instance name.
  6. Provide the following information
    1. Name: Sql Scripted Resource
    2. Connector: Sql Scripted Connector
  7. Click on Schema Mappings tab.
  8. Click on Add button to insert attributes mapping.
  9. Click on Save button.
Schema mapping

Internal mapping types

Internal mapping

External Attributes

Mandatory condition

Account Id

Password

Username

 

 

true

X

 

Password

 

 

true

 

X

UserSchema

surname

lastname

false

 

 

UserSchema

firstname

firstname

false

 

 

UserSchema

fullname

fullname

false

 

 

UserSchema

email

email

false

 

 

UserSchema

organization

organization

false

 

 

  • No labels