Christoph's 2 Cents

A Backup for My Brain!

Oracle Application Express (Apex)Oracle DevelopementPL/SQL

LDAP Authentication with APEX

Here is a quick tutorial on how to set up LDAP authentication with Oracle Application Express 4.1.1.
Let’s assume that my LDAP server is on port 389. The ou group is “People”.
The first thing you need to do if working with Oracle database 11G, is to set up an access control list (ACL) for the Apex schema, so it can resolve and connect to the LDAP server. This step is not neccessary in database 10G.

-- Run as sysdba
l_acl       VARCHAR2(100) := 'ldapacl.xml';
l_desc      VARCHAR2(100) := 'LDAP Authentication for';
l_principal VARCHAR2(30)  := 'APEX_040100'; -- upper case
l_host      VARCHAR2(100) := '';
-- Create the new ACL.
-- Also, provide one starter privilege, granting the schema the privilege to connect.
dbms_network_acl_admin.create_acl(l_acl, l_desc, l_principal, TRUE, 'connect');

-- Now grant privilege to resolve DNS names.
dbms_network_acl_admin.add_privilege(l_acl, l_principal, TRUE, 'resolve');

-- Specify which hosts this ACL applies to.
dbms_network_acl_admin.assign_acl(l_acl, l_host);


Now configue a new authentication scheme in Application Express to authenticate via LDAP.

In your Apex application go to Shared Components -> Authentication Schemes, and click the Create button.

Choose the radio button Based on a pre-configured scheme from the gallery, and click Next.

Provide a name, for example LDAP Authentication.

Select the scheme type LDAP Directory.

Now the new Settings region appears. Fill in the values as in the example below, and make substitutions to host, domain, ou, etc. as necessary.

Note that the substitution string %LDAP_USER% will contain the username that you will enter in the login screen.

For Microsoft Active Directory the entry is slightly different (make substitutions as necessary). Note that in this example, the Active Directory server name is

Once all fields are filled in, click the Create button. You should now see the newly created authentication screen in the list, and marked as Current.

Now try to log into your application.

If you have trouble logging in, you can use the PL/SQL procedure below to trap any error messages:

-- Code by Scott Spadofore
-- OTN:
l_retval PLS_INTEGER;
l_retval2 PLS_INTEGER;
l_session dbms_ldap.session;
l_ldap_host VARCHAR2(256);
l_ldap_port VARCHAR2(256);
l_ldap_user VARCHAR2(256);
l_ldap_passwd VARCHAR2(256);
l_ldap_base VARCHAR2(256);

l_retval := -1;
dbms_ldap.use_exception := TRUE;
l_ldap_host := '';
l_ldap_port := '389';
l_ldap_user := 'uid=cruepprich,ou=People,dc=mydomain,dc=com';
l_ldap_passwd := 'secret';

l_session := dbms_ldap.init(l_ldap_host, l_ldap_port);
l_retval := dbms_ldap.simple_bind_s(l_session,
dbms_output.put_line('Return value: ' || l_retval);
l_retval2 := dbms_ldap.unbind_s(l_session);

dbms_output.put_line(rpad('ldap session ', 25, ' ') || ': ' ||
rawtohex(substr(l_session, 1, 8)) || '(returned from init)');
dbms_output.put_line('error: ' || SQLERRM || ' ' || SQLCODE);
dbms_output.put_line('user: ' || l_ldap_user);
dbms_output.put_line('host: ' || l_ldap_host);
dbms_output.put_line('port: ' || l_ldap_port);

l_retval := dbms_ldap.unbind_s(l_session);

13 thoughts on “LDAP Authentication with APEX

  • Yeja Paku

    Thanks Man. Great Post. Appreciated indeed!!

  • hi,
    what the “l_principal VARCHAR2(30) := ‘APEX_040100’;” means?
    i am usin apex 4.2, what should i put here as a value?


  • Muhammad,
    the principal is the schema to which access privileges are granted. If you’re using Apex 4.2, it would be APEX_040200.


  • Stefan

    Thanks for this post! DN String for Microsoft Active Directory works perfect!

  • Thanks for the post. I now have users logging in through their LDAP account. Great job!

  • Hi,

    Thanks for the post.

    I’m referring to the ‘’ example. In the LDAP settings you have mydomain\ %LDAP_USER%, would “mydomain” be the part in

    For example, if my Active Directory domain is would my domain be example?

    Also, is this %LDAP_USER% an apex built in variable and the value in it is the actual the username when the user log in?


  • Dude,
    you are correct. In your example, “example” would be the domain. The %LDAP_USER% holds the value of the username from the login page.


  • Thanks for the detail explanation. I just have one question. %LDAP_USER% will allow all the LDAP users to access the application. If only a particular groups in Active Directory say (accounting and finance) need to access the application, how do we restrict the users?

  • Hi dear,
    you have explained only one ou which is PEOPLE, what if i have more than 1 ou? and inside these ou, i have child ou as well…
    for example, i have main ou is HCT, under hct i have STAFF, under staff i have ETC, under ETC i have CSS.
    come back to HCT, under hct i have STUDENT, under student i have IT…. and so on.

    please guide me.

    thank you.

Comments are closed.