Accessing SSL encrypted websites using UTL_HTTP and ORAPKI command line utility

Introduction
In an earlier post, I explained the purpose and usage of the Oracle Wallet Manager. This explanation assumed that the user has the ability to use the graphical user interface to execute the OWM program included with each DBMS installation.

However, sometimes there is no graphical user interface available on the server, and the user is limited to SSH access.
Additionally, the disadvantage of using a user interface tool is that it is not scriptable and re-runnable on other server/environments.

For just that reason, Oracle also provides a command line utility to perform the same tasks, called ORAPKI.
This post will show you how to perform the same tasks as we did in the previous post, using only the command line.

Step 1: creating a wallet:
The base command to create a new, empty wallet is:

orapki wallet create -wallet <wallet name or path>

The name of the wallet will be used as a folder within your home folder by default. If you prefer to use a specific folder, the full path to the folder can be used as wallet name as well. Make sure the Oracle user has permission to write to this folder though.

When any command on a wallet is executed, a prompt will be given to enter the wallet password. If the command is to be used in a script, it is better to include the password right away in the command. This can be done by appending -pwd <password> to any command.

orapki wallet create –wallet testwallet –pwd test1234

Step 2: display contents
To show the contents of any wallet, use the display command.

You will see that a number of trusted certificates are included in your wallet after it has been created, just like when it was created through the GUI.

[oracle@myorcl12c ~]$ orapki wallet display -wallet testwallet -pwd test1234

Oracle PKI Tool : Version 12.1.0.1
Copyright (c) 2004, 2012, Oracle and/or its affiliates. All rights reserved.
Requested Certificates:
User Certificates:
Trusted Certificates:
Subject:        OU=Class 2 Public Primary Certification Authority,O=VeriSign\, Inc.,C=US
Subject:        OU=Class 3 Public Primary Certification Authority,O=VeriSign\, Inc.,C=US
Subject:        CN=GTE CyberTrust Global Root,OU=GTE CyberTrust Solutions\, Inc.,O=GTE Corporation,C=US
Subject:        OU=Class 1 Public Primary Certification Authority,O=VeriSign\, Inc.,C=US

Step 3: import certificates
Now we will import previously downloaded certificates into our wallet. (refer to the previous post for details on how to obtain such files)

Firstly I have stored 3 certificate files on the server:
/home/oracle/Certificates/BuiltinObjectToken:EquifaxSecureCA
/home/oracle/Certificates/GoogleInternetAuthority
/home/oracle/Certificates/*.google.be

Then following commands will do the import:

[oracle@myorcl12c ~]$ orapki wallet add -wallet testwallet -trusted_cert -cert /home/oracle/Certificates/BuiltinObjectToken:EquifaxSecureCA -pwd test1234
[oracle@myorcl12c ~]$ orapki wallet add -wallet testwallet -trusted_cert -cert /home/oracle/Certificates/GoogleInternetAuthority -pwd test1234
[oracle@myorcl12c ~]$ orapki wallet add -wallet testwallet -trusted_cert -cert /home/oracle/Certificates/*.google.be -pwd test1234
[oracle@myorcl12c ~]$ orapki wallet display -wallet testwallet -pwd test1234

Oracle PKI Tool : Version 12.1.0.1
Copyright (c) 2004, 2012, Oracle and/or its affiliates. All rights reserved.
Requested Certificates:
User Certificates:
Trusted Certificates:
Subject:        CN=Google Internet Authority,O=Google Inc,C=US
Subject:        OU=Class 3 Public Primary Certification Authority,O=VeriSign\, Inc.,C=US
Subject:        CN=GTE CyberTrust Global Root,OU=GTE CyberTrust Solutions\, Inc.,O=GTE Corporation,C=US
Subject:        CN=*.google.be,O=Google Inc,L=Mountain View,ST=California,C=US
Subject:        OU=Equifax Secure Certificate Authority,O=Equifax,C=US
Subject:        OU=Class 2 Public Primary Certification Authority,O=VeriSign\, Inc.,C=US
Subject:        OU=Class 1 Public Primary Certification Authority,O=VeriSign\, Inc.,C=US

When adding the Wallet reference to your PL/SQL code, the folder /home/Oracle/testwallet should be used for the example above.

Eg:

[oracle@myorcl12c ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.1.0 Production on Tue Aug 27 14:11:43 2013
Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> SET SERVEROUTPUT ON
SQL> DECLARE
2    lo_req  UTL_HTTP.req;
3    lo_resp UTL_HTTP.resp;
4  BEGIN
5    UTL_HTTP.SET_WALLET ('file:/home/oracle/Wallet/','test1234');
6    lo_req := UTL_HTTP.begin_request('https://www.google.com');
7    lo_resp := UTL_HTTP.get_response(lo_req);
8    dbms_output.put_line(lo_resp.status_code);
9    UTL_HTTP.end_response(lo_resp);
10  END;
11  /

200
PL/SQL procedure successfully completed.
SQL>

Step 4: clear wallet
As a final step, use following command to clear the wallet.

orapki wallet remove -wallet testwallet -trusted_cert_all -pwd test1234

Accessing SSL encrypted websites using UTL_HTTP and Oracle Wallet Manager

Introduction
If you have used the UTL_HTTP package in PL/SQL to call upon external web pages or services, you might have seen following error message come by:
ORA-29273: HTTP request failed
ORA-06512: at “SYS.UTL_HTTP”, line 1130
ORA-29024: Certificate validation failure

It indicates that the web site you are trying to access is in fact SSL encrypted and requires a valid certificate to read.
Most modern browsers download this automatically when visiting any encrypted page, but to do this in a PL/SQL procedure, a couple of manual steps need to be taken.

This is where Oracle Wallet Manager comes in. It is shipped with the DBMS software and can typically be found in the $ORACLE_HOME/bin folder.
What this program does, is to facilitate the process of storing certificates in a single file in PKCS #12 format, called a Wallet.
Next, it suffices to simply add a reference to the wallet in your PL/SQL code to be able to use these certificates when accessing SSL encrypted content.

Sample case
Let’s take the following piece of PL/SQL code:

DECLARE
 lo_req  UTL_HTTP.req;
 lo_resp UTL_HTTP.resp;
BEGIN
 lo_req := UTL_HTTP.begin_request('http://www.google.be');
 lo_resp := UTL_HTTP.get_response(lo_req);
 dbms_output.put_line(lo_resp.status_code);
 UTL_HTTP.end_response(lo_resp);
END;
/

This will output the status code “200”. This implies the page returned HTTP 200: OK, indicating the request succeeded.

If you change the URL from http://www.google.be to https://www.google.be, the same command would raise exception “ORA-29024 Certificate validation failure” as mentioned earlier.

Wallet Manager
From the command line, start the wallet manager by simply entering ‘owm’ after having set the correct Oracle environment parameters.
In Windows the program can be found in the start menu under /<oracle version folder>/Integrated Management Tools/Wallet Manager.

Wallet_01
First, let’s create a new wallet.
From the menu, select Wallet, New…

A prompt is shown to enter a password for the Wallet. For this example, we’ll use “test1234”.

Wallet_02

After entering the password, a prompt asks to create a new certificate request, which we will not do for now.

By default, a number of trusted certificates are created within the wallet. These can be seen in the overview screen.

Wallet_03
Now, let’s focus on our google.be website.

First thing to do, is to export the actual certificate from this website, together with the rest of the trust chain. The easiest way to do this is by using a regular browser application. Following screenshots are from Firefox, but a similar approach can be taken with Internet explorer or Chrome.

Go to the website, and click on the padlock in front of the URL.
Wallet_04

Click on “More information” and then “View certificate”. In the Details tab, export each of the certificates in the hierarchy (3 in this case).
Wallet_05

Then go to the Wallet manager, and import all 3 trusted certificates.
Wallet_06

Finally, save the wallet to a desired location. Make sure it’s a folder that can be accessed by the Oracle user, and is preferably not accessible by unauthorized users!
In this example, /home/oracle/Wallet/ is chosen.

Wallet_07

Wallet_08

Now we can return to our SQL*plus session. One extra statement should be added to the script:

UTL_HTTP.SET_WALLET (‘file:<path to the wallet folder, don’t include the filename!>’,<the wallet password>);

This statement should be executed before the begin_request step.

Here’s the full output of the script:

[oracle@myorcl12c ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.1.0 Production on Tue Aug 27 14:11:43 2013
Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> SET SERVEROUTPUT ON
SQL> DECLARE
2    lo_req  UTL_HTTP.req;
3    lo_resp UTL_HTTP.resp;
4  BEGIN
5    UTL_HTTP.SET_WALLET ('file:/home/oracle/Wallet/','test1234');
6    lo_req := UTL_HTTP.begin_request('https://www.google.com');
7    lo_resp := UTL_HTTP.get_response(lo_req);
8    dbms_output.put_line(lo_resp.status_code);
9    UTL_HTTP.end_response(lo_resp);
10  END;
11  /

200
PL/SQL procedure successfully completed.
SQL>

In a follow up post, I will explain how to achieve the same result using the orapki command line utility instead of Wallet manager.

Spice Up Your Oracle Knowledge – SOA EXSite – A Practical View On Oracle ESB

Oracle and Distrilogie are presenting different “Monthly Oracle Partner Training” programs and the first month (September) the focus lies on SOA.
This is a partner-dedicated event aimed at informing the Partner community about Oracle Acquisition strategy and “Best-in-Class” offerings that are critical to meet the customer needs.

My task was to give the partners a practical view on Oracle Enterprise Service Bus using a pre-sales case I’ve worked providing an answer to the customers’ specific technical requirements.

This presentation first gives an insight on the business requirements stated by the customer and then focuses on the business value of ESB for each of these requirements.

  • The first requirement was regarding the ability to work with one General Business Object (GBO) that described the customers’ data model. The answer in ESB is using an XML Schema Definition File (xsd) where we describe the required data in an hierarchical, nested manner. Given the customer the possibility to work with the same object throughout the whole business process.
  • The second requirement ‘How can we make a synchronous call from existing applications to an ESB Service?’ ‘How can we propagate changes to our different systems’? The first answer uses file- and ftp-adapter to bacth-read a CSV file. The file is polled for using the file adapter, format the file in a required format using the transform-functionality of ESB and put this file on the ftp-server of the client. For the second question regarding propagation I’ve given a demo where a JMS queue will listen for new messages. Once a message has been received a call will be made to a procedure on the database to unpack the message and create a new record. In this demo I’ve also mentioned the managed- and non-managed approach when implementing ESB Services which explains the virtualization-features of ESB.
  • The 3d requirement was regarding two-way synchronous services were I’ve integrated the ESB Services with an ADF Faces Web Application. Giving the customer an insight on the ease of integration between Oracle ADF and ESB Services using the data binding layer on Web Services.
  • In the last requirement the customer was curious about the integration-possibilities of web services and database procedures, jobs. The question that popped their mind: ‘Can we schedule a web service call using a database job?’. And this is no hastle at all, not for ESB and not for our Oracle Database. We can use the utl_http-package or utl_dbws-package to call our web service endpoint and the procedure holding the pl/sql functionality will be scheduled using dbms_job.

This demo and presentation gave me and the customers a real practical hands-on experience of Oracle’s Enterprise Service Bus and convinced us of the ease of integration when using such technology.

A pragmatic approach and an open vision using practical demo’s and hands-on sessions is the answer to every Enterprise Integration Challenge.