We are combining two servers onto one new 11g database and installed APEX 4.1.
The APEX application uses authorization schemes, that make a call to LDAP server. The following code worked on the old box but now I am having trouble getting an ldap connection and get this error. Granted execute prives to the calling schema, tested the is_member function using toad in parsing schema and it worked. It doesn't work in the application. I assume this is an application setting that I missed, an APEX setting I missed or a priveledge I missed on the new box.
Can anyone point me in the right direction to fix this issue?
Thanks
ORA-31202: DBMS_LDAP: LDAP client/server error: Operations error. 00000000: LdapErr: DSID-0C090627, comment: In order to perform this operation a successful bind must be completed on the connection., data 0, vece
Technical Info (only visible for developers)
- is_internal_error: true
- apex_error_code: APEX.AUTHORIZATION.UNHANDLED_ERROR
- ora_sqlcode: -31202
- ora_sqlerrm: ORA-31202: DBMS_LDAP: LDAP client/server error: Operations error. 00000000: LdapErr: DSID-0C090627, comment: In order to perform this operation a successful bind must be completed on the connection., data 0, vece
- component.type: APEX_APPLICATION_AUTHORIZATION
- component.id: 257139814582732155
- component.name: STATSVIEW
- error_backtrace:ORA-06512: at "SYS.WWV_DBMS_SQL", line 904 ORA-06512: at "APEX_040100.WWV_FLOW_DYNAMIC_EXEC", line 588 ORA-06512: at "APEX_040100.WWV_FLOW_AUTHORIZATION", line 69 ORA-06512: at "APEX_040100.WWV_FLOW_AUTHORIZATION", line 147 ORA-06512: at "APEX_040100.WWV_FLOW_PLUGIN_ENGINE", line 2089 ORA-06512: at "APEX_040100.WWV_FLOW_AUTHORIZATION", line 378
Statsview Authorization =
<code>
declare
v_cnt number;
begin
select count(*)
into v_cnt
from stars.star_employee
where mgr_id = :APP_USER
or dir_id = :APP_USER;
IF v_cnt > 0 OR (apnew.tsa_ldap_security.is_member(v('APP_USER'), 'Raving Fan Site')
)
THEN
RETURN TRUE;
ELSE
RETURN FALSE;
END IF;
end;
</code>
<code>
FUNCTION is_member
(
p_user IN VARCHAR2,
p_group IN VARCHAR2
) RETURN BOOLEAN IS
l_retval PLS_INTEGER;
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;
group_found BOOLEAN;
BEGIN
apex_application.debug('Starting TSA_LDAP_SECURITY.IS_MEMBER');
group_found := FALSE;
set_user_and_password;
g_session := dbms_ldap.init(g_ldap_server, g_ldap_port);
l_retval := dbms_ldap.simple_bind_s(g_session,
g_ldap_service_base,
g_ldap_service_password);
apex_application.debug('simple_bind_s OK');
l_attrs(1) := 'memberOf'; -- retrieve on the memberOf attributes
l_retval := dbms_ldap.search_s(ld => g_session,
base => g_ldap_auth_base,
scope => dbms_ldap.scope_subtree,
filter => '(sAMAccountName=' || p_user || ')',
attrs => l_attrs,
attronly => 0,
res => l_message);
--apex_application.debug('search OK');
IF dbms_ldap.count_entries(ld => g_session, msg => l_message) > 0
THEN
-- Get all the entries returned by our search.
l_entry := dbms_ldap.first_entry(ld => g_session, msg => l_message);
<<entry_loop>>
WHILE l_entry IS NOT NULL
LOOP
-- Get all the attributes for this entry.
l_attr_name := dbms_ldap.first_attribute(ld => g_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 => g_session,
ldapentry => l_entry,
attr => l_attr_name);
<<values_loop>>
FOR i IN l_vals.FIRST .. l_vals.LAST
LOOP
-- they are all going to be memberOf because we're searching for it.
IF l_attr_name = 'memberOf'
THEN
IF substr(l_vals(i), 4, instr(l_vals(i), ',') - 4) =
p_group
THEN
group_found := TRUE;
END IF;
IF apex_application.g_debug
THEN
-- apex_application.debug('ATTIBUTE_NAME: ' ||
-- l_attr_name || ' = [' ||
-- l_vals(i) || ']');
NULL;
END IF;
END IF;
END LOOP values_loop;
-- apex_application.debug('before next attribute');
l_attr_name := dbms_ldap.next_attribute(ld => g_session,
ldapentry => l_entry,
ber_elem => l_ber_element);
-- apex_application.debug('after next attribute');
END LOOP attibutes_loop;
-- apex_application.debug('before next entry');
l_entry := dbms_ldap.next_entry(ld => g_session, msg => l_entry);
--apex_application.debug('after next entry');
END LOOP entry_loop;
l_retval := dbms_ldap.unbind_s(g_session);
END IF;
RETURN group_found;
EXCEPTION
WHEN OTHERS THEN
htp.hr;
-- this will display a texbox in the page with the error in in.
htp.p(apex_item.textarea(p_idx => 1,
p_value => dbms_utility.format_error_stack,
p_rows => 5,
p_cols => 80));
htp.br;
RAISE;
END is_member;
</code>