8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
Transport Layer Security (TLS) Connections without a Client Wallet in Oracle Database 23c
In previous releases HTTPS callouts from the database required the use of a client wallet. From Oracle database 23c onward we can use the operating system certificate store instead.
Operating systems usually have a certificate store containing the root certificates of popular certificate authorities. This allows the operating system to make trusted connections to sites using those root certificates. These certificate stores are kept up to date with operating patches. Oracle 23c allows us to make use of the operating system certificate store, rather than having to use a wallet, which removes the burden of certificate management from us.
- Test a URL From the Operating System
- Create an ACL
- Test a Database Callout Without a Wallet
- Test a Database Callout With a Wallet
Related articles.
- UTL_HTTP and SSL (HTTPS) using Oracle Wallets
- Retrieving HTML and Binaries into Tables Over HTTP
- Create Self-Signed SSL Certificates
Test a URL From the Operating System
The simplest way to test a HTTPS URL is to use the curl 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.
$ curl -is https://oracle-base.com/sitemap.xml | grep HTTP HTTP/1.1 200 OK $
This means the required root certificate is present in the operating system certificate store.
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 TESTUSER1, which is the user we will make the call from.
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 UTL_HTTP package to return the contents of a URL.
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;
/
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 23c, as we are using the operating system certificate store by default.
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>
We could have achieved a similar result using HTTPURITYPE.
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.
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
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.
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>
We get the same error when we try to use HTTPURITYPE.
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>
We add the correct certificate to the wallet.
orapki wallet add -wallet /u01/wallet -trusted_cert -cert "/tmp/ISRG Root X1.crt" -pwd WalletPasswd123
Now the previous tests work as expected.
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>
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...