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=hou sing,DC=uf l,DC=edu';
l_retval PLS_INTEGER;
l_session DBMS_LDAP.session;
l_attrs DBMS_LDAP.string_collectio n;
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_collectio n;
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('ATTI BUTE_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(l d => 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('Unbi nd return value : ' || l_retval);
-- l_attrs(1) := '*';
DBMS_OUTPUT.PUT_LINE('Usin g 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
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=
l_dn VARCHAR2(256) := 'CN=RadiusUser LDAP,OU=Service Accounts,OU=Housing,DC=hou
l_retval PLS_INTEGER;
l_session DBMS_LDAP.session;
l_attrs DBMS_LDAP.string_collectio
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_collectio
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
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
-- 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(
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('ATTI
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(l
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('Unbi
-- l_attrs(1) := '*';
DBMS_OUTPUT.PUT_LINE('Usin
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER