Free Online Registration Required

The tutorial session you want to view requires your registering with us.

It’s fast and easy, and totally FREE.

And best of all, once you are registered, you’ll also have access to all the other 100’s of FREE Video Tutorials we offer!

 

×
Transcript

Introduction / Agenda

>> John:  My name is John Watson and I work for SkillBuilders heading up our Oracle Database Administration Practice.

 

[pause]

 

A major part of our work is supporting our APEX developers, and more and more I find that our APEX developers are wanting to call web services from their APEX applications. Now, any decent web service is protected by secure sockets and every time the developers come to me with the same problems. First, that the database won’t actually permit them to open a connection to the external service and secondly, that the web services digital certificate is being rejected when they do that, get that connection.

 

I’m going to show you in this demonstration how to permit use of SSL in a safe manner and I’ll use the Oracle website as an example.

 

Copyright SkillBuilders.com 2017

×
Transcript

Demonstration – Create an Access Control List

>> John:  The first step is to create the schema within which I’m going to work. I’ll create my user jw and grant him connect and resource. Not necessarily best practice in the production system, but it will do for this demonstration.

 

[pause]

 

Now I need to create the access control list because without an access control list, no one can go anywhere. This procedure call dbms_network_acl_admin.append_host_ace. Ace is access control entry. I’m going to permit access to the host, the one nominated host – www.oracle.com – for one nominated user or principal.

 

The user I just created jw. So now jw can open connections to that address, but that isn’t enough. We have to configure Oracle to accept the digital certificate that will be required if you attempt to use SSL to that address.

 

Copyright SkillBuilders.com 2017

×
Transcript

Demonstration – Using the orapki Utility to Add Digital Certificates

>> John:  When you use HTTPS in a browser, it will usually work out of the box, no need for any configuration. And this is because browsers ship with a list of trusted certificate issuers. You can see the list here in Firefox. Options, advanced, certificates, and view the certificates. So, on the authorities tab these are the certificates that Firefox will accept or the certificate issuers that Firefox is prepared to accept. There they all are.

 

[pause]

 

As long as the website has bought and installed a certificate on one of these approved issuers, your browser will be happy. But the Oracle database has no such list of trusted certificate issuers. You have to build this up for yourself by downloading the certificates and installing them in a wallet.

 

The first step is to tell Oracle where the trusted certificates wallet is. We do that with entry in the sqlnet.ora file. Wallet location = source, method file – I could be using held up directory for instance but in this case, my wallet is going to be in a file and the metadata is the location of the wallet which will be in c:\tmp\wallet.

 

Then I need to create the directory, mkdir c:\tmp\wallet.

 

And then finally use the Oracle utility orapki to create the wallet. When I create the wallet, I give it the location of the wallet, a password to open the wallet, and I’m going to set it to auto login.

 

I need to obtain the certificates of the trusted certificate issuers from the website I’m going to go to. So I’ll browse to the Oracle.com secure site.

 

[pause]

 

And the exact technique will follow here will depend on the browser you’re using. In Firefox, this icon here will show me the details of the secure socket configuration.

 

[pause]

 

Here we see a certificate chain. I need to download certificates for all components of the chain except for the leaf certificate at the very end. That’s the one that will be validated when we actually make the connection. So the top level first, GeoTrust Global CA.

 

There are various possible export formats. I find the default CRT format seems to work well enough. The next one in the chain, export that as well and that’s saved in to my local file system.

 

To load those certificates into the wallet, I’ll use the orapki [3:23 inaudible] C again. Orapki wallet add. I’m going to add the certificate for GeoTrust Global CA into the nominated wallet as a trusted certificate and supply the password and do the same with the second certificate in the chain. What wallet certificates are actually in there, the wallet display command, it shows me there they are as trusted certificates.

Copyright SkillBuilders.com 2017

×
Transcript

Demonstration – Test the Secure Web Services Call

>> John:  So now let’s see if it is actually working. I’ll log on as the user, jw, for whom I created the access control list, enable server output, and run a simple block of code that will attempt to make an outgoing secure sockets call.

 

[pause]

 

First, UTL_HTTP.SET_WALLET. I’m telling the session where the wallet is, c:\tmp\wallet and the password to open it. Then begin a request. The request https://www.oracle.com, check response code.

 

[pause]

 

Back comes response code 200. It’s working. So that’s what you have to do. Create an access control list that will give your nominated schemas access to the nominated website, then obtain the trusted certificate issuers for that website, load them into a wallet, and then your users, your APEX developers can make use of web services there, no problem. However, should you have difficulties making this work and, believe me, it can be pretty tricky. There are several levels at which the calls can fail, we will be only too happy to assist you with working through them.

 

Copyright SkillBuilders.com 2017

×