Using Oracle Wallets to connect SSL (HTTPS) service in Oracle Database 12c

Recently, I tried to found the complex solution for using Oracle Wallets to connect SSL (HTTPS) service in Oracle Database 12c. I didn't find any and because there are a small changes between Oracle 11g and 12c releases I decided to wrote this blog post. This blog supposes that you have Oracle Database 12c installed and you have admin rights to this database instance.

Get Site Certificates

In order to make connections to a secured resources, we need to get necessary certificates. The easiest way to do this is using a browser (Chrome and Firefox browsers are preferred). The example below uses the Chrome browser (note: in Chrome version 50 the Connection tab has been dropped when you click the (Green) Padlock for site security info). Using the browser, go to the URL you want to access from PL/SQL. For our example "https://www.redhat.com". Than open the developer toolbar (press F12 or Ctrl+Shift+I) and click on the "Security" tab.

Click the "View certificate" button and "Certification Path" tab on the dialog.

For the root node in the "Certification path", highlight the node and click the "View Certificate" button. On the new dialog, click the "Details" tab and click the "Copy to File..." button to save certificate information.

The wizard will appear after click the "Copy to File..." button, do the following.
Click the "Next" button on the welcome screen.

Select the "Base-64 encoded X.509 (.CER)" option and click the "Next" button. 

Enter file name and click the "Next" button.

Click the "Finish" button.
Repeat the previous steps for all trusted certificate in the the certification path(in this example for COMODO SECURE & COMODO RSA Extended Validation Secure Server CA 2). For Oracle Database 12c it is inappropriate to have user cert (in our example certificate for www.oracle.com in the certification path) in the Oracle Wallet. Oracle Database 12c does not want to see the user cert in the wallet as a trusted cert. This was apparently not an issue in previous versions like 11g. After copying certificates to files, copy both to some location on Oracle Database server.

Create an Oracle Wallet Containing the Certificates

Create a new location for new wallet.

oracle@db:~> mkdir -p /home/oracle/wallet/redhat

Create a new wallet

oracle@db:~> orapki wallet create -wallet /home/oracle/wallet/redhat -pwd MyPassword987 -auto_login

Now, we can add the saved certificates.

oracle@db:~> orapki wallet add -wallet /home/oracle/wallet/redhat -trusted_cert -cert 
/var/tmp/Comodo_secure.cer -pwd MyPassword987

oracle@db:~> orapki wallet add -wallet /home/oracle/wallet/redhat -trusted_cert -cert
/var/tmp/Comodo_RSA_Extended.cer -pwd MyPassword987

Test Connection

Now we have a Oracle Wallet with certificates in it, so why not to test the secured connection. To do this, create the following procedure.

CREATE OR REPLACE PROCEDURE showTitle(  i_url          IN VARCHAR2
                                      , i_walletpath   IN VARCHAR2 DEFAULT NULL
                                      , i_walletpasswd IN VARCHAR2 DEFAULT NULL)
AS
  l_httpreq   UTL_HTTP.req;
  l_httpresp  UTL_HTTP.resp;
  l_text      VARCHAR2(32767);
  l_response  CLOB;
  l_title     VARCHAR2(32767);
  
BEGIN

  IF (i_walletpath IS NOT NULL)
  THEN
    UTL_HTTP.set_wallet('file:' || i_walletpath, i_walletpasswd);
  END IF;
  
  l_httpreq  := UTL_HTTP.begin_request(i_url);
  l_httpresp := UTL_HTTP.get_response(l_httpreq);
   
  BEGIN
    LOOP
      UTL_HTTP.read_text(l_httpresp, l_text, 32766);
      l_response := l_response || l_text;
    END LOOP;    
  EXCEPTION
    WHEN UTL_HTTP.end_of_body THEN
      UTL_HTTP.end_response(l_httpresp);
  END;

  l_title := REGEXP_REPLACE(l_response,'.*<title> ?(.+) ?</title>.*', '\1', 1, 1, 'in');
  DBMS_OUTPUT.put_line(l_title);
  
EXCEPTION
  WHEN OTHERS THEN
    UTL_HTTP.end_response(l_httpresp);
    RAISE;  
END;

Connect as a DB user and try it.

SQL> EXEC showTitleTag(  i_url          => 'https://www.redhat.com'
                       , i_walletpath   => '/home/oracle/wallet/redhat'
                       , i_walletpasswd => 'WalletPasswd123');
*
Error at line 1
ORA-29273: HTTP request failed
ORA-06512: at "SYS.UTL_HTTP", line 19
ORA-24247: network access denied by access control list (ACL)
ORA-06512: at line 19
ORA-06512: at line 1

SQL>

What does this error mean?

ORA-24247: network access denied by access control list (ACL).

Cause: No access control list (ACL) has been assigned to the target host or the privilege necessary to access the target host has not been granted to the user in the access control list.

Action: Ensure that an access control list (ACL) has been assigned to the target host and the privilege necessary to access the target host has been granted to the user.

Your application will encounter an ORA-24247 error if it relies on one of the network packages and no proper ACL has been created. For the use of the following packages it is mandatory to have an ACL for the application user in place in 11g:

UTL_TCP
UTL_SMTP
UTL_MAIL
UTL_HTTP
UTL_INADDR

Create ACEs/ACLs

Some procedures and functions in the DBMS_NETWORK_ACL_ADMIN has been noted as deprecated in Oracle Database 12c. There is still concept of Access Control Lists (ACLs), but these are often created implicitly when adding an Access Control Entry (ACE). The biggest change is an Access Control Entry can be limited to specific PL/SQL APIs (UTL_TCP , UTL_SMTP, UTL_MAIL, UTL_HTTP, UTL_INADDR). In the previous Oracle Database releases, once a port was opened for a user, it was accessible to all APIs. These enhancements in Oracle Database 12c gives a greated level of control. The next PL/SQL code appends an access control entry (ACE) to the access control list (ACL) of a network host. 

BEGIN
  DBMS_NETWORK_ACL_ADMIN.append_host_ace (
    host       => 'www.redhat.com', 
    lower_port => 443,
    upper_port => 443,
    ace        => xs$ace_type(privilege_list => xs$name_list('http'),
                              principal_name => '&DBUSER.',
                              principal_type => xs_acl.ptype_db)); 

END;

Note:
  - http: Makes an HTTP request to a host through the UTL_HTTP package and the HttpUriType type

Now we can try our procedure once again.

SQL> EXEC showTitleTag(i_url => 'https://www.redhat.com', i_walletpath => '/home/oracle/wallet/redhat', i_walletpasswd => 'WalletPasswd123');
The world's open source leader

PL/SQL procedure successfully completed.

SQL>

For more info see:
DBMS_NETWORK_ACL_ADMIN
Managing Fine-Grained Access in PL/SQL Packages and Types
Fine-Grained Access to Network Services Enhancements in Oracle Database 12c Release 1

That's all.....