8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Home » Articles » Misc » Here

UTL_HTTP and SSL (HTTPS) using Oracle Wallets

Since Oracle 9i Release 2, the UTL_HTTP package has had the ability to access resources over HTTPS as well as HTTP. This article describes the method for enabling HTTPS access from the UTL_HTTP package.

Related articles.

Access Control List (ACL)

If you are using Oracle 11g, you will need to provide an ACL to allow the UTL_HTTP package to interact with an external host. This is described here.

For this example my database user is TESTUSER1, so this is the principal name, and we will be connecting to this website. Adjust as necessary.

conn sys/SysPassword1@//localhost:1521/freepdb1 as sysdba

begin
  dbms_network_acl_admin.append_host_ace (
    host       => 'oracle-base.com', 
    lower_port => 443,
    upper_port => 443,
    ace        => xs$ace_type(privilege_list => xs$name_list('http'),
                              principal_name => 'testuser1',
                              principal_type => xs_acl.ptype_db)); 
end;
/

conn testuser1/testuser1@//localhost:1521/freepdb1

Test Unsecured Connection

Before we start trying to configure SSL, lets see what happens if we attempt to access a HTTPS resource using the UTL_HTTP package. To do this, create the following procedure.

create or replace procedure show_html_from_url (
  p_url  in  varchar2,
  p_username in varchar2 default null,
  p_password in varchar2 default null
) as
  l_http_request   utl_http.req;
  l_http_response  utl_http.resp;
  l_text           varchar2(32767);
begin
  -- Make a http request and get the response.
  l_http_request  := utl_http.begin_request(p_url);

  -- Use basic authentication if required.
  if p_username is not null and p_password is not null then
    utl_http.set_authentication(l_http_request, p_username, p_password);
  end if;

  l_http_response := utl_http.get_response(l_http_request);

  -- Loop through the response.
  begin
    loop
      utl_http.read_text(l_http_response, l_text, 32766);
      dbms_output.put_line (l_text);
    end loop;
  exception
    when utl_http.end_of_body then
      utl_http.end_response(l_http_response);
  end;
exception
  when others then
    utl_http.end_response(l_http_response);
    raise;
end show_html_from_url;
/

This procedure works for a regular HTTP resource, but what happens if we call it using a HTTPS resource? The following example uses "https://oracle-base.com/".

set serveroutput on
exec show_html_from_url('https://oracle-base.com/');

*
ERROR at line 1:
ORA-29273: HTTP request failed
ORA-06512: at "TESTUSER1.SHOW_HTML_FROM_URL", line 33
ORA-29024: Certificate validation failure
ORA-06512: at "SYS.UTL_HTTP", line 380
ORA-06512: at "SYS.UTL_HTTP", line 1189
ORA-06512: at "TESTUSER1.SHOW_HTML_FROM_URL", line 11
ORA-06512: at line 1

SQL>

The error stack shows the "ORA-29024: Certificate validation failure" error.

Get Site Certificates

In order to make connections to a secured resource, we need to get the necessary certificate. The easiest way to do this is using a browser. The example below uses the Chrome browser.

Using the browser, go to the URL you are attempting to access from PL/SQL. In this case "https://oracle-base.com/". Click the lock icon in the URL bar to display the certificate menu then click on the "Connection is secure" and "certificate is valid". The specifics will change with the browser you use and the version, but there will be something similar to this. You are presented with the "Certificate Viewer" screen.

Certificate Viewer

Click the "Details" tab, highlight the root certificate, not your certificate, and click the "export" button.

Certification Path

Save the certificate to your file system, and make it available on the server. In this example I put the root certificate into "/tmp".

Create an Oracle Wallet Containing the Certificates

Create a new location to hold the wallet.

$ mkdir -p /u01/wallet

Create a new wallet.

$ orapki wallet create -wallet /u01/wallet -pwd WalletPasswd123 -auto_login

If the wallet password is too weak, you will get a message telling you so.

PKI-01002: Invalid password. Passwords must have a minimum length of eight characters and contain alphabetic characters combined with numbers or special characters.

In Oracle 11.2 the same issue causes a failure to create the wallet with the following message.

Unable to save wallet at /u01/wallet

With the wallet created, we can add the certificate we saved earlier.

$ orapki wallet add -wallet /u01/wallet -trusted_cert -cert "/tmp/ISRG Root X1.crt" -pwd WalletPasswd123

The root certificate may fail to load with the following message, which can be ignored. It just means it was already present by default.

Could not install trusted cert at/tmp/ISRG Root X1.crt
PKI-04003: The trusted certificate is already present in the wallet.

Test Secured Connection

We are now ready to access the secured resource, but we must provide the UTL_HTTP package with the wallet details so it can make the secured connections. This is done using the UTL_HTTP.SET_WALLET procedure. Repeating the previous test now works successfully.

set serveroutput on
exec utl_http.set_wallet('file:/u01/wallet', 'WalletPasswd123');
exec show_html_from_url('https://oracle-base.com/');

... HTML output removed ...

PL/SQL procedure successfully completed.

SQL>

From Oracle 11gR2 onward, if you are using the -auto_login option on the wallet, you don't have to specify the wallet password. You just pass NULL instead of the password. Thanks to Jason in the comments for pointing this change out!

set serveroutput on
exec utl_http.set_wallet('file:/u01/wallet', null);
exec show_html_from_url('https://oracle-base.com/');

... HTML output removed ...

PL/SQL procedure successfully completed.

SQL>

You may want to incorporate this into the procedure.

create or replace procedure show_html_from_url (
  p_url              in  varchar2,
  p_username         in  varchar2 default null,
  p_password         in  varchar2 default null,
  p_wallet_path      in  varchar2 default null,
  p_wallet_password  in  varchar2 default null
) as
  l_http_request   utl_http.req;
  l_http_response  utl_http.resp;
  l_text           varchar2(32767);
begin
  -- If using HTTPS, open a wallet containing the trusted root certificate.
  if p_wallet_path is not null and p_wallet_password is not null then
    utl_http.set_wallet('file:' || p_wallet_path, p_wallet_password);
  end if;

  -- Make a HTTP request and get the response.
  l_http_request  := utl_http.begin_request(p_url);

  -- Use basic authentication if required.
  if p_username is not null and p_password is not null then
    utl_http.set_authentication(l_http_request, p_username, p_password);
  end if;

  l_http_response := utl_http.get_response(l_http_request);

  -- Loop through the response.
  begin
    loop
      utl_http.read_text(l_http_response, l_text, 32766);
      dbms_output.put_line (l_text);
    end loop;
  exception
    when utl_http.end_of_body then
      utl_http.end_response(l_http_response);
  end;
exception
  when others then
    utl_http.end_response(l_http_response);
    raise;
end show_html_from_url;
/

Authentication

If you are accessing a site that requires authentication, you will need to do one of two things depending on the type of authentication used.

If the site uses basic authentication, simply specify the credentials in the call to SHOW_HTOM_FROM_URL, which will use them in the UTL_HTTP.SET_AUTHENTICATION call.

set serveroutput on
exec utl_http.set_wallet('file:/u01/app/oracle/admin/DB11G/wallet', 'WalletPasswd123');
exec show_html_from_url('https://oracle-base.com/', 'username', 'password');

... HTML output removed ...

PL/SQL procedure successfully completed.

SQL>

If the page uses digest authentication, then you will need to will need to install the digest_auth_api package, then make the following modification to the test code.

create or replace procedure show_html_from_url (
  p_url              in  varchar2,
  p_username         in  varchar2 default null,
  p_password         in  varchar2 default null,
  p_wallet_path      in  varchar2 default null,
  p_wallet_password  in  varchar2 default null
) as
  l_http_request   utl_http.req;
  l_http_response  utl_http.resp;
  l_text           varchar2(32767);
begin
  -- If using HTTPS, open a wallet containing the trusted root certificate.
  if p_wallet_path is not null and p_wallet_password is not null then
    utl_http.set_wallet('file:' || p_wallet_path, p_wallet_password);
  end if;

  -- Make a HTTP request and get the response.
  l_http_request  := digest_auth_api.begin_request(p_url          => p_url,
                                                   p_username     => p_username,
                                                   p_password     => p_password,
                                                   p_method       => 'GET');

  l_http_response := utl_http.get_response(l_http_request);

  -- Loop through the response.
  begin
    loop
      utl_http.read_text(l_http_response, l_text, 32766);
      dbms_output.put_line (l_text);
    end loop;
  exception
    when utl_http.end_of_body then
      utl_http.end_response(l_http_response);
  end;
exception
  when others then
    utl_http.end_response(l_http_response);
    raise;
end show_html_from_url;
/

You can then call the test code in the same way you did for basic authentication.

set serveroutput on
exec show_html_from_url('https://oracle-base.com/', 'username', 'password', '/u01/wallet', 'WalletPasswd123');

... HTML output removed ...

PL/SQL procedure successfully completed.

SQL>

SSLv3, TLSv1 and POODLE

With the publicity about the POODLE bug, many web masters are turning off SSLv3 support. Depending on your Oracle database version/patch, that can present a bit of a problem for people using UTL_HTTP to access HTTPS resources, as described here.

The MOS note for the following comment has been removed/hidden, so it's possible this was a bug that is now fixed in 12.1.0.2.

Interestingly, if you upgrade to Oracle 12c, you might have problems in the other direction, since Oracle 12c prevents UTL_HTTP calls over HTTPS to anything older than TLSv1.2, as described here.

For more information see:

Hope this helps. Regards Tim...

Back to the Top.