Friday Apr 03, 2009

The Stupid Simple DPS MySQL Example

Rationale

    Even though I use DPS every day, I find myself looking for tips quite frequently.
Here is just a REALLY simple example of how to get started with MySQL as a data store.

There are very good, detailed examples in the documentation but none really dead simple. And that's precisely what this entry aims at.

Bird's Eye View

Below is a graph trying to depict how we map in DPS from SQL to LDAP. To be honest, SQL is a quite radically different model and therefore, even in this "stupid simple" example, there are a number of things that DPS cannot guess, namely:

  1. The data source is configured to point to a specific database (through the jdbc url)
  2. The data view is configured to represent an LDAP objectClass from an SQL table
  3. Each column of the SQL table need to be mapped to an LDAP attribute

 Here's how all this looks from a DPS configuration stand point:


The Meat

   In this example, we have a database engine containing a single database named "DEADSIMPLE". The DEADSIMPLE database has a single table "USERS" with two columns, "NAME" and "PASSWORD". The "USERS" table content is a single row as described in the above figure. This is all to make it as small and as easy as possible.

    We will here want to expose this data from the MySQL database as a proper "person" object, containing a "cn" (common name) attribute, "sn" (surname) attribute and a "userPassword" attribute in order for us to be able to authenticate as user cn=admin,dc=example,dc=com with password "password". Eventually, we want the entry to look as follows:

dn: cn=admin,dc=example,dc=com
objectclass: top
objectclass: person
userpassword: password
sn: 0
cn: admin

And here the log of my session. I'll upate this article later with more details.

$ echo password > /tmp/pwd
$ dpadm create -p 7777 -P 7778 -D cn=dpsadmin -w /tmp/pwd dps
Use 'dpadm start /path/to/sun/dsee/6.3/dps' to start the instance
$ dpadm start dps
Directory Proxy Server instance '/path/to/sun/dsee/6.3/dps' started: pid=966
$ dpconf create-jdbc-data-source -b replication -B jdbc:mysql:/// -J file:/path/to/mysql-connector-java-5.1.6-bin.jar -S com.mysql.jdbc.Driver sourceA
$ dpconf set-jdbc-data-source-prop sourceA db-user:root db-pwd-file:/tmp/pwd
The proxy server will need to be restarted in order for the changes to take effect
$ dpadm restart dps
Directory Proxy Server instance '/path/to/sun/dsee/6.3/dps' stopped
Directory Proxy Server instance '/path/to/sun/dsee/6.3/dps' started: pid=1065

$ dpconf create-jdbc-data-source-pool poolA
$ dpconf attach-jdbc-data-source poolA sourceA
$ dpconf create-jdbc-data-view viewA poolA dc=example,dc=com
$ dpconf create-jdbc-table dpsUsersTable users
$ dpconf add-jdbc-attr dpsUsersTable sn id
$ dpconf add-jdbc-attr dpsUsersTable cn name
$ dpconf add-jdbc-attr dpsUsersTable userPassword password
$ dpconf create-jdbc-object-class viewA person dpsUsersTable cn
$ldapsearch -p 7777 -D cn=admin,dc=example,dc=com -w password -b dc=example,dc=com "(objectClass=\*)"
version: 1
dn: dc=example,dc=com
objectclass: top
objectclass: extensibleObject
description: Glue entry automatically generated
dc: example

dn: cn=admin,dc=example,dc=com
objectclass: top
objectclass: person
userpassword: password
sn: 0
cn: admin


$ dpconf set-jdbc-attr-prop dpsUsersTable sn sql-syntax:INT

$ cat add.ldif
dn: cn=user,dc=example,dc=com
objectClass: person
cn: user
sn: 1
userPassword: password

$ ldapadd -p 7777 -D cn=admin,dc=example,dc=com -w password < add.ldif
adding new entry cn=user,dc=example,dc=com
ldap_add: Insufficient access
ldap_add: additional info: No aciSource setup in connection handler "default connection handler"


$ ldapmodify -p 7777 -D cn=dpsadmin -w password
dn: cn=mysql_aci,cn=virtual access controls
changetype: add
objectClass: aciSource
dpsAci: (targetAttr="\*") (version 3.0; acl "Allow everything for MySQL"; allow(all) userdn="ldap:///anyone";)
cn: mysql_aci

adding new entry cn=mysql_aci,cn=virtual access controls

$ dpconf set-connection-handler-prop "default connection handler" aci-source:mysql_aci

$ ldapadd -p 7777 -D cn=admin,dc=example,dc=com -w password < add.ldif
adding new entry cn=user,dc=example,dc=com

$ ldapsearch -p 7777 -D cn=admin,dc=example,dc=com -w password -b dc=example,dc=com "(objectClass=\*)"
version: 1
dn: dc=example,dc=com
objectclass: top
objectclass: extensibleObject
description: Glue entry automatically generated
dc: example

dn: cn=admin,dc=example,dc=com
objectclass: top
objectclass: person
userpassword: password
sn: 0
cn: admin

dn: cn=user,dc=example,dc=com
objectclass: top
objectclass: person
userpassword: password
sn: 1
cn: user

$ ldapmodify -p 7777 -D cn=admin,dc=example,dc=com -w password
dn: cn=user,dc=example,dc=com
changetype: modify
replace: userPassword
userPassword: newPassword

modifying entry cn=user,dc=example,dc=com

\^C
$ ldapsearch -p 7777 -D cn=admin,dc=example,dc=com -w password -b dc=example,dc=com "(cn=user)"version: 1
dn: cn=user,dc=example,dc=com
objectclass: top
objectclass: person
userpassword: newPassword
sn: 1
cn: user

<script type="text/javascript"> var gaJsHost = (("https:" == document.location.protocol) ? "https://ssl." : "http://www."); document.write(unescape("%3Cscript src='" + gaJsHost + "google-analytics.com/ga.js' type='text/javascript'%3E%3C/script%3E")); </script> <script type="text/javascript"> try { var pageTracker = _gat._getTracker("UA-12162483-1"); pageTracker._trackPageview(); } catch(err) {}</script>
About

Directory Services Tutorials, Utilities, Tips and Tricks

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today