LDAPWiki: Oracle SQL To Access LDAP

Overview#

We are not DBA experts but we have often been involved with integration work using Oracle SQL To Access LDAP.

We shows some Example code to get you started with Oracle SQL To Access LDAP

DBMS_LDAP - Accessing LDAP From PL/SQL [1]#

The DBMS_LDAP package is a PL/SQL API to enable programatic searches and modifications of data within LDAP directories. In this article I'll demonstrate a simple LDAP search.

Loading the DBMS_LDAP package#

The DBMS_LDAP package is not loaded by default. To install it run the following script as SYS.

SQL> @$ORACLE_HOME/rdbms/admin/catldap.sql

Connect And Authenticate#

The first step in any LDAP interaction is to connect to the LDAP server and authenticate yourself. This is done using the init and simple_bind_s functions.

l_session := DBMS_LDAP.init(hostname => l_ldap_host,
                            portnum  => l_ldap_port);

l_retval := DBMS_LDAP.simple_bind_s(ld     => l_session,
                                    dn     => l_ldap_user,
                                    passwd => l_ldap_passwd);

Search LDAP Directory #

Once connected we can search the directory.

l_attrs(1) := '*'; -- retrieve all attributes 
l_retval := DBMS_LDAP.search_s(ld       => l_session, 
                               base     => l_ldap_base, 
                               scope    => DBMS_LDAP.SCOPE_SUBTREE,
                               filter   => 'objectclass=*',
                               attrs    => l_attrs,
                               attronly => 0,
                               res      => l_message);

The starting point for the search (base), depth of the search (scope), the attributes searched for (attrs) and the filter can be modified to build complex searches.

Loop Through Search Responses #

An LDAP search returns a Search Responses which can be looped through. Note: We do not check for SearchResultReferences within this code.

The code assumes all the Search Responses are SearchResultEntrys.

IF DBMS_LDAP.count_entries(ld => l_session, msg => l_message) > 0 THEN
  -- Get all the entries returned by our search.
  l_entry := DBMS_LDAP.first_entry(ld  => l_session,
                                   msg => l_message);

  << entry_loop >>
  WHILE l_entry IS NOT NULL LOOP
    ...
    ...
    l_entry := DBMS_LDAP.next_entry(ld  => l_session,
                                    msg => l_entry);
  END LOOP entry_loop;
END IF;

Loop Through Attributes#

For each entry we loop through the associated attributes.

l_attr_name := DBMS_LDAP.first_attribute(ld        => l_session,
                                         ldapentry => l_entry,
                                         ber_elem  => l_ber_element);
<< attributes_loop >>
WHILE l_attr_name IS NOT NULL LOOP
  ...
  ...
  l_attr_name := DBMS_LDAP.next_attribute(ld        => l_session,
                                          ldapentry => l_entry,
                                          ber_elem  => l_ber_element);
END LOOP attibutes_loop;

Loop Through Values#

Finally, we retrieve the values associated with the attribute.

<< values_loop >>
FOR i IN l_vals.FIRST .. l_vals.LAST LOOP
  DBMS_OUTPUT.PUT_LINE('ATTIBUTE_NAME: ' || l_attr_name || ' = ' || SUBSTR(l_vals(i),1,200));
END LOOP values_loop;

Put It All Together#

If we put all these stages together we get the following.

SET SERVEROUTPUT ON SIZE 1000000
DECLARE
  -- Adjust as necessary.
  l_ldap_host    VARCHAR2(256) := 'server01.tshcomputing.com';
  l_ldap_port    VARCHAR2(256) := '389';
  l_ldap_user    VARCHAR2(256) := 'cn=orcladmin';
  l_ldap_passwd  VARCHAR2(256) := 'password';
  l_ldap_base    VARCHAR2(256) := 'cn=Users,dc=tshcomputing,dc=com';

  l_retval       PLS_INTEGER; 
  l_session      DBMS_LDAP.session;
  l_attrs        DBMS_LDAP.string_collection;
  l_message      DBMS_LDAP.message;
  l_entry        DBMS_LDAP.message;
  l_attr_name    VARCHAR2(256);
  l_ber_element  DBMS_LDAP.ber_element;
  l_vals         DBMS_LDAP.string_collection;
  
BEGIN
  -- Choose to raise exceptions.
  DBMS_LDAP.USE_EXCEPTION := TRUE;

  -- Connect to the LDAP server.
  l_session := DBMS_LDAP.init(hostname => l_ldap_host,
                              portnum  => l_ldap_port);

  l_retval := DBMS_LDAP.simple_bind_s(ld     => l_session,
                                      dn     => l_ldap_user,
                                      passwd => l_ldap_passwd);

  -- Get all attributes
  l_attrs(1) := '*'; -- retrieve all attributes 
  l_retval := DBMS_LDAP.search_s(ld       => l_session, 
                                 base     => l_ldap_base, 
                                 scope    => DBMS_LDAP.SCOPE_SUBTREE,
                                 filter   => 'objectclass=*',
                                 attrs    => l_attrs,
                                 attronly => 0,
                                 res      => l_message);

  IF DBMS_LDAP.count_entries(ld => l_session, msg => l_message) > 0 THEN
    -- Get all the entries returned by our search.
    l_entry := DBMS_LDAP.first_entry(ld  => l_session,
                                     msg => l_message);

    << entry_loop >>
    WHILE l_entry IS NOT NULL LOOP
      -- Get all the attributes for this entry.
      DBMS_OUTPUT.PUT_LINE('---------------------------------------');
      l_attr_name := DBMS_LDAP.first_attribute(ld        => l_session,
                                               ldapentry => l_entry,
                                               ber_elem  => l_ber_element);
      << attributes_loop >>
      WHILE l_attr_name IS NOT NULL LOOP
        -- Get all the values for this attribute.
        l_vals := DBMS_LDAP.get_values (ld        => l_session,
                                        ldapentry => l_entry,
                                        attr      => l_attr_name);
        << values_loop >>
        FOR i IN l_vals.FIRST .. l_vals.LAST LOOP
          DBMS_OUTPUT.PUT_LINE('ATTIBUTE_NAME: ' || l_attr_name || ' = ' || SUBSTR(l_vals(i),1,200));
        END LOOP values_loop;
        l_attr_name := DBMS_LDAP.next_attribute(ld        => l_session,
                                                ldapentry => l_entry,
                                                ber_elem  => l_ber_element);
      END LOOP attibutes_loop;
      l_entry := DBMS_LDAP.next_entry(ld  => l_session,
                                      msg => l_entry);
    END LOOP entry_loop;
  END IF;

  -- Disconnect from the LDAP server.
  l_retval := DBMS_LDAP.unbind_s(ld => l_session);
  DBMS_OUTPUT.PUT_LINE('L_RETVAL: ' || l_retval);
END;
/