Get LDAP user list using PLSQL

Question

One of the new requirements for our database application is to synchronize the contents of the user table with the users in Active Directory. So basically I need to connect to the Active Directory server and retrieve a list of user names, from within a plsql procedure.

What I have achieved so far is connect to the active directory server, using my own credentials, and query some attributes.

Example:

ldap_password := '****';
ldap_user     := 'cn=me,OU=Users,OU=mygroup,DC=mytown,DC=mycompany,DC=com';
ldap_base     := 'OU=Users,OU=mygroup,DC=mytown,DC=mycompany,DC=com';
search_filter := '(&(objectClass=Person)!((sn=him)(cn=me)))';
res_attrs(1)  := 'displayName';
res_attrs(2)  := 'cn';
res_attrs(3)  := 'telephoneNumber';

It seems I can only query my own attributes or somebody else's if I already know who that someone else is.

• How do I get a list of usernames?
• Is this possible using any account or does this require an account with the proper privileges?

Answer 1

I got my script working. The scope setting prevented me from seeing all data. DBMS_LDAP.SCOPE_SUBTREE

Answer 2

Rene, You can do all searched in Active directory via Oracle's LDAP components that it seems you have already touched upon. While I am no expert on LDAP/AD, I believe that you may need rights to perform these actions or better yet get an ID/Password created that has the rights (this way you can keep your id/psw out of the system and allow either an unexpiring pswrd or pswrd that is supported by the AD administrators. I know that I have always had full query access to AD, not sure if that is how I am set up or out-of-the-box functionality.

But look @ this site http://www.oracle-base.com/articles/9i/LDAPFromPLSQL9i.php

as the article demonstrates, I would recommend paring back your searchFilter (get more then whittle it down until it suits your needs)

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);