How to perform large (greater than Sizelimit) LDAP queries with Oracle DBMS_LDAP

Question

I have a PL/SQL package in an 11g database that looks up various attributes and deals with group memberships and so forth. One function in particular is a pipelined function that returns all the members of a group specified in the a function parameter. It works great for groups with less than 1000 members. In that case, I get this error message:

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

I understand that LDAP (and MS Active Directory in particular, which is what I am dealing with) has a query result set size limit of 1000. If an LDAP search results in more than this many entries, the query just fails and returns no results. I do not have the option of modifying the AD schema or anything like that. I know that there is a concept of "paged" result sets in LDAP, but I don't see mention of that functionality in the DBMS_LDAP documentation that I have reviewed.

http://docs.oracle.com/cd/B10501_01/network.920/a96577/smplcode.htm

I would greatly appreciate any advice, guidance, or documentation URLs for solutions or workarounds.

If "query slicing" is the best way, then note that starting with a query for all the top level OUs, and using them with a logical AND in the search filters might work for some situations, but there are no guarantees that there would not be 1000 or more user objects in one OU and are also members of a target security group. So it might have to be a little cleverer than just OU limiting.

Answer 1

After finding a few unofficial peer support responses in forums, I believe that as of 2015, the DBMS_LDAP package as shipped with DB 11g does not implement the standard "paged search results control" (https://www.rfc-editor.org/rfc/rfc2696) .

The (still limited) approach that I took to work around this was to make a nested for-loop that steps through each letter of the alphabet, creating a common name filtering phrase. Combine this with the fact that LDAP group membership can be expressed either via the memberof multi-valued attribute or by the primaryGroupID attribute, and you have 52 separate filtered queries! It works, not too speedily, but it works.

  DECLARE
      l_retval      PLS_INTEGER;
      l_alphabet    varchar2(26) := 'ABCDEFGHIJKLMNOPQRSTUVWXYZ';
      l_slice_prefix varchar2(16);
      l_slice_suffix varchar2(16);
      l_filter     dbms_ldap.string_collection;
      l_slice_filter varchar2(1000);
      l_primaryGroupToken varchar2(128);
   BEGIN     
      l_primaryGroupToken := get_primaryGroupToken(p_group);
            
      l_retval := get_ldap_session();
           
      --LDAP idiosyncracy: primaryGroup is NOT listed in member of. 
      --So you really need two distinct queries and "union" them together.     
            
      l_filter(0) := '(&(objectCategory=person)(objectClass=user)(primaryGroupID='|| l_primaryGroupToken ||')(!(description=Built-in*)))';
      -- lfilter(1) must be populated with output of get_group_dn...
      l_filter(1) := '(&(objectCategory=person)(objectClass=user)(memberof='|| get_group_dn(p_group) ||'))';
   
        --ORA-31202: DBMS_LDAP: LDAP client/server error: Sizelimit exceeded
        --https://msdn.microsoft.com/en-us/library/ms180880%28v=vs.80%29.aspx
        --So shame on MS for the small limit, and shame on Oracle 
        --for not implementing paging.
        --Now we have to implement our own slicing/paging. 
        --In this case we'll just do first char of the CN
        --against each letter of the alphabet. So 26 "non-uniform" pages.
   
      <<filter_loop>> 
      for iq in l_filter.FIRST..l_filter.LAST LOOP
          <<slice_loop>> --see above explanation for sizelimits, this is in lieu of real LDAP control paging.
          FOR alphaindex in 1..26 LOOP 
              l_slice_prefix := '(&(CN='||substr(l_alphabet,alphaindex,1)||'*)';
              l_slice_suffix := ')';
              l_slice_filter := l_slice_prefix || l_filter(iq) || l_slice_suffix;
              l_retval := dbms_ldap.search_s(ld       => g_session,
                                             base     => g_ldap_auth_base,
                                             scope    => dbms_ldap.scope_subtree,
                                             filter   => l_slice_prefix || l_filter(iq) || l_slice_suffix,
                                             attrs    => l_attrs,
                                             attronly => 0,
                                             res      => l_message);
              if L_retval = DBMS_LDAP.SUCCESS then

              -- ...

              END IF;
           END LOOP slice_loop;
        END LOOP filter_loop;
   END;