LDAP Authentication with APEX

November 2, 2012 at 10:37 am | Posted in Oracle Application Express (Apex), Oracle Developement, PL/SQL | 8 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
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;
About these ads

8 Comments »

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?

    Regards.

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

      Regards,
      Christoph

  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!!!


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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

Blog at WordPress.com. | The Pool Theme.
Entries and comments feeds.

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: