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

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. If not configure it

SELECT * FROM DATABASE_PROPERTIES
        WHERE PROPERTY_NAME = 'ROUTE_OUTBOUND_CONNECTIONS';

ALTER DATABASE PROPERTY SET ROUTE_OUTBOUND_CONNECTIONS = 'PRIVATE_ENDPOINT';

1. 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.