8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
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.
- Create User
- Network ACL
- Authentication Function
- Test It
- Create Custom Authentication Scheme
- LDAPS Support
Related articles.
- DBMS_LDAP - Accessing LDAP From PL/SQL
- Fine-Grained Access to Network Services in Oracle Database 11g Release 1
- APEX 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.
- 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.
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:
- DBMS_LDAP - Accessing LDAP From PL/SQL
- Fine-Grained Access to Network Services in Oracle Database 11g Release 1
- Using LDAP for Authentication and Authorization within APEX
- APEX Articles
Hope this helps. Regards Tim...