Integrate on-premise LDAP directory with APEX on Autonomous Database using custom wallet

Soma Dey
Oracle Developers
Published in
4 min readMay 21, 2024
Photo by Jonathan Duran on Unsplash

Customers frequently request the integration of APEX applications running on Autonomous Database with their private network LDAP directory server. This allows users to authenticate using their existing credentials. Typically, LDAP server uses its own private SSL certificate for security purposes. When the Autonomous Database (ADB) instance is on a private endpoint, a customer-managed wallet can be utilized to facilitate this integration.

We understand that APEX_WEB_REQUEST calls do not honor the custom wallet set with UTL_HTTP.set_wallet. However, this can be accomplished by using custom authentication in APEX.

Here is a guide book to integrate the on-prem LDAP directory server with APEX on autonomous database using a custom managed wallet.

Prerequisites:

  1. Verify that the Autonomous Database instance is configured with a private endpoint.
  2. Create a customer managed wallet with the SSL certificate used by LDAP server using orapki utility.
$ORACLE_HOME/bin/orapki wallet create -wallet /home/oracle/walletA -auto_login -pwd ******  

$ORACLE_HOME/bin/orapki wallet add -wallet /home/oracle/walletA -trusted_cert -cert /home/oracle/Cloudpro-Root-CA.cer -pwd ******

$ORACLE_HOME/bin/mkstore -wrl /home/oracle/walletA -createCredential secret-from-the-wallet 'example@oracle.com' ********

$ORACLE_HOME/bin/orapki wallet display -wallet /home/oracle/walletA -pwd *********

3. Upload the bucket in OCI object storage.

Steps to Integrate:

  1. Create a credential in ADB to access the object storage.
BEGIN
DBMS_CLOUD.CREATE_CREDENTIAL(
credential_name => 'PG_CRED_NAME',
username => 'oracleidentitycloudservice/*******@oracle.com',
password => '***********'
);
END;
/

2. create a new a directory object in ADB to upload the wallet file.

CREATE DIRECTORY WALLET_DIR AS 'LDAP_WALLET';

3. Upload the wallet in ADB.

BEGIN 
DBMS_CLOUD.GET_OBJECT(
credential_name => 'PG_CRED_NAME',
object_uri => 'https://*****.objectstorage.ap-hyderabad-1.oci.customer-oci.com/n/******/b/******/o/cwallet.sso',
directory_name => 'WALLET_DIR');
END;
/

4. Grant the required ACLs to APEX schema user to read the credentials from the specified directory.

Here - APEX SCHEMA - WKSP_PGATPWS 
LDAP server hostname - PG-HYD-DC-01.cloudpro.local


grant all on utl_http to WKSP_PGATPWS
grant all on dbms_ldap to WKSP_PGATPWS
grant read on Directory WALLET_DIR to WKSP_PGATPWS

BEGIN
DBMS_NETWORK_ACL_ADMIN.CREATE_ACL (
acl => 'ldap_access_WKSP_PGATPWS.xml',
description => 'Permissions to access LDAP servers.',
principal => 'WKSP_PGATPWS',
is_grant => TRUE,
privilege => 'connect');
COMMIT;
END;
/


BEGIN
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL (
acl => 'ldap_access_WKSP_PGATPWS.xml',
host => 'PG-HYD-DC-01.cloudpro.local',
lower_port => 636,
upper_port => 636
);
COMMIT;
END;
/

BEGIN
dbms_network_acl_admin.append_wallet_ace(
wallet_path => 'dir:WALLET_DIR',
ace => xs$ace_type(
privilege_list => xs$name_list('use_client_certificates', 'use_passwords'),
principal_name => 'WKSP_PGATPWS',
principal_type => xs_acl.ptype_db)
);
END;
/

5. Set the wallet path using UTL_HTTP procedure.

BEGIN
UTL_HTTP.set_wallet('DIR:WALLET_DIR', '<wallet password>');
END;
/

6. Test the DBMS_LDAP connectivity from ADB.

set serveroutput on size 30000
DECLARE
retval PLS_INTEGER;
my_session DBMS_LDAP.session;
ldap_host VARCHAR2(256);
ldap_port VARCHAR2(256);
ldap_user VARCHAR2(256);
ldap_passwd VARCHAR2(256);
ldap_base VARCHAR2(256);
BEGIN
retval := -1;

-- Please customize the following variables as needed
ldap_host := 'PG-HYD-DC-01.cloudpro.local' ;
ldap_port := '636';
ldap_user := 'CN=Administrator,CN=Users,DC=cloudpro,DC=local';
ldap_passwd:= '********';
ldap_base := 'DC=cloudpro,DC=local';
-- end of customizable settings

DBMS_OUTPUT.PUT('DBMS_LDAP Search Example ');
DBMS_OUTPUT.PUT_LINE('to directory .. ');
DBMS_OUTPUT.PUT_LINE(RPAD('LDAP Host ',25,' ') || ': ' || ldap_host);
DBMS_OUTPUT.PUT_LINE(RPAD('LDAP Port ',25,' ') || ': ' || ldap_port);

-- Choosing exceptions to be raised by DBMS_LDAP library.
DBMS_LDAP.USE_EXCEPTION := TRUE;

my_session := DBMS_LDAP.init(ldap_host,ldap_port);
-- to use a wallet and password for mode 2 or 3 use:
retval := DBMS_LDAP.open_ssl(my_session,'DIR:WALLET_DIR','',2);
-- retval := DBMS_LDAP.open_ssl(my_session, null, null, 1);

DBMS_OUTPUT.PUT_LINE (RPAD('Ldap session ',25,' ') || ': ' ||
RAWTOHEX(SUBSTR(my_session,1,8)) || '(returned from init)');

-- bind to the directory
retval := DBMS_LDAP.simple_bind_s(my_session, ldap_user, ldap_passwd);

DBMS_OUTPUT.PUT_LINE(RPAD('simple_bind_s Returns ',25,' ') || ': ' || TO_CHAR(retval));

-- unbind from the directory
retval := DBMS_LDAP.unbind_s(my_session);
DBMS_OUTPUT.PUT_LINE(RPAD('unbind_res Returns ',25,' ') || ': ' || TO_CHAR(retval));

DBMS_OUTPUT.PUT_LINE('Directory operation Successful .. exiting');
-- Handle Exceptions
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(' Error code : ' || TO_CHAR(SQLCODE));
DBMS_OUTPUT.PUT_LINE(' Error Message : ' || SQLERRM);
DBMS_OUTPUT.PUT_LINE(' Exception encountered .. exiting');
END;
/
Sample output….

DBMS_LDAP Search Example to directory ..
LDAP Host : PG-HYD-DC-01.cloudpro.local
LDAP Port : 636
Ldap session : 01000000(returned from init)
simple_bind_s Returns : 0
unbind_res Returns : 0
Directory operation Successful .. exiting


PL/SQL procedure successfully completed.

Elapsed: 00:00:10.626

7. Create a PL/SQL package from APEX schema to leverage the DBMS_LDAP for authentication.

create or replace FUNCTION AUTHENTICATE_USER_LDAP
(p_username in varchar2,
p_password in varchar2)
return boolean
is
l_user_name varchar2(500) := upper(p_username);
l_password varchar2(500);
v_session DBMS_LDAP.session;
v_result PLS_INTEGER;
begin
DBMS_LDAP.use_exception := TRUE;
v_session := DBMS_LDAP.init(hostname => 'PG-HYD-DC-01.cloudpro.local',
portnum => 636);
v_result := DBMS_LDAP.open_ssl(v_session, 'DIR:WALLET_DIR', '', 2);
v_result := DBMS_LDAP.simple_bind_s(v_session, l_user_name, p_password);
v_result := DBMS_LDAP.unbind_s(v_session);
APEX_UTIL.SET_AUTHENTICATION_RESULT(0);
return true;
exception
when others then
APEX_UTIL.SET_AUTHENTICATION_RESULT(7);
APEX_UTIL.SET_CUSTOM_AUTH_STATUS(SQLERRM);
return false;
end authenticate_user_LDAP;
/

8. Create a custom authentication scheme in APEX

9. Test the application with LDAP login.

Conclusion

Integrating an on-premises LDAP directory with APEX on Autonomous Database enhances security and streamlines user authentication by leveraging existing credentials. This integration not only simplifies the authentication process but also helps to maintain the integrity and security of the applications.

--

--

Soma Dey
Oracle Developers

/* Opinions expressed here are my own & do not express the views or opinions of my employer */