Skip Headers
Oracle® Identity Management Integration Guide
10g Release 2 (10.1.2)
B14085-02
  Go To Documentation Library
Home
Go To Product List
Solution Area
Go To Table Of Contents
Contents
Go To Index
Index

Previous
Previous
Next
Next
 

9.4 Example: Synchronizing a Relational Database Table to Oracle Internet Directory

This section demonstrates how to synchronize a relational database table to Oracle Internet Directory. It contains these topics:

In this example, the following relational database table containing employee data is synchronized with Oracle Internet Directory.

Table 9-1 Employee Table

EMPNO ENAME LAST_UPDATE_DATE EMAIL TELEPHONE

98357

JOHN DOE

2-JAN-2000

JOHN.DOE@ACME.COM

435-324-3455

98360

ROGER BECK

3-JUL-2001

ROGER.BECK@ACME.COM

435-324-3600

98365

JIMMY WONG

4-MAR-2001

JIMMY.WONG@ACME.COM

435-324-2390

98370

GEORGE MICHAEL

6-FEB-2002

GEORGE.MICHAEL@ACME.COM

435-324-9232


You can find a sample profile for this example in the directory $ORACLE_HOME/ldap/odi/samples. Also present there are the sample configuration and mapping files. In this example:

9.4.1 Configuring the Additional Configuration Information File

This example uses the same Additional Configuration Information file described earlier in "Preparing the Additional Configuration Information File".

9.4.2 Configuring the Mapping File

The mapping file for this example contains the following:

DomainRules
NONLDAP:dc=testdbsync,dc=com:uid=%,dc=testdbsync,dc=com
AttributeRules
ename: : : :cn: :person
ename : : : :sn: :person
uid : : : :uid: :inetOrgperson:
EMail: : : :mail: :inetOrgperson
Telephone: : : :telephonenumber: :inetOrgperson
empnum: : : :employeenumber: :inetOrgperson

This mapping file specifies the following:

  • Directory entries are created as uid=%,dc=testdbsync,dc=com. The % is a placeholder for the actual value of uid. The uid must be present in the mapping rules so that it has a value after the mapping. Otherwise the DN construction fails.

  • Both the cn and sn attributes are to have the same value as ename.

  • The uid element must have the value of the EMail prefix, which is the element of the e-mail address prior to the '@' character.

  • empnum becomes employeenumber in the directory entry.

  • telephone becomes telephone number in the directory entry.

9.4.3 Configuring the Directory Integration Profile

The directory integration profile for this example contains the attribute values as described in Table 9-2. A sample integration profile with these values populated and the corresponding mapping and configuration files are available in $ORACLE_HOME/ldap/odi/samples directory. You can create the profile by running the Directory Integration and Provisioning Assistant in the createprofile mode and specifying the file as the argument. Alternatively, you can create the profile by using the Oracle Directory Integration and Provisioning Server Administration tool.


See Also:


Table 9-2 Directory Integration Profile for TESTDBIMPORT

Attribute Value

Profile Name (orclOdipAgentName)

TESTDBIMPORT

Synchronization Mode (orclOdipSynchronizationMode)

IMPORT

Professoriats (orclOdipAgentControl)

ENABLE

Agent Execution Command (orclodipAgentExeCommand)

null

Additional Config Info (orclOdipAgentConfigInfo)

As shown in the preceding file. Needs to be uploaded

Connected Directory Account (orclOdipConDirAccessAccount)

testdbsync

Connected Directory Account Password (orclOdipConDirAccessPassword)

testdbsyncpwd

Connected Directory URL (orclOdipConDirURL)

machine.acme.com:1526:iasdb

Interface Type (orclodipDataInterfaceType)

DB

Mapping File:

To be uploaded from a file

OID Matching Filter (orclOdipOIDMatchingFilter)

employeenumber

This means that employeenumber is used to search the directory while looking for a match. If a match is found, then the directory entry is modified. Otherwise, a new entry is created. This is necessary to ensure that the orclOdipOIDMatchingFilter attribute is unique in the database also.

Once a database row is retrieved, the Oracle directory integration and provisioning server searches the directory for that employeenumber in the domain dc=testdbsync,dc=com according to the domain rules. If it gets a match, it updates that entry with the latest values of the columns in the row retrieved. If it does not get a match, it creates a new entry in the directory with all the attributes from the column values.

Last Applied Change Number (orclodipConDirLastAppliedChgNum)

20000101000000

This means that the first time the profile executes, it retrieves and synchronizes all four rows. Subsequently, it retrieves rows only when the LAST_UPDATE_DATE column in the table is updated to the time last modified.


9.4.4 Uploading the Additional Configuration Information File

Use the Directory Integration and Provisioning Assistant to upload the additional configuration information file, as follows:

$ORACLE_HOME/bin/dipassistant modifyprofile [-h hostName] [-p port] 
[-D bindDn] [-w password] -profile profName 
odip.profile.mapfile=absolute path name of configuration file

9.4.5 Uploading the Mapping File

Use the Directory Integration and Provisioning Assistant to upload the mapping file, as follows:

$ORACLE_HOME/bin/dipassistant modifyprofile [-h hostName] [-p port]
[-D bindDn] [-w password] -profile profName 
odip.profile.mapfile=absolute path name of mapping file

9.4.6 The Synchronization Process

In this example, the sequence of steps in the synchronization process is:

  1. The Oracle directory integration and provisioning server starts a new profile thread for the TESTDBIMPORT profile every time the value specified in the scheduling interval (orclOdipSchedulingInterval) attribute expires.

  2. The profile thread reads the additional configuration information to get the SQL to execute, and then runs the SQL.

  3. For every row retrieved from the database, the mapping rules are applied to the record and LDAP attributes are created.

  4. Depending on the OID Matching Filter (orclOdipOIDMatchingFilter) attribute, the directory integration and provisioning server determines whether a matching entry exists in Oracle Internet Directory or not. If it exists, then it is updated. If not, then a new entry is created. After the directory operation, the last applied change number (orclodipConDirLastAppliedChgNum) attribute is updated.

9.4.7 Observations on the Example

When a row is retrieved from the database, it is in the following form:

EmpNum: 98357
EName: JOHN DOE
UID: JOHN.DOE
EMAIL: JOHN.DOE@ACME.COM
TELEPHONE: 435-324-3455
Modified_Date: 20000102000000

After the mapping is performed on this record, the output is in the following form:

dn: uid=john.doe,dc=testdbsync,dc=com
uid: JOHN.DOE
cn: JOHN DOE 
sn: JOHN DOE 
mail: JOHN.DOE@ACME.COM
employeenumber: 98357
telephonenumber: 435-324-3455
objectclass: person
objectclass: inetorgperson

A subtree search is made in the directory with the filter employeenumber=98357 under the domain dc=testdbsync,dc=com. If the search yields an existing entry, then that entry is updated. Otherwise, a new entry is created. Because the OID Matching Filter (orclOdipOIDMatchingFilter) attribute is set to employeenumber, every database record retrieved must have that column. In this case, it is EmpNum as it maps to employeenumber.

Any other attributes in the mapping file that are not in the data retrieved by the SQL are ignored—for example, the attribute birthday.

After the profile thread processes all the change records from the SQL, it updates the directory with correct values for these attributes:

  • Last Applied Change Number (orclodipConDirLastAppliedChgNum)

  • Last Execution Time (orclOdipLastExecutionTime)

  • Last Successful Execution Time (orclOdipLastSuccessfulExecutionTime)