dbms_ldap Tips

A continuous feature throughout Oracle's history has been the Lightweight Directory Access Protocol (LDAP).  LDAP is a powerful tool that is central to Oracle Platform Security.  The dbms_ldap package used to manage LDAP using PL/SQL language can perform many operations in a directory server.  The examples that follow will demonstrate some of the more common tasks.

If the dbms_ldap package cannot be located, execute the command below logged in as a sys user in order to create it.

conn sys@ora11g as sysdba

Connected to Oracle 11g Enterprise Edition Release 11.1.0.6.0

conn / as sysdba

--Create dbms_ldap package

@?/rdbms/admin/catldap.sql

This will show how to perform a search that will return all entries within a specific LDAP base directory.

set serveroutput on size 10000

declare
 v_ldap_message     dbms_ldap.message;
 v_ldap_entry       dbms_ldap.message;
 v_returnval             pls_integer;
 v_session          dbms_ldap.session;
 v_str_collection   dbms_ldap.string_collection;
 v_entry_index      pls_integer;
 v_ber_element      dbms_ldap.ber_element;
 v_attr_index       pls_integer;
 v_dn               varchar2(256);
 v_attrib_name      varchar2(256);
 i                  pls_integer;
 v_info             dbms_ldap.string_collection ;
 v_ldap_base        varchar2(256);
 v_ldap_port        varchar2(256);
 v_ldap_host        varchar2(256);
 v_ldap_user        varchar2(256);
 v_ldap_passwd      varchar2(256);

begin

--Here you can set your LDAP Server information
 v_ldap_host  := 'dbms.f2c.com.br' ;
 v_ldap_port  := '389';
 v_ldap_user  := 'cn=orcladmin';
 v_ldap_passwd:= 'welcome';
 v_ldap_base  := 'dc=demo_entries';

 v_returnval         := -1;
 dbms_output.put(a => 'DBMS_LDAP Search Example ');
 dbms_output.put_line(a => 'to directory .. ');
 dbms_output.put_line(a => rpad('LDAP Host ',25,' ') || ': ' || v_ldap_host);
 dbms_output.put_line(a => rpad('LDAP Port ',25,' ') || ': ' || v_ldap_port);

 dbms_ldap.use_exception := TRUE;

First, the init function is used to establish a connection with the LDAP server. The information about this connection is then displayed.

 v_session := dbms_ldap.init(
                 hostname => v_ldap_host,
                 portnum => v_ldap_port);
 
 --Displaying information about the connection
 dbms_output.put_line(a => rpad('Ldap session ',25,' ')  || ': ' || rawtohex(substr(v_session,1,8)) ||'(returned from init)');

--Now, the simple_bind_s function will achieve the user authentication to LDAP directory server.

 v_returnval := dbms_ldap.simple_bind_s(
                   ld => v_session,
                   dn => v_ldap_user,
                   passwd => v_ldap_passwd);
 
 dbms_output.put_line(rpad('simple_bind_s Returns ',25,' ') || ': '|| to_char(v_returnval));

Here, the search_s function begins searching in a synchronized way for a value placed in the filter parameters.

 v_str_collection(1) := '*'; -- retrieve all attributes
 v_returnval := dbms_ldap.search_s(
              ld => v_session,
              base =>  v_ldap_base,
              scope =>
              dbms_ldap.scope_subtree,
              filter => 'sn~=PAULO',
              attrs => v_str_collection,
              attronly =>  0,
              res => v_ldap_message);
             

 dbms_output.put_line(rpad('Search returns: ',25,' ') || ': ' || to_char(v_returnval));
 dbms_output.put_line (rpad('LDAP message: ',25,' ')  || ': ' || rawtohex(substr(v_ldap_message,1,8))
                                                              ||' "value returned from your search!" ');

Next, the count_entries function is used to count the number of entries in a result set. After this, the first_entry function is used to get the first entry of that set.

 v_returnval := dbms_ldap.count_entries(
                   ld => v_session,
                   msg => v_ldap_message);
                  
 dbms_output.put_line(rpad('Number of Entries ',25,' ') || ': '|| to_char(v_returnval));
 dbms_output.put_line('############################################');

 --Use first_entry function to return the first entry in a result set
 v_ldap_entry := dbms_ldap.first_entry(ld => v_session,msg =>  v_ldap_message);
 v_entry_index := 1;

 -- Get attributes of each entry found
 while v_ldap_entry is NOT NULL loop
   -- print the current entry
   v_dn := dbms_ldap.get_dn(
              ld => v_session,
              ldapentry => v_ldap_entry);
             
   dbms_output.put_line (a => '        dn: ' || v_dn);
   v_attrib_name := dbms_ldap.first_attribute(
                       ld => v_session,
                       ldapentry => v_ldap_entry,
                       ber_elem => v_ber_element);
                      
   v_attr_index := 1;
   while v_attrib_name is NOT NULL loop    

The get_values function is used to get all values associated with a given attribute. After that, the next_attribute function is used to return the next attribute of a given entry in the result set.

     v_info := dbms_ldap.get_values(
                  ld => v_session,
                  ldapentry => v_ldap_entry,
                  attr => v_attrib_name);
                 
     if v_info.count > 0 then
       for i in v_info.first..v_info.last loop
         dbms_output.put_line(a => '           ' || v_attrib_name || ' : ' ||substr(v_info(i),1,200));
       end loop;
     end if;
    

--Function next_attribute return the next attribute of a given entry in the result set
     v_attrib_name := dbms_ldap.next_attribute(
                        ld => v_session,
                        ldapentry => v_ldap_entry,
                        ber_elem => v_ber_element);
     v_attr_index := v_attr_index+1;
   end loop; 

Functions like ber_free and msgfree are used to free memory allocated to the ber_element structure. This frees up the chain of messages returned by synchronous search functions.

   dbms_ldap.ber_free(
      ber => v_ber_element,
      freebuf =>  0);
  
   v_ldap_entry := dbms_ldap.next_entry(
                      ld => v_session,
                      msg => v_ldap_entry);
                        
   dbms_output.put_line('===========================================');
   v_entry_index := v_entry_index+1;
 end loop;
 

 -- Use msgfree function to free up the chain of messages associated with the message handle returned by synchronous search functions

 v_returnval := dbms_ldap.msgfree(
                   lm => v_ldap_message);

Lastly, the unbind_s function is used to close an active LDAP session and finish this example.

 v_returnval := dbms_ldap.unbind_s(
                   ld => v_session);

 dbms_output.put_line(a => rpad('unbind_res Returns ',25,' ') ||  ': ' ||to_char(v_returnval));

 dbms_output.put_line(a => 'Session Closed. Operation terminated!');

 exception
  when others then
   dbms_output.put_line(' Error code    : ' || to_char(sqlcode));
   dbms_output.put_line(' Error Message : ' || sqlerrm);
   dbms_output.put_line(' Exception encountered .. exiting');
end;
/