How to get LDAP groups name using Oracle dbms_ldap package?

Question

I used this example and successfully connected to LDAP server - http://www.oracle-base.com/articles/9i/ldap-from-plsql-9i.php.

SET SERVEROUTPUT ON SIZE 1000000
DECLARE
  -- Adjust as necessary.
  l_ldap_host    VARCHAR2(256) := 'server01.tshcomputing.com';
  l_ldap_port    VARCHAR2(256) := '389';
  l_ldap_user    VARCHAR2(256) := 'cn=orcladmin';
  l_ldap_passwd  VARCHAR2(256) := 'password';
  l_ldap_base    VARCHAR2(256) := 'cn=Users,dc=tshcomputing,dc=com';

  l_retval       PLS_INTEGER; 
  l_session      DBMS_LDAP.session;
  l_attrs        DBMS_LDAP.string_collection;
  l_message      DBMS_LDAP.message;
  l_entry        DBMS_LDAP.message;
  l_attr_name    VARCHAR2(256);
  l_ber_element  DBMS_LDAP.ber_element;
  l_vals         DBMS_LDAP.string_collection;

BEGIN
  -- Choose to raise exceptions.
  DBMS_LDAP.USE_EXCEPTION := TRUE;

  -- Connect to the LDAP server.
  l_session := DBMS_LDAP.init(hostname => l_ldap_host,
                              portnum  => l_ldap_port);

  l_retval := DBMS_LDAP.simple_bind_s(ld     => l_session,
                                      dn     => l_ldap_user,
                                      passwd => l_ldap_passwd);

  -- Get all attributes
  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);

  IF DBMS_LDAP.count_entries(ld => l_session, msg => l_message) > 0 THEN
    -- Get all the entries returned by our search.
    l_entry := DBMS_LDAP.first_entry(ld  => l_session,
                                     msg => l_message);

    << entry_loop >>
    WHILE l_entry IS NOT NULL LOOP
      -- Get all the attributes for this entry.
      DBMS_OUTPUT.PUT_LINE('---------------------------------------');
      l_attr_name := DBMS_LDAP.first_attribute(ld        => l_session,
                                               ldapentry => l_entry,
                                               ber_elem  => l_ber_element);
      << attributes_loop >>
      WHILE l_attr_name IS NOT NULL LOOP
        -- Get all the values for this attribute.
        l_vals := DBMS_LDAP.get_values (ld        => l_session,
                                        ldapentry => l_entry,
                                        attr      => l_attr_name);
        << values_loop >>
        FOR i IN l_vals.FIRST .. l_vals.LAST LOOP
          DBMS_OUTPUT.PUT_LINE('ATTIBUTE_NAME: ' || l_attr_name || ' = ' || SUBSTR(l_vals(i),1,200));
        END LOOP values_loop;
        l_attr_name := DBMS_LDAP.next_attribute(ld        => l_session,
                                                ldapentry => l_entry,
                                                ber_elem  => l_ber_element);
      END LOOP attibutes_loop;
      l_entry := DBMS_LDAP.next_entry(ld  => l_session,
                                      msg => l_entry);
    END LOOP entry_loop;
  END IF;

  -- Disconnect from the LDAP server.
  l_retval := DBMS_LDAP.unbind_s(ld => l_session);
  DBMS_OUTPUT.PUT_LINE('L_RETVAL: ' || l_retval);
END;
/

I got this this result:

Actually, the user have 3 groups, but one group is Primary and stored in primaryGroupID. I tried to do queries for groups, but I couldn't find attribute like ID. How can I get groups info (like a value of memberOf attribute) by primaryGroupID?

Answer 1

As annoying as it may be, this is necessarily a two-part process. memberOf (and LDAP multip-valued attribute) stores all the group memberships except the primaryGroup membership, which is stored in a completely different way, as you have discovered. The key is the "primaryGroupToken" attribute of group objects, which correlates to the primaryGroupID of users.

I was doing the reverse, so as a service to myself, made my own function that will give the primaryGroup ID value/"token":

   --Special from of group membership not appearing in the memberof attribute.
   --Function accepts (flexibly) a SID or a group name and return the token that  
   --would be stored in the attribute "primaryGroupID" of a user object.
FUNCTION get_primaryGroupToken(p_sid_samid IN VARCHAR2) RETURN VARCHAR2 IS
  l_retval PLS_INTEGER;
  l_attrs dbms_ldap.string_collection;
  l_message dbms_ldap.message;
  l_entry dbms_ldap.message;
  l_attr_name VARCHAR2(256);
  l_ber_element dbms_ldap.ber_element;
  l_vals dbms_ldap.string_collection;
  l_primaryGroupToken VARCHAR2(256) := NULL;
  l_filter            VARCHAR2(256);
BEGIN
  IF SUBSTR( p_sid_samid, 2, 1 ) = '-' THEN
    dbms_output.put_line('group spec Is sid');
    l_filter := '(objectSid=' || p_sid_samid || ')';
  ELSE
    dbms_output.put_line('group spec Is samid');
    -- You could probably also use CN here instead of sAMAccountName
    l_filter := '(&(sAMAccountName=' || p_sid_samid || ')(objectClass=group))';
  END IF;
  l_retval   := get_ldap_session();
  l_attrs(1) := 'primaryGroupToken';
  l_retval   := DBMS_LDAP.search_s(ld => g_session, 
                    base => g_ldap_auth_base, 
                    scope => DBMS_LDAP.SCOPE_SUBTREE, 
                    filter => l_filter, 
                    attrs => l_attrs, 
                    attronly => 0, 
                    res => l_message);
  IF DBMS_LDAP.count_entries(ld => g_session, msg => l_message) > 0 THEN
    --Get all the entries returned by our search.
    l_entry := DBMS_LDAP.first_entry(ld => g_session,msg => l_message);
    <<entry_loop>>
    WHILE l_entry IS NOT NULL
    LOOP
      -- Get all the attributes for this entry.
      l_attr_name           := DBMS_LDAP.first_attribute(ld => g_session,ldapentry => l_entry, ber_elem => l_ber_element);
      IF lower(l_attr_name) <> 'primarygrouptoken' THEN
        DBMS_OUTPUT.PUT_LINE('ATTIBUTE_NAME unexpected : ' || l_attr_name );
      ELSE
        l_vals := DBMS_LDAP.get_values (ld => g_session, ldapentry => l_entry, attr => l_attr_name);
      END IF;
      << values_loop >>
      FOR i IN l_vals.FIRST .. l_vals.LAST
      LOOP
        l_primaryGroupToken := l_vals(i);
      END LOOP values_loop;
      IF l_primaryGroupToken IS NULL THEN
        l_attr_name          := DBMS_LDAP.next_attribute(ld => g_session, ldapentry => l_entry, ber_elem => l_ber_element);
        l_entry              := DBMS_LDAP.next_entry(ld => g_session,msg => l_entry);
      ELSE
        EXIT;
      END IF;
    END LOOP entry_loop;
  END IF;
  -- Disconnect from the LDAP server.
  l_retval := DBMS_LDAP.unbind_s(ld => g_session);
  RETURN l_primaryGroupToken;
END get_primaryGroupToken; 

Note that this function is in a package, and the "g_" variables are package globals. And of course eliminate the development only dbms_output calls, etc.

Then I just did two filter searches, one on primaryGroupID and one on memberOf to get all members of the group.

In your case, just kinda to the reverse, have your own function that first evaluates all the memberOf values, then separately treat the primaryGroupToken with an LDAP search for the group with that primaryGroupToken.