Sun Java System Directory Server Enterprise Edition 6.3 Administration Guide

Enable LDAP Clients to Access the Payroll Data in an SQL Database's payroll department stores salary data in an SQL database. The database has two tables, and employee table and a salary table. 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 23–7 JDBC Dataview Providing Access to an SQL Database

Figure shows JDBC data view providing access to an SQL

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:

ProcedureCreate a JDBC Data View For's Payroll Database

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

    $ dpconf create-jdbc-data-source -b payrollsqldb \
      -B jdbc:payrollsqldb:payrollsql://localhost/ \
      -J file://payrollsqldb.jar \
      -S org.payrollsqldb.jdbcDriver payroll-src 
  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-file:password-file-location/myPasswordFile
  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 \
     $ 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 \
  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.

    $ dpconf 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 \
  12. Create a super class on the JDBC object class.

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