Phone
800-766-1884

Using UTL_HTTP and an Oracle Wallet to Establish a Secure URL Connection (SSL)

Expert Oracle Tips by Jeff Hunter

March 18, 2011

This article describes the steps necessary to establish a secure URL connection (SSL) using the UTL_HTTP Oracle PL/SQL package and an Oracle Wallet. In order to fully demonstrate the required steps, I will be establishing a secure URL connection to the Centos web site from within PL/SQL.

In order to establish a connection to a secure URL from an Oracle database server, the following tasks will need to be performed:

Capture all required certificates from the SSL site
Create an Oracle Wallet that is accessible on the database server
Import the required certificate(s) of the SSL site into the Oracle Wallet
Use the UTL_HTTP.SET_WALLET PL/SQL procedure before attempting to access the secure URL

An Oracle Wallet stores all of the encryption keys that the database can use and is required in order to access an SSL site using the UTL_HTTP PL/SQL package. Attempting to establish a secure URL connection without an Oracle wallet (and, of course, the required certificates from the SSL site) will result in the code throwing the Oracle error ORA-29024: Certificate validation failure:

CONNECT scott/tiger

SET serveroutput ON

DECLARE
    HTTP_REQ      UTL_HTTP.REQ;
    HTTP_RESP     UTL_HTTP.RESP;
    URL_TEXT      VARCHAR2(32767);
BEGIN
    DBMS_OUTPUT.ENABLE(1000000);

    HTTP_REQ  := UTL_HTTP.BEGIN_REQUEST('https://www.centos.org/');
    UTL_HTTP.SET_HEADER(HTTP_REQ, 'User-Agent', 'Mozilla/4.0');
    HTTP_RESP := UTL_HTTP.GET_RESPONSE(HTTP_REQ);

    -- Process Request
    LOOP
        BEGIN
            URL_TEXT := null;
            UTL_HTTP.READ_LINE(HTTP_RESP, URL_TEXT, TRUE);
            DBMS_OUTPUT.PUT_LINE(URL_TEXT);

            EXCEPTION
                WHEN OTHERS THEN EXIT;
        END;
    END LOOP;

    UTL_HTTP.END_RESPONSE(HTTP_RESP);
END;
/

DECLARE
*
ERROR at line 1:
ORA-29273: HTTP request failed
ORA-06512: at "SYS.UTL_HTTP", line 1029
ORA-29024: Certificate validation failure
ORA-06512: at line 8

Although this article will be developed using an Oracle database installed on the Linux operating environment, it is fully transferable to Microsoft Windows.

Oracle Wallet

An Oracle Wallet is nothing more than a logical container (a single file named ewallet.p12) that stores all encrypted keys needed by the Oracle database in order to access SSL sites. This is not, however, the only use of an Oracle Wallet. It is also used by many of the more advanced security options in Oracle like Transparent Data Encryption (TDE) or PKI Credentials. These more advanced options are part of Oracle�s Advanced Security Option (ASO) and are only available when using Oracle Enterprise Edition.

Note that creating and importing keys into an Oracle Wallet and then using that Oracle wallet to establish a secure connection can be done using Oracle Standard Edition. Although I've seen it mentioned that Oracle Wallets only work with Enterprise Edition, this is incorrect. The Oracle Wallet simply stores keys which can be used by the Oracle database. There are some security options (like TDE or PKI Credentials) that are part of Oracle's ASO which are only available with Oracle Enterprise Edition. Oracle ASO is an additional license cost on top of Enterprise Edition. Accessing SSL sites from the database using UTL_HTTP and an Oracle Wallet does not require Oracle's Advanced Security Option and can therefore be used with Oracle Standard Edition.

The only downside to using an Oracle Wallet is that you need to know in advance each SSL site you will be accessing when using UTL_HTTP. You will be required to extract the site's public key certificate and import it into an Oracle Wallet before the database can access that secure site. This has to be performed for each secure site you want to access when using UTL_HTTP. This is unlike a web browser which does all of this for you!

For the purpose of this article, I will be using Internet Explorer from a Microsoft Windows workstation to capture the certificate of the SSL site.

As previously mentioned, an Oracle Wallet is simply a file and must be named ewallet.p12. Within Oracle, whenever you specify the location of the Oracle Wallet to open, you only specify the directory containing the wallet. It will be assumed the file ewallet.p12 exists within that directory. For example, in this article, I will be creating the Oracle Wallet on the database server in a non-default directory; namely /u02/oradata/racdb/wallet:

[oracle@racnode1 ~]$ ls -l /u02/oradata/racdb/wallet
total 32
-rw------- 1 oracle oinstall 9797 Sep 13 11:05 ewallet.p12

The Oracle Wallet is created using Oracle Wallet Manager (owm). I prefer to create the Oracle Wallet (file) in a non-default location on the database server. When using the UTL_HTTP package, you specify the wallet location using the UTL_HTTP.SET_WALLET procedure. For example:

UTL_HTTP.SET_WALLET('file:/u02/oradata/racdb/wallet/', 'wallet_password');

Capture SSL Site Certificate

The first step is to capture the SSL site certificate(s). Using Internet Explorer, navigate to the SSL Centos site I have yet to figure out how to do this in Firefox! I believe there is a Firefox add-on though. If you are using Vista or Windows 7, remember to open IE with the "Run as administrator" option.

After the secure site loads, click File -> Properties. You will use IE to export any and all certificates required to access the secured site.

Click Certificates:

Figure 1: IE Properties

Choose the Copy to File... option under the Details tab:

Figure 2: Copy to File

Choose the Base-64 encoded X.509 certificate type:

Figure 3: Base-64 encoded X.509 Certificate Type

Save the certificate to a local directory:

Figure 4: Save CentOS Certificate to Local Direct

Something to note about SSL certificates � they are hierarchical which means that a certificate for a site might be dependent on its authorizing "parent" site to also approve it.

To determine if there is a hierarchical relationship with the site's certificate, click the Certification Path tab in the same Properties dialog:

Figure 5: Check Certification Path

As you can see, the CentOS certificate depends on the GeoTrust certificate. In order to use the CentOS certificate, you will need to export the GeoTrust certificate as well given that CentOS depends on it.

Use the same method to export the dependency certificate(s).

Start by selecting the GeoTrust certificate and clicking the View Certificate button.

Click the Details tab and then the Copy to File... button.

Choose Base-64 encoded X.509 certificate type.

Save the certificate to a local directory:

Figure 6: Save GeoTrust Certificate to Local Directory

Exit from Internet Explorer.

Transfer all of the SSL certificates to the database server. For now, I'll transfer the new certificates to the /home/oracle directory on the database server:

Figure 7: Copy Certificates to Database Server

Create Oracle Wallet

Create the Wallet Directory

[oracle@racnode1 ~]$ mkdir -p /u02/oradata/racdb/wallet

Start Oracle Wallet Manager

[oracle@racnode1 ~]$ owm

Create a New Oracle Wallet

Using Oracle Wallet Manager, create a new wallet:

Figure 8: Create a New Oracle Wallet

Do not select the default location:

Figure 9: Do Not Select Default Location

Provide a password � "wallet_password" and make certain to create a Standard wallet type:

Figure 10: Create Wallet Password

No need to create a certificate request � we will be importing the SSL certificates created in the previous section into the new Oracle Wallet:

Figure 11: Do Not Create a Certificate Request

Import Certificate into the Oracle Wallet

The next step will be to import each certificate into the new Oracle wallet (one at a time). The following example demonstrates how to import the CentOS certificate . Remember to import both certificates (CentOS and GeoTrust) into the Oracle wallet using the same method as described in this example.

Figure 12: Import Trusted Certificate...

Figure 13: Select File That Contains the Certificate.

Figure 14: Select www.centos.org Certificate

Remember to use the same method to import all remaining certificates before saving the Oracle Wallet:

geotrust_ssl.cer

Now, save the new wallet

Figure 15: Save the New Oracle Wallet

Choose the directory to save the wallet in (i.e. /u02/oradata/racdb/wallet):

Figure 16: Save the New Oracle Wallet

Verify new wallet file � (file will be named ewallet.p12)

[oracle@racnode1 ~]$ ls -l /u02/oradata/racdb/wallet
total 32
-rw------- 1 oracle oinstall 9797 Sep 13 11:05 ewallet.p12

Exit Oracle Wallet Manager:

Figure 17: Exit Oracle Wallet Manager

Establish Encrypted Connection

Now that the new Oracle Wallet exists on the database server and all SSL certificates have been imported into the wallet, use the UTL_HTTP.SET_WALLET procedure to identify the Oracle wallet file:

CONNECT scott/tiger

SET serveroutput ON

DECLARE
    HTTP_REQ      UTL_HTTP.REQ;
    HTTP_RESP     UTL_HTTP.RESP;
    URL_TEXT      VARCHAR2(32767);
BEGIN
    DBMS_OUTPUT.ENABLE(1000000);

    UTL_HTTP.SET_WALLET('file:/u02/oradata/racdb/wallet', 'wallet_password');

    HTTP_REQ  := UTL_HTTP.BEGIN_REQUEST('https://www.centos.org/');
    UTL_HTTP.SET_HEADER(HTTP_REQ, 'User-Agent', 'Mozilla/4.0');
    HTTP_RESP := UTL_HTTP.GET_RESPONSE(HTTP_REQ);

    -- Process Request
    LOOP
        BEGIN
            URL_TEXT := null;
            UTL_HTTP.READ_LINE(HTTP_RESP, URL_TEXT, TRUE);
            DBMS_OUTPUT.PUT_LINE(URL_TEXT);

            EXCEPTION
                WHEN OTHERS THEN EXIT;
        END;
    END LOOP;

    UTL_HTTP.END_RESPONSE(HTTP_RESP);
END;
/

Connected.
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
<head>

... < SNIP > ...

</div>
</td>
</tr>
</table>
</center>
</body>
</html>

PL/SQL procedure successfully completed.

CONNECT scott/tiger

SET serveroutput ON

DECLARE
HTTP_REQ UTL_HTTP.REQ;
HTTP_RESP UTL_HTTP.RESP;
URL_TEXT VARCHAR2(32767);
BEGIN
DBMS_OUTPUT.ENABLE(1000000);

UTL_HTTP.SET_WALLET('file:/u02/oradata/racdb/wallet', 'wallet_password');

HTTP_REQ := UTL_HTTP.BEGIN_REQUEST('https://www.centos.org/');
UTL_HTTP.SET_HEADER(HTTP_REQ, 'User-Agent', 'Mozilla/4.0');
HTTP_RESP := UTL_HTTP.GET_RESPONSE(HTTP_REQ);

-- Process Request
LOOP
BEGIN
URL_TEXT := null;
UTL_HTTP.READ_LINE(HTTP_RESP, URL_TEXT, TRUE);
DBMS_OUTPUT.PUT_LINE(URL_TEXT);

EXCEPTION
WHEN OTHERS THEN EXIT;
END;
END LOOP;

UTL_HTTP.END_RESPONSE(HTTP_RESP);
END;
/

Connected.
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
<head>

... < SNIP > ...

</div>
</td>
</tr>
</table>
</center>
</body>
</html>

PL/SQL procedure successfully completed.

CONNECT scott/tiger

SET serveroutput ON

DECLARE
    HTTP_REQ      UTL_HTTP.REQ;
    HTTP_RESP     UTL_HTTP.RESP;
    URL_TEXT      VARCHAR2(32767);
BEGIN
    DBMS_OUTPUT.ENABLE(1000000);

    UTL_HTTP.SET_WALLET('file:/u02/oradata/racdb/wallet', 'wallet_password');

    HTTP_REQ  := UTL_HTTP.BEGIN_REQUEST('https://www.centos.org/');
    UTL_HTTP.SET_HEADER(HTTP_REQ, 'User-Agent', 'Mozilla/4.0');
    HTTP_RESP := UTL_HTTP.GET_RESPONSE(HTTP_REQ);

    -- Process Request
    LOOP
        BEGIN
            URL_TEXT := null;
            UTL_HTTP.READ_LINE(HTTP_RESP, URL_TEXT, TRUE);
            DBMS_OUTPUT.PUT_LINE(URL_TEXT);

            EXCEPTION
                WHEN OTHERS THEN EXIT;
        END;
    END LOOP;

    UTL_HTTP.END_RESPONSE(HTTP_RESP);
END;
/

Connected.
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
<head>

... < SNIP > ...

</div>
</td>
</tr>
</table>
</center>
</body>
</html>

PL/SQL procedure successfully completed.

Get the Complete
Oracle Tuning Details

The landmark book "Oracle Tuning: The Definitive Reference Second Edition" has been updated with over 1,150 pages of expert performance tuning tips. It's packed with scripts and tools to hypercharge Oracle 11g performance and you can buy it for 40% off directly from the publisher.

Download your Oracle scripts now:

www.oracle-script.com

The definitive Oracle Script collection for every Oracle professional DBA

Copyright © 1996 -2017 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation. SQL Server® is the registered trademark of Microsoft Corporation.
Many of the designations used by computer vendors to distinguish their products are claimed as Trademarks