Oracle 8i | Oracle 9i | Oracle 10g | Oracle 11g | Oracle 12c | Miscellaneous | PL/SQL | SQL | Oracle RAC | Oracle Apps | Linux

UTL_HTTP and SSL (HTTPS) using Oracle Wallets

Since Oracle 9i Release 2, the UTL_HTTP package has had the ability to access resources over HTTPS as well as HTTP. This article describes the method for enabling HTTPS access from the UTL_HTTP package.

Access Control List (ACL)

If you are using Oracle 11g, you will need to provide an ACL to allow the UTL_HTTP package to interact with an external host. This is described here.

Test Unsecured Connection

Before we start trying to configure SSL, lets see what happens if we attempt to access a HTTPS resource using the UTL_HTTP package. To do this, create the following procedure.

CREATE OR REPLACE PROCEDURE show_html_from_url (p_url  IN  VARCHAR2) AS
  l_http_request   UTL_HTTP.req;
  l_http_response  UTL_HTTP.resp;
  l_text           VARCHAR2(32767);
BEGIN
  -- Make a HTTP request and get the response.
  l_http_request  := UTL_HTTP.begin_request(p_url);
  l_http_response := UTL_HTTP.get_response(l_http_request);

  -- Loop through the response.
  BEGIN
    LOOP
      UTL_HTTP.read_text(l_http_response, l_text, 32766);
      DBMS_OUTPUT.put_line (l_text);
    END LOOP;
  EXCEPTION
    WHEN UTL_HTTP.end_of_body THEN
      UTL_HTTP.end_response(l_http_response);
  END;
EXCEPTION
  WHEN OTHERS THEN
    UTL_HTTP.end_response(l_http_response);
    RAISE;
END show_html_from_url;
/

This procedure works for a regular HTTP resource, but what happens if we call it using a HTTPS resource? The following example uses "https://gb.redhat.com/".

SET SERVEROUTPUT ON
EXEC show_html_from_url('https://gb.redhat.com/');

*
ERROR at line 1:
ORA-29273: HTTP request failed
ORA-06512: at "SYS.UTL_HTTP", line 1527
ORA-29261: bad argument
ORA-06512: at "TEST.SHOW_HTML_FROM_URL", line 22
ORA-29273: HTTP request failed
ORA-06512: at "SYS.UTL_HTTP", line 1130
ORA-29024: Certificate validation failure
ORA-06512: at line 1

SQL>

The error stack shows the "ORA-29024: Certificate validation failure" error.

Get Site Certificates

In order to make connections to a secured resource, we need to get the necessary certificates. The easiest way to do this is using a browser. The example below uses the Chrome browser.

Using the browser, go to the URL you are attempting to access from PL/SQL. In this case "https://gb.redhat.com/". Click the lock icon in the URL bar to display the certificate menu.

Certificate Menu

Click the "Certificate Information" link to display the Certificate Viewer.

Certificate Viewer General

Click the "Details" tab.

Certificate Viewer Details

For each node in the "Certificate Hierarchy", highlight the node and click the "Export" button to save the certificate information. The hierarchy shows the dependencies between the certificates. If any are missing, access to the resource will fail.

A similar dialog is displayed in Firefox by clicking "URL Icon > More Information > View Certificate > Details Tab".

Create an Oracle Wallet Containing the Certificates

Create a new location to hold the wallet.

$ mkdir -p /u01/app/oracle/admin/DB11G/wallet

Create a new wallet.

$ orapki wallet create -wallet /u01/app/oracle/admin/DB11G/wallet -pwd WalletPasswd123 -auto_login

Note. If the wallet password is too weak, you will get a message telling you so.

Invalid password....
PASSWORD_POLICY : Passwords must have a minimum length of eight 
characters and contain alphabetic characters combined with numbers or 
special characters.

In Oracle 11.2 the same issue causes a failure to create the wallet with the following message.

Unable to save wallet at /home/oracle/wallet

With the wallet created, we can add the certificates we saved earlier.

$ orapki wallet add -wallet /u01/app/oracle/admin/DB11G/wallet -trusted_cert -cert "/host/Builtin Object Token:GTE CyberTrust Global Root" -pwd WalletPasswd123
 
$ orapki wallet add -wallet /u01/app/oracle/admin/DB11G/wallet -trusted_cert -cert "/host/Akamai Subordinate CA 3" -pwd WalletPasswd123
 
$ orapki wallet add -wallet /u01/app/oracle/admin/DB11G/wallet -trusted_cert -cert "/host/*.redhat.com" -pwd WalletPasswd123

The top-level certificate will probably fail to load with the following message, which can be ignored.

Could not install trusted cert at/host/Builtin Object Token:GTE CyberTrust Global Root
PKI-04003: The trusted certificate is already present in the wallet.

Test Secured Connection

We are now ready to access the secured resource, but we must provide the UTL_HTTP package with the wallet details so it can make the secured connections. This is done using the UTL_HTTP.SET_WALLET procedure. Repeating the previous test now works successfully.

SET SERVEROUTPUT ON
EXEC UTL_HTTP.set_wallet('file:/u01/app/oracle/admin/DB11G/wallet', 'WalletPasswd123');
EXEC show_html_from_url('https://gb.redhat.com/');

... HTML output removed ...

PL/SQL procedure successfully completed.

SQL>

For more information see:

Hope this helps. Regards Tim...

Back to the Top.