Encountering a certificate validation failure while utilizing the APEX_WEB_SERVICE API to call a public web service prompted this error in a newly installed local Oracle APEX environment. This issue doesn’t arise when utilizing Oracle OCI Autonomous or APEX services, as Oracle automatically generates a TLS wallet, inclusive of almost every SSL/TLS certificate.
select apex_web_service.make_rest_request
(p_url => 'https://api.publicapis.org/entries',
p_http_method => 'GET') from dual;
/* Output */
ORA-06512: at "APEX_230100.WWV_FLOW_WEB_SERVICES", line 1325
ORA-06512: at "APEX_230100.WWV_FLOW_WEB_SERVICES", line 897
ORA-29024: Certificate validation failure
ORA-06512: at "SYS.UTL_HTTP", line 380
In the event of encountering such an error, the following steps were undertaken to address and resolve the issue:
Create Access Control List (ACLs)
Failing to configure Access Control Lists (ACLs) when making web service calls triggers an “ORA-12545: Connect failed” error. ACLs serve as a vital security layer, enabling outbound requests from the APEX application to the targeted web services. In the provided example, an ACL is established to grant the TALA schema connection access to any hosts.
-- Example ACL to allow the schema TALA
-- connect access to any host '*'
BEGIN
DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
host => '*',
ace => xs$ace_type(privilege_list => xs$name_list('connect'),
principal_name => 'TALA',
principal_type => xs_acl.ptype_db));
END;
Most of the https endpoints are secured by TLS/SSL certificates. This are the standard by all major web browsers to ensure a safer internet experience for users. Websites secured by TLS/SSL certificates are more trusted by internet users because they encrypt and protect private information transferred to and from their website. They also represent, or certify, your website’s brand identity.
Create Database Wallet
Before initiating the database wallet creation, the certificate must be exported from the web services. This can be conveniently done by making a call from a web browser.
Following the export, transfer the certificate to the database server and commence the wallet creation using the orapki utility. While the utility might not be found in the bin folder, it is available in Oracle SQLcl (SQL Developer Command Line).
# find the "xdb_wallet" folder and create a TLS_folder
# start from /opt directory
find . -type d -name "xdb_wallet"
./oracle/admin/FREE/xdb_wallet
# in this case it is located in ./oracle/admin/FREE/xdb_wallet
cd /opt/oracle/admin/FREE/xdb_wallet
# copy the exported certificate here
# log in to SQL command
sql /nolog
# command to create wallet
SQL> orapki wallet create -wallet . -auto_login
# add certificate to wallet command
SQL> orapki wallet add -wallet . -pwd <walletPassword> -trusted_cert -cert ISRG_Root_X1.crt;
# To view the certificate
SQL> orapki wallet display -wallet ./;
After importing the certificate, it should be visible in the Trusted Certificates.
Reference the Wallet to Apex
Web services can be invoked by referencing the wallet from the API. The wallet path can be explicitly specified in the API call, as illustrated below:
select apex_web_service.make_rest_request
(p_url => 'https://api.publicapis.org/entries',
p_http_method => 'GET',
p_wallet_path => 'file:/opt/oracle/admin/FREE/xdb_wallet/tls_wallet') from dual;
To simplify, the wallet path can be configured from the internal workspace, allowing APEX_WEB_SERVICE to use the configured TLS_WALLET by default, unless the p_wallet_path parameter is explicitly stated. Given that the wallet is set as auto-login, there is no need to provide the wallet password or the p_wallet_pwd parameter in the API call.
APEX_WEB_SERVICE will use the TLS_WALLET configured here unless you specify the p_wallet_path parameter. Since I created the wallet as auto-login, I don’t need to put the wallet password and I don’t need to specify the p_wallet_pwd from the API.
