Create User

Create a user to hold the authentication function.

CREATE USER apex_ldap_auth IDENTIFIED BY "&password"

DEFAULT TABLESPACE APEX

QUOTA UNLIMITED ON APEX;

ALTER USER apex_ldap_auth ACCOUNT LOCK;

Create ACL

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 => '&domaincontroller',

lower_port => 389,

upper_port => NULL);

COMMIT;

END;

/

Create Function

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) := 'mydomaincontroller';

l_ldap_port VARCHAR2(256) := '389';

l_ldap_base VARCHAR2(256) := 'dc=ad,dc=co,dc=uk';

l_dn_prefix VARCHAR2(100) := 'DOMAIN\'; -- 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);

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

/

GRANT EXECUTE ON apex_ldap_auth.ldap_auth TO &workspaceschema;

Test

SET SERVEROUTPUT ON

DECLARE

l_result BOOLEAN;

BEGIN

l_result := apex_ldap_auth.ldap_auth('&user', '&pass');

IF l_result THEN

DBMS_OUTPUT.put_line('OK');

ELSE

DBMS_OUTPUT.put_line('NOT OK');

END IF;

END;

/

Configure APEX to use Function

• Application > Shared Components > Authentication Schemes

• Click the "Create" button.

• Select the "Based on a pre-configured scheme from gallery" option and click the "Next" button.

• Enter a name, select the Scheme Type of "Custom" and an Authentication Function Name of "apex_ldap_auth.ldap_auth".

• Click the "Create Authentication Scheme" option.

• Run the application and test the authentication.

Notes

• If you were previously logging in with your email address, or another authentication scheme, then you will login as your AD account name (SAM) under the new authentication scheme (i.e. name@mydomaon.com will become myADname). This will mean that any private interactive grid reports will no longer be available when you log in using the new authentication scheme.

Bibliography