Skip Headers

Oracle Internet Directory Application Developer's Guide
Release 2 (9.0.2)

Part Number A95193-01
Go To Documentation Library
Home
Go To Product List
Solution Area
Go To Table Of Contents
Contents
Go To Index
Index

Go to previous page Go to next page

A
Sample Usage

This appendix provides sample code.

This section contains these topics

Using DBMS_LDAP from a Database Trigger

The DBMS_LDAP API can be invoked from database triggers to synchronize any changes to a database table with an enterprise-wide LDAP server. The following example illustrates how changes to a table called 'EMP' are synchronized with the data in an LDAP server using triggers for insert, update, and delete. There are two files associated with this sample:

These files can be found in the plsql directory under $ORACLE_HOME/ldap/demo

The trigger.sql File

This SQL file creates a database table called 'EMP' and creates a trigger on it called LDAP_EMP which will synchronize all changes happening to the table with an LDAP server. The changes to the database table are reflected/replicated to the LDAP directory using the DBMS_LDAP package.

This script assumes the following:

The aforementioned variables could be customized for different environments by changing the appropriate variables in the code below.

Table Definition

Employee Details(Columns) in Database Table(EMP):

EMP_ID

Number

FIRST_NAME

Varchar2

LAST_NAME

Varchar2

MANAGER_ID

Number

PHONE_NUMBER

Varchar2

MOBILE

Varchar2

ROOM_NUMBER

Varchar2

TITLE

Varchar2

LDAP Schema Definition & Mapping to Relational Schema EMP

Corresponding Data representation in LDAP directory:

DN

cn=FIRST_NAME LAST_NAME, o=acme, dc=com]

cn

FIRST_NAME LAST_NAME

sn

LAST_NAME

givenname

FIRST_NAME

manager

DN

telephonenumber

PHONE_NUMBER

mobile

MOBILE

employeeNumber

EMP_ID

userpassword

FIRST_NAME

objectclass

person

organizationalperson

inetOrgPerson

top

--Creating EMP table

PROMPT Dropping Table EMP ..
drop table EMP;

PROMPT Creating Table EMP ..
CREATE TABLE EMP (

EMP_ID    NUMBER,	Employee Number
FIRST_NAME  VARCHAR2(256),	First Name
LAST_NAME  VARCHAR2(256),	Last Name
MANAGER_ID  NUMBER,	Manager Number
PHONE_NUMBER VARCHAR2(256),	Telephone Number
MOBILE   VARCHAR2(256),	Mobile Number
ROOM_NUMBER  VARCHAR2(256),	Room Number
TITLE   VARCHAR2(256)	Title in the company
); --Creating Trigger LDAP_EMP PROMPT Creating Trigger LDAP_EMP .. CREATE OR REPLACE TRIGGER LDAP_EMP AFTER INSERT OR DELETE OR UPDATE ON EMP FOR EACH ROW DECLARE
retval   PLS_INTEGER; 
emp_session  DBMS_LDAP.session;
emp_dn   VARCHAR2(256);
emp_rdn   VARCHAR2(256);
emp_array  DBMS_LDAP.MOD_ARRAY;
emp_vals  DBMS_LDAP.STRING_COLLECTION ;
ldap_host  VARCHAR2(256);
ldap_port  VARCHAR2(256);
ldap_user  VARCHAR2(256);
ldap_passwd  VARCHAR2(256);
ldap_base  VARCHAR2(256);
BEGIN
retval       := -1;
-- Customize the following variables as needed
ldap_host  := NULL;
ldap_port  := '389';
ldap_user  := 'cn=orcladmin';
ldap_passwd:= 'welcome';
ldap_base  := 'o=acme,dc=com';
-- end of customizable settings 
DBMS_OUTPUT.PUT('Trigger [LDAP_EMP]: Replicating changes ');
DBMS_OUTPUT.PUT_LINE('to directory .. ');
DBMS_OUTPUT.PUT_LINE(RPAD('LDAP Host ',25,' ') || ': ' || ldap_host);
DBMS_OUTPUT.PUT_LINE(RPAD('LDAP Port ',25,' ') || ': ' || ldap_port);

-- Choosing exceptions to be raised by DBMS_LDAP library.
DBMS_LDAP.USE_EXCEPTION := TRUE;

-- Initialize ldap library and get session handle.
emp_session := DBMS_LDAP.init(ldap_host,ldap_port);

DBMS_OUTPUT.PUT_LINE (RPAD('Ldap session ',25,' ')  || ': ' ||
RAWTOHEX(SUBSTR(emp_session,1,8)) ||
'(returned from init)');
        
-- Bind to the directory
retval := DBMS_LDAP.simple_bind_s(emp_session,
ldap_user,ldap_passwd);

DBMS_OUTPUT.PUT_LINE(RPAD('simple_bind_s Returns ',25,' ') || ': ' 
          || TO_CHAR(retval));
 
-- Process New Entry in the database

IF INSERTING THEN

-- Create and setup attribute array for the New entry
emp_array := DBMS_LDAP.create_mod_array(14);

-- RDN to be - cn="FIRST_NAME LAST_NAME"

emp_vals(1) := :new.FIRST_NAME || ' ' || :new.LAST_NAME;

DBMS_LDAP.populate_mod_array(emp_array,DBMS_LDAP.MOD_ADD,
'cn',emp_vals);

emp_vals(1) := :new.LAST_NAME;

DBMS_LDAP.populate_mod_array(emp_array,DBMS_LDAP.MOD_ADD,
            'sn',emp_vals);

emp_vals(1) := :new.FIRST_NAME;

DBMS_LDAP.populate_mod_array(emp_array,DBMS_LDAP.MOD_ADD,
            'givenname',emp_vals);

emp_vals(1) := 'top';
emp_vals(2) := 'person';
emp_vals(3) := 'organizationalPerson';
emp_vals(4) := 'inetOrgPerson';

DBMS_LDAP.populate_mod_array(emp_array,DBMS_LDAP.MOD_ADD,
            'objectclass',emp_vals);

emp_vals.DELETE;
emp_vals(1) := :new.PHONE_NUMBER;

DBMS_LDAP.populate_mod_array(emp_array,DBMS_LDAP.MOD_ADD,
            'telephonenumber',emp_vals);

emp_vals(1) := :new.MOBILE;

DBMS_LDAP.populate_mod_array(emp_array,DBMS_LDAP.MOD_ADD,
            'mobile',emp_vals);

emp_vals(1) := :new.ROOM_NUMBER;

DBMS_LDAP.populate_mod_array(emp_array,DBMS_LDAP.MOD_ADD,
            'roomNumber',emp_vals);

emp_vals(1) := :new.TITLE;

DBMS_LDAP.populate_mod_array(emp_array,DBMS_LDAP.MOD_ADD,
            'title',emp_vals);

emp_vals(1) := :new.EMP_ID;

DBMS_LDAP.populate_mod_array(emp_array,DBMS_LDAP.MOD_ADD,
            'employeeNumber',emp_vals);

emp_vals(1) := :new.FIRST_NAME;

DBMS_LDAP.populate_mod_array(emp_array,DBMS_LDAP.MOD_ADD,
            'userpassword',emp_vals);
   
-- DN for Entry to be Added under 'ldap_base' [o=acme, dc=com]
 
emp_dn := 'cn=' || :new.FIRST_NAME || ' ' || 
:new.LAST_NAME || ', ' || ldap_base ;
DBMS_OUTPUT.PUT_LINE(RPAD('Adding Entry for DN ',25,' ') || ': [' 
           || emp_dn || ']');

-- Add new Entry to ldap directory
retval := DBMS_LDAP.add_s(emp_session,emp_dn,emp_array);
DBMS_OUTPUT.PUT_LINE(RPAD('add_s Returns ',25,' ')  || ': '
           || TO_CHAR(retval));

-- Free attribute array (emp_array)
DBMS_LDAP.free_mod_array(emp_array);

END IF; -- INSERTING

-- Process Entry deletion in database 

IF DELETING THEN

-- DN for Entry to be deleted under 'ldap_base' [o=acme, dc=com]

emp_dn := 'cn=' || :old.FIRST_NAME || ' ' || 
:old.LAST_NAME || ', ' || ldap_base ;
DBMS_OUTPUT.PUT_LINE(RPAD('Deleting Entry for DN ',25,' ') || 
         ': [' || emp_dn || ']');

-- Delete entry in ldap directory
retval := DBMS_LDAP.delete_s(emp_session,emp_dn);
     DBMS_OUTPUT.PUT_LINE(RPAD('delete_s Returns ',25,' ') || ': ' ||
           TO_CHAR(retval));

END IF; -- DELETING

-- Process updated Entry in database

IF UPDATING THEN

-- Since two Table columns(in this case) constitue a RDN
-- check for any changes and update RDN in ldap directory
-- before updating any other attributes of the Entry.

IF :old.FIRST_NAME <> :new.FIRST_NAME OR
     :old.LAST_NAME  <> :new.LAST_NAME THEN

emp_dn := 'cn=' || :old.FIRST_NAME || ' ' || 
    :old.LAST_NAME || ', ' || ldap_base; 

emp_rdn := 'cn=' || :new.FIRST_NAME || ' ' || :new.LAST_NAME;

DBMS_OUTPUT.PUT_LINE(RPAD('Renaming OLD DN ',25,' ') ||
           ': [' || emp_dn || ']'); 
DBMS_OUTPUT.PUT_LINE(RPAD(' => NEW RDN ',25,' ') || 
           ': [' || emp_rdn || ']' );
retval := DBMS_LDAP.modrdn2_s(emp_session,emp_dn,emp_rdn,
             DBMS_LDAP.MOD_DELETE);
DBMS_OUTPUT.PUT_LINE(RPAD('modrdn2_s Returns ',25,' ') || ': ' ||
             TO_CHAR(retval));
END IF;

-- DN for Entry to be updated under 'ldap_base' [o=acme, dc=com]

emp_dn := 'cn=' || :new.FIRST_NAME || ' ' || 
   :new.LAST_NAME || ', ' || ldap_base;

DBMS_OUTPUT.PUT_LINE(RPAD('Updating Entry for DN ',25,' ') || 
            ': [' || emp_dn || ']');

-- Create and setup attribute array(emp_array) for updated entry
emp_array := DBMS_LDAP.create_mod_array(7);

emp_vals(1) := :new.LAST_NAME;

DBMS_LDAP.populate_mod_array(emp_array,DBMS_LDAP.MOD_REPLACE,
            'sn',emp_vals);

emp_vals(1) := :new.FIRST_NAME;

DBMS_LDAP.populate_mod_array(emp_array,DBMS_LDAP.MOD_REPLACE,
            'givenname',emp_vals);

emp_vals(1) := :new.PHONE_NUMBER;

DBMS_LDAP.populate_mod_array(emp_array,DBMS_LDAP.MOD_REPLACE,
            'telephonenumber',emp_vals);

emp_vals(1) := :new.MOBILE;

DBMS_LDAP.populate_mod_array(emp_array,DBMS_LDAP.MOD_REPLACE,
            'mobile',emp_vals);

emp_vals(1) := :new.ROOM_NUMBER;

DBMS_LDAP.populate_mod_array(emp_array,DBMS_LDAP.MOD_REPLACE,
            'roomNumber',emp_vals);

emp_vals(1) := :new.TITLE;

DBMS_LDAP.populate_mod_array(emp_array,DBMS_LDAP.MOD_REPLACE,
            'title',emp_vals);

emp_vals(1) := :new.EMP_ID;

DBMS_LDAP.populate_mod_array(emp_array,DBMS_LDAP.MOD_REPLACE,
            'employeeNumber',emp_vals);

-- Modify entry in ldap directory
  retval := DBMS_LDAP.modify_s(emp_session,emp_dn,emp_array);

DBMS_OUTPUT.PUT_LINE(RPAD('modify_s Returns ',25,' ') || ': ' ||
            TO_CHAR(retval));

-- Free attribute array (emp_array)
DBMS_LDAP.free_mod_array(emp_array);

END IF; -- UPDATING

-- Unbind from ldap directory
retval := DBMS_LDAP.unbind_s(emp_session);

DBMS_OUTPUT.PUT_LINE(RPAD('unbind_res Returns ',25,' ') ||  ': ' ||
           TO_CHAR(retval));

DBMS_OUTPUT.PUT_LINE('Directory operation Successful .. exiting');

-- Handle Exceptions
EXCEPTION
WHEN OTHERS THEN
-- TODO : should the trigger call unbind at this point ??
--   what if the exception was raised from unbind itself ??

DBMS_OUTPUT.PUT_LINE(' Error code    : ' || TO_CHAR(SQLCODE));
DBMS_OUTPUT.PUT_LINE(' Error Message : ' || SQLERRM);
DBMS_OUTPUT.PUT_LINE(' Exception encountered .. exiting');
END; / -------------------------------END OF trigger.sql---------------------------

Using DBMS_LDAP for a Search

The following example illustrates using the DBMS_LDAP API to perform an LDAP search in a PL/SQL program. This example searches for the entries created using the trigger example described previously. It assumes a base of o=acme,dc=com and performs a subtree search to retrieve all entries that are subordinates of the base entry. The code shown below is contained in a file called search.sql which can be found in the $ORACLE_HOME/ldap/demo/plsql directory.

The search.sql File

This SQL file contains the PL/SQL code required to perform a typical search against an LDAP server.

This script assumes the following:

Java Sample Code

This section contains Java sample code.

This section contains these topics:

User Class Sample Code

/*
 * SampleUser.java
 *
 * This is a sample usage of the User class in oracle.ldap.util package
 * found in ldapjclnt9.jar.  You can define a user using DN, GUID, or
 * a simple name representing the user.  The following methods are exercised
 * in this sample program:
 *
 * - User.authenticateUser() - to authenticate a user with the appropriate
 *   credentials
 * - User.getProperties() - to obtain properties of the user
 * - User.setProperties() - to add, replace, or delete properties of the user 
 *
 */

import oracle.ldap.util.*;
import oracle.ldap.util.jndi.*;

import java.io.*;
import java.util.*;
import javax.naming.*;
import javax.naming.directory.*;

public class SampleUser {

   public static void main(String argv[])
          throws NamingException {

      // Create InitialDirContext

      InitialDirContext ctx = ConnectionUtil.getDefaultDirCtx( "sandal",
                                       "3060",
                                       "cn=orcladmin",
                                       "welcome" );

      // Create Subscriber object
 
      Subscriber mysub = null;

      try {
         // Creation using DN
         mysub = new Subscriber( ctx, Util.IDTYPE_DN, "o=oracle,dc=com", false 
);
      }
      catch (UtilException e) {
         /*
          * Exception encountered in subscriber object constructor
          */
      }

      // Create User Objects

      User myuser = null,
           myuser1 = null;
   
      try {
         // Create User using a subscriber DN and the User DN

         myuser = new User ( ctx,
                             Util.IDTYPE_DN,
                             "cn=user1,cn=users,o=oracle,dc=com",
                             Util.IDTYPE_DN,
                             "o=oracle,dc=com",
                             false );

         // Create User using a subscriber object and the User
         // simple name

         myuser1 = new User ( ctx,
                              Util.IDTYPE_SIMPLE,
                              "user1",
                              mysub,
                              false );
      }
      catch ( UtilException e ) {
         /*
          * Exception encountered in User object constructor
          */
      }

      // Authenticate User
      try {
         myuser1.authenticateUser(ctx,User.CREDTYPE_PASSWD,"welcome");
      }
      catch ( UtilException e ) {
         /*
          * Authenticate fails
          */
      }

      // Perform User operations

      try {
         PropertySetCollection result = null;

         // Get telephonenumber of user

         String[] userAttrList = {"telephonenumber"};
         result = myuser1.getProperties(ctx,userAttrList);
         
         /*
          * Do work with result
                .
                .
                .
          */ 
          Util.printResults(result);

         // Set telephonenumber of user

         // Create JNDI ModificationItem 

         ModificationItem[] mods = new ModificationItem[1]; 
         mods[0] = new ModificationItem(DirContext.REPLACE_ATTRIBUTE,
                           new BasicAttribute("telephonenumber", "444-6789"));

         // Perform modification using User object

         myuser.setProperties(ctx, mods);
      }
      catch ( UtilException e ) {
         /*
          * Exception encountered in User object operations
          */
      }
   }
} // End of SampleUser.java

Subscriber Class Sample Code

/*
 * SampleSubscriber.java
 *
 * This is a sample usage of the Subscriber class in oracle.ldap.util package
 * found in ldapjclnt9.jar.  You can define a group using a DN, GUID, or a 
 * simple name of the subscriber.  The following methods are exercised in 
 * this sample program:
 *
 * - Subscriber.getProperties() - to obtain properties of the group
 *
 */  

import oracle.ldap.util.*;
import oracle.ldap.util.jndi.*;

import java.io.*;
import java.util.*;
import javax.naming.*;
import javax.naming.directory.*;

public class SampleSubscriber {

   public static void main(String argv[])
          throws NamingException {

      // Create InitialDirContext

      InitialDirContext ctx = ConnectionUtil.getDefaultDirCtx( "sandal",
                                       "3060",
                                       "cn=orcladmin",
                                       "welcome" );

      // Create Subscriber object
 
      Subscriber mysub = null,
                 mysub1 = null,
                 mysub2 = null;
      try {
      
         // Creation using DN
         mysub = new Subscriber( ctx,
                                         Util.IDTYPE_DN,
                                         "o=oracle,dc=com",
                                         false );

         // Creation using Simple Name 
         mysub1 = new Subscriber( ctx,
                                          Util.IDTYPE_SIMPLE,
                                          "Oracle",
                                          false );
 
         // Creation using GUID
         mysub2 = new Subscriber( ctx,
                                          Util.IDTYPE_GUID,
                                          "93B37BBC3B1F46F8E034080020F73460",
                                          false );
      }
      catch (UtilException e) {
         /*
          * Exception encountered in subscriber object constructor
          */
      }

 
      // Set the attribute list for attributes returned
      String[] attrList = { "cn",
                            "orclcommonusersearchbase",
                            "orclguid" };

      // Get Subscriber Properties

      PropertySetCollection result = null;
      try {
         result = mysub.getProperties(ctx,attrList); 
      }
      catch (UtilException e) {
         /*
          * Exception encountered when searching for subscriber properties
          */
      }

      /*
       * Do work with the result
       */

      Util.printResults(result);
   }
}

Group Class Sample Code

/*
 * SampleGroup.java
 *
 * This is a sample usage of the Group class in oracle.ldap.util package
 * found in ldapjclnt9.jar.  You can define a group using DN or GUID.
 * The following methods are exercised in this sample program: 
 *
 * - Group.isMember() - to see if a particular user is 
 *   a member of this group 
 * - Util.getGroupMembership() - to obtain the list of groups which a
 *   particular user belongs to
 * - Group.getProperties() - to obtain properties of the group 
 *
 */

import oracle.ldap.util.*;
import oracle.ldap.util.jndi.*;

import java.io.*;
import java.util.*;
import javax.naming.*;
import javax.naming.directory.*;

public class SampleGroup {

   public static void main(String argv[])
          throws NamingException {

      // Create InitialDirContext

      InitialDirContext ctx = ConnectionUtil.getDefaultDirCtx( "sandal",
                                       "3060",
                                       "cn=orcladmin",
                                       "welcome" );

      // Create Group Object
      Group mygroup = null;
      try {
         mygroup = new Group ( Util.IDTYPE_DN,
                               "cn=group1,cn=Groups,o=oracle,dc=com" );
      }
      catch ( UtilException e ) {
         /*
          * Error encountered in Group constructor
          */
      }
  
      // Create User Object

      User myuser = null;
      try {
         // Create User using a subscriber DN and the User DN
         myuser = new User ( ctx,
                             Util.IDTYPE_DN,
                             "cn=orcladmin,cn=users,o=oracle,dc=com",
                             Util.IDTYPE_DN,
                             "o=oracle,dc=com",
                             false );
      }
      catch ( UtilException e ) {
         /*
          * Exception encountered in User object constructor
          */
      }
      
      // Perform Group Operations

      try {

         // isMember method 

         if (mygroup.isMember( ctx,
                               myuser,
                               true ) ) {
            /*
             * myuser is a member of this group 
             * Do work
             *           .
             *           .
             *           .
             */
             System.out.println("is member");
         }

         // Get all nested groups that a user belongs to

         PropertySetCollection result = Util.getGroupMembership( ctx,
                                                                 myuser,
                                                                 new String[0],
                                                                 true );
         /*
          * Do work with result
          *           .
          *           .
          *           .
          */
         Util.printResults ( result );
 
         // Get Group Properties

         result = getProperties( ctx, null );
                                
         /*
          * Do work with result
          *           .
          *           .
          *           .
          */
      }
      catch ( UtilException e ) {
         /*
          * Exception encountered in getGroupMembership
          */
      }
   }
} // End of SampleGroup.java

Print Sample Code

/*
 * SamplePrint.java
 *
 * This sample program demonstrates the usage of the PropertySetCollection 
 * class which is a key structure used in the oracle.ldap.util package for 
 * obtaining search results.  A sample printResults() method is implemented
 * that neatly prints out the values of a PropertySetCollection.  
 * A ProperSetCollection contains a set of PropertySets.  A PropertySet is 
 * analogous to an LDAP entry which is identified by the DN.  Each PropertySet
 * contains a set of zero or more Properties.  A Property is analogous to a
 * particular attribute of an LDAP entry and it may contain one or more
 * values.  The printResults() method takes in a PropertySetCollection and
 * navigates through it in a systemmatic way, printing out the results to 
 * the system output.
 *
 */
 
import oracle.ldap.util.*;
import oracle.ldap.util.jndi.*;

import java.io.*;
import java.util.*;
import javax.naming.*;
import javax.naming.directory.*;

public class SamplePrint {

   public static void printResults( PropertySetCollection resultSet )
   {
      // for loop to go through each PropertySet 
      for (int i = 0; i < resultSet.size(); i++ )
      {
         // Get PropertySet
         PropertySet curEntry = resultSet.getPropertySet( i );
         Object obj = null;

         // Print DN of PropertySet
         System.out.println("dn: " + curEntry.getDN());

         // Go through each Property of the PropertySet
         for (int j = 0; j < curEntry.size(); j++)
         {
            // Get Property
            Property curAttr = curEntry.getProperty( j );
  
            // Go through each value of the Property
            for (int k = 0; k < curAttr.size(); k++)
            {
              obj = curAttr.getValue(k);
              if( obj instanceof java.lang.String) {
               System.out.println( curAttr.getName() + ": "
                                + (String) obj);
              }
              else if (obj instanceof byte[]) {
               System.out.println( curAttr.getName() + ": "
                                + (new java.lang.String((byte [])obj)));
              }
            }
         }
         System.out.println();
      }
   }

} // End of SamplePrint.java


Go to previous page Go to next page
Oracle
Copyright © 2002 Oracle Corporation.

All Rights Reserved.
Go To Documentation Library
Home
Go To Product List
Solution Area
Go To Table Of Contents
Contents
Go To Index
Index