📋Steps in this guide1/5

Transport Layer Security (TLS) Connections without a Client Wallet in Oracle Database 23ai/26ai

In previous releases making HTTPS callouts from the database required the use of a client wallet. From Oracle database 23ai/26ai onward we can use the operating system certificate store instead.

oracle 23configurationintermediate

by OracleDba

40 views

Test a URL From the Operating System

The simplest way to test a HTTPS URL is to use the command from the operating system command line. If a valid HTTPS connection is possible, we should get a "200 OK" response. We can see it works fine. This means the required root certificate is present in the operating system certificate store.

Code/Command (click line numbers to comment):

1
2
3

$ curl -is https://oracle-base.com/sitemap.xml | grep HTTP
HTTP/1.1 200 OK
$

Create an ACL

In order to do a database callout we need an Access Control List (ACL) for the host. The following example creates an ACL for the host "oracle-base.com" on port 443. The principal is , which is the user we will make the call from.

Code/Command (click line numbers to comment):

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 a Database Callout Without a Wallet

We create a procedure using the package to return the contents of a URL. In previous releases attempting to use the procedure without opening a wallet would result in a certificate validation failure. That is not the case in Oracle 23ai/26ai, as we are using the operating system certificate store by default. We could have achieved a similar result using .

Code/Command (click line numbers to comment):

create or replace procedure show_html_from_url (
  p_url  in  varchar2
) 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);

  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;
/

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

<?xml version="1.0" encoding="UTF-8"?>
<urlset xmlns="http://www.sitemaps.org/schemas/sitemap/0.9">
  <url>

... output removed ...

  <url>
</urlset>

PL/SQL procedure successfully completed.

SQL>

set serveroutput on long 1000000
select HTTPURITYPE.createuri('https://oracle-base.com/sitemap.xml').getclob();

HTTPURITYPE.CREATEURI('HTTPS://ORACLE-BASE.COM/SITEMAP.XML').GETCLOB()
--------------------------------------------------------------------------------
<?xml version="1.0" encoding="UTF-8"?>
<urlset xmlns="http://www.sitemaps.org/schemas/sitemap/0.9">
  <url>

... output removed ...

  <url>
</urlset>

SQL>

Test a Database Callout With a Wallet

We can still use a wallet containing the relevant root certificate, but once we reference the wallet it takes priority. If the wallet doesn't contain the correct root certificate, the call will fail. To demonstrate this we make a new wallet containing a self-signed certificate, rather than the root certificate of the URL we are calling. We connect to a new session, open the wallet, and attempt to run the procedure to make the callout. As expected, this results in a certificate validation failure. We get the same error when we try to use . We add the correct certificate to the wallet. Now the previous tests work as expected.

Code/Command (click line numbers to comment):

mkdir -p /u01/wallet
orapki wallet create -wallet /u01/wallet -pwd WalletPasswd123 -auto_login

orapki wallet add -wallet /u01/wallet -pwd WalletPasswd123 \
  -dn "CN=`hostname`, OU=Example Department, O=Example Company, L=Birmingham, ST=West Midlands, C=GB" \
  -keysize 1024 -self_signed -validity 365

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

set serveroutput on long 1000000
exec utl_http.set_wallet('file:/u01/wallet', null);

exec show_html_from_url('https://oracle-base.com/sitemap.xml');
*
ERROR at line 1:
ORA-29273: HTTP request failed
ORA-06512: at "TESTUSER1.SHOW_HTML_FROM_URL", line 26
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 9
ORA-06512: at line 1
Help: https://docs.oracle.com/error-help/db/ora-29273/

SQL>

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

set serveroutput on
exec utl_http.set_wallet('file:/u01/wallet', null);

select HTTPURITYPE.createuri('https://oracle-base.com/sitemap.xml').getclob();

ERROR:
ORA-29273: HTTP request failed
ORA-06512: at "SYS.HTTPURITYPE", line 38
ORA-29024: Certificate validation failure
ORA-06512: at "SYS.UTL_HTTP", line 380
ORA-06512: at "SYS.UTL_HTTP", line 1189
ORA-06512: at "SYS.HTTPURITYPE", line 23
Help: https://docs.oracle.com/error-help/db/ora-29273/

SQL>

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

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

set serveroutput on long 1000000
exec utl_http.set_wallet('file:/u01/wallet', null);

exec show_html_from_url('https://oracle-base.com/sitemap.xml');

<?xml version="1.0" encoding="UTF-8"?>
<urlset xmlns="http://www.sitemaps.org/schemas/sitemap/0.9">
  <url>

... output removed ...

  <url>
</urlset>

PL/SQL procedure successfully completed.

SQL>


select HTTPURITYPE.createuri('https://oracle-base.com/sitemap.xml').getclob();

HTTPURITYPE.CREATEURI('HTTPS://ORACLE-BASE.COM/SITEMAP.XML').GETCLOB()
--------------------------------------------------------------------------------
<?xml version="1.0" encoding="UTF-8"?>
<urlset xmlns="http://www.sitemaps.org/schemas/sitemap/0.9">
  <url>

... output removed ...

  <url>
</urlset>

SQL>

Mark a Self-Signed Certificate as Trusted

We are not always working with certificates from a certificate authority. Sometimes we may need to make callouts to services using self-signed certificates. Fortunately we can mark self-signed certificates as trusted on the OS of the database server. The following actions were performed as the "root" user. We create a self-signed certificate, as demonstrated here . We copy it to the "/etc/pki/ca-trust/source/anchors/" directory and run to make the change take effect. We use to check the certificate is present. For more information see: - Transport Layer Security Connections without a Client Wallet - UTL_HTTP and SSL (HTTPS) using Oracle Wallets - Retrieving HTML and Binaries into Tables Over HTTP - Create Self-Signed SSL Certificates Hope this helps. Regards Tim...

Code/Command (click line numbers to comment):

mkdir -p ~/certs

openssl req \
  -newkey rsa:4096 -nodes -sha256 -keyout ~/certs/${HOSTNAME}.key \
  -x509 -days 3650 -out ~/certs/${HOSTNAME}.crt \
  -subj "/C=GB/ST=West Midlands/L=Birmingham/O=Example Company/OU=Devs/CN=Tim Hall/[email protected]"

cp ~/certs/${HOSTNAME}.crt /etc/pki/ca-trust/source/anchors/
update-ca-trust

# trust list --filter=ca-anchors | grep -B 4 -A 2 "Tim Hall"
pkcs11:id=%EE%3A%98%8F%93%C2%64%23%E0%42%7F%52%97%54%6D%87%7F%C0%2F%05;type=cert
    type: certificate
    label: Tim Hall
    trust: anchor
    category: authority
#

Comments (0)

Please to add comments

No comments yet. Be the first to comment!