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
- Access Control List (ACL)
- Test Unsecured Connection
- Get Site Certificates
- Create an Oracle Wallet Containing the Certificates
- Test Secured Connection
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.
Click the "Certificate Information" link to display the Certificate Viewer.
Click the "Details" tab.
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...