LDAP Authentication with APEX

November 2, 2012 at 10:37 am | Posted in Oracle Application Express (Apex), Oracle Developement, PL/SQL | 13 Comments
Tags: , , , ,

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 ldap.mydomain.com 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 ldap.mydomain.com';
  l_principal VARCHAR2(30)  := 'APEX_040100'; -- upper case
  l_host      VARCHAR2(100) := 'ldap.mydomain.com';
  -- 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 ad.mydomain.com.

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: https://forums.oracle.com/forums/thread.jspa?threadID=954602
  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 := 'ldap.mydomain.com';
  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);


RSS feed for comments on this post. TrackBack URI

  1. For more information about LDAP authentication and some best practices, see http://www.ldapguru.info/ldap/authentication-best-practices.html

  2. Thanks Man. Great Post. Appreciated indeed!!

  3. 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.


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

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

  6. Excellent!!!

  7. Hi,

    Thanks for the post.

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

    For example, if my Active Directory domain is ad.example.com 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.


  8. 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?

  9. 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.

  10. Reblogged this on ..:::: EasyOraDBA | Shadab Mohammad ::::...

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

Entries and comments feeds.

%d bloggers like this: