APEX_LDAP.AUTHENTICATE fails authentication for user names with high ASCII characters after upgrade to APEX 4.2.1

Environment: Application Express 4.2.1.00.08, Oracle Database 10.2.0.5.0, Microsoft Active Directory

Ok, this is a weird problem… We use a custom authentication function to validate and authenticate our application users against MS Active Directory. This custom function uses APEX_LDAP.AUTHENTICATE, which is actually some kind of wrapper around DBMS_LDAP.

After the upgrade from APEX 4.1.1 to 4.2.1, one of our users couldn’t log in any more. I double checked and his user name and password were correct. He could log in without any problems to the Windows domain. And he had used APEX before the upgrade, I saw that in the application logs.

It took some time to find out what the real problem was… Apparently, the user has a French character in his last name (e accent or “é”) and this caused the APEX_LDAP.AUTHENTICATE to fail and return false! When we replaced the French “é” by a low ASCII “e”, he could log in again…

I’m currently working with Oracle support on this. They must have changed something in APEX_LDAP.AUTHENTICATE that’s causing this behaviour… The strange thing is, DBMS_LDAP still works fine!! Based on feedback from Oracle support, I already tried to escape the user name using the function APEX_ESCAPE.LDAP_DN, but this didn’t help…

Below some examples.

This doesn’t work any more (it prints “not ok”):

begin
if APEX_LDAP.AUTHENTICATE(
p_username =>'TEST AIMÉ',
p_password => 'Abcd4567',
p_search_base => 'OU=Persons,OU=Users,OU=Belgium,OU=Domain Users,DC=be,DC=mydomain,DC=com',
p_host => 'dc.be.mydomain.com',
p_port => 389) then
dbms_output.put_line('ok');
else
dbms_output.put_line('not ok');
end if;
end;

But this still works (it prints “User authenticated!”):

DECLARE
    vSession DBMS_LDAP.session;
    vResult  PLS_INTEGER;
BEGIN
    DBMS_LDAP.use_exception := TRUE;
    vSession := DBMS_LDAP.init
                  ( hostname => 'dc.be.mydomain.com'
                  , portnum  => 389
                  );
    vResult  := DBMS_LDAP.simple_bind_s
                  ( ld     => vSession
                  , dn     => 'CN=TEST AIMÉ,OU=Persons,OU=Users,OU=Belgium,OU=Domain Users,DC=be,DC=mydomain,DC=com'
                  , passwd => 'Abcd4567'
                  );
    DBMS_Output.put_line('User authenticated!');
    vResult  := DBMS_LDAP.unbind_s(vSession);
END;

Weird, isn’t it??

Matthias

ORA-24247 during LDAP authentication from APEX 4.1.1 on Oracle 11gR2

Environment: APEX 4.1.1, Oracle database 11.2.0.3.0, Oracle Linux 6.2

In Oracle database 11g, access to external network resources has been more restricted than in previous versions. Access to network resources is now controlled through ACL’s (Access Control Lists). This can lead to various problems when you migrate APEX applications from a server running Oracle 10g to one running 11g.For example, if you wrote your own LDAP authentication functions using the built-in DBMS_LDAP package, you will receive the following error message when you try to authenticate to LDAP:

ORA-24247: network access denied by access control list (ACL)

This is because the owner of the authentication function lacks access to the required network resources. You can easily test this with the following piece of PL/SQL code:

declare
l_session dbms_ldap.session;
begin
l_session := dbms_ldap.init('windowsdc.mydomain.com',389);
end;

In this case, “windowsdc.mydomain.com” is a Windows LDAP server running Microsoft’s Active Directory.

To grant access to a specific network resource in 11g, you first need to create a ACL (Access Control List). I executed this with SYS as SYSDBA:

BEGIN
   DBMS_NETWORK_ACL_ADMIN.CREATE_ACL (
    acl          => 'ldap_access.xml',
    description  => 'Permissions to access LDAP servers.',
    principal    => 'MATTHIASH',
    is_grant     => TRUE,
    privilege    => 'connect');
   COMMIT;
END;

In this example, “ldap_access.xml” is the name of my ACL, and “MATTHIASH” is the name of the user account which needs access to the LDAP server. This account owns my custom LDAP authentication function.

Next, you need to add the LDAP server to the ACL we just created (don’t forget to COMMIT):

BEGIN
   DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL (
    acl          => 'ldap_access.xml',
    host         => 'windowsdc.mydomain.com',
    lower_port   => 389,
    upper_port   => 389
    );
   COMMIT;
END;

You can check the ACL using the following queries:

SELECT * FROM DBA_NETWORK_ACLS;
SELECT * FROM DBA_NETWORK_ACL_PRIVILEGES;

That’s it! You can now test the DBMS_LDAP.INIT example again and it should work! You can add more users to the ACL as follows:

begin
      DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE('/sys/acls/ldap_access.xml',
     'NEWUSER', TRUE, 'connect');
     COMMIT;
end;

Removing users from a ACL can be done using the package  DBMS_NETWORK_ACL_ADMIN.DELETE_PRIVILEGE:

begin
      DBMS_NETWORK_ACL_ADMIN.DELETE_PRIVILEGE('/sys/acls/ldap_access.xml', 
     'NEWUSER', TRUE, 'connect');
     COMMIT;
end;

 

Matthias