Sun Java System Directory Server Enterprise Edition 6.2 Administration Guide

Joining an LDAP Directory and a MySQL Database

The procedures in this section describe a sample virtual configuration that joins an LDAP directory and a MySQL database. The LDAP directory is the primary data source, that contains most of the user information. The mySQL database contains additional information about the users. The resulting configuration is illustrated in the following figure.

Figure 23–1 Sample Virtual Configuration

Figure shows join data view comprising LDAP data view
and JDBC data view

You can use the sample data provided in install-path/ds6/ldif/Example.ldif to duplicate this example, or you can substitute the sample data with your own data.

This configuration can be broken into three sections:

For simplicity, all the commands in this section assume that the Directory Proxy Server is running on the local host in /local/dps. The commands also assume that the following environment variables have been set:

DIR_PROXY_PORT

1389

LDAP_ADMIN_PWF

pwd.txt, a file containing the administrator password.

DIRSERV_PORT

4389

LDAP_ADMIN_USER

cn=Directory Manager

Configuring and Testing the LDAP Data View

ProcedureTo Configure the LDAP Data View

Before You Begin

The tasks in this section assume the following information:

  1. Create an LDAP data source named myds1 for the Directory Server instance.


    % dpconf create-ldap-data-source myds1 host1:4389
  2. Enable the data source, and allow write operations to the data source.


    % dpconf set-ldap-data-source-prop myds1 is-enabled:true is-read-only:false
  3. Create an LDAP data source pool named myds1-pool.


    % dpconf create-ldap-data-source-pool myds1-pool
  4. Attach the LDAP data source to the LDAP data source pool.


    % dpconf attach-ldap-data-source myds1-pool myds1
  5. Specify that the data source should receive 100% of the bind, add, search, and modify operations from that data source pool.


    % dpconf set-attached-ldap-data-source-prop myds1-pool myds1 add-weight:100 \
     bind-weight:100 modify-weight:100 search-weight:100
  6. Create an LDAP data view for the data source pool, named myds1–view, with a base DN of dc=example,dc=com.


    % dpconf create-ldap-data-view myds1-view myds1-pool dc=example,dc=com

ProcedureTo Test the LDAP Data View

  1. As a user under dc=example,dc=com, search all entries in the LDAP data source to verify that you can read from the data view.


    % ldapsearch -p 1389 -D "uid=kvaughan,ou=people,dc=example,dc=com" -w bribery \
     -b dc=example,dc=com "objectclass=*"

    Note –

    You must use the credentials of a user under dc=example,dc=com. If you want to use cn=Directory Manager, you must define a data view to handle that DN.


  2. As a user under dc=example,dc=com, modify the userPassword attribute to verify that you can write to the data view.


    % ldapmodify -p 1389 -D "uid=kvaughan,ou=people,dc=example,dc=com" -w bribery
    dn: uid=kvaughan,ou=people,dc=example,dc=com
    changetype: modify
    replace: userPassword
    userPassword: myNewPassword

    Note –

    A default ACI in Directory Server allows users to modify their own passwords.


Configuring and Testing the JDBC Data View

The following tasks assume that a mySQL database is installed, running and populated with data, and that the mySQL database has the following characteristics:

The following table describes the tables in the database, and their composite fields. You need this information to set up the JDBC data view.

mySQL Table 

Fields 

EMPLOYEE

ID, SURNAME,PASSWORD, TITLE, COUNTRY_ID

COUNTRY

ID, NAME

PHONE

USER_ID, NUMBER

ProcedureTo Configure the JDBC Data View

  1. Create a JDBC data source named mysql1 for the SQL database.


    % dpconf create-jdbc-data-source -b sample_sql -B jdbc:mysql://host2.example.com:3306 \
     -J file:/net/host2.example/local/mysql/lib/jdbc.jar -S com.mysql.jdbc.Driver mysql1
  2. Specify the user name and password file for the SQL database.


    % dpconf set-jdbc-data-source-prop mysql1 db-pwd-file:sqlpwd.txt db-user:root
  3. Restart the proxy server.


    % dpadm restart /local/dps
  4. Enable the data source, and allow write operations to the data source.


    % dpconf set-jdbc-data-source-prop mysql1 is-enabled:true is-read-only:false
  5. Create a JDBC data source pool named mysql1–pool.


    % dpconf create-jdbc-data-source-pool mysql1-pool
  6. Attach the JDBC data source to the data source pool.


    % dpconf attach-jdbc-data-source mysql1-pool mysql1
  7. Create a JDBC data view for the data source pool, named myjdbc1–view, with a base DN of o=sql.


    % dpconf create-jdbc-data-view mysql1-view mysql1-pool o=sql
  8. Create a JDBC table for each table in the MySQL database.


    % dpconf create-jdbc-table employee1 EMPLOYEE
    % dpconf create-jdbc-table country1 COUNTRY
    % dpconf create-jdbc-table phone1 PHONE

    The name of the table in the SQL database is case sensitive. Make sure that you use the same case that is used in the SQL database.

  9. Create a JDBC attribute for each column in each table.

    Creating a JDBC attribute maps the MySQL column to an LDAP attribute.


    % dpconf add-jdbc-attr employee1 uid ID
    % dpconf add-jdbc-attr employee1 sn SURNAME
    % dpconf add-jdbc-attr employee1 userPassword PASSWORD
    % dpconf add-jdbc-attr employee1 room ROOM
    % dpconf add-jdbc-attr phone1 tel NUMBER
    % dpconf add-jdbc-attr country1 country NAME

    It is not necessary to create JDBC attributes for the phone1 user_id and country1 id columns, because these columns are used only in the context of the MySQL database. They will not have a corresponding LDAP attribute.

  10. Create a JDBC object class for the LDAP person object class.

    In this step, the employee1 table is identified as the primary table, and the country1 and phone1 tables are identified as secondary tables. The creation of a JDBC object class also requires a DN. In this example, the DN is constructed from the uid attribute and the base DN of the data view.


    % dpconf create-jdbc-object-class mysql1-view person employee1 country1 phone1 uid
  11. Define the join rules between the primary table and the secondary tables.

    A join rule is defined on the secondary table and determines how data from that table is linked to data from the primary table.


    % dpconf set-jdbc-table-prop country1 filter-join-rule:'ID=${EMPLOYEE.COUNTRY_ID}'
    % dpconf set-jdbc-table-prop phone1 filter-join-rule:'USER_ID=${EMPLOYEE.ID}'
  12. Specify the super class for the JDBC object class.

    The super class indicates the LDAP object class from which the JDBC object class inherits attributes.


    % dpconf set-jdbc-object-class-prop mysql1-view person super-class:top

ProcedureTo Create the Required ACIs

Before you can test the JDBC data view, you must enable write access to the data view by configuring ACIs. By default, write access to non-LDAP data views is denied. For the purposes of this example, it is sufficient to add one global ACI that allows users to modify their own passwords.

  1. As the Proxy Manager, add a pool of ACIs to the JDBC data source and add a global ACI that allows users to modify their own entries.


    % ldapmodify -p 1389 -D "cn=proxy manager" -w password
    dn: cn=mysql1,cn=virtual access controls
    changetype: add
    objectclass: acisource
    dpsaci: (targetattr="*") (target = "ldap:///o=sql") \
     (version 3.0; acl "enable all access for all users "; allow(all) \
    userdn="ldap:///uid=kvaughan,o=sql";)
    cn: mysql1
  2. Create a connection handler to handle connections to the o=sql domain.


    % dpconf create-connection-handler mysql1-handler
  3. Enable the connection handler and configure it to handle all binds from users in the o=sql domain.


    % dpconf set-connection-handler-prop mysql1-handler is-enabled:true \
     bind-dn-filters:"uid=.*,o=sql"
  4. Configure the connection handler to use the pool of ACIs added previously.


    % dpconf set-connection-handler-prop mysql1-handler aci-source:mysql1

ProcedureTo Test the JDBC Data View

  1. As a user under o=sql, search the JDBC data source to verify that you can read from the data view.


    % ldapsearch -p 1389 -D "uid=kvaughan,o=sql" -w mypwd -b o=sql "objectclass=*"

    Note –

    You must use the credentials of a user under o=sql, or an anonymous bind.


  2. As a user under o=sql, modify the userPassword attribute to verify that you can write to the data view.


    % ldapmodify -p 1389 -D "uid=kvaughan,o=sql" -w mypwd
    dn: uid=kvaughan,o=sql
    changetype: modify
    replace: userPassword
    userPassword: myNewpwd

Creating and Testing the Join Data View

ProcedureTo Create the Join Data View

  1. Create a join data view named myjoin1–view.

    Specifying the LDAP data view as the primary data view, and the JDBC data view as the secondary data view.


    % dpconf create-join-data-view myjoin1-view myds1-view mysql1-view o=join
  2. Define a join rule on the secondary data view.

    The following join rule specifies that the uid attribute of entries from the secondary data view should match the uid attribute of entries from the primary data view.


    % dpconf set-jdbc-data-view-prop mysql1-view filter-join-rule:uid='${myds1-view.uid}'
  3. If the filter join rule is set on the join data view, you need to set a virtual transformation rule on the secondary data view to be able to add an entry on the join data view.


    dpconf add-virtual-transformation secondary-view-name \
    write add-attr-value dn uid=\${uid}

    Note –

    Without setting this rule, addition of entries to join data view would not be possible.


  4. Define the set of attributes that can be read from and written to the primary data view through a join data view.


    % dpconf set-ldap-data-view-prop myds1-view viewable-attr:dn viewable-attr:cn \
     viewable-attr:sn viewable-attr:givenName viewable-attr:objectClass viewable-attr:ou \
     viewable-attr:l viewable-attr:uid viewable-attr:mail viewable-attr:telephoneNumber \
     viewable-attr:facsimileTelephoneNumber viewable-attr:roomNumber viewable-attr:userPassword
    % dpconf set-ldap-data-view-prop myds1-view writable-attr:dn writable-attr:cn \
     writable-attr:sn writable-attr:givenName writable-attr:objectClass writable-attr:ou \
     writable-attr:l writable-attr:uid writable-attr:mail writable-attr:telephoneNumber \
     writable-attr:facsimileTelephoneNumber writable-attr:roomNumber writable-attr:userPassword

    These definitions apply only in the context of the join view. By default all attributes can be read and written if you access the LDAP data view directly.

  5. Define the set of attributes that can be read from and written to the secondary data view through a join data view.


    % dpconf set-jdbc-data-view-prop mysql1-view viewable-attr:dn viewable-attr:objectclass \
     viewable-attr:sn viewable-attr:room viewable-attr:userpassword viewable-attr:jobtitle \
     viewable-attr:country viewable-attr:tel
    % dpconf set-jdbc-data-view-prop mysql1-view writable-attr:dn writable-attr:objectclass \
     writable-attr:sn writable-attr:room writable-attr:userpassword writable-attr:jobtitle \
     writable-attr:country writable-attr:tel

    These definitions apply only in the context of the join view. By default all attributes can be read and written if you access the JDBC data view directly.

ProcedureTo Create the Required ACIs

  1. As the proxy manager, add a global ACI that allows anonymous access to the join data view.


    % ldapmodify -p 1389 -D "cn=proxy manager" -w password
    dn: cn=myjoin1,cn=virtual access controls
    changetype: add
    objectclass: acisource
    dpsaci: (targetattr="*") (target = "ldap:///o=join") \
     (version 3.0; acl "anonymous_access"; allow(all) userdn="ldap:///anyone";)
    cn: myjoin1
  2. Create a connection handler to handle connections to the o=join domain.


    % dpconf create-connection-handler myjoin1-handler
  3. Enable the connection handler and configure it to handle all binds from users under o=join.


    % dpconf set-connection-handler-prop myjoin1-handler is-enabled:true \
     bind-dn-filters:"uid=.*,ou=people,o=join"
  4. Configure the connection handler to use the pool of ACIs added previously.


    % dpconf set-connection-handler-prop myjoin1-handler aci-source:myjoin1

ProcedureTo Test the Join Data View

  1. As an anonymous user, search the join data view.

    In this step, we search Kirsten Vaughan's entry to see whether data from both join views is retrieved.


    % ldapsearch -p 1389 -b o=join "uid=kvaughan"

    Note that the returned entry includes the attributes from both the LDAP data view and the JDBC data view.

  2. As a user under o=join, modify the userPassword attribute to verify that you can write to the join data view.


    % ldapmodify -p 1389 -D "uid=kvaughan,ou=people,o=join" -w myNewPassword
    dn: uid=kvaughan,ou=people,o=join
    changetype: modify
    replace: userPassword
    userPassword: myPassword