Get LDAP user list using PLSQL

One of the new requirements for our database application is to synchronize the contents of the us开发者_如何学Pythoner 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?

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

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

Active Directory has about 4 naming attributes.

• sAMAccountName (aka Pre-Windows2000 name) is a 20 or so character short name that must be unique within each domain.
• userPrinicipalName, usually sAMAccountName@domain.name, but it turns out AD will honour almost any string. (I know this experimentally as we once accidentally reset 2000 out of 6000 such values in a running AD domain.
• displayName, that which shows up in ADUC (dsa.msc, Active Directory Users and Computers)
• The CN= part of the DN. Using ADUC, the CN is usually the Display Name. However it too can be anything legal in an LDAP name.

So which 'name' are you looking for? Basically query for any of those attributes in the list and see what you get.

As for seeing other objects, yes, you would need an account with sufficient rights to see those attributes for users.