DBA Tips Archive for Oracle

  


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

by Jeff Hunter, Sr. Database Administrator

Contents

Introduction

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 Firefox from a Linux workstation running CentOS 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/app/oracle/racdb/wallet.


[oracle@racnode1 ~]$ ls -l /u02/app/oracle/racdb/wallet total 12 -rw------- 1 oracle oinstall 7424 Mar 10 14:55 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 which will be fully demonstrated later in this guide. For example:


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

Capture SSL Site Certificate

The first step is to capture the SSL site certificate(s). Using Firefox, navigate to the SSL site (i.e. https://www.centos.org/).

 

The required certificates can also be exported using Microsoft Windows Internet Explorer. After the secured website loads:

  • Click "File -> Properties" then click "Certificates".
  • Choose the "Copy to File..." option under the "Details" tab.
  • Choose the "Cryptographic Message Syntax Standard - PKCS #7 Certificates (.P7B)" certificate type. Check the option to "Include all certificates in the certification path if possible".
  • Save the certificate to a local directory (i.e. centos_ssl.p7b).

If you are using Vista or Windows 7, remember to open IE with the "Run as administrator" option.

You will use the Firefox web browser to export any and all certificates required to access the secured site. After the secure site loads, select Tools -> Page Info (Ctrl+I) and then click the Security option.

Figure 1: Firefox Page Info - https://www.centos.org/

Click the View Certificate button.

Figure 2: Firefox View Certificate - https://www.centos.org/

Click the Details tab. Select the root certificate for the target site which in this example is www.centos.org and then click the Export button.

Figure 3: Firefox View Certificate Details - https://www.centos.org/

Provide a name for the certificate file (i.e. centos_ssl.p7b). For the file type, make certain to export the certificate as X.509 Certificate with chain (PKCS #7). This will export the root certificate for the target site and all subordinate certificates in a single PKCS #7 binary certificate package. Click the Save button to save the certificate file.

 

SSL certificates are hierarchical which means that a certificate for a site might be dependent on its authorizing "parent" site to also approve it. In this example, the www.centos.org certificate depends on the Go Daddy Secure Certification Authority certificate and that certificate depends on the Go Daddy Class 2 Certification Authority certificate. If the certificate type was to be saved as Base-64 encoded X.509 and not the X.509 Certificate with chain (PKCS #7) certificate type, you would need to export all certificates in the dependency hierarchy and import them all into the Oracle Wallet (next section).

Figure 4: Firefox Save Certificate to a File - https://www.centos.org/


[jhunter@critter ~]$ ls -l centos_ssl.p7b -rw-r--r-- 1 jhunter jhunter 3702 Mar 10 13:44 centos_ssl.p7b

Close out the remaining dialog boxes and exit from Firefox.

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


[jhunter@critter ~]$ scp centos_ssl.p7b oracle@racnode1: oracle@racnode1's password: *********** centos_ssl.p7b 100% 3702 3.6KB/s 00:00

Create Oracle Wallet

Log in to the database server as the Oracle software owner to create an Oracle Wallet.

Create the Wallet Directory


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

Start Oracle Wallet Manager


[oracle@racnode1 ~]$ owm

Create a New Oracle Wallet

Using Oracle Wallet Manager, create a new wallet.

Figure 5: Create a New Oracle Wallet

Do not select the default wallet location.

Figure 6: Do Not Select Default Wallet Location

Provide a password – "wallet_password" and make certain to create a Standard wallet type.

Figure 7: 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 8: Do Not Create a Certificate Request

Import Certificate into the Oracle Wallet

The next step will be to import the SSL certificates for the target site into the new Oracle wallet. The certificate file created in the previous section was an X.509 Certificate with chain (PKCS #7) certificate type and therefore contains all dependency certificates in the hierarchy.

Figure 9: Import Trusted Certificate...

Figure 10: Select File That Contains the Certificate

Figure 11: Select SSL Certificate File

Now, save the new wallet.

Figure 12: Save the New Oracle Wallet

Choose the directory to save the wallet in (i.e. /u02/app/oracle/racdb/wallet).

Figure 13: Save the New Oracle Wallet

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


[oracle@racnode1 ~]$ ls -l /u02/app/oracle/racdb/wallet total 12 -rw------- 1 oracle oinstall 7424 Mar 10 14:55 ewallet.p12

Exit Oracle Wallet Manager:

Figure 14: 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/app/oracle/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 server environment. Jeff's other interests include mathematical encryption theory, tutoring advanced mathematics, 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 20 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 and Mathematics.



Copyright (c) 1998-2014 Jeffrey M. Hunter. All rights reserved.

All articles, scripts and material located at the Internet address of http://www.idevelopment.info is the copyright of Jeffrey M. Hunter and is protected under copyright laws of the United States. This document may not be hosted on any other site without my express, prior, written permission. Application to host any of the material elsewhere can be made by contacting me at jhunter@idevelopment.info.

I have made every effort and taken great care in making sure that the material included on my web site is technically accurate, but I disclaim any and all responsibility for any loss, damage or destruction of data or any other property which may arise from relying on it. I will in no case be liable for any monetary damages arising from such loss, damage or destruction.

Last modified on
Sunday, 10-Mar-2013 15:20:53 EDT
Page Count: 36317