Link to home
Start Free TrialLog in
Avatar of WayneGro
WayneGro

asked on

APEX 4.0, integration with LDAP using SamAccountname

I'm not able to easily use LDAP because our SAMAccountnames are our login id's and "CU=first last".  We wrote the following procedure in Oracle 11g, and called it within LDAP UserName edit function (return ldap_test_wayne).  

create or replace
FUNCTION LDAP_TEST_WAYNE ( p_username IN VARCHAR2)
RETURN varchar2 IS
   --p_username     VARCHAR2(256) := 'samAccountname=WAYNEL';
   l_ldap_host    VARCHAR2(256) := 'hnadm.housing.ufl.edu';
   l_ldap_port    VARCHAR2(256) := '389';
   l_ldap_user    VARCHAR2(256) := '';
   l_ldap_passwd  VARCHAR2(256) := 'uvNtVT1mJq';
   l_ldap_base    VARCHAR2(256) := 'OU=Housing,DC=housing,DC=ufl,DC=edu';
   l_dn           VARCHAR2(256) := 'CN=RadiusUser LDAP,OU=Service Accounts,OU=Housing,DC=housing,DC=ufl,DC=edu';
 
   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
   l_attrs(1) := 'cn';
 -- 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_dn,
                                      passwd => l_ldap_passwd);

  -- Get all attributes
  -- retrieve all attributes
  l_retval := DBMS_LDAP.search_s(ld       => l_session,
                                 base     => l_ldap_base,
                                 scope    => DBMS_LDAP.SCOPE_SUBTREE,
                                 filter   => p_username,
                                 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));
          IF l_attr_name = 'cn' then
            l_ldap_user := SUBSTR(l_vals(i),1,200);
          END IF;
        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('Unbind return value : ' || l_retval);
--  l_attrs(1) := '*';
 DBMS_OUTPUT.PUT_LINE('Using username : ' || l_ldap_user);
 
 return l_ldap_user;
END LDAP_TEST_WAYNE;

There must be an easier way... and we haven't gotten it to work at this point.

-HELP
ASKER CERTIFIED SOLUTION
Avatar of WayneGro
WayneGro

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of WayneGro
WayneGro

ASKER

this is the basic answer for connecting to LDAP but wont help when connecting with Samaccountname.  So this doesn't really answer anything.