Both the REQUEST and REQUEST_PIECES functions in UTL_HTTP support the retrieval of URLS that are protected by SSL – however the documentation on doing so is sparse at best.  SSL support is provided by using the last two parameters to the UTL_HTTP.request and UTL_HTTP.request_pieces procedures.  These parameters are the wallet_path and wallet_password. 

Oracle uses the wallet as a metaphor for how a person stores their security credentials – just like you would keep your driver’s license and credit cards in your wallet for identification purposes, the Oracle wallet stores the credentials needed by the SSL protocol. The wallet_path is the directory where your wallet is stored on the database server machine. This wallet is password protected to prevent someone from using your credentials. That is the purpose of the wallet_password parameter, it is used to access the wallet. The password prevents people from copying the wallet directory and trying to impersonate you, as they will be unable to open and access the wallet. This is analogous to using a PIN for using an ATM machine. If someone steals your bank card, they need to have your PIN to get to your accounts.

The wallet, or the concept of a wallet is used not only by the Oracle database, but also in Web browsers. The important aspect is that when you connect to a site, e.g., www.amazon.com, how do you know that it is really Amazon.com?  You have to get their certificate. Their certificate is digitally signed by someone – that someone is called a Certificate Authority or CA. How does my browser or database know to trust the CA that signed that certificate?  For example, I could create a certificate for Amazon.com and sign it from hackerAttackers.com. My browser and database should not accept this certificate even though it is a legitimate X.509 certificate. 

The answer to this trust issue is the wallet stores a set of trusted certificates. A trusted certificate is a certificate from a CA that you trust. The Oracle wallet comes with some common trusted certificates. You also have the ability to add certificates as necessary. Your browser does the same thing. If you ever connect to a site where your browser does not have the CA in its wallet, you will get a pop-up window that notifies you of this as well as a wizard that allows you to proceed or abort your connection.

Let’s see some examples of how to use SSL. First, we need to create a new wallet. You can invoke the owm (Oracle Wallet Manager) program on UNIX, or launch it from the Windows start menu on Windows. The screen you receive to do this will look like this:



All you need to do is click on the green “plus” icon located on the left hand side of the display.  It will prompt you for a password for this wallet – you are making up the password at this point so enter whatever password you would like.  You may get a warning about a directory not existing at this point – if you do, you should simply ignore it.  It is the expected behavior if you have never created a wallet before.  OWM at this point will ask you:




You do not need to create a certificate request. The certificate request is so you can get a certificate for yourself. This would be used in SSL v.3 where the server needs the identification of the client. Most Web sites do not authenticate users via certificates, but rather username and password. This is because an e-commerce site doesn’t care who is buying from them as long as they get the money. But you care that you are sending the money (and credit card information) to the correct entity, so we use SSL v.2 to identify the server e.g., Amazon.com, and to provide all the encryption of data. So, click NO in response to this and save the wallet by clicking on the yellow floppy disk icon and we are ready to go. 

Let’s go to Amazon.com first. Amazon’s certificate was signed by Secure Server Certificate Authority, RSA Data Security, Inc. This is one of the defaults in the Oracle wallet.

tkyte@TKYTE816> declare
  2     l_output long;
  3
  4     l_url varchar2(255) default
  5           '

  6
  7     l_wallet_path varchar2(255) default
  8           'file:C:\Documents and Settings\Thomas Kyte\ORACLE\WALLETS';
  9
 10
 11  begin
 12    l_output := utl_http.request
 13               ( url             => l_url,
 14                 proxy           => 'www-proxy.us.oracle.com',
 15                 wallet_path     => l_wallet_path,
 16                 wallet_password => 'oracle'
 17               );
 18    dbms_output.put_line(trim(substr(l_output,1,255)));
 19  end;
 20  /



<html>
<head>
<title>Amazon.com Error Page</title>
</head>
<body bgcolor="#FFFFFF"
link="#003399" alink="#FF9933" vlink="#996633" text="#000000">
<a name="top"><!--Top of
Page--></a>
<table border=0 width=100% cellspacing=0 cellpadding=0>
<tr

PL/SQL procedure successfully completed.

What?  An Error Page?  Don’t worry, this is accurate - try it in your browser. The reason is that there is no session information being passed. The point is that the connection worked, we just retrieved an SSL protected document.

Let’s try another site. How about E*Trade?

tkyte@TKYTE816> declare
  2     l_output long;
  3
  4     l_url varchar2(255) default
  5           '

  6
  7     l_wallet_path varchar2(255) default
  8           'file:C:\Documents and Settings\Thomas Kyte\ORACLE\WALLETS';
  9
 10
 11  begin
 12    l_output := utl_http.request
 13               ( url             => l_url,
 14                 proxy           => 'www-proxy.us.oracle.com',
 15                 wallet_path     => l_wallet_path,
 16                 wallet_password => 'oracle'
 17               );
 18    dbms_output.put_line(trim(substr(l_output,1,255)));
 19  end;
 20  /
declare
*
ERROR at line 1:
ORA-06510: PL/SQL: unhandled user-defined exception
ORA-06512: at "SYS.UTL_HTTP", line 174
ORA-06512: at line 12

That apparently does not work. E*Trade has a certificate signed by www.verisign.com/CPS Incorp.by Ref, which is not a default trusted certificate. In order to access this page, we’ll have to add that certificate to our Oracle wallet - assuming of course that we trust Verisign!  Here is the trick. Go to the site (

 with Microsoft Internet Explorer. Double-click the lock icon on the bottom right corner of the window. This will pop-up a window that looks similar to this one:

 

Select the Certification Path tab on the top of this screen. This lists the certificate you are viewing i.e., the one for E*Trade (trading.etrade.com), as well as who issued the certificate. We need to add the person who signed the certificate (the issuer) to our trusted certificates in the Oracle wallet. The issuer is www.verisign.com/CPS Incorp.by Ref. LIABILITY LTD as depicted by the tree-like hierarchy.


 

Click the View Certificate button while the www.verisign.com/CPS Incorp. by Ref. is highlighted. This shows information for the issuer’s certificate. Click the Details tab and you should see:




 


Now we need to Click the Copy to File… button. Save the file locally as a Base-64 encoded X.509 (CER) file.  The following screen shows the selection you should make, you can name the file anything you choose and save it anywhere.  We’ll be importing it in a moment, just remember where you save it to:

 

Now we can import this into our Oracle Wallet. Open the wallet in OWM and right click on the Trusted Certificates – this will present you with a popup menu that has “Import Trusted Certificate”:









You will select that option and in the next dialog that comes up, choose “Select a file that contains the certificate”


Use the standard “file open” dialog that comes up to choose the certificate you just saved.  Your screen should now look something like this:



Now, save the wallet using the yellow floppy disk icon and let’s try our example again:

tkyte@TKYTE816> declare
  2     l_output long;
  3
  4     l_url varchar2(255) default
  5           '