example: utl_http and SSL/TLS on 12c #1

UTL_HTTP and TLS

We are not using SSL, but as name we keep using it. Obviously we use the newer version TLS. However, older versions of Oracle (up to 11.2.0.4) may not support TLSv1.2:

SQL> select utl_http.request('https://www.oracle.com',null,'file:/u01/app/oracle/product/12/db/wallet','Welcome1') 
   from dual;

ERROR at line 1:
ORA-29273: HTTP request failed
ORA-06512: at "SYS.UTL_HTTP", line 1720
ORA-28860: Fatal SSL error
ORA-06512: at line 1

Example

So we go through the basics, which is not straightforward on 12c, because of several security restrictions.

# prep our user

SQL> create user test identified by Welcome1;

User created.

SQL> grant connect,resource to test;

Grant succeeded.

begin
  dbms_network_acl_admin.create_acl (
    acl         => 'utl_http.xml',
    description => 'HTTP Access',
    principal   => 'TEST',
    is_grant    => TRUE,
    privilege   => 'connect',
    start_date  => null,
    end_date    => null);
 
  dbms_network_acl_admin.add_privilege (
    acl        => 'utl_http.xml',
    principal  => 'TEST',
    is_grant   => TRUE,
    privilege  => 'resolve',
    start_date => null,
    end_date   => null
  );
 
  dbms_network_acl_admin.assign_acl (
    acl        => 'utl_http.xml',
    host       => 'www.oracle.com',
    lower_port => 443,
    upper_port => 443
  );
end;
/

Examine the certificate chain:

openssl s_client -connect www.oracle.com:443 -showcerts \
   </dev/null &> /tmp/request
depth=2 C = US, O = GeoTrust Inc., CN = GeoTrust Global CA
verify return:1
depth=1 C = US, O = GeoTrust Inc., CN = GeoTrust SSL CA - G3
verify return:1
depth=0 C = US, ST = California, L = Redwood Shores, O = Oracle Corporation, OU = Content Management Services IT, CN = www.oracle.com
verify return:1
CONNECTED(00000003)
---

We need all certificates in the chain to trust the site identified as "CN = www.oracle.com";
We need the Certificate Authority certificate (#2) "CN = GeoTrust Global CA" and we need the Chain certificate (#1) "CN = GeoTrust SSL CA - G3".

At this point, we see in the openssl request, only the site certificate and the chain certificate are returned. The root certificate is assumed to be installed at the client site. On CentOS, that is installed in /etc/pki/tls/certs/ca-bundle.crt

In order to obtain the root certificate, we can extract one with a browser and transfer that to the server, or try to extract the certificate from the bundle on our server. We do the latter.

We start first with the chain certificate from the request; it contained 2 certificates; the chain and the server's certificate. We need only the first:

grep -n 'BEGIN CERTIFICATE' /tmp/request |tail -1
70:-----BEGIN CERTIFICATE-----
grep -n 'END CERTIFICATE' /tmp/request |tail -1
95:-----END CERTIFICATE-----

We copy the lines between 70 and 95 to the chain certificate:

head -95 /tmp/request |tail -26 > /tmp/chain.cer

We review the CA bundle on our server to contain a GeoTrust certificate:

openssl crl2pkcs7 -nocrl -certfile /etc/pki/tls/certs/ca-bundle.crt \
 | openssl pkcs7 -print_certs -text -noout|grep 'CN=GeoTrust Global CA'
        Issuer: C=US, O=GeoTrust Inc., CN=GeoTrust Global CA
        Subject: C=US, O=GeoTrust Inc., CN=GeoTrust Global CA
        Issuer: C=US, O=GeoTrust Inc., CN=GeoTrust Global CA 2
        Subject: C=US, O=GeoTrust Inc., CN=GeoTrust Global CA 2

So we need to identy the correct certificate in the bundle. We do so by observing all issuers:

openssl crl2pkcs7 -nocrl -certfile /etc/pki/tls/certs/ca-bundle.crt \
  | openssl pkcs7 -print_certs -text -noout|grep 'Issuer:' > /tmp/issuers.out

# Count the root certificates by issuers and double check that against the bundle. They should match:

cat /tmp/issuers.out |wc -l
167
grep 'BEGIN CERTIFICATE' /etc/pki/tls/certs/ca-bundle.crt| wc -l
167

Check, so we need find what certificate is 'CN=GeoTrust Global CA'

grep -n 'CN=GeoTrust Global CA' /tmp/issuers.out 
80:        Issuer: C=US, O=GeoTrust Inc., CN=GeoTrust Global CA

The 80th certificate we need to extract from the bundle:

grep -n  'BEGIN CERTIFICATE' /etc/pki/tls/certs/ca-bundle.crt \
|head -80|tail -1
1913:-----BEGIN CERTIFICATE-----
grep -n  'END CERTIFICATE' /etc/pki/tls/certs/ca-bundle.crt \
|head -80|tail -1
1932:-----END CERTIFICATE-----

That is the line numbers we need to extract between:

head -1932 /etc/pki/tls/certs/ca-bundle.crt | tail -20 > /tmp/root.cer

With the two certificates, we can construct our wallet;

echo $ORACLE_HOME
/u01/app/oracle/product/12/db
myWalletDir=$ORACLE_HOME/mywallet
mkdir $ORACLE_HOME/mywallet
# create the wallet
orapki wallet create -wallet $myWalletDir \
-auto_login -pwd Welcome1
# add the certificates
orapki wallet add -wallet $myWalletDir \
-trusted_cert -cert /tmp/root.cer -pwd Welcome1
orapki wallet add -wallet $myWalletDir \
-trusted_cert -cert /tmp/chain.cer -pwd Welcome1
# display the resulting wallet contents
orapki wallet display -wallet $myWalletDir -pwd Welcome1
Oracle PKI Tool : Version 12.1.0.2
Copyright (c) 2004, 2014, Oracle and/or its affiliates. All rights reserved.

Requested Certificates: 
User Certificates:
Trusted Certificates: 
Subject:        CN=GeoTrust SSL CA - G3,O=GeoTrust Inc.,C=US
Subject:        CN=GeoTrust Global CA,O=GeoTrust Inc.,C=US

ls $myWalletDir
cwallet.sso  cwallet.sso.lck  ewallet.p12  ewallet.p12.lck
echo $myWalletDir
/u01/app/oracle/product/12/db/mywallet

Now we can query. We use the wallet in format file:/path/to/myWalletDir, and the password of the wallet:

sqlplus test/Welcome1

select utl_http.request('https://www.oracle.com',null,
  'file:/u01/app/oracle/product/12/db/mywallet','Welcome1') from dual;

UTL_HTTP.REQUEST('HTTPS://WWW.ORACLE.COM',NULL,'FILE:/U01/APP/ORACLE/PRODUCT/12/
--------------------------------------------------------------------------------

<!DOCTYPE html>

<html lang="en-US" class="no-js">

And some housecleaning when we are done:

sqlplus / as sysdba

begin
  DBMS_NETWORK_ACL_ADMIN.DROP_ACL (
    acl         => 'utl_http.xml');
end;
/

drop user test;