Oracle PL/SQL: Using DBMS_LDAP To Query Active Directory

It's always useful to be able to retrieve details from Active Directory when working within an Oracle Database (I'll do a separate post later on how we use LDAP records to update our employee email addresses stored in Oracle).

Oracle have created a package called DBMS_LDAP, I have to say when I first found it I thought it was new but it has apparently existed in one form or another since Oracle 9i. It is fairly self explanatory to use, the main (useful) function is SEARCH_S which does the querying. The function takes the following parameters;

SYS.DBMS_LDAP.SEARCH_S Parameter List

As you can see calling this isn't going to be simple based on the complexity of the parameters (RAW, TABLE OF ...) but actually the DBMS_LDAP package has a number of helpful definitions that mean it's not quite so daunting as it may initially appear so here is a simple example;

DECLARE
  v_SearchUsername         VARCHAR2(40) := '@@';
  v_LDAPSession            DBMS_LDAP.SESSION;
  v_LDAPAttributeQueryList DBMS_LDAP.STRING_COLLECTION;
  v_LDAPQueryResults       DBMS_LDAP.MESSAGE;
  v_BerElement             DBMS_LDAP.BER_ELEMENT;
 
  v_FunctionReturnValue    PLS_INTEGER;
BEGIN
  :Result := '';
  v_LDAPSession := DBMS_LDAP.INIT('@LDAP server@', '@port@');
  v_FunctionReturnValue := DBMS_LDAP.SIMPLE_BIND_S(v_LDAPSession,
                                                   '@domain@\@user@',
                                                   '@password@');
  v_LDAPAttributeQueryList(1) := 'mail';
  v_FunctionReturnValue := DBMS_LDAP.SEARCH_S(
    ld       => v_LDAPSession,
    base     => '@base location@', -- "DC=xx,DC=yy=DC=zz"
    scope    => DBMS_LDAP.SCOPE_SUBTREE,
    filter   => 'samaccountname=' || v_SearchUsername,
    attrs    => v_LDAPAttributeQueryList,
    attronly => 0,
    res      => v_LDAPQueryResults);
  v_FunctionReturnValue := DBMS_LDAP.COUNT_ENTRIES(v_LDAPSession,
                                                   v_LDAPQueryResults);
  IF DBMS_LDAP.FIRST_ENTRY(v_LDAPSession, v_LDAPQueryResults) IS NOT NULL THEN
    :Result := DBMS_LDAP.GET_VALUES(
                 v_LDAPSession,
                 DBMS_LDAP.FIRST_ENTRY(v_LDAPSession, v_LDAPQueryResults),
                 DBMS_LDAP.FIRST_ATTRIBUTE(
                   v_LDAPSession,
                   DBMS_LDAP.FIRST_ENTRY(
                     v_LDAPSession,
                     v_LDAPQueryResults),
                   v_BerElement)) (0);
  END IF;
  v_FunctionReturnValue := DBMS_LDAP.UNBIND_S(v_LDAPSession);
END;

You need to do a little updating in order to make this work for your configuration (entering the server, a active directory user account with permission to do the query, your base location, etc) but on our system this runs in a tiny fraction of a second.

NOTE: This patch of code is only returning the first record returned. You will encounter problems if the user name exists in different domains, but that issue hasn't arisen for us and I guess most companies will probably be ok.

The v_berElement is required by the FIRST_ENTRY call in order to allow you to iterate through the results but as we're just interested in the first record returned it is declared, used, but never referenced again in the code above.

Final comment, if you are running 11g you are likely to get;

"ORA-24247: network access denied by access control list (ACL)"

When you attempt to run the command (unless your user has already been granted LDAP access). You need to update the access control list granting access to the connected user. The solution is readily available on Google (but I might create a post over the next few days as I need to do it myself!).