7 Replies Latest reply on Jul 6, 2011 4:39 AM by Billy~Verreynne

    Active Directory group membership

    partlycloudy
      Oracle 11.1

      Over the years, there have been various threads on the OTN forums and elsewhere about using Oracle-provided facilities like DBMS_LDAP to interact with LDAP-based directory services like Microsoft Active Directory. Most of the examples seem to work only with Oracle's own directory products, not AD.

      Is there a way to query Microsoft AD to read group membership given the domain/username? Can you point me to some sample code?

      [To clarify, this is NOT about using AD for authentication, just to get AD group membership for a given user]

      Thanks
        • 1. Re: Active Directory group membership
          Billy~Verreynne
          LDAP is LDAP. So DBMS_LDAP should work with any compliant LDAP server. So source code for using one LDAP server will not be different from using another.

          This part of the woods AD is used and we interface via DBMS_LDAP to it for authentication and looking up details such a telephone number and manager/supervisor.

          For example, why does DBMS_LDAP.search_s() not work for you?
          • 2. Re: Active Directory group membership
            unficyp
            I had the same problem a couple of month ago, i came up with this test code (oh yes.."borrowed" from many examples across the net :p)
            This works on 10.2.0.4 against my 2003 Server AD
                DECLARE  
                l_ldap_host     VARCHAR2(256) := 'xxx';
                l_ldap_port     VARCHAR2(256) := '389';
                l_ldap_user     VARCHAR2(256) := 'xxx';
                l_ldap_passwd   VARCHAR2(256) := 'xxx';
                l_ldap_base     VARCHAR2(256) := 'xxx';
                  
                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_vals    DBMS_LDAP.string_collection;  
              l_attr_name    VARCHAR2(256);
              l_ber_element  DBMS_LDAP.ber_element;
                      
                BEGIN  
                DBMS_LDAP.USE_EXCEPTION := TRUE;
                DBMS_LDAP.UTF8_CONVERSION := FALSE;
                --
                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
                            );                                      
                  
            -- search memberOF Attribute
                l_attrs(0) := 'memberOf';  
                l_retval := DBMS_LDAP.search_s  
                (  
                  ld       => l_session,   
                  base     => l_ldap_base,   
                  scope    => DBMS_LDAP.SCOPE_SUBTREE,  
                  filter   => '(cn=USERNAME)',
                  attrs    => l_attrs,  
                  attronly => 0,  
                  res      => l_message);    
                   
                l_retval := DBMS_LDAP.count_entries(ld => l_session, msg => l_message);
                
                IF l_retval > 0 THEN
                  l_entry := DBMS_LDAP.first_entry(ld  => l_session,
                                                 msg => l_message);
                << entry_loop >>
                WHILE l_entry IS NOT NULL LOOP
                  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
                    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;
            
                l_retval := DBMS_LDAP.unbind_s(ld => l_session);  
                END;  
                /  
            Hope this helps.
            I also tried to use DBMS_LDAP_UTL.get_group_membership but this always tells me that i'm not a member of any group...

            Edited by: user11165826 on 02.07.2011 02:55
            • 3. Re: Active Directory group membership
              partlycloudy
              Billy - That's right but the sample code I came across on OTN forums mentioned that the way Oracle Directory stores information/attributes is slightly different than AD so the code would work only with the former even though both are LDAP-compliant. I am a novice at this, would you mind posting the code you use to lookup the telephone number using search_s?

              user111.. - Thanks, I will try that. But note that I do not have the password for the user. This is in the context of an Oracle Apex application where the user is already authenticated using Apache mod_ntlm so all I have is the authenticated username and I want to get some supplemental information about this user from AD. How would this sample code work without the password; can I use the login/password of some other known/privileged account and use that to retrieve information about some other user?

              Thanks
              • 4. Re: Active Directory group membership
                unficyp
                This should be possible because LDAP allows Binds without a password.
                This is treated as an anonymous bind.
                Just make sure your AD allows anonymous binds / queries

                It's also possible to use another account to query other users informations.
                (i don't know which privileges this account must have)

                Edit:
                http://www.inside-oracle-apex.com/dbms_ldapsimple_bind_s-apex_ldapauthenticate-and-null-password/

                Edited by: user11165826 on 02.07.2011 05:55

                Edited by: user11165826 on 02.07.2011 06:03
                • 5. Re: Active Directory group membership
                  Billy~Verreynne
                  Here's a basic example:
                  SQL> declare
                    2          LDAP_SERVER constant varchar2(200) := 'domain.co.za';
                    3          LDAP_PORT constant number := 389;                    
                    4          LDAP_USER constant varchar2(200) := 'oraPLSQLuser';     
                    5          LDAP_PASSW constant varchar2(200) := 'tIgEr';      
                    6          LDAP_BASE constant varchar2(200) := 'DC=domain,DC=co,DC=za';
                    7                                                                      
                    8          rc              integer;                                    
                    9          ldapSession     DBMS_LDAP.session;                          
                   10          ntUser          varchar2(30);                               
                   11          attrName        varchar2(255);                              
                   12          attrList        DBMS_LDAP.string_collection;                
                   13          valList         DBMS_LDAP.string_collection;                
                   14          ldapMessage     DBMS_LDAP.message;                          
                   15          ldapEntry       DBMS_LDAP.message;                          
                   16          berElem         DBMS_LDAP.ber_element;                      
                   17                                                                      
                   18          --// very primitive assertion interface - should be catering
                   19          --// for unique error code and messages in a prod environment
                   20          procedure assert( condition boolean ) is                     
                   21          begin                                                        
                   22                  if not condition then                                
                   23                          raise_application_error(                     
                   24                                  -20001,                              
                   25                                  'LDAP call unsuccessful.'            
                   26                          );                                           
                   27                  end if;                                              
                   28          end;                                                         
                   29                                                                       
                   30          procedure W( line varchar2 ) is                              
                   31          begin                                                        
                   32                  DBMS_OUTPUT.put_line( line );                        
                   33          end;                                                         
                   34  begin                                                                
                   35          --// logon to the Microsoft Active Directory Server          
                   36          DBMS_LDAP.USE_EXCEPTION := false;                             
                   37          W( 'Logging on to AD server;' );                             
                   38          ldapSession := DBMS_LDAP.init( LDAP_SERVER, LDAP_PORT );     
                   39                                                                       
                   40          rc := DBMS_LDAP.simple_bind_s(                               
                   41                  ld => ldapSession,                                   
                   42                  dn => LDAP_USER,                                     
                   43                  passwd => LDAP_PASSW                                 
                   44          );                                                           
                   45          assert( rc = DBMS_LDAP_UTL.SUCCESS  );                                                                 
                   46                                                                       
                   47          --// set the NTLM user and attributes that we want                         
                   48          ntUser := 'verreynneb';                                         
                   49          attrList(1) := 'givenName';                                  
                   50          attrList(2) := 'mail';                                       
                   51          attrList(3) := 'mobile';                                     
                   52          attrList(4) := 'telephoneNumber';                            
                   53          attrList(5) := 'manager';                                    
                   54                                                                       
                   55          --// so a search on the username (NTLM username typically)   
                   56          W( 'Doing a basic search on NT username' );                  
                   57          rc := DBMS_LDAP.search_s(                                    
                   58                  ld => ldapSession,                                   
                   59                  base => LDAP_BASE,                                   
                   60                  scope => DBMS_LDAP.SCOPE_SUBTREE,                    
                   61                  filter => '(&(objectclass=USER)(SAMAccountName='||ntUser||'))',
                   62                  attrs => attrList,                                             
                   63                  attronly => 0,                                                 
                   64                  res => ldapMessage                                             
                   65          );                                                                     
                   66                                                                                 
                   67          assert( rc = DBMS_LDAP_UTL.SUCCESS  );                                 
                   68                                                                                 
                   69          if DBMS_LDAP.count_entries(ldapSession,ldapMessage) > 0 then           
                   70                  W( '1st entry - only 1 expected as we did a unique account lookup' );
                   71                  ldapEntry := DBMS_LDAP.first_entry( ldapSession, ldapMessage );
                   72
                   73                  while (ldapEntry is not null) loop
                   74                          --// get the attribute
                   75                          attrName := DBMS_LDAP.first_attribute(
                   76                                          ld => ldapSession,
                   77                                          ldapEntry => ldapEntry,
                   78                                          ber_elem  => berElem
                   79                                  );
                   80                          while (attrName is not null) loop
                   81                                  --// get the list of values for the attribute
                   82                                  valList := DBMS_LDAP.get_values(
                   83                                                  ld => ldapSession,
                   84                                                  ldapEntry => ldapEntry,
                   85                                                  attr =>  attrName
                   86                                          );
                   87
                   88                                  --// for simplicity sake, we expect a scalar name-value and
                   89                                  --// thus a single value only
                   90                                  W( attrName||'='||valList(0) );
                   91
                   92                                  --// proceed to process the next attribute
                   93                                  attrName :=  DBMS_LDAP.next_attribute(
                   94                                                  ld => ldapSession,
                   95                                                  ldapEntry => ldapEntry,
                   96                                                  ber_elem  => berElem
                   97                                          );
                   98                          end loop;
                   99
                  100                          --// not really needed in this case as we're processing a single SAMaccount entry
                  101                          ldapEntry := DBMS_LDAP.next_entry( ldapSession, ldapEntry );
                  102                  end loop;
                  103          end if;
                  104
                  105          W( 'Disconnecting from AD server' );
                  106          rc := DBMS_LDAP.unbind_s( ld => ldapSession );
                  107  end;
                  108  /
                  Logging on to AD server;
                  Doing a basic search on NT username
                  1st entry - only 1 expected as we did a unique account lookup
                  telephoneNumber=000 111 222 333
                  givenName=Billy
                  mail=billy.verreynne@domain.co.za
                  manager=CN=The Manager,OU=Developer,OU=Western Cape,DC=domain,DC=co,DC=za
                  mobile=000 111 222 333
                  Disconnecting from AD server
                  
                  PL/SQL procedure successfully completed.
                  
                  SQL>
                  • 6. Re: Active Directory group membership
                    partlycloudy
                    Thank you both, that was very helpful. One more question. The dsget Windows command-line utility to query AD has a -expand option to recursively show group memberships i.e. if user A is in group B and group B is in group C and so on. Does DBMS_LDAP provide an equivalent method? Thanks
                    • 7. Re: Active Directory group membership
                      Billy~Verreynne
                      Yes - enumerating attribute lists/trees should be supported. Exact details I do not know as we only use DBMS_LDAP for very basis interaction - authentication and looking up specific attributes.

                      I suggest you look through the documentation for DBMS_LDAP (in Oracle® Fusion Middleware Application Developer's Guide for Oracle Identity Management).

                      Also, if you can find a basic (generic or other language) LDAP protocol example of the LDAP calls to make for enumeration, then it should be fairly easy to implement that call sequence via DBMS_LDAP.