Application Express and HTTPS: Never see "Certificate Validation Error" again

In this article we'll talk about performing HTTPS requests with Application Express. At the first glance, this is pretty simple - instead of an URL beginning with http://, we use one beginning with https://. As an example, we'll try out the HTTPS URL of the USGS (US Geological Survey), which is also being used in the article about using REST services in Application Express. The following code example tries to fetch a JSON feed from earthquake.usgs.gov using HTTPS.

select apex_web_service.make_rest_request(
    p_url         => 'https://earthquake.usgs.gov/earthquakes/feed/v1.0/summary/2.5_day.geojson', 
    p_http_method => 'GET' ) from dual;

In most cases, the first attempt fails:

ORA-29273: HTTP request failed
ORA-29024: Certificate validation failure
ORA-06512: at "SYS.UTL_HTTP", line 380

Blogs and discussion forums are full of questions and answers how to deal with this situation. This article provides an explanation and explains how to solve the problem for PL/SQL as well as for Application Express. The error message basically states that the Oracle database was not able to validate the SSL certificate, which the external web server sent in order to prove its identity. To get certificate validation working, we need to create an Oracle Wallet and configure it in Application Express. But before doing that, here is a simple explanation about HTTPS and SSL certificates - "in a nutshell" (these explanations are far away from being complete, but they should be sufficient to understand what the Oracle Database is doing and what the Oracle Wallet is needed for).

When an HTTPS request to a webserver is done, the first step is the SSL handshake, in which client and server negotiate about the details of the SSL encryption. As part of this handshake, the server sends its SSL certificate, which is similar to an ID card for humans: The certificate is there to prove, that the responding server is really the one, which the client expects.

The obvious question is now, whether the ID card is authentic - and this authenticity is certified by a Certificate Authority (CA). The web servers' SSL certificate is "signed" by the certificate authority using - again - a certificate. Now we can ask, whether the CA's certificate is authentic ... so it might be signed by another CA - and we have another certificate in the game. It's obvious that, at some point, this chain must come to an end. The client has to trust one CA certificate without looking into who has signed it - such a trusted certificate is then the end of the chain.

In a browser like Firefox, Chrome or others, all common CA certificates are pre-installed and this list of certificates is also being updated with each browser update. The Oracle Database maintains such certificates in an Oracle Wallet - but this wallet has to be created; and right after creation it is empty. We have to add the CA certificates we need.

To create and configure the wallet, we need access to the file system of the database server; when the wallet is ready, it must reside in a folder in the database server's file system. On a private development system (on a laptop) this is typically no problem; on a production system you will need the help of the database administrator. Thus, as the next step, log into the database server and create a wallet using the orapki utility, which is part of the database installation.

$ orapki
Oracle PKI Tool : Version 12.2.0.1.0
Copyright (c) 2004, 2016, Oracle and/or its affiliates. All rights reserved.

orapki [crl|wallet|cert|help] <-nologo> <-jsafe>

Create the (empty) wallet as follows:

$ orapki wallet create -wallet https_wallet -pwd ****** -auto_login
Oracle PKI Tool : Version 12.2.0.1.0
Copyright (c) 2004, 2016, Oracle and/or its affiliates. All rights reserved.

Operation is successfully completed.

The folder, in which you have invoked orapki should now contain a subfolder named https_wallet. These files make up the wallet. When referencing the wallet later on, only the path up to the folder https_wallet is needed.

$ ls 
https_wallet 
$ ls https_wallet/
cwallet.sso  cwallet.sso.lck  ewallet.p12  ewallet.p12.lck

Next, use your browser to navigate to the HTTPS URL you are about to access with APEX_WEB_SERVICE or UTL_HTTP (in this example, we use Firefox). The following screenshots show, how earthquake.usgs.gov is accessed with the browser and how SSL certificate information can be revealed (start by clicking the tiny lock left to the browser address bar).

Look up SSL-related information in the browser

The fourth dialog of the above screenshots displays the Certificate Chain: The SSL certificate of earthquake.usgs.gov is signed with a CA certificate from Symantec; and this has been signed by Verisign Class 3 Public Primary Certification Authority. The last one is not signed by another CA, Firefox trusts it.

Thus, in order to access earthquake.usgs.gov with APEX_WEB_SERVICE or UTL_HTTP, we need to add the certificate from Verisign Class 3 Public Primary Certification Authority into the Oracle Wallet as a Trusted Certificate. First we need to download it - and the good news is that you can use the Firefox browser to do this: In the dialog, which displays the Certificate Chain, first click on Verisign Class 3 Public Primary Certification Authority and then in the lower left corner, on the Export button. Download the file (VeriSignClass3PublicPrimaryCertificationAuthority-G5.crt) to your local computer and tranfer it to the filesystem of the database server.

Download the CA certificate to your local computer with the Firefox browser

Add the CA certificate to your wallet with the orapki utility.

$ orapki wallet add -wallet https_wallet 
                    -cert VeriSignClass3PublicPrimaryCertificationAuthority-G5.crt 
                    -trusted_cert 
                    -pwd ******
Oracle PKI Tool : Version 12.2.0.1.0
Copyright (c) 2004, 2016, Oracle and/or its affiliates. All rights reserved.

Operation is successfully completed.

Now the database should be able to validate the SSL certificate of earthquake.usgs.gov. Before configuring the wallet for Application Express, we might test it in SQL*Plus: When using APEX_WEB_SERVICE.MAKE_REST_REQUEST, we can pass a wallet path as the P_WALLET_PATH argument.

select apex_web_service.make_rest_request(
    p_url         => 'https://earthquake.usgs.gov/earthquakes/feed/v1.0/summary/2.5_day.geojson', 
    p_http_method => 'GET',
    p_wallet_path => 'file:///path/to/directory/https_wallet' ) from dual;

APEX_WEB_SERVICE.MAKE_REST_REQUEST('HTTPS://EARTHQUAKE.USGS.GOV/EARTHQUAKES/FEED
--------------------------------------------------------------------------------
{"type":"FeatureCollection","metadata":{"generated":1496331042000,"url":"https:/
/earthquake.usgs.gov/earthquakes/feed/v1.0/summary/2.5_day.geojson","title":"USG
S Magnitude 2.5+ Earthquakes, Past Day","status":200,"api":"1.5.7","count":15},"
features":[{"type":"Feature","properties":{"mag":3.18,"place":"27km WSW of Westm
:

When this test has been successful, it makes sense to configure the wallet in Application Express, making it available to all workspaces and applications and to all APEX PL/SQL packages. Then we don't have to pass a wallet argument in APEX_WEB_SERVICE calls any more.

Log into the INTERNAL workspace of your Application Express installation and navigate to Instance Settings.

Workspace INTERNAL - Instance Settings

Then navigate to the Wallet section and enter the path to the wallet directory (with the file:// prefix) as the Wallet Path attribute. Since we have used the -auto_login switch when creating the wallet, we don't need to set a password.

Configure the path to the wallet files as the Wallet Path attribute in Application Express Instance Settings

If you need to add more certificates, for other HTTPS endpoints, simply repeat the above steps and add these to the existing wallet. As a second example, we want to access the Github API (api.github.com).

begin
    apex_web_service.g_request_headers( 1 ).name  := 'User-Agent';
    apex_web_service.g_request_headers( 1 ).value := 'Application Express';
end;
/

select apex_web_service.make_rest_request(
    p_url         => 'https://api.github.com/',
    p_http_method => 'GET' ) from dual;

ORA-29273: HTTP request failed
ORA-29024: Certificate validation failure
ORA-06512: at "SYS.UTL_HTTP", line 380

Open the github.com page in the Firefox browser, open the dialog containing the SSL certificate details, navigate to the certificate chain, select the root CA certificate and export it to your local computer.

Export the root certificate for the github.com website

Finally add the file DigiCertHighAssuranceEVRootCA.crt to your existing wallet with orapki.

$ orapki wallet add -wallet https_wallet 
                    -cert /apex_install/DigiCertHighAssuranceEVRootCA.crt 
                    -trusted_cert 
                    -pwd ******
Oracle PKI Tool : Version 12.2.0.1.0
Copyright (c) 2004, 2016, Oracle and/or its affiliates. All rights reserved.

Operation is successfully completed.

When you are testing with SQL*Plus, log out and in again in order to make sure that the wallet files are being re-read.

begin
    apex_web_service.g_request_headers( 1 ).name  := 'User-Agent';
    apex_web_service.g_request_headers( 1 ).value := 'Application Express';
end;
/

select apex_web_service.make_rest_request(
    p_url         => 'https://api.github.com/',
    p_http_method => 'GET' ) as response from dual;


RESPONSE
--------------------------------------------------------------------------------
{"current_user_url":"https://api.github.com/user","current_user_authorizations_h

With these steps you can add the necessary CA certificates for almost every HTTPS endpoint in the intranet or the internet.

Finally we'll talk about another class of HTTPS endpoints: Servers using a self-signed certificate. Development or test servers often skip the process of getting an authentic SSL certificate from a certificate authority. Instead a self-signed SSL certificate is generated. This kind of certificate is not signed by a CA, instead it's signs itself. Of course, such a certificate proves nothing, and therefore every browser shows a warning when such a web site is being accessed. The Oracle database will consequently also throw a "certificate validation error" for these as well.

Browsers show a warning when a web site with a self-signed certificate is accessed

Imn Firefox, click on the Add Exception button (other browsers should show a similar one). Then you should see a dialog as follows.

The browser shows details about the web site using the self-signed certificate

Clicking the Confirm Security Exception button would lead the browser to continue accessing the web site and to finally display it. But we want to access this HTTPS endpoint with the database, so we need to download this self-signed certificate. Thus click the View button in order to get to the details of the certificate itself. You should already know this dialog. It shows a "certificate chain" with only one entry and in the lower left, you'll find the Export button.

Export the self-signed certificate

Then, as before, add the downloaded file to the existing wallet with orapki.

$ orapki wallet add -wallet https_wallet 
                    -cert /apex_install/serverwithselfsignedcert.crt 
                    -trusted_cert 
                    -pwd ******
Oracle PKI Tool : Version 12.2.0.1.0
Copyright (c) 2004, 2016, Oracle and/or its affiliates. All rights reserved.

Operation is successfully completed.

If you have multiple server using a self-signed certificate you have to take these steps for each of them. If you have multiple databases or larger systems with requirements to access multiple HTTPS endpoints on the internet or within the intranet, you might think about setting up a process to maintain Oracle Wallets ... this process should define steps to ...

• ... get new CA certificates into the Wallets. Request, Approval, Implementation
• ... renew expired CA certificates
• ... deploy the wallet to all databases after changes have been applied.

back to blogs.oracle.com/aoex