Pages

Wednesday, October 12, 2011

Using UTL_HTTP to access SSL (https) web sites

Demonstration in this article is given on Oracle Database - Enterprise Edition - version 11.2.0.3

Not like non-SSL (http) web site, accessing SSL (https) web sites with UTL_HTTP  needs additional configuration steps outside of PL/SQL programming as following.

1. Create wallet on database server where UTL_HTTP is called from

$ $ORACLE_HOME/bin/orapki wallet create -wallet /u01/app/oracle/admin/DB01/wallet -auto_login -pwd Pasword11203
Oracle PKI Tool : Version 11.2.0.3.0 - Production
Copyright (c) 2004, 2011, Oracle and/or its affiliates. All rights reserved.

If -auto_login option is not used, UTL_HTTP.SET_WALLET has to be called with the wallet password (here given Password11203). Check wallet built-in certificates:

$ $ORACLE_HOME/bin/orapki wallet display -wallet /u01/app/oracle/admin/DB01/wallet
Oracle PKI Tool : Version 11.2.0.3.0 - Production
Copyright (c) 2004, 2011, Oracle and/or its affiliates. All rights reserved.

Requested Certificates:
User Certificates:
Trusted Certificates:
Subject:        OU=Class 1 Public Primary Certification Authority,O=VeriSign\, Inc.,C=US
Subject:        OU=Class 3 Public Primary Certification Authority,O=VeriSign\, Inc.,C=US
Subject:        OU=Class 2 Public Primary Certification Authority,O=VeriSign\, Inc.,C=US
Subject:        OU=Secure Server Certification Authority,O=RSA Data Security\, Inc.,C=US
Subject:        CN=GTE CyberTrust Global Root,OU=GTE CyberTrust Solutions\, Inc.,O=GTE Corporation,C=US


2. Import certificate of the web sites

Certificate file GoogleAccount.cer is exported from FireFox while https://account.google.com is open. If you do not know how to export certificate of your SSL web sites, try to GOOGLE with  "export ssl certificate from Internet Explorer Firefox".

$ $ORACLE_HOME/bin/orapki wallet add -wallet /u01/app/oracle/admin/DB01/wallet -trusted_cert -cert  /tmp/GoogleAccount.cer -pwd Pasword11203
Oracle PKI Tool : Version 11.2.0.3.0 - Production
Copyright (c) 2004, 2011, Oracle and/or its affiliates. All rights reserved.

$ $ORACLE_HOME/bin/orapki wallet display -wallet /u01/app/oracle/admin/DB01/wallet
Oracle PKI Tool : Version 11.2.0.3.0 - Production
Copyright (c) 2004, 2011, Oracle and/or its affiliates. All rights reserved.

Requested Certificates:
User Certificates:
Trusted Certificates:
Subject:        CN=GeoTrust Global CA,O=GeoTrust Inc.,C=US
Subject:        OU=Class 2 Public Primary Certification Authority,O=VeriSign\, Inc.,C=US
Subject:        OU=Secure Server Certification Authority,O=RSA Data Security\, Inc.,C=US
Subject:        OU=Class 3 Public Primary Certification Authority,O=VeriSign\, Inc.,C=US
Subject:        OU=Class 1 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

The root certificate (CN=GeoTrust Global CA,O=GeoTrust Inc.,C=US) of https://account.google.com is imported successfully.

3. Test UTL_HTTP

system@DB01> DECLARE
  2    v_http_request    UTL_HTTP.REQ;
  3    v_http_response   UTL_HTTP.RESP;
  4    v_text            VARCHAR2(255);
  5  BEGIN
  6    --UTL_HTTP.SET_PROXY ('username:password@proxy-server:port');  -- if proxy is needed
  7    UTL_HTTP.SET_WALLET('file:/u01/app/oracle/admin/DB01/wallet');
  8    v_http_request := UTL_HTTP.BEGIN_REQUEST(url => 'https://accounts.google.com', method => 'GET', http_version => 'HTTP/1.1');
  9    v_http_response := UTL_HTTP.GET_RESPONSE(v_http_request);
 10    UTL_HTTP.READ_TEXT(v_http_response, v_text, 100);
 11    DBMS_OUTPUT.PUT_LINE (v_text);
 12    UTL_HTTP.END_RESPONSE(v_http_response);
 13  END;
 14  /

<!DOCTYPE html>
<html lang="en">
  <head>
  <meta charset="utf-8">
  <meta content="width=300, init

PL/SQL procedure successfully completed.

If the wallet is created without '-auto_login' option, UTL_HTTP.SET_WALLET has to be called as UTL_HTTP.SET_WALLET('file:/u01/app/oracle/admin/DB01/wallet','Pasword11203').Here, 'Pasword11203' is password of the wallet.If the wallet is not configured properly or required certificate is not imported into wallet, UTL_HTTP will return 'ORA-29024: Certificate validation failure',

system@DB01> DECLARE
  2    v_http_request    UTL_HTTP.REQ;
  3    v_http_response   UTL_HTTP.RESP;
  4    v_text            VARCHAR2(255);
  5  BEGIN
  6    --UTL_HTTP.SET_PROXY ('username:password@proxy-server:port');  -- if proxy is needed
  7    --UTL_HTTP.SET_WALLET('file:/u01/app/oracle/admin/DB01/wallet');
  8    v_http_request := UTL_HTTP.BEGIN_REQUEST(url => 'https://accounts.google.com', method => 'GET', http_version => 'HTTP/1.1');
  9    v_http_response := UTL_HTTP.GET_RESPONSE(v_http_request);
 10    UTL_HTTP.READ_TEXT(v_http_response, v_text, 100);
 11    DBMS_OUTPUT.PUT_LINE (v_text);
 12    UTL_HTTP.END_RESPONSE(v_http_response);
 13  END;
 14  /
declare
*
ERROR at line 1:
ORA-29273: HTTP request failed
ORA-06512: at "SYS.UTL_HTTP", line 1130
ORA-29024: Certificate validation failure
ORA-06512: at line 8

No comments: