Access to HTTPS via utl_http using the orapki wallet command

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

i.e. https://support.oracle.com

Go to URL of website

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.

Certificate chain

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).

Certificate details

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.

Export certificate

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.

Next certificate

Create Oracle Wallet with orapki

Create the wallet with orapki

$> orapki wallet create -wallet /home/oracle/wallet -pwd password123 -auto_login

Add certificates

$> 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.”)

View contents

$> 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.

Connect!

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”.

Result:

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>

In PL/SQL

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;

Conclusion

The solution is not that hard to implement. You need to:

1. Get the certificates
2. Create a wallet
3. Add certificates to wallet
4. Create ACL and open access to site and port
5. Connect to HTTPS!

I hope this blog post will help and good luck!

Ratings:

VN:F [1.9.22_1171]

please wait...

Rating: 4.7/5 (6 votes cast)

Tags: acl, Oracle, security, SSL, Wallet Manager

• 

 Print This PDF Version