Follow Us on Twitter

Access to HTTPS via utl_http using the orapki wallet command

by Ian Hoogeboom on May 27, 2010 · 43 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)

{ 42 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

Nils Stritzel December 11, 2018 at 9:16 pm

12c does not want to see the user cert in the wallet as a trusted cert, i.e. only import 2 certificates (in the example it would be the ones from verisign).
If you import the user certificate, it won’t work.
see https://community.oracle.com/thread/2593054

Reply

Tina June 25, 2019 at 12:41 pm

Hi guys you can check this thing as per your demand
https://amazonexamdumps.com/downloads/CLF-C01

Reply

Tommy June 25, 2019 at 12:44 pm

HI friends you have to check one time which thing is beneficial for your future.
https://amazonexamdumps.com/downloads/AWS-Certified-Big-Data-Specialty

Reply

Jafry June 25, 2019 at 12:44 pm
noshi June 25, 2019 at 12:45 pm
Prepare4sure July 9, 2019 at 8:09 am

We have all the study guide and preparation material that will allow you to prepare for any certification exam. You can get the list of well-adjusted questions and answers that will help you prepare for the exam and you can also practice using our practice test software for the real assessment.

Reply

Certstalk July 31, 2019 at 6:05 pm

A place to share your knowledge and better understand the Certifications world.
Certstalk is the best sites where you can easily discuss your certifications exam. It’s a great forum where you can put your suggestions and ask your questions relevant to your exam.
Visit Here http://certstalk.com

Reply

Braindumpspro August 30, 2019 at 1:04 pm

helps you to pass you IT Certification tests in One Attempt with actual braindumps Questions & answers tested by professionals.

Reply

onlinelatestarticles September 24, 2019 at 3:30 pm

Article Marketing Advice That Will Help You Succeed

If you think you have a great idea you would like to market on the internet, do not wait around until someone else also thinks of it. Act as soon as you can to turn your ideas into money. Article marketing is a great way you can do this, and we’ll go over some of the best tips you can find on article marketing below.

When writing an article, don’t overuse keywords. Incorporate them well into your article. Make sure they appear in the title, first paragraph and in the concluding paragraph. Don’t use them in the body of the article as this gets repetitive.

Visit Here: http://onlinelatestarticles.com/

Reply

bannka September 28, 2019 at 10:13 am

Bannka is the mobile phone paparazzi app
1 | Shoot
2 | Tap to upload
3 | People see instantly

Visit Here: http://www.bannka.com/

Reply

saveforkids September 28, 2019 at 10:14 am

Advice From Those Who Know Thanks To Multi-level Marketing Success

Success is something we all wish to achieve. It doesn’t matter if you are a lawyer or a multi-level marketer, the fact is that your knowledge is what will set you apart from the competition. Take your time now to read this article and learn all you can about the field.

VOLUNTEER/EMPLOYMENT
Want to help Save for Kids?
We help the community by offering assistance in any way needed. This assistance is funded by our many fund raising campaigns. These campaigns take a lot of work and there are always positions open for volunteers and paid employees. Join a

Reply

vortexlive.ca September 28, 2019 at 10:15 am

Multi-Level Marketing – What It Is And How You Can Succeed

Who are you? Are you someone who is driven to succeed? Are you the type of person who sees a project out to the bitter end? If this sounds like you, this article has what you need to know about multi-level marketing so that you can turn it into a lucrative career.

Practice transparent communication with your downline. Holding back information is dishonest and will not lead to trust. It is very important that the members of your downline feel they can trust you and that you have their best interests at heart. Even if your team members have not ever met you and only know you virtually, you must strive to be honest, trustworthy and supportive. If your team members don’t trust you, you will never have success with MLM.

Visit Here http://www.vortexlive.ca

Reply

safariskenyatanzania September 28, 2019 at 10:17 am

A Quick Reference List Of Multi-level Marketing Tips

How big was your last pay check? Did it make your eyes bulge out of your head when you saw it? I doubt it, but what if you could join a field where this happens all the time? Multi-level marketing is here for you to leverage, but only by using the tips below.

Visit Here: http://www.safariskenyatanzania.com/

Reply

americanrare September 28, 2019 at 10:18 am

Become educated about different marketing tactics. You may be good at direct selling, but you may be struggling with technology. You can try learning about how online marketing can help boost your business. On the flip side, if you are great at online technology but poor at direct selling, try boosting your communication skills.

Visit Here: http://www.americanrare.com/

Reply

homeimprovementgate September 28, 2019 at 1:51 pm

Home Improvement Made Easy: Tips And Tricks

The most important thing to remember about making home improvements is that they don’t need to be difficult. A little foresight and planning now can save a lot of money and hassle later. Read the article below for some simple ways to make home improvement easier.

Make sure you take before and after photos of any work or improvements you do to your home. You may like to look back on all the hard work and changes when it feels like the project as a whole will never be done. Keep pictures on your computer or have them printed for a scrapbook.

Learn your personal style before beginning any home customization. Determining your style has a large impact on your future home improvement choices. If you’re not paying attention, you might choose decorations in multiple styles without noticing that they clash. Style changes will add extra time, as well as extra money, to any home improvement project.

Reply

fithomeimprovement September 28, 2019 at 1:57 pm

Love Your Home And Show It Off

You can prevent future problems in your house by improving some simple home improvement skills. Little problems do not turn into big problems. This article will give you the techniques necessary to handle most home improvement projects. You may find that it’s best to leave some more technical work to a professional.

When working with a contractor to do a home renovation, decide on the whole project at the beginning and stick to that plan. Constantly changing the plan can slow down the renovation process and scare off your contractor from doing anything independently. Also, constantly changing renovations often end up looking piecemeal, since they weren’t created with a clear vision in mind.

Visit Here: https://www.fithomeimprovement.com

Reply

voicepets September 28, 2019 at 2:12 pm

Proper Care For A Cat With These Tips.

Cats are fundamental different from humans. Aside from them being covered in fur and walking on all fours, they have different diet requirements. There are certain foods that a cat must eat, and some that a cat can’t eat. The following article can give you some dietary tips to aid you in feeding your cat.

Taking your cat in for regular check-ups at the veterinarian’s office is part of being a responsible pet owner. Your cat should have regular well visits, as well as all the recommended vaccinations for his age. It is best to always take your cat to the same veterinarian. This ensures that your vet will have an excellent understanding of your pet’s medical history.

Visit Here: http://www.voicepets.com

Reply

petsblog September 28, 2019 at 2:17 pm

Dog Care Tips That Will Work Like Magic

You must take dog ownership seriously. It is up to you to provide a healthy and loving environment for your dog. Knowing what it takes to achieve this balance with your dog means learning all you can, so educate yourself fully by reading the information which lies below.

When you are trying to teach your dog new tricks, you should always be patient. Different dogs will learn things differently. If your dog isn’t picking up a new trick right away, give him time. You could also try changing your teaching method. Your dog may respond better to a different approach.

Visit Here: https://www.petsblog.org

Reply

rarecareer September 28, 2019 at 2:20 pm

Cool College Tips You Really Need To Know

College may be a time for many new experiences, but it’s also a time for serious learning. In order to pass your college courses, you need to be focused, do your work, and study. The following tips will help you study better for your college classes and lead you to good grades.

College is filled with a lot of stress as the best thing that you can do in regards to school is to prepare in advance. Avoid procrastination at all times, as this will only add to the stresses that you already face. By doing your work ahead of time, you can feel prepared and organized as college goes on.

Visit Here: https://rarecareer.com

Reply

jgen October 4, 2019 at 9:55 am

As appealing as being a home business owner is, it can also be frightening. How should you begin? What makes a home business successful? Questions like these need to be addressed. This article will give you some home business advice and guidance.

https://tinyurl.com/y4s62hvd

Reply

safecaronline October 4, 2019 at 9:57 am

Your vehicle is an important possession and you should take good care of it. Perhaps you’re not sure if you know enough about cars, but are willing to learn. If that is the case, continue reading to learn simple maintenance tips.

https://tinyurl.com/y2umawar

Reply

wellnessfox October 4, 2019 at 10:02 am

There are a lot of people that are becoming more and more interested in web design. This means that more and more people are building their own website. This article will give you some tips on how to get a leg up on the competition.

https://tinyurl.com/y6tobxdz

Reply

Elisabeth t thompson October 4, 2019 at 10:10 am

Buying a home or property is a whirlwind experience. When it comes to selling that same property, it’s a whole new ballgame. Suddenly the tables are turned. You need a whole new set of rules to play by, and that is what this article will give you.

When selling your home, remove anything of a personal nature.
https://tinyurl.com/y6eezuf4

Reply

vipwallpapers October 5, 2019 at 6:26 am

You might not understand everything that goes into making a website. You have to realize that you need to be concerned with everything from text boxes, border sizes, bandwidth allotment and overall data sizes of your site. If you use these tips, you will be prepared to handle everything that web design throws at you.

NoScript is your friend and a test your site has to pass in order for it to be deemed functional. Download and run the NoScript extension on Firefox and find out if the site is readable? Certain content cannot be read unless scripts are present. A blank site without scripts can create big problems.
https://tinyurl.com/y6lvxyoz

Reply

homesimprovements October 7, 2019 at 10:36 am

Undertaking a home improvement project not only makes your home a more pleasant place to live, but will also increase its value. Before you get started, though, make sure you give yourself an advantage and help yourself avoid common errors by making use of the tips and techniques in this article..

Visit Here: https://www.homesimprovements.net/2018/03/09/sheetrock-whats-going-on-here/

Reply

homeimprovementmix October 7, 2019 at 10:38 am

To save money on home maintenance, check all of your faucets and pipes for leaks and have them repaired quickly. Leaking faucets can waste quite a bit of water, costing you in higher bills. Also, pipes that are leaking within your walls or under your house can lead to serious water damage.

Visit Here: https://homeimprovementmix.com/tips-to-clean-your-house-together-with-all-family-members/

Reply

ehomeimprovement October 7, 2019 at 10:40 am

In northern climates where heavy snow covers the ground all winter, you may lose track of where the borders of pathways are. To avoid this it is a good idea to put a 4′ X 4′ lumber bordering the paths. Then, when you are shoveling snow, you will know exactly where the edge of the path should be.

Visit Here :http://ehomeimprovement.co.uk/from-kitchen-cupboards-to-shower-entryways-five-different-ways-to-enhance-the-resale-estimation-of-your-home/

Reply

thehomeimprovement October 7, 2019 at 10:42 am

Choosing to use wooden flooring instead of carpeting can save you a lot of hassle in the long run. Carpet can contain allergens and cleaning it is sometimes out of the question. The cost of ripping up and replacing your carpet can almost be as costly as just putting down wooden flooring. Wooden floors can be refinished and repainted as needed; also providing a more rural and cottage style environment.

Visit Here: http://thehomeimprovement.us/what-you-can-anticipate-from-an-extravagance-home/

Reply

ohomeimprovement October 7, 2019 at 10:44 am

No matter what kind of home improvement project you are considering, the advice in this article can help you get the best results. Whether you are redoing a kitchen, installing a new shower or adding a second floor, the tips and ideas in this article, will help your project go smoothly.

Visit Here: https://www.ohomeimprovement.co.uk/index.php/2018/08/04/adorn-your-home-for-happiness-utilizing-structure-brain-science/

Reply

Tommy November 8, 2019 at 1:44 pm
certsway November 9, 2019 at 5:01 pm

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