8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Home » Articles » Misc » Here

Oracle Application Express (APEX) LDAP Authentication

There are several ways to set up LDAP authentication within APEX, but some of them do not seem to work as well as others. In my opinion, the most consistent way to do it is to code the authentication manually as a stored procedure using DBMS_LDAP and define a custom authentication scheme to call it. This article describes that approach.

I've used this method for all versions from APEX 4 onward. I still prefer this method over the LDAP authentication scheme built into APEX, as this method can be defined centrally, and it is really easy for a developer to include it into their application.

Related articles.

Create User

We need a user to hold the authentication function. Notice the user doesn't have CREATE SESSION privilege, as nothing should ever need to connect to it.

CREATE USER apex_ldap_auth IDENTIFIED BY NeverLogIn123
  DEFAULT TABLESPACE APEX
  QUOTA UNLIMITED ON APEX;

ALTER USER apex_ldap_auth ACCOUNT LOCK;

Network ACL

From Oracle 11g onward, access to network services is controlled using Access Control Lists (ACLs). You will need to create an ACL to allow access to the LDAP server. An example of an ACL is shown below.

BEGIN
  DBMS_NETWORK_ACL_ADMIN.create_acl (
    acl          => 'ldap_acl_file.xml', 
    description  => 'ACL to grant access to LDAP server',
    principal    => 'APEX_LDAP_AUTH',
    is_grant     => TRUE, 
    privilege    => 'connect',
    start_date   => SYSTIMESTAMP,
    end_date     => NULL);

  DBMS_NETWORK_ACL_ADMIN.assign_acl (
    acl         => 'ldap_acl_file.xml',
    host        => 'ldap.example.com', 
    lower_port  => 389,
    upper_port  => NULL);

  DBMS_NETWORK_ACL_ADMIN.assign_acl (
    acl         => 'ldap_acl_file.xml',
    host        => 'ldaps.example.com', 
    lower_port  => 686,
    upper_port  => NULL);

  COMMIT;
END;
/

Authentication Function

The LDAP_AUTH function is created in the APEX_LDAP_AUTH schema. It accepts the username and password and returns a boolean of TRUE if the authentication is successful or FALSE if not. You will need to adjust the LDAP settings before using it.

CREATE OR REPLACE FUNCTION apex_ldap_auth.ldap_auth(
  p_username IN VARCHAR2,
  p_password IN VARCHAR2
)
RETURN BOOLEAN IS
  l_ldap_host     VARCHAR2(256) := 'ldap.example.com';
  l_ldap_port     VARCHAR2(256) := '389';
  l_ldap_base     VARCHAR2(256) := 'dc=adf,dc=example,dc=com';
  l_dn_prefix     VARCHAR2(100) := 'ADF\'; -- Amend as desired'.

  l_retval        PLS_INTEGER;
  l_session       DBMS_LDAP.session;
BEGIN
  IF p_username IS NULL OR p_password IS NULL THEN
    RAISE_APPLICATION_ERROR(-20000, 'Credentials must be specified.');
  END IF;

  -- Choose to raise exceptions.
  DBMS_LDAP.use_exception := TRUE;
  
  -- Connect to the LDAP server.
  l_session := DBMS_LDAP.init(hostname => l_ldap_host,
                              portnum  => l_ldap_port);
  
  l_retval := DBMS_LDAP.simple_bind_s(ld     => l_session,
                                      dn     => l_dn_prefix || p_username,
                                      passwd => p_password);

  l_retval := DBMS_LDAP.unbind_s(l_session); 

  -- No exceptions mean you are authenticated.
  RETURN TRUE;
EXCEPTION
  WHEN OTHERS THEN
    -- Exception means authentication failed. 
    l_retval := DBMS_LDAP.unbind_s(ld => l_session);
    APEX_UTIL.set_custom_auth_status(p_status => 'Incorrect username and/or password');
    RETURN FALSE;    
END;
/

The previous function only checks LDAP authentication. If you need to authorize against a specific LDAP group as well, you will need to add a little more code, such as the following. Thanks the Roel Hartman for his blog post on this subject, which saved me a lot of time.

CREATE OR REPLACE FUNCTION apex_ldap_auth.ldap_auth(
  p_username IN VARCHAR2,
  p_password IN VARCHAR2
)
RETURN BOOLEAN IS
  l_ldap_host     VARCHAR2(256) := 'ldap.example.com';
  l_ldap_port     VARCHAR2(256) := '389';
  l_ldap_base     VARCHAR2(256) := 'dc=adf,dc=example,dc=com';
  l_dn_prefix     VARCHAR2(100) := 'ADF\'; -- Amend as desired'.
  l_auth_group    VARCHAR2(100) := 'MY_APP_LDAP_GROUP'; -- Amend as desired'.

  l_retval        PLS_INTEGER;
  l_session       DBMS_LDAP.session;
  l_attrs         DBMS_LDAP.string_collection;
  l_message       DBMS_LDAP.message;
  l_entry         DBMS_LDAP.message;
  l_attr_name     VARCHAR2(256);
  l_ber_element   DBMS_LDAP.ber_element;
  l_vals          DBMS_LDAP.string_collection;
  l_ok            BOOLEAN;
BEGIN
  IF p_username IS NULL OR p_password IS NULL THEN
    RAISE_APPLICATION_ERROR(-20000, 'Credentials must be specified.');
  END IF;

  -- Choose to raise exceptions.
  DBMS_LDAP.use_exception := TRUE;
  
  -- Connect to the LDAP server.
  l_session := DBMS_LDAP.init(hostname => l_ldap_host,
                              portnum  => l_ldap_port);
  
  l_retval := DBMS_LDAP.simple_bind_s(ld     => l_session,
                                      dn     => l_dn_prefix || p_username,
                                      passwd => p_password);
                                      
  -- No exceptions mean you are authenticated. Now check if authorized.

  -- Get all "memberOf" attributes    
  l_attrs(1) := 'memberOf';

  -- Searching for the user info using his samaccount (windows login)
  l_retval := DBMS_LDAP.search_s(ld       => l_session,
                                 base     => l_ldap_base,
                                 scope    => DBMS_LDAP.scope_subtree,
                                 filter   => '(&(objectClass=*)(sAMAccountName=' || p_username || '))',
                                 attrs    => l_attrs,
                                 attronly => 0,
                                 res      => l_message);
  
  -- Get the first and only entry.
  l_entry := DBMS_LDAP.first_entry(ld  => l_session,
                                   msg => l_message);
  
  -- Get the first Attribute for the entry.
  l_attr_name := DBMS_LDAP.first_attribute(ld        => l_session,
                                           ldapentry => l_entry,       
                                           ber_elem  => l_ber_element);

  -- Loop through all "memberOf" attributes  
  WHILE l_attr_name IS NOT NULL LOOP
    -- Get the values of the attribute
    l_vals := DBMS_LDAP.get_values(ld        => l_session,
                                   ldapentry => l_entry,
                                   attr      => l_attr_name);

    -- Check the contents of the value
    FOR i IN l_vals.FIRST .. l_vals.LAST LOOP
      -- Check the user is a member of the required group. 
      l_ok := INSTR(UPPER(l_vals(i)), l_auth_group) > 0 ;
      EXIT WHEN l_ok;
    END LOOP;
    EXIT WHEN l_ok;    

    l_attr_name := DBMS_LDAP.next_attribute(ld        => l_session,
                                            ldapentry => l_entry,       
                                            ber_elem  => l_ber_element );
  END LOOP;

  l_retval := dbms_ldap.unbind_s(ld => l_session);
 
  IF NOT l_ok THEN
    APEX_UTIL.set_custom_auth_status (p_status => 'You are not in the correct LDAP group to use this application.');
  END IF;  

  -- Return authentication + authorization result.  
  RETURN l_ok;
EXCEPTION
  WHEN OTHERS THEN
    -- Exception means authentication failed. 
    l_retval := DBMS_LDAP.unbind_s(ld => l_session);
    APEX_UTIL.set_custom_auth_status (p_status => 'Incorrect username and/or password');
    RETURN FALSE;    
END;
/

Make sure the relevant workspace user can execute the procedure. For example, if my workspace user were called MY_WORKSPACE_USER, I would do the following.

GRANT EXECUTE ON apex_ldap_auth.ldap_auth TO my_workspace_user;

Test It

Regardless of the type of authentication function you use, you can test it using the following code.

SET SERVEROUTPUT ON
DECLARE
  l_result BOOLEAN;
BEGIN
  l_result := apex_ldap_auth.ldap_auth('myuser', 'mypassword');
  IF l_result THEN
    DBMS_OUTPUT.put_line('OK');
  ELSE
    DBMS_OUTPUT.put_line('NOT OK');
  END IF;
END;
/
OK

PL/SQL procedure successfully completed.

SQL>

Create Custom Authentication Scheme

With the authentication function in place, you can create a custom authentication scheme as follows.

LDAPS Support

The DBMS_LDAP package supports LDAP over SSL (LDAPS), so this code can easily be adjusted to work with LDAPS using the method described here.

The example below is a repeat of the first example, but this time using LDAPS. Notice the OPEN_SSL call, specifying a wallet location and wallet password. The wallet must already exist in this directory, and it must contain the root certificate of the LDAPS server as a trusted certificate. You can read more about wallets here.

CREATE OR REPLACE FUNCTION apex_ldap_auth.ldap_auth(
  p_username IN VARCHAR2,
  p_password IN VARCHAR2
)
RETURN BOOLEAN IS
  l_ldap_host     VARCHAR2(256) := 'ldaps.example.com';
  l_ldap_port     VARCHAR2(256) := '636';
  l_ldap_base     VARCHAR2(256) := 'dc=adf,dc=example,dc=com';
  l_dn_prefix     VARCHAR2(100) := 'ADF\'; -- Amend as desired'.

  l_retval        PLS_INTEGER;
  l_session       DBMS_LDAP.session;
BEGIN
  IF p_username IS NULL OR p_password IS NULL THEN
    RAISE_APPLICATION_ERROR(-20000, 'Credentials must be specified.');
  END IF;

  -- Choose to raise exceptions.
  DBMS_LDAP.use_exception := TRUE;
  
  -- Connect to the LDAP server.
  l_session := DBMS_LDAP.init(hostname => l_ldap_host,
                              portnum  => l_ldap_port);
  
  l_retval := DBMS_LDAP.open_ssl(ld              => l_session,
                                 sslwrl          => 'file:/home/oracle/wallet',
                                 sslwalletpasswd => 'WalletPassword',
                                 sslauth         => 2); 

  l_retval := DBMS_LDAP.simple_bind_s(ld     => l_session,
                                      dn     => l_dn_prefix || p_username,
                                      passwd => p_password);
                                      
  l_retval := DBMS_LDAP.unbind_s(l_session); 

  -- No exceptions mean you are authenticated.
  RETURN TRUE;
EXCEPTION
  WHEN OTHERS THEN
    -- Exception means authentication failed. 
    l_retval := DBMS_LDAP.unbind_s(ld => l_session);
    APEX_UTIL.set_custom_auth_status(p_status => 'Incorrect username and/or password');
    RETURN FALSE;    
END;
/

For more information see:

Hope this helps. Regards Tim...

Back to the Top.