Tuesday, August 14, 2012

APEX and ACL trouble (ORA-31204)

Today, I had to resolve an issue with an APEX LDAP call, that succeeded in the developer database, but failed in test with an ORA-31204: DBMS_LDAP: PL/SQL - Invalid LDAP Session.
As this concerned 11G databases, my initial thought was: "Is an ACL defined?" On the other hand, I would have expected an error like "Access denied by ACL".
Anyway, check acl's:

column host format a30
column acl format a40
set pages 66 lines 132
select host, lower_port, upper_port, acl from dba_network_acls;

This resulted in to different lists; development had to more entries than test, and yes, test lacked an ACL where the LDAP server was mentioned. Checking the principals:

col principal for a30
select acl, principal from dba_network_acl_privileges;

This revealed APEX040100 was not in the list. The following code creates an ACL, adds the resolve privilege, and adds the LDAP server to this ACL:

begin
dbms_network_acl_admin.create_acl (
acl => 'ldap.xml',
description => 'Allow ldap queries',
principal => 'APEX_040100',
is_grant => TRUE,
privilege => 'connect'
);
end;
/


begin
dbms_network_acl_admin.add_privilege (
acl => 'ldap.xml',
principal => 'APEX_040100',
is_grant => TRUE,
privilege => 'resolve'
);
end;
/

begin
dbms_network_acl_admin.assign_acl(
acl => 'ldap.xml',
host => 'ldap.home.local'
);
end;
/

commit;

After that, not only the APEX user APEX_040100 is listed as principal for the newly created ACL, also the LDAP query now succeeds.