LDAP Authentication with APEX

November 2, 2012 at 10:37 am | Posted in Oracle Application Express (Apex), Oracle Developement, PL/SQL | 16 Comments
Tags: apex, application express, authentication, ldap, oracle

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
DECLARE
  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';
BEGIN
  -- 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);

  COMMIT;
END;

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
DECLARE
  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);
BEGIN

  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,
  l_ldap_user,
  l_ldap_passwd);
  dbms_output.put_line('Return value: ' || l_retval);
  l_retval2 := dbms_ldap.unbind_s(l_session);

EXCEPTION
  WHEN OTHERS THEN
    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);
END;