utl_http + https = certificate + orapki (+ acl)
When wanting to connect to an http site using the ssl protocol (https), you need to create and setup and Oracle Wallet. If you do not, you will get an “ORA-29024: Certificate validation failure” error.
This Wallet is a container, secured with password, which stores certificates needed to setup the secured socket connection.
You can use the Oracle Wallet Manager to do this, but for the database, this is only included in Enterprise Edition with Advanced Security option pack.
orapki is the command version of the graphical Oracle Wallet Manager interface.
Complete the certificate chain
The main issue is to complete the certificate chain. This is important when connecting to an address which is using certificates signed by using intermediate certificates. For more information about this, have a look on my blog about the Oracle Wallet with intermediate certificates.
When you want to connect to a secure web-service or website, you need to get all these certificates. I will explain in short how you can get the certificates with Internet Explorer if you can connect to the address with a web browser.
Go to the website in a web browser
Open Security properties
Open the security properties by clicking the lock icon at the button of the browser or next to the URL and open the [Certification Path] tab. Here you will see the complete certificate chain, which is the key to get this working. Click the certificate to export.
View details and export certificate
On the [Details] tab you can copy or export this certificate to a file. (In Windows Vista or Windows 7 you must run Internet Explorer as Administrator before the [Copy to File...] button is enabled).
Export it to a [Base-64 encoded X.509 (.CER)] file.
Exporting the complete chain in Firefox does not work when importing to the wallet. I’m not sure if this is a limitation of Firefox or the Wallet software.
Complete the chain!
Now go back to the Certification Path tab and click the next or parent certificate in the chain.
Click [View Certificate] and repeat the exporting process until all certificates in the chain are exported.
Create Oracle Wallet with orapki
Create the wallet with orapki
$> orapki wallet create -wallet /home/oracle/wallet -pwd password123 -auto_login
$> orapki wallet add -wallet /home/oracle/wallet -trusted_cert -cert verisignclass3.cer -pwd password123 $> orapki wallet add -wallet /home/oracle/wallet -trusted_cert -cert www.verisign.com.cer -pwd password123 $> orapki wallet add -wallet /home/oracle/wallet -trusted_cert -cert oracle.com.cer -pwd password123
(You will get an error when importing the first certificate, it is already present by default “PKI-04003: The trusted certificate is already present in the wallet.”)
$> orapki wallet display -wallet /home/oracle
A working example
Access Control List
As sysdba, you are allowed to connect to the outside world, but as a normal database user you need access thought the Access Control List. Run as sys, replace SCOTT with user_name or role:
begin dbms_network_acl_admin.create_acl ( acl => 'utl_http.xml', description => 'HTTP Access', principal => 'SCOTT', is_grant => TRUE, privilege => 'connect', start_date => null, end_date => null ); dbms_network_acl_admin.add_privilege ( acl => 'utl_http.xml', principal => 'SCOTT', is_grant => TRUE, privilege => 'resolve', start_date => null, end_date => null ); dbms_network_acl_admin.assign_acl ( acl => 'utl_http.xml', host => '*.oracle.com', lower_port => 443, upper_port => 443 ); commit; end;
For more information about the ACL, have a look at my blog about the Access Control List.
If all went well, you can now connect to the https site:
select utl_http.request('https://support.oracle.com', NULL,'file:/home/oracle/wallet','password123') from dual;
Point to the location of the wallet, do not include the wallet file name! Otherwise you will get “ORA-28759 failure to open file”.
UTL_HTTP.REQUEST('HTTPS://SUPPORT.ORACLE.COM',NULL,'FILE:/HOME/ORACLE/WALLET','P -------------------------------------------------------------------------------- <HTML> <HEAD> <title>Oracle Configuration Support Manager</title> <meta http-equiv="REFRESH" content="0;url=/CSP/ui/flash.html"></HEAD> <BODY> </BODY> </HTML>
create or replace package whitehorses as procedure connect_to_oracle; end whitehorses; create or replace package body whitehorses as procedure connect_to_oracle is req utl_http.req; resp utl_http.resp; data varchar2(32767); begin utl_http.set_wallet('file:' || '/home/oracle/wallet', 'password123'); req := utl_http.begin_request('https://support.oracle.com'); utl_http.set_header(req, 'User-Agent', 'Mozilla/4.0'); resp := utl_http.get_response(req); loop begin data := null; utl_http.read_line(resp, data, TRUE); -- process your data here exception when others then exit; end; end loop; utl_http.end_response(resp); end connect_to_oracle; end whitehorses;
The solution is not that hard to implement. You need to:
- Get the certificates
- Create a wallet
- Add certificates to wallet
- Create ACL and open access to site and port
- Connect to HTTPS!
I hope this blog post will help and good luck!Access to HTTPS via utl_http using the orapki wallet command,