README
/**
* DO NOT ALTER OR REMOVE COPYRIGHT NOTICES OR THIS HEADER.
*
* Copyright (c) 2014 ForgeRock AS. All rights reserved.
*
* The contents of this file are subject to the terms
* of the Common Development and Distribution License
* (the License). You may not use this file except in
* compliance with the License.
*
* You can obtain a copy of the License at
* http://forgerock.org/license/CDDLv1.0.html
* See the License for the specific language governing
* permission and limitations under the License.
*
* When distributing Covered Code, include this CDDL
* Header Notice in each file and include the License file
* at http://forgerock.org/license/CDDLv1.0.html
* If applicable, add the following below the CDDL Header,
* with the fields enclosed by brackets [] replaced by
* your own identifying information:
* "Portions Copyrighted [year] [name of copyright owner]"
*/
Sample 3 - Scripted SQL
-----------------------
This sample demonstrates an example configuration for a scripted SQL
connector, a connector that communicates with a database through configurable
SQL scripts. This example requires a fresh installation of OpenIDM.
For documentation pertaining to this example see:
http://openidm.forgerock.org/doc/install-guide/index.html#more-sample3
To try the example, follow these steps.
1. Copy the MySQL Connector/J .jar to the OpenIDM bundle/ directory.
$ cp mysql-connector-java-5.1.18-bin.jar /path/to/openidm/bundle/
3. Set up MySQL to listen on localhost:3306, connecting as root:password.
4. Import the sample data definition language file into MySQL.
$ ./bin/mysql -u root -p < /path/to/openidm/samples/sample3/data/sample_HR_DB.mysql
Enter password:
$ ./bin/mysql -u root -p
Enter password:
mysql> use HRDB;
mysql> select * from Users;
+----+--------+------------------------------------------+-----------+----------+---------------+---------------------------+--------------+---------------------+
| id | uid | password | firstname | lastname | fullname | email | organization | timestamp |
+----+--------+------------------------------------------+-----------+----------+---------------+---------------------------+--------------+---------------------+
| 1 | bob | e38ad214943daad1d64c102faec29de4afe9da3d | Bob | Fleming | Bob Fleming | Bob.Fleming@example.com | HR | 2014-04-04 07:03:12 |
| 2 | rowley | 2aa60a8ff7fcd473d321e0146afd9e26df395147 | Rowley | Birkin | Rowley Birkin | Rowley.Birkin@example.com | SALES | 2014-04-04 07:03:12 |
| 3 | louis | 1119cfd37ee247357e034a08d844eea25f6fd20f | Louis | Balfour | Louis Balfour | Louis.Balfour@example.com | SALES | 2014-04-04 07:03:12 |
| 4 | john | a1d7584daaca4738d499ad7082886b01117275d8 | John | Smith | John Smith | John.Smith@example.com | SUPPORT | 2014-04-04 07:03:12 |
| 5 | jdoe | edba955d0ea15fdef4f61726ef97e5af507430c0 | John | Doe | John Doe | John.Does@example.com | ENG | 2014-04-04 07:03:12 |
+----+--------+------------------------------------------+-----------+----------+---------------+---------------------------+--------------+---------------------+
5 rows in set (0.01 sec)
mysql> quit
Bye
* Note that these passwords are hashed, and not available to be read into OpenIDM as cleartext.
* sha1 is used to hash these passwords for compatiblity reasons; in production uses, more secure algorithms should be used.
5. Start OpenIDM with the configuration for sample 3.
$ /path/to/openidm/startup.sh -p samples/sample3
6. Run reconciliation:
$ curl -k -H "Content-type: application/json" -u "openidm-admin:openidm-admin" -X POST "https://localhost:8443/openidm/recon?_action=recon&mapping=systemHrdb_managedUser"
7. Retrieve the list of users from OpenIDM's internal repository:
$ curl -k -u "openidm-admin:openidm-admin" --request GET "https://localhost:8443/openidm/managed/user/?_queryId=query-all&fields=_openidm_id,userName,sn,givenName&_prettyPrint=true"
{
"result" : [ {
"_id" : "5b8c0ea8-3f11-4588-97af-723a76c8ef40",
"_rev" : "0",
"userName" : "rowley",
"sn" : "Birkin",
"givenName" : "Rowley"
}, {
"_id" : "7de6b0f6-2930-43fc-8e66-c6dd79e37160",
"_rev" : "0",
"userName" : "john",
"sn" : "Smith",
"givenName" : "John"
}, {
"_id" : "6fc473c4-9837-43f0-af6f-6fb4aa13a666",
"_rev" : "0",
"userName" : "louis",
"sn" : "Balfour",
"givenName" : "Louis"
}, {
"_id" : "163237fd-934d-4160-878d-c59f32a3eec9",
"_rev" : "0",
"userName" : "jdoe",
"sn" : "Doe",
"givenName" : "John"
}, {
"_id" : "3bbc3706-b6e2-4013-960a-6d1beed582e1",
"_rev" : "0",
"userName" : "bob",
"sn" : "Fleming",
"givenName" : "Bob"
} ],
"resultCount" : 5,
"pagedResultsCookie" : null,
"remainingPagedResults" : -1
}
8. Query for an individual user (by userName):
$ curl -k -u "openidm-admin:openidm-admin" --request GET "https://localhost:8443/openidm/managed/user?_queryId=for-userName&uid=rowley&_prettyPrint=true"
{
"result" : [ {
"userName" : "rowley",
"mail" : "Rowley.Birkin@example.com",
"sn" : "Birkin",
"passwordAttempts" : "0",
"lastPasswordAttempt" : "Fri Apr 18 2014 11:40:38 GMT-0700 (PDT)",
"address2" : "",
"givenName" : "Rowley",
"country" : "",
"city" : "",
"lastPasswordSet" : "",
"organization" : "SALES",
"postalCode" : "",
"_id" : "5b8c0ea8-3f11-4588-97af-723a76c8ef40",
"_rev" : "1",
"accountStatus" : "active",
"telephoneNumber" : "",
"roles" : [ "openidm-authorized" ],
"postalAddress" : "",
"stateProvince" : ""
} ],
"resultCount" : 1,
"pagedResultsCookie" : null,
"remainingPagedResults" : -1
}
You can login to the OpenIDM UI (https://localhost:8443/openidmui) with any of
the users that were created. Consult the values from "sample_HR_DB.mysql" to
retrieve their cleartext passwords. Users can update their profile or their password;
the changes will be synced back to LDAP.