Thursday, 31 May 2012

Oracle HTTPS Requests: Set up the Wallet

In addition to setting up the ACL, if you wish to make HTTPS requests you will need to set up a wallet - and more than likely, import trusted certificates.

On the oracle server, open the wallet manager. In linux this is with the command: owm.

Assuming no current wallet exists, create a new one by going to File --> New. At this point you will need to give the wallet a password. Save the wallet and take note of the directory it installed to (this is what you need to pass to utl_http, and no single file). The default directory this is saved to is generally: $ORACLE_HOME/owm/wallets/oracle.

The next step is to import trusted certificates into the wallet. The process varies between OS/Browser. First, go to the website you are intending on making requests to and view the certificate information which is normally done by clicking a button in the address bar.

Linux Chrome:

  1. Click the button the left of the URL
  2. Click certificate information
  3. Click Details
  4. Click Export
  5. Specify the type as PKCS #7, certificate chain
  6. Save to a convenient location. I find it best to actually save it into the same folder as the oracle wallet, so you have a future reference
Windows Chrome:

  1. Click the button to the left of the URL
  2. Click certificate information
  3. Click Details
  4. Click Copy to File...
  5. Click Next
  6. Click Cryptographic Message Syntax Standard - PKCS #7 Certificates (.P7B)
  7. Click Next
  8. Save to a convenient location. I find it best to actually save it into the same folder as the oracle wallet, so you have a future reference
(The same dialogue can be got to in Internet Explorer by going to File --> Properties --> Certificates)

It is important to export the certificates in a type that allows you to export the full certificate chain, otherwise it is highly likely, and HTTPS requests will still not work.

To import the certificates into the wallet, from the wallet manager:

Right click on the tree node that reads Trusted Certificates, and select the option Import Trusted Certificate... 
Select the option Select a file that contains the certificates
Locate the file and click OK

If all went well, you should see at least 1 new certificate added to the node Trusted Certificates.

In any PL/SQL code, you then just need to pass in the wallet parameters - wallet directory, and wallet password (the wallet path parameter is used with the syntax "file:path", where path is the actual path the wallet directory). I normally do this with the set_wallet procedure, but the two properties are parameters on the functions/procedures to execute http requests (e.g. utl_http.begin_request).

Confirm the wallet is working with the following:

 req utl_http.req;
 resp utl_http.resp;
 rw varchar2(32767);

 utl_http.set_wallet('file:<path-to-wallet-directory>', '<wallet-password>');

 req := utl_http.begin_request( '<secure-url>');
 resp := utl_http.get_response(req);

   rw := null; 
   utl_http.read_line(resp, rw, TRUE); 
   --do something with rw if you like
   exception when others 
 end loop; 


Wednesday, 30 May 2012

Oracle HTTP Requests: Set up the ACL

In order to use utl_http requests from Oracle 11g onward (or any network services for that matter), you need to set up an ACL. Two generic permissions to grant are connect and resolve, where * can be used as a wild card.

A good template I commonly use, just replacing the file name and schema name. This should be executed as a user with dba privileges.


 l_filename varchar2(200) := 'file_test_http.xml';
 l_schema varchar2(200) := 'FILE_TEST';


                acl => l_filename

      NULL; -- ACL does not exist yet

              acl           => l_filename
            , description   => 'All requests to test utl_http'
            , principal     => l_schema -- schema name
            , is_grant      => TRUE
            , privilege     => 'connect'

              acl       => l_filename
            , principal => l_schema -- schema name
            , is_grant  => TRUE
            , privilege => 'resolve'

              acl   =>l_filename
            , host  => '*'

End User's Guide

Just noticed this on the documentation index.

Apex now has an end users guide:

I'm not yet certain it's a good idea linking users to this documentation in any user guides you create for your apps, given Oracle seems to like changing the structure of their links every couple of years, leaving documentation links that no longer work. Even so, it's worth knowing :-)

Current sections include: