JDBC Data Views

A JDBC data view enables you to make a relational database accessible to LDAP client applications. The following configuration objects are required to set up a JDBC data view:

• JDBC data source. Defined for each relational database. Currently, only one JDBC data source is supported per JDBC data view.

• JDBC data source pool. Defined for each JDBC data source.

• JDBC data view. Aggregates JDBC object classes into a single data view accessible by LDAP client applications.

• JDBC object class. Maps one or more JDBC tables to an LDAP object class.

• JDBC table. Defined for each relational database table.

• JDBC attribute. Defines an LDAP attribute from a specified column in a JDBC table.

The following diagram shows how an LDAP client application is able to view an Oracle database in the format of an LDAP DIT, through the configuration of the JDBC objects described previously. These objects are discussed in more detail in the following sections.

An LDAP client application can also bind to a JDBC data view, or to a join data view that includes a JDBC data view. In this case Directory Proxy Server obtains the password from the JDBC database to do the password check. The password can be obtained in clear, SHA, or SSHA.

JDBC Data Sources and Data Source Pools

A JDBC data source is defined for each relational database. The properties of a JDBC data source include the name and location of the relational database, and the user name and password required to access the database. For a complete list of the properties that can be set for a JDBC data source, run the following command:

$ dpconf get-jdbc-data-source-prop -h myHost -p 2389 -d "cn=Proxy Manager"\
 jdbc-data-source-name

Currently, only one JDBC data source is supported for each JDBC data view. In other words, you cannot load balance across JDBC data sources.

Like LDAP data sources, JDBC data sources are organized into data source pools. The properties of a JDBC data source pool are similar to those of an LDAP data source pool. For more information about LDAP data source pools see LDAP Data Source Pools.

JDBC Object Classes

A JDBC object class maps an LDAP object class to one or more relational database tables. A JDBC object class works in a similar way to a join data view (see Join Data Views). Just as a join data view has primary and secondary source data views, a JDBC object class can obtain its information from more than one table. One table must be defined as the primary table, and additional tables, if they exist, are defined as secondary tables. The primary table controls the list of entries and additional information on these entries is extracted from the secondary tables.

When you define a JDBC object class, you must specify the following operands:

• The name of the JDBC data view to which this object class is attached.

• The name of the JDBC object class.

• The primary JDBC table from which the object class will obtain its list of entries.

• A DN pattern that controls how DNs are constructed in the data view.

• Optionally, one or more secondary JDBC tables.

JDBC Tables

A JDBC table must be created for each relational database table that will be used in the JDBC data view. When you create a JDBC table you specify the name of the table in the relational database, and the name you want to assign to this table in the JDBC data view.

The following properties apply to JDBC tables:

• SQL table. (sql-table) Specifies the name of the relational database table.

  This value must be specified when you create the JDBC table but can be changed if the SQL table name changes.

• Single row table. (is-single-row-table) Specifies that an LDAP entry has only one matching row in the relational database table.

  Generally, performance is improved if this property is set to true because there is no ordering in the SQL request.

• Shared entries. (contains-shared-entries) This property determines what should be done if a row in a secondary table is used by more than one entry in the primary table.

  Imagine for example, that the primary table contains a list of user details and the secondary table contains department numbers. A single department number in the secondary table might apply to more than one user in the primary table. If a user is deleted, you do not necessarily want that user's department number to be deleted from the secondary table.

  The contains-shared-entries property is set on secondary tables only. If this property is set to TRUE, deleting an LDAP entry will result in deletion of the user in the primary table but not in the deletion of the corresponding row in the secondary table.

• Filter join rule. (filter-join-rule) A filter join rule defines the relationship between primary and secondary tables.

  A filter join rule is mandatory on secondary tables, and indicates how an entry should be retrieved from the secondary table based on something in the primary table.

  Only one filter join rule can be configured on each secondary table. A filter join rule takes the form of a filter that is used to construct an LDAP attribute.

  For example, the following command creates a filter join on the secondary phone table. This rule stipulates that an entry be retrieved from the phone table if the user_id field in that table matches the id field in the employee table.

  $ dpconf set-jdbc-table-prop -h myHost -p 2389 -d "cn=Proxy Manager" \
   phone filter-join-rule:user_id=\${employee.id}

JDBC Attributes

JDBC attributes map LDAP attributes to entries in relational database tables. The definition of a JDBC attribute includes the name of the LDAP attribute, and the table and column in which the corresponding information is located.

For example, the following command maps the employeeNumber attribute to the ID field of the EMPLOYEE table.

$ dpconf add-jdbc-attr -h myHost -p 2389 -d "cn=Proxy Manager" \
 EMPLOYEE employeeNumber ID

The following properties apply to JDBC attributes:

• LDAP syntax. (ldap-syntax) This property defines the syntax used to construct the LDAP attribute from an entry in the relational database table.

  Changes to JDBC attribute syntax require a server restart before they are taken into account.

• SQL column. (sql-column) The column in the relational database table from which the LDAP attribute is obtained.

• SQL syntax. (sql-syntax) This property defines the syntax used to construct an entry in the relational database table from an LDAP entry.

Case Sensitivity in JDBC Data Views

In some cases, the LDAP attribute might be case insensitive, while the corresponding column in the relational database is case sensitive. Directory Proxy Server handles this by adding an UPPER keyword to equality and substring indexes. This can have serious performance implications. If the relational database requires case-sensitivity, you should therefore create specific indexes on the upper case values.