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

Published

APEX version

Database version

June 2017

all

all

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.

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.

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.

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

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.

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

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.

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.

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.