Novell Home

LDAP Enabling for Oracle

Novell Cool Solutions: AppNote
By Michel Bluteau

Digg This - Slashdot This

Posted: 16 Nov 2005
 

LDAP-enabling Oracle HR Applications through Oracle Internet Directory and Integration Server

Overview

This article provides an alternative approach to the traditional use of the Identity Manager JDBC Driver for synchronizing identities in one or more Oracle tables for an application. That approach was described in other articles I submitted earler. The idea here is to OID-enable an application so the Identity Manager LDAP Driver can be used instead.

While there is still a requirement to translate relational data into LDAP (classes/attributes) format, this can happen within the Oracle realm instead of within Novell Identity Manager. Some of the advantages include improved security, and sometimes a more effective way to detect changes to existing identities or the creation and suppression of identities. One of the drawbacks is that this is mostly for replacing the Publisher Channel (from Oracle to eDirectory) and not the Subscriber Channel (from eDirectory to Oracle). Still, under some circumstances, where the Oracle application is the authoritative Data Source, this would be an approach that would surely deserve some attention.

Requirements

Oracle Internet Directory and Integration Server, both included with Oracle Application Server 10g, are required. For my example, I have used 10g release 2.

I will not cover the Identity Manager side of things with the LDAP Driver, since I have already covered that in a separate article. You can refer to this article, among many others, at:

http://www.novell.com/coolsolutions/author/783.html

Creating a Sample Oracle Application

The first step is to create a fictitious Oracle application. We are not really interested in the front end of the application, so we do not need a friendly user interface for our example. We will just create a table to contain our identities.

You can use SQLPlus or iSQLPlus to create the table. I created the table on a separate Oracle 10gr2 Database server, and I am running OID and Integration Server on an Oracle 10gr2 Application Server running on top of SUSE Linux Enterprise Server SP1.

Let's first create a couple of Oracle accounts / schema:

/****************************************************************************/
/* Create hrApp schema/user.                        */
/****************************************************************************/

CREATE USER hrApp IDENTIFIED BY getmail
default tablespace USERS
quota unlimited on USERS
temporary tablespace TEMP;
GRANT CONNECT TO hrApp;
GRANT CREATE SESSION, ALTER SESSION, CREATE TABLE, CREATE SEQUENCE, CREATE PROCEDURE, CREATE VIEW TO hrApp;

/****************************************************************************/
/* Create hrProxy schema/user.                       */
/****************************************************************************/

CREATE USER hrProxy IDENTIFIED BY getmail
default tablespace USERS
quota unlimited on USERS
temporary tablespace TEMP;
GRANT CONNECT TO hrProxy;
GRANT CREATE SESSION, ALTER SESSION TO hrProxy;

We will use the HrApp schema for hosting our table/application, and we will use the hrProxy account in order to access the table.

/****************************************************************************/
/* Create hrApp table.                           */
/****************************************************************************/

CREATE TABLE hrApp.PERSON
(
    person_id        NUMBER(32)       NOT NULL
                            UNIQUE,
    person_type       NUMBER(4)    NOT NULL,        
    lname      VARCHAR(48),
    fname      VARCHAR(48),
    mname      VARCHAR(48),
    start_date       date,
    date_of_birth      date,
    email_address      VARCHAR(48),
    employee_number     NUMBER(32),       
    gender     VARCHAR(2),
    title      VARCHAR(48),
    suffix     VARCHAR(6),
    telephoneNumber     VARCHAR(48),
    effective_start_date      date,
    effective_end_date       date,
    last_update_date        date
);   


/****************************************************************************/
/* Grant hrProxy with read access to hrApp.PERSON.             */
/****************************************************************************/

GRANT SELECT,UPDATE ON hrApp.PERSON TO hrProxy;

We will use the following statement in order to insert records into our table:

/****************************************************************************/
/* Insert a record into hrApp.PERSON.                    */
/****************************************************************************/

INSERT INTO hrApp.PERSON (person_id,person_type ,lname,fname,mname,start_date,date_of_birth,email_address,employee_number,gender,title,suffix,telephoneNumber,effective_start_date,effective_end_date,last_update_date)
values (10001,1 ,'Smith','John','F',SYSDATE,'28-OCT-1970','jsmith@novl.ca',10001,'M','Manager','M.','418-222-4444',SYSDATE,'28-OCT-2006',SYSDATE);
COMMIT;

1. Use hrProxy to access the PERSON table located in the hrApp schema.

Figure 1: hrProxy should be able to access the PERSON table located in the hrApp schema.

2. Launch the Oracle Directory Integration and Provisioning Server Administration console on Linux or Unix with "dipassistant -gui".

Figure 2: Launching the Oracle Directory Integration and Provisioning Server Administration console

You can also use Oracle Directory Manager instead of dipassistant.

Figure 3: Using Oracle Directory Manager

You can create a new Integration Profile for synchronization with our database/table, but I have been running into a few issues with that. For example, my new Profile does not show up in the list of Profiles for the Configuration Set. It only shows up when I click Create, and it does not load when the Integration Server is started.

3. Before using the Integration Server included with Application Server 10gr2, you need to register it with the following command on Linux or UNIX:

odisrvreg -D cn=orcladmin -w password -h Ora10gAS -p 3060

4. After that, you can leverage the example config file, DBReader.cfg.master in the $ORACLE_HOME/ldap/odi/samples/ directory. Below is the cfg file I have been using for my example:

[DBQUERY]
SELECT: SELECT \
     PERSON_ID AS USER_ID, \
     PERSON_TYPE AS employeeType , \
     LNAME, \
     FNAME, \
     MNAME, \
     EMAIL_ADDRESS AS EMAIL, \
     EMPLOYEE_NUMBER AS employeeNumber, \
     telephoneNumber, \
     TITLE, \
     GENDER, \
     SUFFIX, \
     TO_CHAR(DATE_OF_BIRTH,'YYYYMMDDHH24MISS') AS BirthDate, \
     TO_CHAR(LAST_UPDATE_DATE,'YYYYMMDDHH24MISS') AS MASTER_DATE \
FROM HRAPP.PERSON \
WHERE \
   LAST_UPDATE_DATE > TO_DATE(:MASTER_DATE,'YYYYMMDDHH24MISS') \
ORDER BY MASTER_DATE

[SYNC-PARAMS]
CHANGEKEYATTRS: MASTER_DATE

dbreader.cfg includes the SQL Query that will be executed against our table in order to retrieve the records. In my example, I use MASTER_DATE, which is stored in an attribute in OID, to determine if a record has been modified within the polling interval.

Note: The first time you run the Oracle Driver for the Database/table, you need to remove the WHERE clause. This is because the OID attribute for MASTER_DATE has not been populated yet, and that will cause the SQL Query to fail.

5. Here is the temporary dbreader.cfg file you need to run when running the driver for the first time (i.e., for the bootstrap process):

[DBQUERY]
SELECT: SELECT \
     PERSON_ID AS USER_ID, \
     PERSON_TYPE AS employeeType , \
     LNAME, \
     FNAME, \
     MNAME, \
     EMAIL_ADDRESS AS EMAIL, \
     EMPLOYEE_NUMBER AS employeeNumber, \
     telephoneNumber, \
     TITLE, \
     GENDER, \
     SUFFIX, \
     TO_CHAR(DATE_OF_BIRTH,'YYYYMMDDHH24MISS') AS BirthDate, \
     TO_CHAR(LAST_UPDATE_DATE,'YYYYMMDDHH24MISS') AS MASTER_DATE \
FROM HRAPP.PERSON \
ORDER BY MASTER_DATE

[SYNC-PARAMS]
CHANGEKEYATTRS: MASTER_DATE

6. You will also need a mapping file in order to map the table's columns to OID attributes. Here is the mapping file I am using for my example:

DomainRules
NONLDAP:cn=hrUsers,dc=novl,dc=ca:cn=%,cn=hrUsers,dc=novl,dc=ca
AttributeRules
fname: : : :cn: :person
lname: : : :sn: :person
lname: : : :cn: :person
email: : : :cn: :person: trunc(email,'@')
fname,lname: : : :cn: :person: fname+","+lname
EmployeeNumber: : : :employeenumber: :inetOrgperson
EMail: : : :mail: :inetOrgperson
TelephoneNumber: : : :telephonenumber: :person
Title: : : :title: :organizationalperson
#gender: : : :sex: :person
#BirthDate: : : :birthday: :organizationalperson
employeenumber:1: : :userpassword: :person: "welcome"+employeenumber
changetype

Note that the initial user password is a concatenation of "welcome" and the employee number. The cn is set to fname, but you can modify this to fit your needs. While our example is simple and uses only one table, you can use a SELECT Query against multiple tables in order to feed OID. Consult the Oracle documentation for more information, or the dbreader.cfg.master for a more complex example.

To update the integration profile with dbreader.cfg and dbreader.map, that you will create under %ORACLE_HOME/ldap/odi/conf, use the following 2 commands:

dipassistant mp -host Ora10gAS.novl.ca -port 3060 -passwd getmail1 -profile TaggedImport odip.profile.configfile=$ORACLE_HOME/ldap/odi/conf/dbreader.cfg
dipassistant mp -host Ora10gAS.novl.ca -port 3060 -passwd getmail1 -profile TaggedImport odip.profile.mapfile=/opt/oracle/product/10.1.2/OracleAS_3/infra/ldap/odi/conf/dbreader.map

7. Click Edit to modify an existing Integration Profile, TaggedImport, in order to have a working driver.

Figure 4: TaggedImport default Integration Profile

8. Enable the driver.

9. Change the Scheduling/Polling interval to 10 seconds.

10. Set the Debug Level to 63.

Figure 5: Integration Profile General page

11. Provide the name of the account for accessing the table (hrProxy) and the password.

12. Provide the connection URL (dns_name:port:service_name) for the database - you can find this in the tnsnames.ora (under $ORACLE_HOME/network/admin) for the Database server.

13. You also need to change the interface type to DB.

Figure 6: Integration Profile, Execution Page

14. Set the OID Matching Filter to Employeenumber, which will be used as the unique identifier for the table's records.

Figure 7: Integration Profile, Mapping page

The Integration Profile, Status page will tell you the status of Synchronization, as well as the last time Sync was done successfully and the last time it was executed.

Figure 8: Integration Profile, Status page

For detailed logs for sync events or errors, look under $ORACLE_HOME/ldap/log and $ORACLE_HOME/ldap/odi/log.

15. Save your changes so you will be ready to start the driver.

Make sure that the OID context you specified in the mapping file dbreader.map exists. You may want to use a different context than the default one for users(CN=Users,dc=org,dc=com) - otherwise you may need to add auxiliary class references to your config files (e.g., orclUserv2).

Start the Integration Server and the ENABLED drivers by using this command:

oidctl connect=Ora10gAS server=odisrv configset=1 instance=2 flags="port=3060 debug=63" start

By replacing start with stop, you can also stop the driver (for example, in order to modify the config files and then re-start the driver for the changes to take effect). Remember to leave out the WHERE clause from dbreader.cfg the first time you run the driver.

If your table contained existing records, they should result in new users in OID. Any subsequent user creation/modification/deletion should also be taken care of.

You should now see a couple of users that have been created by the Driver/Integration Profile.

Figure 9: Directory Manager for OID

Conclusion

This completes the LDAP enablement of an Oracle application with tables containing identities. These identities can be used to feed Novell eDirectory through the Identity Manager LDAP driver connected to Oracle Internet Directory.

For more information, see the following URLs:

Oracle® Identity Management Integration Guide
10g Release 2 (10.1.2)
B14085-02
Synchronization with Relational Database Tables

http://download-east.oracle.com/docs/cd/B14099_14/idmanage.1012/b14085/odip_db.htm#i1042820

Administration of Directory Synchronization

http://download-east.oracle.com/docs/cd/B14099_14/idmanage.1012/b14085/odip_adm_sync.htm#sthref341

Please, do not hesitate to send feedback, comments, or questions my way. Thanks for taking the time to read this article.


Novell Cool Solutions (corporate web communities) are produced by WebWise Solutions. www.webwiseone.com

© 2014 Novell