Hi
Using APEX5 on Oracle XE 11gr2 on Windows 7 (Test Box)
Trying to achieve LDAP Auth on a Test box, but receive error ORA-31204: DBMS_LDAP: PL/SQL - Invalid LDAP Session. when I execute
DECLARE
P_USERNAME VARCHAR2(200);
P_PASSWORD VARCHAR2(200);
v_Return BOOLEAN;
BEGIN
P_USERNAME := 'sso_test_am';
P_PASSWORD := 'Global100';
v_Return := AU_LOGINS.AUTHENTICATE(
P_USERNAME => P_USERNAME,
P_PASSWORD => P_PASSWORD
);
END;
/
[See AU_LOGINS.AUTHENTICATE code below]
The AUTHENTICATE function works in our DEV and PROD APEX4.2 environment, but not in APEX5 environment. Can anybody help with this??
I use this code to set up the ACL's
1) Delete existing ACL's
BEGIN
DBMS_NETWORK_ACL_ADMIN.drop_acl (
acl => 'power_users.xml');
DBMS_NETWORK_ACL_ADMIN.drop_acl (
acl => 'ldap_acl_file.xml');
COMMIT;
END;
/
2) Create new ACLS's
BEGIN
DBMS_NETWORK_ACL_ADMIN.create_acl (
acl => 'power_users.xml',
description => 'APEX5 Network Services',
principal => 'APEX_050000',
is_grant => TRUE,
privilege => 'connect'
);
DBMS_NETWORK_ACL_ADMIN.create_acl (
acl => 'ldap_acl_file.xml',
description => 'APEX5 Access to LDAP Active Directories',
principal => 'APEX_050000',
is_grant => TRUE,
privilege => 'connect'
);
COMMIT;
END;
/
3) Assign ACL's
BEGIN
DBMS_NETWORK_ACL_ADMIN.assign_acl (
acl => 'power_users.xml',
host => '*');
DBMS_NETWORK_ACL_ADMIN.assign_acl (
acl => 'ldap_acl_file.xml',
host => 'host1.net',
lower_port => 389,
upper_port => NULL);
DBMS_NETWORK_ACL_ADMIN.assign_acl (
acl => 'ldap_acl_file.xml',
host => 'host2.net',
lower_port => 389,
upper_port => NULL);
DBMS_NETWORK_ACL_ADMIN.assign_acl (
acl => 'ldap_acl_file.xml',
host => 'host3.net',
lower_port => 389,
upper_port => NULL);
COMMIT;
END;
/
4) I then run AUTHENTICATE function and receive error
ORA-31204: DBMS_LDAP: PL/SQL - Invalid LDAP Session
5) Authenticate function
create or replace PACKAGE BODY au_logins
AS
FUNCTION authenticate
(p_username IN VARCHAR2
,p_password IN VARCHAR2
)
RETURN BOOLEAN
is
--
-- New SSG domain combining SSG and Barcrest users
--
p_dn_sggaming varchar2(100) := p_username || '@xxxxxx.net';
p_ldap_host_sggaming varchar2(50) := 'host1.net';
--
-- Barcrest domain
--
p_dn_bar varchar2(100) := p_username || '@yyyyyyy.loc';
p_ldap_host_bar varchar2(50) := 'host2.net';
--
-- current SG domain
--
p_dn_sg varchar2(100) := p_username || '@zzzzzzz.global';
p_ldap_host_sg varchar2(50) := 'host3.net';
p_ldap_port number := 389;
l_retval pls_integer;
l_retval2 pls_integer;
l_session dbms_ldap.session;
begin
if p_password is null then
return false;
end if;
l_retval := -1;
dbms_ldap.use_exception := TRUE;
begin
l_session := dbms_ldap.init( p_ldap_host_sggaming, p_ldap_port );
l_retval := dbms_ldap.simple_bind_s( l_session, p_dn_sggaming, p_password );
l_retval2 := dbms_ldap.unbind_s( l_session );
return true;
exception when others then
l_retval2 := dbms_ldap.unbind_s( l_session );
begin
l_session := dbms_ldap.init( p_ldap_host_bar, p_ldap_port );
l_retval := dbms_ldap.simple_bind_s( l_session, p_dn_bar, p_password );
l_retval2 := dbms_ldap.unbind_s( l_session );
return true;
exception when others then
l_retval2 := dbms_ldap.unbind_s( l_session );
begin
l_session := dbms_ldap.init( p_ldap_host_sg, p_ldap_port );
l_retval := dbms_ldap.simple_bind_s( l_session, p_dn_sg, p_password );
l_retval2 := dbms_ldap.unbind_s( l_session );
return true;
exception when others then
l_retval2 := dbms_ldap.unbind_s( l_session );
return false;
end;
end;
end;
exception when others then
dbms_output.put_line(SQLERRM);
return false;
end authenticate;
END au_logins;
Kind Regards
Ade