A procedure is working fine when called directly but getting error when called from function

raghunadhan · Aug 5, 2021 8:22AM

We are migrating AIMS to Active Directory as LDAP .we are integrating Apex with AD. We had packages to authenticate to AD that we use .one of the procedures gets users attribute from AD. when it called separately(that is directly) it is working fine. This procedures also called from an authentication function of package but it is throwing invalid ldap session.

That is -- when this procedures is commented in that autentication function this function is working fine when not commented it is throwing invalid ldap session..

Billy Verreynne · Aug 5, 2021 9:34AM

The error is accurate - the LDAP session is invalid.

Why? We do not have your code and environment, and nor have you provided any technical details that can be used to diagnose the problem.

DBMS_LDAP works just fine when used correctly.

raghunadhan · Aug 5, 2021 11:51AM

create or replace PACKAGE BODY "C2C2_LDAP" AS

g_session DBMS_LDAP.session;

g_search_base VARCHAR2(4000);

g_host VARCHAR2(255);

g_port VARCHAR(10);

---------------------------------------------------------------------------------------

-- Change Seq Change By Change Date Project ID Description

-- ---------- ---------------- ----------- ---------- ---------------------- --

-- 001 Oslin Romero 01/09/2011 2011-62 Validate when a --

-- Exp Pass password is expired --

---------------------------------------------------------------------------------------

FUNCTION decrypt_pwd(p_pwd IN VARCHAR2)

RETURN VARCHAR2 IS

BEGIN

RETURN p_pwd;

END decrypt_pwd;

PROCEDURE do_init(p_ldap_host IN VARCHAR2, p_ldap_port IN NUMBER, p_use_ssl IN VARCHAR2 DEFAULT 'N') AS

l_wallet_path VARCHAR2(4000);

l_wallet_pwd VARCHAR2(4000);

l_retval PLS_INTEGER;

BEGIN

g_session := DBMS_LDAP.init(p_ldap_host, p_ldap_port);

IF p_use_ssl IN ('Y', 'A') THEN

IF p_use_ssl = 'A' THEN

-- l_wallet_path := wwv_flow_platform.get_preference( p_preference_name => 'WALLET_PATH' );

-- l_wallet_pwd := wwv_flow_platform.get_preference( p_preference_name => 'WALLET_PWD' );

SELECT wallet_path, -- 001 Begin

pkg_security.decrypt_string(wallet_pwd)

-- 001 End

INTO l_wallet_path, l_wallet_pwd

FROM c2c2_ldap_setting

WHERE is_current = 'Y';

IF l_wallet_pwd IS NOT NULL THEN

l_wallet_pwd := decrypt_pwd(l_wallet_pwd);

END IF;

l_retval := DBMS_LDAP.open_ssl(ld => g_session,

sslwrl => l_wallet_path,

sslwalletpasswd => l_wallet_pwd,

sslauth => 2);

ELSE

l_retval := DBMS_LDAP.open_ssl(ld => g_session, sslwrl => NULL, sslwalletpasswd => NULL, sslauth => 1);

END IF;

EXCEPTION

WHEN OTHERS THEN

l_retval := DBMS_LDAP.unbind_s(g_session);

RAISE;

END do_init;

FUNCTION do_connect(p_ldap_host IN VARCHAR2,

p_ldap_port IN NUMBER,

p_use_ssl IN VARCHAR2 DEFAULT 'N',

p_service_account IN VARCHAR2 DEFAULT NULL,

p_service_account_pwd IN VARCHAR2 DEFAULT NULL)

RETURN PLS_INTEGER AS

l_retval PLS_INTEGER;

BEGIN

do_init(p_ldap_host, p_ldap_port, p_use_ssl);

l_retval := DBMS_LDAP.simple_bind_s(g_session, p_service_account, p_service_account_pwd);

RETURN l_retval;

END do_connect;

PROCEDURE disconnect_session IS

l_return PLS_INTEGER;

BEGIN

l_return := DBMS_LDAP.unbind_s(g_session);

END disconnect_session;

PROCEDURE unbind IS

l_dummy PLS_INTEGER;

BEGIN

l_dummy := DBMS_LDAP.unbind_s(g_session);

END unbind;

PROCEDURE bind(p_username IN VARCHAR2 DEFAULT NULL,

p_password IN VARCHAR2 DEFAULT NULL,

p_search_base IN VARCHAR2,

p_host IN VARCHAR2,

p_port IN VARCHAR2 DEFAULT 389,

p_use_ssl IN VARCHAR2 DEFAULT 'N') IS

l_dummy PLS_INTEGER;

l_ldap_user VARCHAR2(255);

BEGIN

g_search_base := p_search_base;

g_host := p_host;

g_port := p_port;

l_ldap_user := 'cn='

|| p_username;

IF p_search_base IS NOT NULL THEN

l_ldap_user := l_ldap_user

|| ','

|| p_search_base;

END IF;

BEGIN

do_init(p_host, p_port, p_use_ssl);

l_dummy := DBMS_LDAP.simple_bind_s(g_session, l_ldap_user, p_password);

EXCEPTION

WHEN OTHERS THEN

l_dummy := DBMS_LDAP.unbind_s(g_session);

RAISE;

END;

END bind;

FUNCTION authenticate(p_username IN VARCHAR2 DEFAULT NULL,

p_password IN VARCHAR2 DEFAULT NULL,

p_search_base IN VARCHAR2,

p_host IN VARCHAR2,

p_port IN VARCHAR2 DEFAULT 389,

p_use_ssl IN VARCHAR2 DEFAULT 'N')

RETURN BOOLEAN AS

l_dummy PLS_INTEGER;

BEGIN

--

-- dbms_ldap.simple_bind_s will accept a null password and allow the bind. Since the purpose of this

-- function is to authenticate, we expect that developers using it would *not* expect a null password

-- to result in successful authentication. In the interest of better security out-of-the-box, this

-- function returns false if the provided password is null.

--

IF p_password IS NULL THEN

RETURN FALSE;

END IF;

g_search_base := p_search_base;

g_host := p_host;

g_port := p_port;

DBMS_LDAP.use_exception := TRUE;

BEGIN

bind(p_username => p_username,

p_password => p_password,

p_search_base => g_search_base,

p_host => g_host,

p_port => g_port,

p_use_ssl => p_use_ssl);

unbind;

RETURN TRUE;

EXCEPTION

WHEN OTHERS THEN

l_dummy := DBMS_LDAP.unbind_s(g_session);

RETURN FALSE;

END;

EXCEPTION

WHEN OTHERS THEN

RETURN FALSE;

END authenticate;

FUNCTION is_member(p_username IN VARCHAR2,

p_pass IN VARCHAR2 DEFAULT NULL,

p_auth_base IN VARCHAR2,

p_host IN VARCHAR2,

p_port IN VARCHAR2 DEFAULT 389,

p_use_ssl IN VARCHAR2 DEFAULT 'N',

p_group IN VARCHAR2,

p_group_base IN VARCHAR2)

RETURN BOOLEAN IS

l_retval PLS_INTEGER;

l_session DBMS_LDAP.session;

l_user_handle dbms_ldap_utl.handle;

l_user_type PLS_INTEGER;

l_user_id VARCHAR2(2000);

l_user_id2 VARCHAR2(2000);

l_group_handle dbms_ldap_utl.handle;

l_group_id VARCHAR2(2000);

l_group_type PLS_INTEGER;

l_result BOOLEAN := FALSE;

BEGIN

l_user_type := DBMS_LDAP_UTL.TYPE_DN;

l_user_id := 'cn='

|| p_username;

IF p_auth_base IS NOT NULL THEN

l_user_id := l_user_id

|| ','

|| p_auth_base;

END IF;

l_group_type := dbms_ldap_utl.type_dn;

l_group_id := 'cn='

|| p_group;

IF p_group_base IS NOT NULL THEN

l_group_id := l_group_id

|| ','

|| p_group_base;

END IF;

-- Choosing exceptions to be raised by DBMS_LDAP library.

DBMS_LDAP.use_exception := TRUE;

-- bind

do_init(p_host, p_port, p_use_ssl);

IF p_pass IS NULL THEN

l_user_id2 := NULL;

ELSE

l_user_id2 := l_user_id;

END IF;

l_retval := DBMS_LDAP.simple_bind_s(ld => g_session, dn => l_user_id2, passwd => p_pass);

-- if l_retval != dbms_ldap_utl.success then

-- -- handle errors

-- end if;

-- get user handle

l_retval := dbms_ldap_utl.create_user_handle(user_hd => l_user_handle,

user_type => l_user_type,

user_id => l_user_id);

-- if l_retval != dbms_ldap_utl.success then

-- -- handle errors

-- end if;

--get group handle

l_retval := dbms_ldap_utl.create_group_handle(group_hd => l_group_handle,

group_type => l_group_type,

group_id => l_group_id);

-- if l_retval != dbms_ldap_utl.success then

-- -- handle errors

-- end if;

-- Check Membership

l_retval := dbms_ldap_utl.check_group_membership(ld => g_session,

user_handle => l_user_handle,

group_handle => l_group_handle,

NESTED => dbms_ldap_utl.direct_membership);

IF l_retval != dbms_ldap_utl.success THEN

l_result := FALSE;

ELSE

l_result := TRUE;

END IF;

-- unbind

dbms_ldap_utl.free_handle(l_user_handle);

dbms_ldap_utl.free_handle(l_group_handle);

unbind;

RETURN l_result;

EXCEPTION

WHEN OTHERS THEN

dbms_ldap_utl.free_handle(l_user_handle);

dbms_ldap_utl.free_handle(l_group_handle);

unbind;

RAISE;

END is_member;

FUNCTION member_of(p_username IN VARCHAR2 DEFAULT NULL,

p_pass IN VARCHAR2 DEFAULT NULL,

p_auth_base IN VARCHAR2,

p_host IN VARCHAR2,

p_port IN VARCHAR2 DEFAULT 389,

p_use_ssl IN VARCHAR2 DEFAULT 'N')

RETURN wwv_flow_global.vc_arr2 IS

l_retval PLS_INTEGER;

l_session DBMS_LDAP.session;

l_result DBMS_LDAP.message;

l_user_handle dbms_ldap_utl.handle;

l_user_type PLS_INTEGER;

l_user_id VARCHAR2(2000);

l_groups dbms_ldap_utl.property_set_collection;

l_attrs DBMS_LDAP.string_collection;

l_group_names DBMS_LDAP.string_collection;

l_group_values DBMS_LDAP.string_collection;

l_groups_out wwv_flow_global.vc_arr2;

l_count NUMBER;

BEGIN

l_user_type := dbms_ldap_utl.type_dn;

IF p_username IS NOT NULL THEN

l_user_id := 'cn=';

END IF;

l_user_id := l_user_id

|| p_username;

IF p_auth_base IS NOT NULL THEN

l_user_id := l_user_id

|| ','

|| p_auth_base;

END IF;

-- Choosing exceptions to be raised by DBMS_LDAP library.

DBMS_LDAP.use_exception := TRUE;

-- bind

do_init(p_host, p_port, p_use_ssl);

--need to store publicUser and passwd as prefs not accessible to the public

--

l_retval := DBMS_LDAP.simple_bind_s(ld => g_session, dn => l_user_id, passwd => p_pass);

-- get user handle

l_retval := dbms_ldap_utl.create_user_handle(user_hd => l_user_handle,

user_type => l_user_type,

user_id => l_user_id);

l_attrs.DELETE();

l_attrs(1) := 'cn';

l_retval := dbms_ldap_utl.get_group_membership(ld => g_session,

user_handle => l_user_handle,

NESTED => dbms_ldap_utl.direct_membership,

attr_list => l_attrs,

ret_groups => l_groups);

l_count := 1;

IF l_groups.count > 0 THEN

FOR i IN l_groups.first .. l_groups.last LOOP

l_retval := dbms_ldap_utl.get_property_names(l_groups(i), l_group_names);

IF l_group_names.count > 0 THEN

FOR j IN l_group_names.first .. l_group_names.last LOOP

l_retval := dbms_ldap_utl.get_property_values(l_groups(i), l_group_names(j), l_group_values);

IF l_group_values.count > 0 THEN

FOR K IN l_group_values.first .. l_group_values.last LOOP

l_groups_out(l_count) := l_group_values(K);

l_count := l_count

+ 1;

END LOOP; --k

END IF;

END LOOP; --j

END IF;

END LOOP; --i

END IF;

-- unbind

dbms_ldap_utl.free_handle(l_user_handle);

unbind;

RETURN l_groups_out;

EXCEPTION

WHEN OTHERS THEN

dbms_ldap_utl.free_handle(l_user_handle);

unbind;

RAISE;

END member_of;

FUNCTION member_of2(p_username IN VARCHAR2 DEFAULT NULL,

p_pass IN VARCHAR2 DEFAULT NULL,

p_auth_base IN VARCHAR2,

p_host IN VARCHAR2,

p_port IN VARCHAR2 DEFAULT 389,

p_use_ssl IN VARCHAR2 DEFAULT 'N')

RETURN VARCHAR2 IS

l_return VARCHAR2(32767);

l_array wwv_flow_global.vc_arr2;

BEGIN

l_array := member_of(p_username => p_username,

p_pass => p_pass,

p_auth_base => p_auth_base,

p_host => p_host,

p_port => p_port,

p_use_ssl => p_use_ssl);

l_return := wwv_flow_utilities.table_to_string(l_array);

RETURN l_return;

END member_of2;

PROCEDURE get_user_attributes(p_use_current_settings IN VARCHAR2 DEFAULT 'N', -- if Y then get then use bind using the data in c2c2_ldap_setting

p_full_dn IN VARCHAR2 DEFAULT NULL,

p_username IN VARCHAR2 DEFAULT NULL,

p_pass IN VARCHAR2 DEFAULT NULL,

p_auth_base IN VARCHAR2 DEFAULT NULL,

p_host IN VARCHAR2 DEFAULT NULL,

p_port IN VARCHAR2 DEFAULT 389,

p_use_ssl IN VARCHAR2 DEFAULT 'N',

p_attributes IN wwv_flow_global.vc_arr2,

p_attribute_values OUT wwv_flow_global.vc_arr2) IS

l_retval PLS_INTEGER;

l_session DBMS_LDAP.session;

l_result DBMS_LDAP.message;

l_user_handle dbms_ldap_utl.handle;

l_user_type PLS_INTEGER;

l_c2_ldap_rec c2c2_ldap_setting%ROWTYPE;

l_user_id VARCHAR2(2000);

l_user_id2 VARCHAR2(2000);

l_pass VARCHAR2(2000);

l_host VARCHAR2(2000);

l_port VARCHAR2(2000);

l_use_ssl VARCHAR2(200);

l_auth_base VARCHAR2(2000);

l_count NUMBER;

l_attrs DBMS_LDAP.string_collection;

l_attr_names DBMS_LDAP.string_collection;

l_attr_vals DBMS_LDAP.string_collection;

l_pset_collection dbms_ldap_utl.property_set_collection;

l_position NUMBER;

--** ORC 23/08/2011 Obtain Operational Attributes -Ini-

res_attrs DBMS_LDAP.string_collection;

res_message DBMS_LDAP.message;

temp_entry DBMS_LDAP.message;

temp_ber_elmt DBMS_LDAP.BER_ELEMENT;

temp_vals DBMS_LDAP.STRING_COLLECTION;

l_vUserNameAtt c2c2_ldap_setting.username_attribute%TYPE;

attr_index PLS_INTEGER;

entry_index PLS_INTEGER;

l_vFilter VARCHAR2(100);

temp_dn VARCHAR2(512);

temp_attr_name VARCHAR2(512);

l_nAux NUMBER(4) := 0;

l_vAux VARCHAR2(10000);

--** ORC 23/08/2011 Obtain Operational Attributes -Fin-

BEGIN

l_user_type := dbms_ldap_utl.type_dn;

l_pass := p_pass;

l_host := p_host;

l_port := p_port;

l_use_ssl := p_use_ssl;

l_auth_base := p_auth_base;

IF p_full_dn IS NOT NULL THEN

l_user_id := p_full_dn;

ELSE

l_user_id := 'cn='

|| p_username;

IF p_auth_base IS NOT NULL THEN

l_user_id := l_user_id

|| ','

|| p_auth_base;

END IF;

IF p_pass IS NULL THEN

l_user_id2 := NULL;

ELSE

l_user_id2 := l_user_id;

END IF;

IF p_use_current_settings = 'Y' THEN

SELECT *

INTO l_c2_ldap_rec

FROM c2c2_ldap_setting

WHERE is_current = 'Y';

IF l_c2_ldap_rec.ldap_search_pwd IS NOT NULL THEN

l_user_id2 := l_c2_ldap_rec.ldap_search_user;

-- 001 Begin

l_pass := pkg_security.decrypt_string(l_c2_ldap_rec.ldap_search_pwd);

-- 001 End

END IF;

l_host := l_c2_ldap_rec.ldap_host;

l_port := l_c2_ldap_rec.ldap_port;

l_use_ssl := l_c2_ldap_rec.ldap_use_ssl;

END IF;

-- Choosing exceptions to be raised by DBMS_LDAP library.

DBMS_LDAP.use_exception := TRUE;

-- bind

do_init(l_host, l_port, l_use_ssl);

--need to store publicUser and passwd as prefs not accessible to the public

--

l_retval := DBMS_LDAP.simple_bind_s(ld => g_session, dn => l_user_id2, passwd => l_pass);

-- if l_retval != dbms_ldap_utl.success then

-- -- handle errors

-- end if;

-- get user handle

l_retval := dbms_ldap_utl.create_user_handle(user_hd => l_user_handle,

user_type => l_user_type,

user_id => l_user_id);

-- if l_retval != dbms_ldap_utl.success then

-- -- handle errors

-- end if;

FOR i IN p_attributes.first .. p_attributes.last LOOP

l_attrs(i) := p_attributes(i);

p_attribute_values(i) := NULL; -- initialize results to null

DBMS_OUTPUT.put_line( i

|| '-'

|| l_attrs(i));

END LOOP;

l_retval := dbms_ldap_utl.get_user_properties(ld => g_session,

user_handle => l_user_handle,

attrs => l_attrs,

ptype => dbms_ldap_utl.entry_properties,

ret_pset_coll => l_pset_collection);

-- if l_retval != dbms_ldap_utl.success then

-- return false;

-- end if;

l_count := 1;

IF l_pset_collection.count > 0 THEN

FOR i IN l_pset_collection.first .. l_pset_collection.last LOOP

l_retval := dbms_ldap_utl.get_property_names(l_pset_collection(i), l_attr_names);

--dbms_output.put_line(i || '-' || l_pset_collection(i));

IF l_attr_names.count > 0 THEN

FOR j IN l_attr_names.first .. l_attr_names.last LOOP

l_retval := dbms_ldap_utl.get_property_values(l_pset_collection(i), l_attr_names(j), l_attr_vals);

--dbms_output.put_line(j || '-' || l_attr_names(j));

-- determine original position

l_position := -1;

FOR P IN p_attributes.first .. p_attributes.last LOOP

IF p_attributes(P) = l_attr_names(j) THEN

l_position := P;

EXIT; -- exit the loop

END IF;

END LOOP;

IF l_attr_vals.count > 0 THEN

FOR K IN l_attr_vals.first .. l_attr_vals.last LOOP

--p_attribute_values(l_count) := l_attr_vals(k);

p_attribute_values(l_position) := l_attr_vals(K);

--dbms_output.put_line(l_position || '-' || p_attribute_values(l_position));

l_count := l_count

+ 1;

END LOOP; --k

END IF;

END LOOP; --j

END IF;

END LOOP; --i

END IF;

--** ORC 23/08/2011 Obtain Operational Attributes -Ini-

IF 1 = 2 THEN --p_full_dn = 'gmpersonid=WO22952,ou=People,dc=gm,dc=com' THEN

SELECT username_attribute, expiry_date_attribute, first_name_attribute

INTO l_vUserNameAtt, res_attrs(1), res_attrs(2)

FROM c2c2_ldap_setting

WHERE is_current = 'Y';

FOR l_iCount IN p_attributes.first .. p_attributes.last LOOP

IF p_attributes(l_iCount) = res_attrs(1) THEN

l_nAux := l_iCount;

END IF;

END LOOP;

res_attrs(1) := 'nsaccountlock';

l_vfilter := p_full_dn;

--res_attrs(1) := '';

l_retval := DBMS_LDAP.SEARCH_S(ld => g_session,

base => g_search_base,

SCOPE => DBMS_LDAP.SCOPE_SUBTREE,

FILTER => 'gmPersonId=AD0462', --l_vfilter,

attrs => res_attrs,

attronly => 0,

res => res_message);

l_vAux := 'retval: '

|| to_char(l_retval)

|| ', filter: '

|| to_char(l_vfilter);

l_vAux := l_vAux

|| ', full_dn: '

|| p_full_dn;

l_vAux := l_vAux

|| ', success: '

|| DBMS_LDAP.success;

l_vAux := l_vAux

|| '--END--';

-- -------------------------------------------------------------------------

-- After the search is performed, the API stores the count of the number of

-- entries returned.

-- -------------------------------------------------------------------------

l_retval := DBMS_LDAP.COUNT_ENTRIES(g_session, res_message);

-- -------------------------------------------------------------------------

-- Retrieve the first entry.

-- -------------------------------------------------------------------------

temp_entry := DBMS_LDAP.FIRST_ENTRY(g_session, res_message);

entry_index := 1;

-- -------------------------------------------------------------------------

-- Loop through each of the entries one by one.

-- -------------------------------------------------------------------------

WHILE temp_entry IS NOT NULL LOOP

-- ---------------------------------------------------------------------

-- Print out the current entry.

-- ---------------------------------------------------------------------

temp_dn := DBMS_LDAP.GET_DN(g_session, temp_entry);

--DBMS_OUTPUT.PUT_LINE(' dn: '

-- || temp_dn);

temp_attr_name := DBMS_LDAP.FIRST_ATTRIBUTE(g_session, temp_entry, temp_ber_elmt);

attr_index := 1;

WHILE temp_attr_name IS NOT NULL LOOP

temp_vals := DBMS_LDAP.GET_VALUES(g_session, temp_entry, temp_attr_name);

IF temp_vals.count > 0 THEN

FOR i IN temp_vals.first .. temp_vals.last LOOP

IF l_nAux > 0 THEN

p_attribute_values(l_nAux) := temp_vals(i);

l_vAux := l_vAux

|| ':'

|| temp_vals(i);

END IF;

--DBMS_OUTPUT.PUT_LINE( rpad(' '

-- || temp_attr_name,

-- 19,

-- ' ')

-- || ': '

-- || substr(temp_vals(i), 1, 200));

END LOOP;

l_nAux := 100000000000000;

END IF;

temp_attr_name := DBMS_LDAP.NEXT_ATTRIBUTE(g_session, temp_entry, temp_ber_elmt);

attr_index := attr_index

+ 1;

END LOOP;

temp_entry := DBMS_LDAP.NEXT_ENTRY(g_session, temp_entry);

--DBMS_OUTPUT.PUT_LINE('===================================================');

entry_index := entry_index

+ 1;

END LOOP;

END IF;

--** ORC 23/08/2011 Obtain Operational Attributes -Fin-

-- unbind

dbms_ldap_utl.free_handle(l_user_handle);

unbind;

EXCEPTION

WHEN OTHERS THEN

dbms_ldap_utl.free_handle(l_user_handle);

unbind;

RAISE_application_error(-20000,

l_vAux

|| ', ERR:'

|| SQLERRM);

END get_user_attributes;

PROCEDURE get_all_user_attributes(p_full_dn IN VARCHAR2 DEFAULT NULL,

p_username IN VARCHAR2 DEFAULT NULL,

p_pass IN VARCHAR2 DEFAULT NULL,

p_auth_base IN VARCHAR2 DEFAULT NULL,

p_host IN VARCHAR2,

p_port IN VARCHAR2 DEFAULT 389,

p_use_ssl IN VARCHAR2 DEFAULT 'N',

p_attributes OUT wwv_flow_global.vc_arr2,

p_attribute_values OUT wwv_flow_global.vc_arr2) IS

l_retval PLS_INTEGER;

l_session DBMS_LDAP.session;

l_result DBMS_LDAP.message;

l_user_handle dbms_ldap_utl.handle;

l_user_type PLS_INTEGER;

l_user_id VARCHAR2(2000);

l_user_id2 VARCHAR2(2000);

l_count PLS_INTEGER;

l_attrs DBMS_LDAP.string_collection;

l_attr_names DBMS_LDAP.string_collection;

l_attr_vals DBMS_LDAP.string_collection;

l_pset_collection dbms_ldap_utl.property_set_collection;

--

BEGIN

l_user_type := dbms_ldap_utl.type_dn;

IF p_full_dn IS NOT NULL THEN

l_user_id := p_full_dn;

ELSE

l_user_id := 'cn='

|| p_username;

IF p_auth_base IS NOT NULL THEN

l_user_id := l_user_id

|| ','

|| p_auth_base;

END IF;

-- Choosing exceptions to be raised by DBMS_LDAP library.

DBMS_LDAP.use_exception := TRUE;

-- bind

do_init(p_host, p_port, p_use_ssl);

--need to store publicUser and passwd as prefs not accessible to the public

--

IF p_pass IS NULL THEN

l_user_id2 := NULL;

ELSE

l_user_id2 := l_user_id;

END IF;

l_retval := DBMS_LDAP.simple_bind_s(ld => g_session, dn => l_user_id2, passwd => p_pass);

-- if l_retval != dbms_ldap_utl.success then

-- -- handle errors

-- end if;

-- get user handle

l_retval := dbms_ldap_utl.create_user_handle(user_hd => l_user_handle,

user_type => l_user_type,

user_id => l_user_id);

-- if l_retval != dbms_ldap_utl.success then

-- -- handle errors

-- end if;

l_attrs(1) := '*';

l_retval := dbms_ldap_utl.get_user_properties(ld => g_session,

user_handle => l_user_handle,

attrs => l_attrs,

ptype => dbms_ldap_utl.entry_properties,

ret_pset_coll => l_pset_collection);

-- if l_retval != dbms_ldap_utl.success then

-- return false;

-- end if;

l_count := 1;

IF l_pset_collection.count > 0 THEN

FOR i IN l_pset_collection.first .. l_pset_collection.last LOOP

l_retval := dbms_ldap_utl.get_property_names(l_pset_collection(i), l_attr_names);

IF l_attr_names.count > 0 THEN

FOR j IN l_attr_names.first .. l_attr_names.last LOOP

l_retval := dbms_ldap_utl.get_property_values(l_pset_collection(i), l_attr_names(j), l_attr_vals);

p_attributes(j) := l_attr_names(j);

IF l_attr_vals.count > 0 THEN

--

-- For now, only get back the first value

--

p_attribute_values(j) := l_attr_vals(l_attr_vals.first);

ELSE

p_attribute_values(j) := NULL;

END IF;

END LOOP; --j

END IF;

END LOOP; --i

END IF;

-- unbind

dbms_ldap_utl.free_handle(l_user_handle);

unbind;

EXCEPTION

WHEN OTHERS THEN

dbms_ldap_utl.free_handle(l_user_handle);

unbind;

RAISE;

END get_all_user_attributes;

-------------------------------------------------------------------------------

FUNCTION get_groups(p_ldap_host IN VARCHAR2, -- ldap.somecompany.com

p_ldap_port IN NUMBER, -- 389

p_use_ssl IN VARCHAR2 DEFAULT 'N', -- N= no SSL, Y = SSL no auth, A = SSL with auth

p_username IN VARCHAR2, -- cn=TMUTH

p_service_account IN VARCHAR2 DEFAULT NULL, -- cn=Admin

p_service_account_pwd IN VARCHAR2 DEFAULT NULL, -- welcome123

p_search_base IN VARCHAR2 DEFAULT NULL, -- ou=Employees,dc=SomeCompany,dc=com

p_scope IN BINARY_INTEGER DEFAULT DBMS_LDAP.scope_subtree, -- scope_subtree | scope_base | scope_onelevel

p_group_attribute IN VARCHAR2 DEFAULT 'cn' -- dn

)

RETURN dbms_ldap_utl.string_collection IS

l_retval PLS_INTEGER;

l_attrs DBMS_LDAP.string_collection;

l_scope BINARY_INTEGER;

l_message DBMS_LDAP.message;

l_entry DBMS_LDAP.message;

l_dn VARCHAR2(1000);

l_dn2 VARCHAR2(1000);

l_groups dbms_ldap_utl.property_set_collection;

l_group_names DBMS_LDAP.string_collection;

l_group_attrs DBMS_LDAP.string_collection;

l_group_values DBMS_LDAP.string_collection;

l_groups_out DBMS_LDAP.string_collection;

l_count NUMBER;

l_user_handle dbms_ldap_utl.handle;

l_user_type PLS_INTEGER := dbms_ldap_utl.type_dn;

l_timeout DBMS_LDAP.timeval;

BEGIN

DBMS_LDAP.use_exception := TRUE;

l_user_type := dbms_ldap_utl.type_dn;

l_timeout.seconds := 10;

l_timeout.useconds := 0;

l_retval := do_connect(p_ldap_host => p_ldap_host,

p_ldap_port => p_ldap_port,

p_service_account => p_service_account,

p_service_account_pwd => p_service_account_pwd);

l_attrs(1) := 'NO_ATTRS';

l_retval := DBMS_LDAP.search_st(ld => g_session,

base => p_search_base,

SCOPE => p_scope,

FILTER => '('

|| p_username

|| ')',

attrs => l_attrs,

attronly => 0,

tv => l_timeout,

res => l_message);

l_entry := DBMS_LDAP.first_entry(g_session, l_message);

IF DBMS_LDAP.count_entries(g_session, l_message) > 1 THEN

raise_application_error(-20000,

DBMS_LDAP.count_entries(g_session, l_message)

|| ' entries found. The search string must only return 1 distict user.');

END IF;

IF DBMS_LDAP.count_entries(g_session, l_message) = 0 THEN

raise_application_error(-20000, '0 entries found. The search string must return 1 user.');

END IF;

l_dn := DBMS_LDAP.get_dn(g_session, l_entry);

-- Get User Handle

l_retval := dbms_ldap_utl.create_user_handle(user_hd => l_user_handle,

user_type => l_user_type,

user_id => l_dn);

-- Get Groups

l_group_attrs.DELETE();

l_group_attrs(1) := 'cn';

l_retval := DBMS_LDAP_UTL.get_group_membership(ld => g_session,

user_handle => l_user_handle,

NESTED => dbms_ldap_utl.nested_membership,

attr_list => l_group_attrs,

ret_groups => l_groups);

--

l_count := 1;

IF l_groups.count > 0 THEN

FOR i IN l_groups.first .. l_groups.last LOOP

l_group_names(1) := p_group_attribute;

l_retval := dbms_ldap_utl.get_property_values(l_groups(i), l_group_names(1), l_group_values);

IF l_group_values.count > 0 THEN

FOR K IN l_group_values.first .. l_group_values.last LOOP

l_groups_out(l_count) := l_group_values(K);

l_count := l_count

+ 1;

END LOOP; --k

END IF;

END LOOP; --i

END IF;

dbms_ldap_utl.free_handle(l_user_handle);

disconnect_session;

RETURN l_groups_out;

EXCEPTION

WHEN OTHERS THEN

dbms_ldap_utl.free_handle(l_user_handle);

disconnect_session;

RAISE;

END get_groups;

FUNCTION get_groups_string(p_ldap_host IN VARCHAR2, -- ldap.somecompany.com

p_ldap_port IN NUMBER, -- 389

p_use_ssl IN VARCHAR2 DEFAULT 'N', -- N= no SSL, Y = SSL no auth, A = SSL with auth

p_username IN VARCHAR2, -- cn=TMUTH

p_service_account IN VARCHAR2 DEFAULT NULL, -- cn=Admin

p_service_account_pwd IN VARCHAR2 DEFAULT NULL, -- welcome123

p_search_base IN VARCHAR2 DEFAULT NULL, -- ou=Employees,dc=SomeCompany,dc=com

p_scope IN BINARY_INTEGER DEFAULT DBMS_LDAP.scope_subtree, -- scope_subtree | scope_base | scope_onelevel

p_group_attribute IN VARCHAR2 DEFAULT 'cn', -- dn

p_delimiter IN VARCHAR2 DEFAULT ':' -- ~

)

RETURN VARCHAR2 IS

l_groups_string VARCHAR2(32767);

l_groups DBMS_LDAP.string_collection;

BEGIN

l_groups := c2c2_ldap.get_groups(p_ldap_host => p_ldap_host,

p_ldap_port => p_ldap_port,

p_username => p_username,

p_service_account => p_service_account,

p_service_account_pwd => p_service_account_pwd,

p_search_base => p_search_base,

p_scope => p_scope,

p_group_attribute => p_group_attribute);

FOR i IN l_groups.first .. l_groups.last LOOP

IF i > 1 THEN

l_groups_string := l_groups_string

|| p_delimiter;

END IF;

l_groups_string := l_groups_string

|| l_groups(i);

END LOOP; --i

RETURN l_groups_string;

END get_groups_string;

-------------------------------------------------------------------------------

FUNCTION authenticate_ally(p_username IN VARCHAR2, p_password IN VARCHAR2)

RETURN BOOLEAN IS

l_c2_ldap_rec c2c2_ldap_setting%ROWTYPE;

l_use_ssl VARCHAR2(200);

l_search_user VARCHAR2(256);

l_search_pass VARCHAR2(256);

l_uid_attribute VARCHAR2(256);

l_username_attribute VARCHAR2(256);

l_attrs DBMS_LDAP.string_collection;

l_filter VARCHAR2(256);

l_message DBMS_LDAP.message;

l_entry DBMS_LDAP.message;

l_full_user_dn VARCHAR2(256);

l_retval PLS_INTEGER;

--001

client_server_error EXCEPTION;

PRAGMA EXCEPTION_INIT(client_server_error, -31202);

--001

BEGIN

SELECT *

INTO l_c2_ldap_rec

FROM c2c2_ldap_setting

WHERE is_current = 'Y';

g_host := l_c2_ldap_rec.ldap_host;

g_port := l_c2_ldap_rec.ldap_port;

l_use_ssl := l_c2_ldap_rec.ldap_use_ssl;

l_search_user := l_c2_ldap_rec.ldap_search_user;

-- 001 Begin

l_search_pass := pkg_security.decrypt_string(l_c2_ldap_rec.ldap_search_pwd);

-- 001 End

g_search_base := l_c2_ldap_rec.ldap_search_base;

l_uid_attribute := l_c2_ldap_rec.uid_attribute;

l_username_attribute := l_c2_ldap_rec.username_attribute;

l_filter := '('

|| l_username_attribute

|| '='

|| lower(p_username)

|| ')';

-- connect to ldap server

do_init(g_host, g_port, l_use_ssl);

-- bind as application user

l_retval := DBMS_LDAP.simple_bind_s(ld => g_session, dn => l_search_user, passwd => l_search_pass);

IF l_retval != dbms_ldap_utl.success THEN

-- unable to authenticate search user

APEX_APPLICATION.g_print_success_message := 'Unable to Authenticate Search User.';

l_retval := DBMS_LDAP.unbind_s(g_session);

RETURN FALSE;

END IF;

-- search for user in ldap

l_attrs(1) := 'dn';

l_retval := DBMS_LDAP.search_s(g_session,

g_search_base,

DBMS_LDAP.scope_subtree,

l_filter,

l_attrs,

0,

l_message);

-- return NULL if 0 or multiple entries are returned for a user

l_retval := DBMS_LDAP.count_entries(g_session, l_message);

IF l_retval != 1 THEN

APEX_APPLICATION.g_print_success_message := 'Invalid Login Credentials. Invalid User.';

l_retval := DBMS_LDAP.unbind_s(g_session);

RETURN FALSE;

END IF;

l_entry := DBMS_LDAP.first_entry(g_session, l_message);

l_full_user_dn := DBMS_LDAP.get_dn(g_session, l_entry);

-- bind to authenticate user

l_retval := DBMS_LDAP.simple_bind_s(ld => g_session, dn => l_full_user_dn, passwd => p_password);

IF l_retval != dbms_ldap_utl.success THEN

-- unable to authenticate user

--c2c2_security_pub.increment_login_failures(p_username);

APEX_APPLICATION.g_print_success_message := 'Invalid Login Credentials. Invalid Password';

l_retval := DBMS_LDAP.unbind_s(g_session);

RETURN FALSE; --**

END IF;

l_retval := DBMS_LDAP.unbind_s(g_session);

c2c2_security_pub.reset_login_failures(p_username);

-- retrieve user's ldap attributes

-- c2c2_security_pub.set_user_ldap_attributes(p_full_dn => l_full_user_dn, p_user_pwd => p_password);

-- c2c2_security_pub.sync_ldap_user(p_full_dn => l_full_user_dn);

RETURN TRUE; --success

EXCEPTION

--001

WHEN client_server_error THEN

BEGIN

--RAISE;

APEX_APPLICATION.g_print_success_message := 'Client/Server ERROR';

IF instr(SQLERRM, 'password expired!') > 0 THEN

APEX_APPLICATION.g_print_success_message := 'Your password has expired. Please visit '

|| '<a href="https://exchange.gmacfs.com/web/portal'

|| '/home">Ally Pulse</a></f'

|| 'ont> for account maintenance.';

ELSIF instr(SQLERRM, 'Invalid credentials') > 0 THEN

APEX_APPLICATION.g_print_success_message := 'Invalid Login Credentials.';

END IF;

l_retval := DBMS_LDAP.unbind_s(g_session);

RETURN FALSE;

END;

--001

WHEN OTHERS THEN

BEGIN

--001

APEX_APPLICATION.g_print_success_message := 'There was a ERROR in the authentication function.';

--001

l_retval := DBMS_LDAP.unbind_s(g_session);

RETURN FALSE;

END;

END authenticate_ally;

FUNCTION get_full_DN(p_username IN VARCHAR2)

RETURN VARCHAR2 IS

l_c2_ldap_rec c2c2_ldap_setting%ROWTYPE;

l_use_ssl VARCHAR2(200);

l_search_user VARCHAR2(256);

l_search_pass VARCHAR2(256);

l_uid_attribute VARCHAR2(256);

l_username_attribute VARCHAR2(256);

l_attrs DBMS_LDAP.string_collection;

l_filter VARCHAR2(256);

l_message DBMS_LDAP.message;

l_entry DBMS_LDAP.message;

l_full_user_dn VARCHAR2(256);

l_retval PLS_INTEGER;

BEGIN

SELECT *

INTO l_c2_ldap_rec

FROM c2c2_ldap_setting

WHERE is_current = 'Y';

g_host := l_c2_ldap_rec.ldap_host;

g_port := l_c2_ldap_rec.ldap_port;

l_use_ssl := l_c2_ldap_rec.ldap_use_ssl;

l_search_user := l_c2_ldap_rec.ldap_search_user;

-- 001 Begin

l_search_pass := pkg_security.decrypt_string(l_c2_ldap_rec.ldap_search_pwd);

-- 001 End

g_search_base := l_c2_ldap_rec.ldap_search_base;

l_uid_attribute := l_c2_ldap_rec.uid_attribute;

l_username_attribute := l_c2_ldap_rec.username_attribute;

l_filter := '('

|| l_username_attribute

|| '='

|| lower(p_username)

|| ')';

-- connect to ldap server

do_init(g_host, g_port, l_use_ssl);

-- bind as application user

l_retval := DBMS_LDAP.simple_bind_s(ld => g_session, dn => l_search_user, passwd => l_search_pass);

IF l_retval != dbms_ldap_utl.success THEN

-- unable to authenticate search user

l_retval := DBMS_LDAP.unbind_s(g_session);

RETURN '';

END IF;

-- search for user in ldap

l_attrs(1) := 'dn';

l_retval := DBMS_LDAP.search_s(g_session,

g_search_base,

DBMS_LDAP.scope_subtree,

l_filter,

l_attrs,

0,

l_message);

-- return NULL if 0 or multiple entries are returned for a user

l_retval := DBMS_LDAP.count_entries(g_session, l_message);

IF l_retval != 1 THEN

l_retval := DBMS_LDAP.unbind_s(g_session);

RETURN '';

END IF;

l_entry := DBMS_LDAP.first_entry(g_session, l_message);

l_full_user_dn := DBMS_LDAP.get_dn(g_session, l_entry);

RETURN l_full_user_dn;

END;

FUNCTION get_user_attribute(i_vUID IN VARCHAR2, i_vAttribute IN VARCHAR2) RETURN VARCHAR2 AS

l_attributes wwv_flow_global.vc_arr2;

l_attribute_values wwv_flow_global.vc_arr2;

BEGIN

l_attributes(1) := i_vAttribute;

c2c2_ldap.get_user_attributes(p_use_current_settings => 'Y',

p_full_dn => C2C2_LDAP.get_full_dn(i_vUID),

p_attributes => l_attributes,

p_attribute_values => l_attribute_values);

RETURN l_attribute_values(1);

END get_user_attribute;

FUNCTION INACTIVE_USER_CHK RETURN VARCHAR2 AS

A VARCHAR2(100);

BEGIN

A := 'INACTIVE USER';

return A;

APEX_APPLICATION.g_print_success_message := A;

END INACTIVE_USER_CHK;

END c2c2_ldap;

In above package i commented two procedures .while iam calling that two procedures directly it is not throwing any error but when i call that procedures from another package it is thrwoing invalid ldap session.

raghunadhan · Aug 6, 2021 7:31AM

Can anyone help on this.

Billy Verreynne · Aug 6, 2021 11:10AM

Very difficult to read the code in the above format, and follow its design and process flow.

There are issues I've spotted. LDAP session created and not closed in some cases. Functions with unreachable code (e.g. return statement terminates function and statements following will never be executed). Etc.

My suggestion is to make sure the LDAP session is created once only, and properly closed afterwards. Do not leak session handles.

Add proper instrumentation calls - write trace messages to DBMS_OUTPUT for normal client sessions, or APEX_DEBUG for APEX web sessions.

Do not repeat code blocks - modularise.

And realise that PL/SQL is an implementation of the Ada programming language. Thus use style and standards as per the Ada Style Guide. The code posted is not exactly pleasant to look at.

Forum Stats

Quick Links

Categories

A procedure is working fine when called directly but getting error when called from function

Answers

Data

Databases

Development

Development Tools

Open Source

Programs

Forum Stats

Categories

A procedure is working fine when called directly but getting error when called from function

Answers