My Environment is as follows;
Oracle Database 10g Release 2
Oracle Forms 10g 10.2
My Issue is as Follows,
I have a Package (Which I will post at the end) that authenticates using dbms_ldap. For me to authenticate, I have to hard code the CN=XXXX value to the user I'm connecting. Example if I have a user called abc.xyu and his CN according to the AD is Abc Xyz. I have to hard code the value Abc Xyz on the CN field for me to be able to log in using the abc.xyz user. This is a problem because I have many users in the AD whom I would want to authenticate.
My question is, How to I dynamically get the CN value by passing just the username and password of the user??
---Code Begins Here-----
create or replace package heri.ldap
as
l_ldap_host varchar2 (256) := 'ad.domain.com';
l_ldap_port varchar2 (256) := '389';
l_ldap_user varchar2 (256)
:= 'CN=XXXXX,OU=XXXXX,DC=XXXXX,DC=XXXXX';
l_ldap_base varchar2 (256) := 'DC=XXXXX,DC=XXXXX';
type usr_rec_type is record (
user_login varchar2 (50),
user_fullname varchar2 (50),
user_id varchar2 (50)
);
type usr_attr_type is record (
id number (20),
login varchar2 (256),
attr varchar2 (256),
val varchar2 (256)
);
type usr_tab_type is table of usr_rec_type;
type usr_tab_attr is table of usr_attr_type;
function get_userdata (p_string in varchar2, p_password varchar2)
return usr_tab_attr pipelined;
end ldap;
/
CREATE OR REPLACE package body ldap
as
function get_userdata (p_string in varchar2, p_password varchar2)
return usr_tab_attr pipelined
is
l_ldap_passwd varchar2 (256) := p_password;
user_row usr_attr_type;
l_filter varchar2 (256) := p_string;
l_retval pls_integer;
l_session dbms_ldap.session;
l_attrs dbms_ldap.string_collection;
v_entry_id number (12);
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
dbms_ldap.use_exception := true;
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
);
l_attrs (1) := 'sAMAccountName';
l_attrs (2) := 'employeeNumber';
l_attrs (3) := 'displayName';
l_attrs (4) := 'description';
l_retval :=
dbms_ldap.search_s (ld => l_session,
base => l_ldap_base,
scope => dbms_ldap.scope_subtree,
filter => l_filter,
attrs => l_attrs,
attronly => 0,
res => l_message
);
if dbms_ldap.count_entries (ld => l_session, msg => l_message) > 0
then
l_entry := dbms_ldap.first_entry (ld => l_session,
msg => l_message);
<<entry_loop>>
v_entry_id := 0;
while l_entry is not null
loop
v_entry_id := v_entry_id + 1;
-- Get all Attributes of the Entry
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
--AND l_attr_name IN('sAMAccountName','employeeNumber','displayName')
loop
l_vals :=
dbms_ldap.get_values (ld => l_session,
ldapentry => l_entry,
attr => l_attr_name
);
<<values_loop>>
user_row.login := null;
for i in l_vals.first .. l_vals.last
loop
if l_attr_name = 'sAMAccountName'
then
user_row.login := l_vals (i);
else
user_row.login := null;
end if;
user_row.id := v_entry_id;
user_row.attr := l_attr_name;
user_row.val := l_vals (i);
pipe row (user_row);
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;
-- Close Connection to LDAP Server
l_retval := dbms_ldap.unbind_s (ld => l_session);
return;
end get_userdata;
end ldap;
/
---Code Ends Here ------