LDAP Authentication with users from different OU on Oracle APEX

On My company, the LDAP Administrator wanted to organize the users in the LDAP directory creating Organization Units (OU) for different groups of users, this made to fail my authentication scheme because it authenticated against the DN cn=%LDAP_USER%,ou=users,dc=company,dc=local but I had users in the DN cn=%LDAP_USER%,ou=CityA,ou=users,dc=company,dc=local, they couldn't authenticate on the app. In my environment, I have APEX 19.1, and Oracle 11G R2.

APEX has a built-in LDAP Authentication method where you can add the parameters to connect to the LDAP, one of those parameters is "Use Exact Distinguished Name (DN)" if you set this to "No", you can use a filter to find the user, but trying to use it I got this error:

In order to perform this operation a successful bind must be completed on the connection.

This is because I need a bind connection to perform searches, but fixing this was out of my hands, if you don't have this problem or you can modify the LDAP Directory to allow unbind searches, try to find more about that, if you have this problem too, keep reading. I searched a lot of posts and all of them solved this using a custom Authentication method, You'll need it if there are some users with the same user name on different OU, that is not my case, so I can make it more simple. If you have that problem, go to this post LDAP Authentications across multiple OU groups, I use some of it's code here. This is what We'll need to this

ACL to the LDAP Directory: We'll use functions to access to the LDAP directory, and our schema needs an ACL to connect.
A function to find the user and get the Complete DN
A user with a non expire password and limited privileges to log in and perform searches
Some configuration on the Built-in LDAP Authentication Schema

To create the ACL you need System privileges and run this Script replacing the corresponding data. Warning! I don't have a DataBase, so I can't test this code. If I have errors, please tell me.

BEGIN

--Create the ACL
DBMS_NETWORK_ACL_ADMIN.CREATE_ACL (
		acl => 'ldap.xml',
		description => 'LDAPConnection',
		principal => 'yourSchema',
		is_grant => TRUE,
		privilege => 'connect',
		start_date => null,
		end_date   => null); 

--Add Resolve privilege		
DBMS_NETWORK_ACL_ADMIN.add_privilege (
    acl        => 'ldap.xml',
    principal  => 'yourSchema',
    is_grant   => TRUE,
    privilege  => 'resolve',
    start_date => null,
    end_date   => null
  );		
		
--Add host and port
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL (
		acl => 'ldap.xml',
		host => 'ldapURL(ex:company.local)'
		lower_port  => 389 --ldap port, by default it is 389
		);
END;

When you have access to the LDAP Server, you can use this function to get the DN replacing the variables with your values. Maybe you need to modify line 26, it is working like this for me.

CREATE OR REPLACE function GET_DN( username varchar2) return varchar2
IS

l_ldap_host VARCHAR2(256) := 'ldap.domain.com'; --your ldap host
l_ldap_port VARCHAR2(256) := '389'; -- default port is '389';
l_ldap_user VARCHAR2(256) := 'searchUser'; --user used to search
l_ldap_passwd VARCHAR2(256) := 'PasswordIs0'; --password of the user
l_ldap_base VARCHAR2(256) := 'ou=users,dc=company,dc=local'; --ldap base for the search user

l_session DBMS_LDAP.session;
l_retval PLS_INTEGER;
l_attributes   DBMS_LDAP.STRING_COLLECTION;
l_ldap_filter  VARCHAR2(256);
l_message      DBMS_LDAP.MESSAGE;
l_num_entries  PLS_INTEGER;
l_user_dn      VARCHAR2(2000);


BEGIN

-- Connect to the LDAP server
l_session := DBMS_LDAP.init(hostname => l_ldap_host,
							portnum => l_ldap_port);

--Blind the connection using the user
l_retval := dbms_ldap.simple_bind_s( l_session, 'cn='||l_ldap_user ||','||l_ldap_base, l_ldap_passwd ); 


l_attributes(1) :='*';
l_ldap_filter := '(cn='||UserName||')';

--perfomr the search of the user
l_retval := DBMS_LDAP.search_s(ld => l_session,
                                 base => l_ldap_base,
                                 scope => DBMS_LDAP.SCOPE_SUBTREE,
                                 filter => l_ldap_filter,
                                 attrs => l_attributes,
                                 attronly => 0,
                                 res => l_message);

--Count then entries (you should get 1 or 0) 
l_num_entries:=DBMS_LDAP.count_entries(ld => l_session,
                                          msg => l_message);

--if found something, get the dn 
IF l_num_entries > 0 THEN
	l_user_dn := DBMS_LDAP.get_dn(ld => l_session,
								  ldapentry => l_message);
end if;

--Return the DN or null if nothing was found
return l_user_dn;
  

END GET_DN;

This function will search the user and return the DN, if you want to test it you can run this on a SQL window using an existent username

begin

dbms_output.put_line(GET_DN('USERNAME'));

end;

...

Now we have to create the authentication scheme of LDAP type (if you already have one, you can use it but I recommend creating another if we mess it up)

go to Shared Component / Authentication Schemes and create one new, select Based on a pre-configured scheme from the gallery and then select LDAP Directory Type and fill the fields with the corresponding data, I'll explain some fields

Host and Port: Correspond to your LDAP directory server, they should be the same used on the GET_DN function

Use SSL: Sorry, I don't understand at all this field, but here you can bind a connection if you configure a wallet, I couldn't do it, so I choose "No"

Distinguished Name (DN) String: This is the DN that will be used to search the user, usually you would use something like "cn=%LDAP_USER%,ou=users,dc=ldap,dc=domain" but we'll search the user's DN, so we only add %LDAP_USER%

Use Exact Distinguished Name (DN): We are getting the exact DN., so we set this to Yes.

LDAP Username Edit Function: Here we are using the function GET_DN to search the exact DN of the user and return it as the %LDAP_USER% variable

begin

return GET_DN( apex_escape.ldap_dn(:USERNAME));

end;

The apex_escape.ldap_dn() function is really important to escape special characters on the user's input before searching the DN.

Username Escaping: We are getting the User's DN, it includes special characters that's why we have to choose "No Escaping", to avoid the escaping of the special characters on the DN.

With this, APEX will escape the user's input on the "user field" of your login page, use the GET_DN function to find the User's DN, and then authenticate using that DN and the password typed by the user.

If everything goes fine, now your users can authenticate even if they are on different Organization Units using standard APEX Authentication schemas so, let's celebrate!!!!!