Oracle Utl_http Access HTTPS type

Https://oracle-base.com/articles/misc/utl_http-and-ssl

http://blog.whitehorses.nl/2010/05/27/access-to-https-via-utl_http-using-the-orapki-wallet-command/

Utl_http and SSL (HTTPS) using Oracle wallets

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

• Access Control List (ACL)
• Test unsecured Connection
• Get Site Certificates
• Create an Oracle Wallet containing the certificates
• Test Secured Connection
• Authentication
• SSLv3, TLSv1 and POODLE

Access Control List (ACL)

If you is using Oracle 11g, you'll need to provide a ACL to allow the package to interact with anUTL_HTTPexternal host. This was described here.

• Fine-grained Access to Network Services in Oracle Database 11g Release 1

Test unsecured Connection

Before we start trying to configure SSL, lets see what happens if we attempt to access a HTTPS resource using theUTL_HTTPPA Ckage. To does 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, and what happens if we call it using a HTTPS resource? The following example uses "https://gb.redhat.com/".

SET SERVEROUTPUT ON
EXEC show_html_from_url(‘https://gb.redhat.com/‘);

*
ERROR at line 1:
ORA-29273: HTTP request failed
ORA-06512: at "SYS.UTL_HTTP", line 1527
ORA-29261: bad argument
ORA-06512: at "TEST.SHOW_HTML_FROM_URL", line 22
ORA-29273: HTTP request failed
ORA-06512: at "SYS.UTL_HTTP", line 1130
ORA-29024: Certificate validation failure
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 is using a browser. The example below uses the Chrome browser.

Using the browser, go to the URL is attempting to access from PL/SQL. In the case "https://gb.redhat.com/". Click the lock icon in the URL bar to display the Certificate menu and click on the "Connection" tab.

Click the "Certificate Information" link and click the "Certification Path" tab on the resulting dialog.

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

On the resulting wizard, do the following.

• Click the "Next" button on the welcome.
• Select the "Base-64 encoded (. CER) "option and click the" Next button. Other formats work, but I ' ve found the the most consistent.
• Enter suitable file name and click the "Next" button.
• Click the "Finish" button.

A Similar dialog is displayed in Firefox by clicking "URL Icon > More information > View Certificate > Details Ta B ".

Thanks to Erik for pointing out I don ' t need to download the intermediate certificates. Just the root certificate.

Create an Oracle Wallet containing the certificates

Create a new location to the wallet.

$ mkdir-p/u01/app/oracle/admin/db11g/wallet

Create a new wallet.

$ ORAPKI Wallet Create-wallet/u01/app/oracle/admin/db11g/wallet-pwd Walletpasswd123-auto_login

If The wallet password is too weak, you'll get a message telling you.

Invalid password....
PASSWORD_POLICY : 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/app/oracle/admin/db11g/wallet

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

$ orapki Wallet Add-wallet/u01/app/oracle/admin/db11g/wallet-trusted_cert-cert "/HOST/BALTIMORECYBERTRUSTROOT.CRT" -pwd WalletPasswd123

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

Test Secured Connection

We are now ready for access the secured resource, but we must provide the package with theUTL_HTTPwallet details so it can m Ake the secured connections. This was done using theUTL_HTTP.SET_WALLETprocedure. Repeating the previous test now works successfully.

SET SERVEROUTPUT ON
EXEC UTL_HTTP.set_wallet(‘file:/u01/app/oracle/admin/DB11G/wallet‘, ‘WalletPasswd123‘);
EXEC show_html_from_url(‘https://gb.redhat.com/‘);

... HTML output removed ...

PL/SQL procedure successfully completed.

SQL>

Authentication

If you is accessing a site that requires authentication, you'll need to do one of the one and the things depending on the type of a Uthentication used.

If the site uses Basic authentication, simply specify the credentialsSHOW_HTOM_FROM_URLin the call to, which'll use them in thecall.

SET SERVEROUTPUT ON
EXEC UTL_HTTP.set_wallet(‘file:/u01/app/oracle/admin/DB11G/wallet‘, ‘WalletPasswd123‘);
EXEC show_html_from_url(‘https://gb.redhat.com/‘, ‘username‘, ‘password‘);

... HTML output removed ...

PL/SQL procedure successfully completed.

SQL>

If the page uses Digest authentication, then you'll need to would need to install the DIGEST_AUTH_API package and 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) 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  := 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-a-do for basic authentication.

SET SERVEROUTPUT ON
EXEC UTL_HTTP.set_wallet(‘file:/u01/app/oracle/admin/DB11G/wallet‘, ‘WalletPasswd123‘);
EXEC show_html_from_url(‘https://gb.redhat.com/‘, ‘username‘, ‘password‘);

... HTML output removed ...

PL/SQL procedure successfully completed.

SQL>

SSLv3, TLSv1 and POODLE

With the publicity about the POODLE bug, many web Masters is turning off SSLv3 support. Depending on your Oracle database Version/patch, which can present a bit of a problem for people using toUTL_HTTPaccess HTTP S resources, as described here.

• UTL_HTTPPackage fails with ORA-29273 ORA-28860 if Using TLSv1 (Doc ID 727118.1): Basically, older database releases only allow HTTPS using the SSLV3 protocol fromUTL_HTTP. If you want-to-use the TLSV1 protocol your need to make sure your is on a patched up version of 11.2.

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

• UTL_HTTPGives Error over HTTPS Using RDBMS 12.1.0.1.0 (Doc ID 1675966.1) So you might has trouble accessing legacy systems, Witho UT reverting to HTTP.

For more information see:

• Orapki Utility
• Utl_http

Hope this helps. Regards Tim ...

Oracle Utl_http Access HTTPS type