Access a secure web page(web service) from pl/sql

A Simple Example of Using UTL_HTTP to Access a Secure Web Page [ID 265616.1]
		Modified 26-NOV-2008     Type BULLETIN     Status PUBLISHED

PURPOSE
-------

Step by step guide to configuring UTL_HTTP for https. 

 
SCOPE & APPLICATION
-------------------

This note is intended to assist anyone attempting to develop SQL or PLSQL which
will communicate with a secure web site using https.  After following the steps 
in this note the reader should have an understanding of the addition steps 
required for the use of UTL_HTTP with the https protocol.  This note does not 
contain a complete description of the usage of the ULT_HTTP package and is should 
be read in conjunction with the Oracle documentation. All Oracle documentation is
available from http://otn.oracle.com.  
 

A Simple Example of Using ULT_HTTP to Access a Secure Web Site
----------------------------------------------------------------
 
UTL_HTTP only supports server authentication of SSL.  UTL_HTTP must have the complete
trusted certificate chain used by the web server. Oracle's certificate store is called 
an Wallet.  An Oracle Wallet is a PKCS#12 format certificate store. Oracle Wallets are 
administered via Oracle Wallet Manager, OWM.

1. Identify the CA used to sign the server certificate on the web server

There are many ways in which you can find certificate authority used to sign a web 
site, amongst the easiest is to attach via a browser.  If you use Microsoft 
Internet Explorer to attach to a secure web server you should get a small 
padlock icon in the bottom right hand corner of your browser.  Alternatively 
you can go to File->Properties and select the Certificates button. Next 
navigate to the Certification Path tab and there you will see a visual display 
of the signing certificate chain.

Example: https://www.oracle.com

a. Attached you browser to https://www.oracle.com
b. Navigate to File->Properties->Certificates->Certification Path 

https://www.oracle.com only has a simple certification chain, i.e. no 
intermediate certificate. The server certificate has a subject of "www.oracle.com" and the certificate 
was issued by "VeriSign/RSA Secure Server CA". In this case the Certificate 
Authority, CA, is therefore Verisign.

If you select the "VeriSign/RSA Secure Server CA" entry in the Certificate 
Chain windows you can then view the issuer certificate in greater detail.


2. Import the complete Trusted Certificate chain into Oracle Wallet Manager.

There are several ways to get a certificate authorities, CA, trusted certificates. 
In general you can connect to the Certificate Authorities web site and download 
their trusted certificates.  In the case of a small number of well known CA’s, 
OWM comes preinstalled with their trusted certificates. UTL_HTTP requires the 
complete signing certificate chain, but not the web server’s own User 
Certificate. This means that if the chain includes any intermediate 
certificates such as  "www.verisign.com/CPS Incorp.by Ref. LIABILITY LTD.(c)97 
VeriSign" then you will also need to obtain a copy of this certificate and 
import it to OWM.

Example.

a. Start OWM

UNIX:

set the DISPLAY correctly

$> owm &

Windows:

Navigate to Wallet Manager from the Integrated Management Tools folder from 
Start button.

b. Create a new wallet,

Wallet->New

c. Enter a new wallet password which conforms to the conditions stated on the  
screen and select OK
d. Select No, You do not want to create a certificate request at this time.

Under the trusted certificates you will see that the wallet has some 
preinstalled certificates. You will see that one of these is "Secure Server 
Certification Authority".  This is the trusted certificate that was used to 
sign https://www.oracle.com.  You do not need to import any additional 
certificates.

e. Save the wallet, File->Save

Note: If a trusted certificate with an identical Subject Name does not already 
exist in your new wallet then connect to the CA’s web site and locate the 
missing trusted certificate, i.e. for Verisign go to http://www.verisign.
com/support/roots.html.  You can obtain the latest copy of "www.verisign.
com/CPS Incorp.by Ref. LIABILITY LTD.(c)97 VeriSign" at https://www.verisign.
com/support/install/intermediate.html.


3. Call UTL_HTTP.REQUEST to access the secure web site.

The simplest UTL_HTTP procedure call is REQUEST.  To use REQUEST to access a 
secure web page you must provide the location of your wallet and you wallet 
password.  If you use a proxy to access the internet then you will also have to 
supply it's location to the REQUEST procedure call.

Example:

SQL> select utl_http.request('https://www.oracle.com','http://proxy.com:80', 
'file:/etc/ORACLE/WALLETS','welcome1') from dual;

Note: 

a. Only the location of the wallet is provided, not the actual file
b. The wallet location has the prefix, 'file:'
c. If you do not have a proxy then use '' or NULL
d. The proxy port may be required.

4. Troubleshooting

If you complete these steps and you still have a failure provide the following 
information to Oracle Support:

a. Certificate Authority information. If the website is available externally 
provide the URL. If not take a screenshot of the Certification Path from Step 1.
b. Provide you wallet and password
c. Enable server sqlnet trace at level SUPPORT and trace a new database session 
and manual call to UTL_HTTP.REQUEST.
d. If the database runs on a machine which offers a truss like utility then 
truss the server process using:

$> truss -fae -o /tmp/truss.out -p 


5. Changing to your own secure web site

a. Determine your own web servers Certificate Authority.  Ensure you import the 
entire signing certificate chain into your wallet.  This must include the root 
certificate and any intermediate certificates but not the web servers server/user 
certificate.

b. Ensure all the trusted certificates in your wallet have a valid expiration 
date, see Note.260332.1 Expiration of VeriSign Class 2/Class 3 Certificates on 
Jan 7,2004 

c. On Windows the Database Service must have the Log On user who created the 
wallet.

d. On Unix the database software owner must have permission to read the wallet, 
ewallet.p12.

RELATED DOCUMENTS
-----------------

PL/SQL Packages and Types Reference10g Release
Supplied PL/SQL Packages and Types Reference Release