Enable LDAP Clients to Access the Payroll Data in an SQL Database

Example.com's payroll department stores salary data in an SQL database. The database has two tables, and employee table and a salary table. Example.com has an LDAP client application that requires access to that data. The client application requires the SQL data to look like LDAP data.

The following diagram illustrates the requirements of the client application.

Figure 24–7 JDBC Dataview Providing Access to an SQL Database

To fulfill this application requirement, a JDBC data view is created that maps columns in the SQL tables to LDAP attributes.

For simplicity, the commands used in this section assume the following information:

• A Directory Proxy Server instance runs on the local host, with the default LDAP port (389).

• The Directory Proxy Server instance is located at /local/myDPS.

• The path to the file containing the Proxy Manager password has been set as a variable, LDAP_ADMIN_PWF. For more information about setting Directory Proxy Server environment variables, see Environment Variables in Sun Java System Directory Server Enterprise Edition 6.0 Installation Guide.

• The SQL database is up and running.

• The JAVA_HOME variable has been set to the correct Java path.

• The password to the SQL database is myPassword.

Create a JDBC Data View For Example.com's Payroll Database

1. Create a JDBC data source for the payroll database.

   $ dpconf create-jdbc-data-source payroll-src myPassword

2. Configure the JDBC data source with the properties of the SQL database.

   $ dpconf set-jdbc-data-source-prop payroll-src db-user:proxy
     db-pwd:myPassword
     db-url:jdbc:payrollsqldb:payrollsql://localhost
     driver-url:file://payrollsqldb.jar
     driver-class:org.payrollsqldb.jdbcDriver
     

3. Enable the JDBC data source.

   $ dpconf set-jdbc-data-source-prop payroll-src is-enabled:true

4. Create a JDBC data source pool for the payroll database.

   $ dpconf create-jdbc-data-source-pool payroll-pool

5. Attach the payroll data source to the data source pool.

   $ dpconf attach-jdbc-data-source payroll-pool payroll-src

6. Create a JDBC data view for the payroll database, with a virtual DN of o=payroll.

   $ dpconf create-jdbc-data-view payroll-view payroll-pool o=payroll

7. Create a JDBC table for each table in the SQL database.

   $ dpconf create-jdbc-table jdbc-employee employee
   $ dpconf create-jdbc-table jdbc-salary salary

8. Add a JDBC attribute for each column in the SQL tables.

   $ dpconf add-jdbc-attr jdbc-employee eid employee_id
   $ dpconf add-jdbc-attr jdbc-employee first firstname
   $ dpconf add-jdbc-attr jdbc-employee last lastname
   $ dpconf add-jdbc-attr jdbc-employee description description
   $ dpconf add-jdbc-attr jdbc-employee spouse spousename
   $ dpconf add-jdbc-attr jdbc-salary salary salary
   $ dpconf add-jdbc-attr jdbc-salary social ssn

9. Specify which attributes can be viewed and which can be written, through the JDBC data view.

   $ dpconf set-jdbc-data-view-prop payroll-view \
    viewable-attr:eid
    viewable-attr:first
    viewable-attr:last
    viewable-attr:desc
    viewable-attr:spouse
    viewable-attr:salary
    viewable-attr:social 
    $ dpconf set-jdbc-data-view-prop payroll-view \
    writable-attr:eid
    writable-attr:first
    writable-attr:last
    writable-attr:description
    writable-attr:spouse
    writable-attr:salary
   writable-attr:social

10. Create a JDBC object class that maps to an LDAP object class.

    The following command creates an object class that maps to the LDAP person object class. The object class specifies that the employee table should be used as the primary table, and that the salary table should be used as the secondary table. The eid attribute should be used to construct the DN.

    $ dpcfg create-jdbc-object-class payroll-view \
     person jdbc-employee jdbc-salary eid

11. Create a filter join rule on the secondary table that specifies how data from the secondary table should be linked to data from the primary table.

    The following join rule specifies that data should be joined based on the employee_id attribute.

    $ dpconf set-jdbc-table-prop jdbc-salary filter-join-rule:'employee_id=\${employee.employee_id}'

12. Create a super class on the JDBC object class.

    $ set-jdbc-object-class-prop payroll-view person super-class:extensibleObject