DBA Tips Archive for Oracle

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 web site https://www.centos.org/ 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:

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 protected 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:

Capture SSL Site Certificate

The first step is to capture the SSL site certificate(s). Using Internet Explorer, navigate to the SSL site (i.e. https://www.centos.org/). 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.

Figure 1: IE Properties - https://www.centos.org/

Figure 2: Copy to File

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

Figure 4: Save CentOS Certificate to Local Directory

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 www.centos.org certificate depends on the GeoTrust certificate. In order to use the www.centos.org certificate, you will need to export the GeoTrust certificate as well given that www.centos.org depends on it.

Figure 6: Save GeoTrust Certificate to Local Directory

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

Start Oracle Wallet Manager

Create a New Oracle Wallet

Figure 8: Create a New Oracle Wallet

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 www.centos.org certificate (centos_ssl.cer). Remember to import both certificates (centos_ssl.cer and geotrust_ssl.cer) 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:

Figure 15: Save the New Oracle Wallet

Figure 16: Save the New Oracle Wallet


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

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.

About the Author

Jeffrey Hunter is an Oracle Certified Professional, Java Development Certified Professional, Author, and an Oracle ACE. Jeff currently works as a Senior Database Administrator for The DBA Zone, Inc. located in Pittsburgh, Pennsylvania. His work includes advanced performance tuning, Java and PL/SQL programming, developing high availability solutions, capacity planning, database security, and physical / logical database design in a UNIX, Linux, and Windows server environment. Jeff's other interests include mathematical encryption theory, programming language processors (compilers and interpreters) in Java and C, LDAP, writing web-based database administration tools, and of course Linux. He has been a Sr. Database Administrator and Software Engineer for over 18 years and maintains his own website site at: http://www.iDevelopment.info. Jeff graduated from Stanislaus State University in Turlock, California, with a Bachelor's degree in Computer Science.

Contents

Introduction