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
About these ads

2 thoughts on “Accessing SSL encrypted websites using UTL_HTTP and ORAPKI command line utility

  1. Thank you for explaning it so well. Is it possible to use a different OS user than Oracle software owner user to create and store wallets ? Could you please explain how this can be achieved using LDAP? Thanks,

    • Hi Deepak,

      The ORAPKI utility can be executed by any OS user that has execute rights to the binary.
      Watch out though: if you execute it with another user, the created wallet files will need to be readable by the oracle OS user to be able to use this wallet in the database. (use CHOWN or CHMOD for that)

      I’m afraid I do not understand how you would use LDAP for wallets? Could you clearify?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s