How to use DBMS_LDAP (part 3: Searching)

Table of Contents

1. Introduction
2. Establishing a Connection
3. Searching Hierarchical Data (this page)
4. Browsing Attributes
5. Timeouts
6. Modifying Data

Searching Hierarchical Data

Once you’ve established a connection, the next thing you’ll probably want to do is start searching for records. Either to pull the data back to a client, or to modify one or more records or simply to check for existence of some records. Such as authorization checks based on a user’s membership in one or more groups; or searching for users with particular attributes.

One of the important things to remember about LDAP searches is, unlike the flat nature of a SQL WHERE-clause against a table, the data is hierarchical. That is, you must specify a depth scope when looking at data.

So, using the sample hierarchy from the introduction:

DC=NET
    DC=Example
        OU=People
            OU=Managers
                uid=1234
                    CN=Jane Doe
                    hiredate=2005-04-03
                    email=jane.doe@example.net
                    department=Acquisitions
                    objectClass=person
                uid=3456
                    CN=John Doe
                    hiredate=2006-05-04
                    email=john.doe@example.net
                    department=Sales
                    objectClass=person
            OU=Consultants
                uid=1987
                    CN=Mickey Mouse
                    hiredate=2005-12-11
                    email=mickey.mouse@example.net
                    department=Acquisitions
                    objectClass=person
                uid=6543
                    CN=Donald Duck
                    hiredate=2008-07-06
                    email=donald.duck@example.net
                    department=Sales
                    objectClass=person

• We could search for the existence of a particular record directly.

  uid=1987,ou=consultants,ou=people,dc=example,dc=net

• We could search one level, perhaps getting a list of all consultants.

  ou=consultants,ou=people,dc=example,dc=net

• We can search an entire subtree, for example, all people regardless of group.

  ou=people,dc=example,dc=net

Within DBMS_LDAP the three search scopes are defined by constants:

SCOPE_BASE     CONSTANT NUMBER := 0;
SCOPE_ONELEVEL CONSTANT NUMBER := 1;
SCOPE_SUBTREE  CONSTANT NUMBER := 2;

While some LDAP servers and clients support a 4th type subordinate sub-tree searching; DBMS_LDAP does not provide such a mechanism. You could mimic the functionality (but not the efficiency) with a sub-tree search combined with an appropriate filter to eliminate the top level group from the results.

In addition to the search scope, you must also specify what will be returned from a search that matches the criteria. There are three levels of detail in the results:

• Entry Distinguished Names only
• Entry Distinguished Names, along with names of Attributes for each entry
• Entry Distinguished Names, along with the names and values of Attributes for each entry

Attributes are specified by a string collection regardless of the level of detail. You can provide a string collection populated with the names of the attributes to be returned for each entry. Alternately, define the collection to have only one entry with a wildcard “*” to get all attributes, or provide a collection of one entry consisting of the magic string “1.1”, meaning return no attributes at all.
Within this article, all searches will use “1.1”, hence excluding attributes from the results. Including the attributes and browsing their names and values will be explored in the next chapter of this series.

Searching with BASE scope, no attributes

The simplest search is just a BASE scope, meaning you will provide the entire DN as a location. If you include no attributes, then this type of search is mosty just a an existence check. If you can find the record it exists, if you can’t, it doesn’t.
In the example below we’ll search the public Debian LDAP server for a specific host name. To use a BASE scope, we use the constant DBMS_LDAP.SCOPE_BASE. We’ll return no attributes, so our search list is “1.1”. You must specify a filter on the search, in this case we’ll leave it open to any record that matches the specified DN. Every record has an objectclass attribute, so our filter is a wildcard on that attribute: “objectclass=*”. The attronly parameter doesn’t apply since we aren’t returning any attributes so just leave it NULL.

In these examples, if we find a match we’ll display the DN as a single string and also break the DN into its constituent levels within the hierarchy.

If we don’t find a matching entry then the search_s function will raise an exception.

DECLARE
    v_result               PLS_INTEGER;
    v_session              DBMS_LDAP.session;
    v_search_attrs         DBMS_LDAP.string_collection;
    v_search_results       DBMS_LDAP.MESSAGE;
    v_entry                DBMS_LDAP.MESSAGE;
    v_distinguished_name   VARCHAR2(256);
    v_dn_pieces            DBMS_LDAP.string_collection;
BEGIN
    DBMS_LDAP.use_exception := TRUE;

    v_session := DBMS_LDAP.init(hostname => 'db.debian.org', portnum => DBMS_LDAP.port);

    v_search_attrs(1) := '1.1';

    v_result :=
        DBMS_LDAP.search_s(ld         => v_session,
                           base       => 'host=beach,ou=hosts,dc=debian,dc=org',
                           scope      => DBMS_LDAP.scope_base,
                           filter     => 'objectclass=*',
                           attrs      => v_search_attrs,
                           attronly   => NULL,
                           res        => v_search_results);

    v_entry := DBMS_LDAP.first_entry(v_session, v_search_results);

    v_distinguished_name := DBMS_LDAP.get_dn(v_session, v_entry);
    DBMS_OUTPUT.put_line('DN: ' || v_distinguished_name);
    v_dn_pieces := DBMS_LDAP.explode_dn(dn => v_distinguished_name, notypes => 0);

    FOR i IN v_dn_pieces.FIRST .. v_dn_pieces.LAST
    LOOP
        DBMS_OUTPUT.put_line(i || ': ' || v_dn_pieces(i));
    END LOOP;

    v_result := DBMS_LDAP.msgfree(v_entry);
    v_result := DBMS_LDAP.unbind_s(v_session);
END;
/

DN: host=beach,ou=hosts,dc=debian,dc=org
0: host=beach
1: ou=hosts
2: dc=debian
3: dc=org

Instead of raising an exception we could disable exceptions for the search and then check the result value of the search_s function. You can test this by changing “beach” in the sample to any host name not in their server, like “xxxxxxx”.

DECLARE
    v_result               PLS_INTEGER;
    v_session              DBMS_LDAP.session;
    v_search_attrs         DBMS_LDAP.string_collection;
    v_search_results       DBMS_LDAP.MESSAGE;
    v_entry                DBMS_LDAP.MESSAGE;
    v_distinguished_name   VARCHAR2(256);
    v_dn_pieces            DBMS_LDAP.string_collection;
BEGIN
    DBMS_LDAP.use_exception := TRUE;

    v_session := DBMS_LDAP.init(hostname => 'db.debian.org', portnum => DBMS_LDAP.port);

    v_search_attrs(1) := '1.1';

    DBMS_LDAP.use_exception := FALSE;
    v_result :=
        DBMS_LDAP.search_s(ld         => v_session,
                           base       => 'host=xxxxxxx,ou=hosts,dc=debian,dc=org',
                           scope      => DBMS_LDAP.scope_base,
                           filter     => 'objectclass=*',
                           attrs      => v_search_attrs,
                           attronly   => NULL,
                           res        => v_search_results);

    IF v_result = DBMS_LDAP.no_such_object
    THEN
        DBMS_OUTPUT.put_line('Base DN not found');
    ELSE
        DBMS_LDAP.use_exception := TRUE;
        v_entry := DBMS_LDAP.first_entry(v_session, v_search_results);

        v_distinguished_name := DBMS_LDAP.get_dn(v_session, v_entry);
        DBMS_OUTPUT.put_line('DN: ' || v_distinguished_name);
        v_dn_pieces := DBMS_LDAP.explode_dn(dn => v_distinguished_name, notypes => 0);

        FOR i IN v_dn_pieces.FIRST .. v_dn_pieces.LAST
        LOOP
            DBMS_OUTPUT.put_line(i || ': ' || v_dn_pieces(i));
        END LOOP;

        v_result := DBMS_LDAP.msgfree(v_entry);
    END IF;

    v_result := DBMS_LDAP.unbind_s(v_session);
END;
/

Base DN not found

For the remainder of the examples I will simply raise exceptions on a failed search.

Searching with scope of One Sub-Level, no attributes

Instead of searching for a record at a specific DN, you could instead search for any entries within one level of the directory matching your search criteria. Here we’ll again search within the hosts level, but we’ll allow all records under hosts. As the name implies, the constant to do one-level searching is DBMS_LDAP.SCOPE_ONELEVEL.

DECLARE
    v_result               PLS_INTEGER;
    v_session              DBMS_LDAP.session;
    v_search_attrs         DBMS_LDAP.string_collection;
    v_search_results       DBMS_LDAP.MESSAGE;
    v_entry                DBMS_LDAP.MESSAGE;
    v_distinguished_name   VARCHAR2(256);
BEGIN
    DBMS_LDAP.use_exception := TRUE;

    v_session := DBMS_LDAP.init(hostname => 'db.debian.org', portnum => DBMS_LDAP.port);

    v_search_attrs(1) := '1.1';

    v_result :=
        DBMS_LDAP.search_s(ld         => v_session,
                           base       => 'ou=hosts,dc=debian,dc=org',
                           scope      => DBMS_LDAP.scope_onelevel,
                           filter     => 'objectclass=*',
                           attrs      => v_search_attrs,
                           attronly   => 1,
                           res        => v_search_results);

    v_result := DBMS_LDAP.count_entries(v_session, v_search_results);

    DBMS_OUTPUT.put_line('Entry count: ' || v_result);

    v_entry := DBMS_LDAP.first_entry(v_session, v_search_results);

    WHILE v_entry IS NOT NULL
    LOOP
        v_distinguished_name := DBMS_LDAP.get_dn(v_session, v_entry);
        DBMS_OUTPUT.put_line('DN: ' || v_distinguished_name);
       
        v_entry := DBMS_LDAP.next_entry(v_session, v_entry);
    END LOOP;

    v_result := DBMS_LDAP.unbind_s(v_session);
END;
/

Entry count: 192
DN: host=klecker,ou=hosts,dc=debian,dc=org
DN: host=lobos,ou=hosts,dc=debian,dc=org
DN: host=wieck,ou=hosts,dc=debian,dc=org
DN: host=schumann,ou=hosts,dc=debian,dc=org
DN: host=handel,ou=hosts,dc=debian,dc=org
DN: host=geo1,ou=hosts,dc=debian,dc=org
DN: host=geo2,ou=hosts,dc=debian,dc=org
DN: host=draghi,ou=hosts,dc=debian,dc=org
DN: host=kaufmann,ou=hosts,dc=debian,dc=org
DN: host=wolkenstein,ou=hosts,dc=debian,dc=org
DN: host=sibelius,ou=hosts,dc=debian,dc=org
DN: host=tchaikovsky,ou=hosts,dc=debian,dc=org
DN: host=pettersson,ou=hosts,dc=debian,dc=org
DN: host=lully,ou=hosts,dc=debian,dc=org
DN: host=abel,ou=hosts,dc=debian,dc=org
DN: host=arnold,ou=hosts,dc=debian,dc=org
DN: host=antheil,ou=hosts,dc=debian,dc=org
...

Searching with scope of entire Sub-Tree, no attributes

And finally the last scope to explore is the Sub-Tree scope which will search for records at any and all depths within a directory tree below the level of the specified base DN. Here again the package includes a constant: DBMS_LDAP.SCOPE_SUBTREE. Since we’re searching the entire tree the results could get large, especially if our filter allows every object class to be returned.

So, in this example, we’ll restrict the filter to object classes of type “debiangroup”. We’ll start the search at the top of the tree looking for everything under “dc=debian,dc=org”.

DECLARE
    v_result               PLS_INTEGER;
    v_session              DBMS_LDAP.session;
    v_search_attrs         DBMS_LDAP.string_collection;
    v_search_results       DBMS_LDAP.MESSAGE;
    v_entry                DBMS_LDAP.MESSAGE;
    v_distinguished_name   VARCHAR2(256);
BEGIN
    DBMS_LDAP.use_exception := TRUE;

    v_session := DBMS_LDAP.init(hostname => 'db.debian.org', portnum => DBMS_LDAP.port);

    v_search_attrs(1) := '1.1';

    v_result :=
        DBMS_LDAP.search_s(ld         => v_session,
                           base       => 'dc=debian,dc=org',
                           scope      => DBMS_LDAP.scope_subtree,
                           filter     => 'objectclass=debiangroup',
                           attrs      => v_search_attrs,
                           attronly   => 1,
                           res        => v_search_results);

    v_result := DBMS_LDAP.count_entries(v_session, v_search_results);

    DBMS_OUTPUT.put_line('Entry count: ' || v_result);

    v_entry := DBMS_LDAP.first_entry(v_session, v_search_results);

    WHILE v_entry IS NOT NULL
    LOOP
        v_distinguished_name := DBMS_LDAP.get_dn(v_session, v_entry);
        DBMS_OUTPUT.put_line('DN: ' || v_distinguished_name);
       
        v_entry := DBMS_LDAP.next_entry(v_session, v_entry);
    END LOOP;

    v_result := DBMS_LDAP.unbind_s(v_session);
END;
/

Entry count: 2172
DN: gid=nm,ou=users,dc=debian,dc=org
DN: gid=qa,ou=users,dc=debian,dc=org
DN: gid=adm,ou=users,dc=debian,dc=org
DN: gid=wnpp,ou=users,dc=debian,dc=org
DN: gid=list,ou=users,dc=debian,dc=org
DN: gid=dput_OBS,ou=users,dc=debian,dc=org
DN: gid=visi,ou=users,dc=debian,dc=org
DN: gid=above,ou=users,dc=debian,dc=org
DN: gid=udcvs,ou=users,dc=debian,dc=org
DN: gid=guest,ou=users,dc=debian,dc=org
DN: gid=debwww,ou=users,dc=debian,dc=org
DN: gid=doccvs,ou=users,dc=debian,dc=org
DN: gid=globus,ou=users,dc=debian,dc=org
DN: gid=debcvs,ou=users,dc=debian,dc=org
DN: gid=popcon,ou=users,dc=debian,dc=org
DN: gid=webwml,ou=users,dc=debian,dc=org
DN: gid=wb-ppc,ou=users,dc=debian,dc=org
DN: gid=buildd,ou=users,dc=debian,dc=org
DN: gid=aptcvs,ou=users,dc=debian,dc=org
DN: gid=Debian,ou=users,dc=debian,dc=org
DN: gid=wb-arm,ou=users,dc=debian,dc=org
...

Searching with Filters

The Search API requires a filter, so each of the examples above includes one but all were fairly simple. Either the generic all filter of ‘objectclass=*’
or a specific object class ‘objectclass=”debian group”‘. The LDAP filter syntax is much richer though. The parameter is simply a VARCHAR2, and the filter string is passed “as is” to the server, so the supported functionality is determined by the server and not the DBMS_LDAP package.

A basic LDAP filter takes the form of:

(atttribute_name operator value)

• The attribute name is the name of any attribute that might be found within an entry. It need not be an attribute that exists in every entry, as many(most) will not.
• The operator could be =, >=, or <=. Inequality comparisons can be a little tricky. From the client side, all of the data appears as text; but within the LDAP server the data is managed by a schema with types and defined comparison rules. Those rules may use case-sensitive text comparison, or case-insensitive. Numerals may be compared as numeric or text data depending on the schema's definition for that attribute. Some data may not have ordering rules making less-than or greater-than comparisons meaningless. An attribute classifying an entry as "animal", "vegetable", or "mineral" for example would not necessarily have an ordering criteria, only an equality or inequality comparison.
• The filter value could be a string like the “debian group” example earlier, or a wild card like “*”, or a combination of string and wildcard such as “abc*” to find all entries with an attribute beginning with “abc”.
• Technically, a filter should be wrapped in parentheses; but if you have a single filter condition with no modifiers, then DBMS_LDAP will accept it as is.

The basic filters can then be modified by AND, OR, or NEGATION operators.

• The AND operator “&” applies the intersection of conditions that follow.
  (&(a=1)(b=2)) – Find records where a=1 and b=2, i.e. (a,b) = (1,2)
  (&(x=10)(y=100)(z=1000)) – Find records where x=10 and y=100 and z=1000, i.e. (x,y,z) = (10,100,1000)
  As a matter of efficiency, it is usually better to have an AND condition with many sub-filters than a more extensive filter using many AND conditions with fewer filters in each.

  It is legal to have a filter consisting of only the AND operator: (&). This filter condition simply evaluates to TRUE for any entry.

• The OR operator “|” applies the union of the conditions that follow.
  (|(a=1)(b=2)) – Find any records where a=1 or b=2
  (|(x=10)(y=100)(z=1000)) – Find records where x=10 or y=100 or z=1000
  Similar to AND operators, it is usually better to have an OR condition with many sub-filters than to use many OR conditions across fewer sub-filters.

  It is legal to have a filter consisting of only the OR operator: (|). This filter condition simply evaluates to FALSE for any entry.

• The NEGATION operator “!” simply negates whatever condition follows it.
  (!(objectClass=server)) – Find all records that do not have a server object class
  Unlike the AND and OR operators, the NEGATION operator can not operate on a list of conditions, only a single filter condition can be negated with a single NEGATION operator.

And finally, you can nest filter conditions within one another, thus allowing more complicated conditions such as: Find all Managers named Jane Doe or Consultants named Mickey Mouse.

(|(&(OU=Managers)(CN=Jane Doe))(&(OU=Consultants)(CN=Mickey Mouse)))

With some formatting, the logic is a little easier to follow.

(|
 (&(OU=Managers)(CN=Jane Doe))
 (&(OU=Consultants)(CN=Mickey Mouse))
)

Or, we could find John Doe and Donald Duck by negating the previous search.

(!
   (|
      (&(OU=Managers)(CN=Jane Doe))
      (&(OU=Consultants)(CN=Mickey Mouse))
   )
)

Data size limits and result set pagination

There is one additional filtering criteria you may run into with large directories. That is a size limit on returned results. LDAP servers may be willing to pump an unlimited amount of data back to a client, or may have the limits set high enough that your searches never exceed their thresholds; but eventually you will likely come across a result set too large to be returned. When that happens you will get an exception:

ORA-31202: DBMS_LDAP: LDAP client/server error: Sizelimit exceeded

or, if you have exceptions turned off, then search_s will return a result code of DBMS_LDAP.SIZELIMIT_EXCEEDED.

While many LDAP servers support paginated results to handle this situation, DBMS_LDAP, unfortunately, does not have a means of invoking such functionality.

If you encounter this the only way to get around it from the client side is to construct a series of filters that will span the entire data set.

In the example below I use the following filter:

filter     => '&(status=Active)(uid=ab*)'

Simply searching for all accounts failed. Searching for all Active accounts still failed. Searching for all Active accounts with a uid starting with an “a” still failed. When I got to the point of limiting the uid to those starting with “ab” for just Active accounts I finally got small enough results to pass the server’s size limit. I could then iterate across aa-zz combinations for both Active and not Active accounts to pull all of the data. Of course I might miss ids with leading numeric characters; but hopefully, if you had to do manual pagination you would work with the server’s admins to determine what the best spanning ranges would be.

DECLARE
    v_result               PLS_INTEGER;
    v_session              DBMS_LDAP.session;
    v_search_attrs         DBMS_LDAP.string_collection;
    v_search_results       DBMS_LDAP.MESSAGE;
    v_entry                DBMS_LDAP.MESSAGE;
    v_distinguished_name   VARCHAR2(256);
    v_dn_pieces            DBMS_LDAP.string_collection;
BEGIN
    DBMS_LDAP.use_exception := TRUE;

    v_session := DBMS_LDAP.init(hostname => 'ldap.andrew.cmu.edu', portnum => DBMS_LDAP.port);

    v_search_attrs(1) := '1.1';
    DBMS_LDAP.use_exception := FALSE;
    v_result :=
        DBMS_LDAP.search_s(ld         => v_session,
                           base       => 'ou=account,dc=cmu,dc=edu',
                           scope      => DBMS_LDAP.scope_onelevel,
                           filter     => '&(status=Active)(uid=ab*)',
                           attrs      => v_search_attrs,
                           attronly   => 1,
                           res        => v_search_results);

    IF v_result != DBMS_LDAP.success
    THEN
        DBMS_OUTPUT.put_line('Search failed with code: ' || v_result || ' ' || DBMS_LDAP.err2string(v_result));
    ELSE
        DBMS_LDAP.use_exception := FALSE;
        v_result := DBMS_LDAP.count_entries(v_session, v_search_results);

        DBMS_OUTPUT.put_line('Entry count: ' || v_result);

        v_entry := DBMS_LDAP.first_entry(v_session, v_search_results);

        WHILE v_entry IS NOT NULL
        LOOP
            v_distinguished_name := DBMS_LDAP.get_dn(v_session, v_entry);
            DBMS_OUTPUT.put_line('DN: ' || v_distinguished_name);

            v_entry := DBMS_LDAP.next_entry(v_session, v_entry);
        END LOOP;
    END IF;

    v_result := DBMS_LDAP.unbind_s(v_session);
END;
/
Entry count: 71
DN: uid=ABHAT,ou=account,dc=cmu,dc=edu
DN: uid=ABRUNK,ou=account,dc=cmu,dc=edu
DN: uid=ABIHAUS,ou=account,dc=cmu,dc=edu
DN: uid=ABUSE,ou=account,dc=cmu,dc=edu
DN: uid=ABOY,ou=account,dc=cmu,dc=edu
DN: uid=ABERENDEYEV,ou=account,dc=cmu,dc=edu
DN: uid=abenavides,ou=account,dc=cmu,dc=edu
DN: uid=abelsaj,ou=account,dc=cmu,dc=edu
DN: uid=abhinav,ou=account,dc=cmu,dc=edu
DN: uid=abeer,ou=account,dc=cmu,dc=edu
DN: uid=abhishekveldurthy,ou=account,dc=cmu,dc=edu
DN: uid=ab,ou=account,dc=cmu,dc=edu
DN: uid=abhay,ou=account,dc=cmu,dc=edu
DN: uid=abinajay,ou=account,dc=cmu,dc=edu
DN: uid=abhayjoseph,ou=account,dc=cmu,dc=edu
DN: uid=abhishekjain,ou=account,dc=cmu,dc=edu
DN: uid=abreis,ou=account,dc=cmu,dc=edu
DN: uid=abhinavas,ou=account,dc=cmu,dc=edu
...

That covers all of the basic of searching for entries within an LDAP hierarchy.
In the next section we’ll extend the search results to include attribute lists with and without their values.