Skip Navigation Links | |
Exit Print View | |
Oracle Directory Server Enterprise Edition Administration Guide 11g Release 1 (11.1.1.5.0) |
Part I Directory Server Administration
2. Directory Server Instances and Suffixes
3. Directory Server Configuration
6. Directory Server Access Control
7. Directory Server Password Policy
8. Directory Server Backup and Restore
9. Directory Server Groups, Roles, and CoS
10. Directory Server Replication
13. Directory Server Attribute Value Uniqueness
15. Directory Server Monitoring
Part II Directory Proxy Server Administration
16. Directory Proxy Server Tools
17. Directory Proxy Server Instances
19. Directory Proxy Server Certificates
20. Directory Proxy Server Load Balancing and Client Affinity
21. Directory Proxy Server Distribution
22. Directory Proxy Server Virtualization
Creating and Configuring LDIF Data Views
To Configure an LDIF Data View
Defining Access Control on Virtual Data Views
To Define a New ACI Storage Repository
To Configure Virtual Access Controls
Defining Schema Checking on Virtual Data Views
Creating and Configuring Join Data Views
To Configure a Join Data View to Enable Referencing of a Data View by Multiple Join Data Views
To Configure the Secondary View of a Join View
Creating and Configuring Coordinator Data Views
To Create a Coordinator Data View
To Configure a Coordinator Data View
Joining an LDAP Directory and a MySQL Database
Configuring and Testing the LDAP Data View
Configuring and Testing the JDBC Data View
Creating and Testing the Join Data View
Joining Multiple Disparate Data Sources
Client Application Requirements
Aggregate Data From the HR LDAP Directory and the Administration LDIF File
Add Data From Company 22 to Example.Com's DIT by Renaming the DN
Add Company 22's Data to the HR Data
Enable LDAP Clients to Access the Payroll Data in an SQL Database
23. Virtual Data Transformations
24. Connections Between Directory Proxy Server and Back-End LDAP Servers
25. Connections Between Clients and Directory Proxy Server
26. Directory Proxy Server Client Authentication
27. Directory Proxy Server Logging
28. Directory Proxy Server Monitoring and Alerts
Part III Directory Service Control Center Administration
A JDBC data view enables you to make a relational database accessible to LDAP client applications. For information about how JDBC data views work, see JDBC Data Views in Oracle Directory Server Enterprise Edition Reference.
For information about how to create and configure JDBC data views, see the following procedures.
You cannot use DSCC to perform this task. Use the command line, as described in this procedure.
$ dpconf create-jdbc-data-source -h host -p port -b db-name -B db-url -J driver-url \ [-J driver-url]... -S driver-class 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. To access multiple JDBC data sources, you can create a data view for each data source, and join them together with a join data view.
The following properties must be set when you create a JDBC data source:
The name of the relational database, for example, payrolldb.
The URL to the database, in the form jdbc:vendor:driver://dbhost:dbport.
The db-url is not a complete JDBC database URL, because it does not contain the database name. (The database name is specified by the db-name property.)
You must finish db-url with a / for MySQL, DB2, and Derby databases and with a : for Oracle database.
The JDBC driver class, for example org.hsqldb.jdbcDriver.
The path to the JDBC driver, for example file:///path/to/hsqldb/lib/hsqldb.jar.
The driver-url property is multivalued. Hence, driver-url can support multiple JAR files for the JDBC driver to ensure connectivity to the JDBC source on different platforms.
If a 3rd party JDBC driver, which is other than the JDBC driver provided by the DB Vendor, is used to connect to the RDBMS back-end, set the db-vendor property. For more information about the db-vendor property, see db-vendor(5dpconf)
$ dpconf create-jdbc-data-source-pool -h host -p port pool-name
$ dpconf attach-jdbc-data-source -h host -p port pool-name source-name
$ dpconf create-jdbc-data-view -h host -p port view-name pool-name suffix-DN
$ dpconf list-jdbc-data-views -h host -p port
You cannot use DSCC to perform this task. Use the command line, as described in this procedure.
$ dpconf get-jdbc-data-view-prop -h host -p port view-name
The default properties of a JDBC data view are as follows:
alternate-search-base-dn : - attr-name-mappings : none base-dn : o=sql1 contains-shared-entries : - description : - distribution-algorithm : - dn-join-rule : - dn-mapping-attrs : none dn-mapping-source-base-dn : none excluded-subtrees : - filter-join-rule : - is-enabled : true is-read-only : false is-routable : true jdbc-attr-date-format : yyyy-MM-dd jdbc-attr-time-format : hh:mm:ss jdbc-attr-timestamp-format : yyyy-MM-dd hh:mm:ss jdbc-data-source-pool : pool-name lexicographic-attrs : all lexicographic-lower-bound : none lexicographic-upper-bound : none non-viewable-attr : - non-writable-attr : - numeric-attrs : all numeric-default-data-view : false numeric-lower-bound : none numeric-upper-bound : none pattern-matching-base-object-search-filter : all pattern-matching-dn-regular-expression : all pattern-matching-one-level-search-filter : all pattern-matching-subtree-search-filter : all process-bind : - replication-role : master viewable-attr : all except non-viewable-attr writable-attr : all except non-writable-attr
$ dpconf set-jdbc-data-view-prop -h host -p port view-name property:value \ [property:value ... ]
Following lists the components that constitutes date and time:
Letter Date or Time Component Examples G Era designator AD y Year 1996; 96 M Month in year July; Jul; 07 w Week in year 27 W Week in month 2 D Day in year 189 d Day in month 10 F Day of week in month 2 E Day in week Tuesday; Tue a Am/pm marker PM H Hour in day (0-23) 0 k Hour in day (1-24) 24 K Hour in am/pm (0-11) 0 h Hour in am/pm (1-12) 12 m Minute in hour 30 s Second in minute 55 S Millisecond 978 z Time zone Pacific Standard Time; PST; GMT-08:00 Z Time zone -0800
The following examples show how date and time patterns are interpreted in the U.S. locale. The given date and time are 2001-07-04 12:08:56 local time in the U.S. Pacific Time time zone.
Date and Time Pattern Result "yyyy.MM.dd G 'at' HH:mm:ss z" 2001.07.04 AD at 12:08:56 PDT "EEE, MMM d, ''yy" Wed, Jul 4, '01 "h:mm a" 12:08 PM "hh 'o''clock' a, zzzz" 12 o'clock PM, Pacific Daylight Time "K:mm a, z" 0:08 PM, PDT "yyyyy.MMMMM.dd GGG hh:mm aaa" 02001.July.04 AD 12:08 PM "EEE, d MMM yyyy HH:mm:ss Z" Wed, 4 Jul 2001 12:08:56 -0700 "yyMMddHHmmssZ" 010704120856-0700 "yyyy-MM-dd'T'HH:mm:ss.SSSZ" 2001-07-04T12:08:56.235-0700
When you configure a JDBC data view, you must also configure the following objects:
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.
% dpconf create-jdbc-table jdbc-table-name db-table
The name of the db-table is case sensitive. Make sure that you use the identical case that is used in the relational database, otherwise operations that target that table might fail.
% dpconf add-jdbc-attr table-name attr-name sql-column
Creating a JDBC attribute maps the table column to an LDAP attribute.
% dpconf set-jdbc-attr-prop table-name attr-name ldap-syntax:ces
The value of ldap-syntax is cis by default. This implies that the jdbc-attr is case insensitive. Change the value to ces if your relational database is case sensitive.
Certain relational databases, such as Oracle and DB2, are case sensitive by default. LDAP is case insensitive by default. When Directory Proxy Server detects that a column of the relational database table is case sensitive, an ldapsearch query with the corresponding attribute in the filter is translated into a SQL query using the function UPPER.
For example, the query ldapsearch -b "dc=mysuffix" "(attr=abc)" is translated into the following SQL query:
SELECT * FROM mytable WHERE (UPPER(attr)='ABC')
By default, this type of query is not indexed. Queries of this nature can therefore have a substantial performance impact.
You can alleviate the performance impact in two ways:
By setting the ldap-syntax property of the jdbc-attr to ces.
By creating an index with the function UPPER for each jdbc-attr that might be used in an LDAP filter.
Note - If your relational database is not case sensitive, use ldap-syntax with the default value, that is, cis. ldap-syntax:ces is not supported with the case insensitive databases.
% dpconf create-jdbc-object-class view-name objectclass primary-table \ [secondary-table... ] DN-pattern
Creating a JDBC object class essentially specifies an LDAP object class with which these tables will be associated. The JDBC object class also specifies the primary table and the secondary tables, if they exist.
When you create a JDBC object class, you specify a DN pattern. DN pattern describes which attributes are to be used to construct DN of the entry. For example, when you specify DN pattern as uid then the DN of the entry is constructed using attribute uid and view base of the data view. For example, uid=bjensen,ou=people,dc=example,dc=com. The DN pattern can constitute multiple attributes. In that case, attributes should be separated by , (comma). For example, if DN pattern is specified as uid,country, DN of the entry returned by the data view is uid=bjensen,country=America,ou=people,dc=example,dc=com.
When data source contains duplicate entries and the duplicate entries are not required, set perform-distinct-select to true using the following command:
% dpconf set-jdbc-object-class-prop view-name objectclass perform-distinct-select:true
All the subtree components defined in the DN pattern of JDBC object class should have a JDBC object class defined for them. For example, if there is a DN pattern uid,ou in a JDBC object class, there should be a JDBC object class definition with a DN pattern ou. This is necessary for Directory Proxy Server to construct a properly structured DIT. Otherwise, the subtree with values like ou=xxx,base-DN would not be returned in the search results.
% dpconf set-jdbc-table-prop secondary-table-name filter-join-rule:join-rule
A join rule is defined on the secondary table and determines how data from that table is linked to data from the primary table. How you define the relationships between the primary and secondary tables of an object class is important. For more information, see Defining Relationships Between JDBC Tables.
% dpconf set-jdbc-object-class-prop view-name objectclass super-class:value
The super class indicates the LDAP object class from which the JDBC object class inherits.
In the simplest case, a JDBC object class contains only a single (primary) table. There is no secondary table, and thus no need to define relationships between tables.
If the object class contains more than one table, the relationships between these tables must be clearly defined. The relationships between tables are always defined on the secondary table. The following properties of a secondary table enable you to define these relationships:
is-single-row-table specifies that an LDAP entry has only one matching row in the table.
contains-shared-entries specifies that a row in the secondary table is used by more than one row in the primary table.
filter-join-rule indicates how an entry should be retrieved from the secondary table based on something in the primary table.
The following examples illustrate how the filter join rule is defined, based on the values of the first two properties. These examples assume that the object class has one primary table and one secondary table.
Example 22-1 is-single-row-table:true and contains-shared-entries:true
These are the default values of these properties. In this case, the relationship between the primary and secondary tables is n->1, that is, n rows in the primary table reference one shared row in the secondary table.
In the relational database, a foreign key (FK) is defined in the primary table, and points to a column in the secondary table.
Take, for example, an organization in which several employees can share the same manager. Two relational database tables are defined, with the following structure:
primary table : EMPLOYEE [ID, NAME, FK_MANAGER_ID] secondary table : MANAGER [ID, NAME]
The following object class and attributes are defined:
object-class : employee attr : name (from primary EMPLOYEE.NAME) attr : manager (from secondary MANAGER.NAME)
The following filter join rule is defined in the secondary table:
ID=\${EMPLOYEE.FK_MANAGER_ID}"
In case of multiple secondary tables, you must configure filter-join-rule on each secondary table. For more information on how to configure filter-join-rule for multiple secondary tables, see the Step 12.
With this configuration, the following behavior occurs for LDAP operations:
Adding an employee entry. If the manager in the employee entry does not exist in the table, a new row is created. If the manager does exist, an existing row is used.
Replacing the value of the “manager” attribute in an entry. The value of the row MANAGER.NAME is changed.
Deleting an employee entry. The row in the secondary table is not deleted because the manager entries are shared.
Deleting the “manager” attribute from an entry. The row in the secondary table is deleted and the foreign key (EMPLOYEE.FK_MANAGER_ID) is set to NULL.
If an attribute is deleted, the value of the attribute is set to NULL. But if the attribute is defined as a NOT NULL attribute in the table definition, the deletion is not possible.
Example 22-2 is-single-row-table:true and contains-shared-entries:false
In this case, the relationship between the primary and secondary tables is 1->1 or 1<-1, that is, one row in the primary table is referenced by one row in the secondary table.
In the relational database, the foreign key (FK) might be defined in the primary table, or in the secondary table.
Take, for example, an organization in which the UID of employees is stored in one table, and the surname of employees is stored in a second table. Two relational database tables are defined, with the following structure:
primary table : UID [ID, VALUE, FK_SN_ID] secondary table : SN [ID, VALUE]
The following object class and attributes are defined:
object-class : employee attr : uid (from primary UID.VALUE) attr : sn (from secondary ID.VALUE)
The following filter join rule is defined in the secondary table:
ID=\${UID.FK_SN_ID}
You can use it in the following manner using the dpconf command:
dpconf set-jdbc-table-prop SN filter-join-rule:ID=\${UID.FK_SN_ID}
This configuration could be the other way around, with the foreign key FK_UID_ID stored in the secondary table, and pointing to UID.ID.
Example 22-3 is-single-row-table:false and contains-shared-entries:false
In this case, the relationship between the primary and secondary tables is 1->n, that is, one row in the primary table is referenced by n rows in the secondary table. This example illustrates the case of multi-valued attributes. A multi-valued attribute is represented as a set of rows in the secondary table, with one row per attribute value.
In the relational database, the foreign key is defined in the secondary table, and points to a column in the primary table.
Take, for example, an organization in which an employee can have several telephone numbers. Two relational database tables are defined, with the following structure:
primary table : EMPLOYEE [ID, NAME] secondary table : PHONE [ID, VALUE, USER_ID]
The following object class and attributes are defined:
object-class : employee attr : cn (from primary EMPLOYEE.NAME) attr : telephoneNumber (from secondary PHONE.VALUE)
The following filter join rule is defined in the secondary table:
USER_ID=\${EMPLOYEE.ID}
Example 22-4 is-single-row-table:false and contains-shared-entries:true
This case is currently unsupported in Directory Proxy Server.