Follow Us on Twitter

Access to HTTPS via utl_http using the orapki wallet command

by Ian Hoogeboom on May 27, 2010 · 13 comments

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.

Tip 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

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

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

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

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

Next certificate

Solution 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

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

Tip 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!

Access to HTTPS via utl_http using the orapki wallet command, 4.8 out of 5 based on 8 ratings
Ratings:
VN:D [1.9.22_1171]
Rating: 4.8/5 (8 votes cast)

{ 12 comments… read them below or add one }

Zack Schwenk July 28, 2010 at 8:51 pm

Thanks for this post! It really helped me out.

Reply

oratechi October 17, 2010 at 10:45 am

if we have Oracle RAC do we have to configure Oracle Wallet manager in all nodes and utl_http.set_wallet location must be same paths?

Reply

Ahmad August 4, 2014 at 9:00 am

when I follow the steps and try to create encrypted tablespace it gave me error message wallet can not be opened.Please advise

Reply

lily May 10, 2016 at 12:34 pm

when i follow the steps but get a error message “file open failure”, and then i GRANT READ,WRITE to wallet directory ,but i get the same error message like before .

I also tried to change the security of the directory , but it doesn’t work . Anyone has any idea?

Reply

Ricardo Martin May 31, 2016 at 6:08 pm

very useful. All details covered and well explained. Especially the part about certificates, which was where my main problem (Complete the chain!).

Reply

PGanesh June 25, 2016 at 10:18 am

It is throwing below error. Please help.

ora-28759 failure to open file

Reply

Patrick Sinke June 28, 2016 at 4:39 pm

Action: Check the following:
Ensure that the Oracle wallet is located either in the default location (ORACLE_HOME/Apache/Apache/conf/ssl.wlt/default) or in the location specified by the SSLWallet directive in the ORACLE_HOME/Apache/Apache/conf/ssl.conf file. This should be the same directory location where you saved the wallet.

Enable Oracle Net tracing to determine the name of the file that cannot be opened and the reason.

Ensure that auto login was enabled when you saved the Oracle wallet. See Section 15.1.4.14, “Using Auto Login” for details.

Reply

Akash July 11, 2016 at 8:43 pm

Will this work on
Windows 7 and Oracle 11.2.0.1.0 Standard?

Reply

Raghavendra M April 5, 2018 at 4:33 pm

I am in windows and using sqldeveloper for plsql procedures, now when I tried orapki in command prompt in 11g I am getting ‘unknown command “orapki” – rest of line ignored.’ error. Can you please tell me how to use orapki utility to create wallet for local windows folder.

Reply

Manikandan July 1, 2018 at 6:48 pm

we have followed all the steps mentioned above, still we get the below error

ORA-29273: HTTP request failed
ORA-06512: at “SYS.UTL_HTTP”, line 1130
ORA-28759: failure to open file

OS:Windows10, Oracle:11g

Please help.

Reply

1Z0-071 VCE September 14, 2018 at 1:26 pm

VceTests provides valid Oracle 1Z0-071 exam dumps that are verified by expert IT professionals. We have Oracle Database 12c SQL 1Z0-071 real exam questions of Oracle Database 12c with verified answers that can be of great help for students looking for Oracle Database 12c SQL questions answers. Get best preparation study material for 1Z0-071 with latest practice test question from VceTests. https://www.vcetests.com/1Z0-071-vce.html

Reply

Leave a Comment

 

{ 1 trackback }

Previous post:

Next post:

About Whitehorses
Company profile
Services
Technology

Whitehorses website

Home page
Whitebooks
Jobs

Follow us
Blog post RSS
Comment RSS
Twitter