Adding Web Service Trusted Certificates to a Wallet in Oracle Database Cloud Service

Introduction

This post documents how to add trusted TLS/SSL certificates to an Oracle Database as a Service (DBaaS) wallet.

It uses an example of an Oracle Business Intelligence Cloud Service (BICS) REST API call to Delete Cached Data as documented in REST APIs for Oracle BI Cloud Service. This call is issued from PL/SQL and requires that the trusted certificates from the BICS web service exist in the DBaaS wallet.

Two methods are detailed: A GUI method that uses the Oracle Wallet Manager application and a command-line method that uses the orapki utility.

The following topics are covered:

Accessing DBaaS using SSH

Testing with the APEX_WEB_SERVICE Package

Extracting Trusted Certificates using a Browser

Importing Trusted Certificates into a Wallet

Using API Testing Tools

Using API tools to initially test web service calls is a routine best practice.

Note: API testing tools such as SoapUI, CuriousConcept, Postman, and so on are third-party tools for using SOAP and REST services. Oracle does not provide support for these tools or recommend a particular tool for its APIs. You can select the tool based on your requirements.

Below are links for cURL and Postman:

Postman API Tool

cURL Download Site

cURL Online

Note: a Base64 encoding utility may be found here: Base64 Decoding and Encoding Testing Tool Use this utility to encode a username:password pair.

The following is the cURL command for the Oracle BICS REST API call:

curl -i -k -X DELETE https://<trial-id>-<identity-domain>.analytics.us2.oraclecloud.com/bimodeler/api/v1/dbcache -H “authorization: Basic <Base64 encoded username:password>” -H “x-id-tenant-name: <identity domain>”

Note: If using Linux replace the double quote (“) with a single quotes (‘).

Accessing DBaaS using SSH

To gain access to the tools, utilities and other resources on a compute node associated with an Oracle Database Cloud Service e.g. DBaaS, you use Secure Shell (SSH) client software to establish a secure connection and log in as the user oracle.

This post uses PuTTY as the SSH client. It can be downloaded here: PuTTY Download Note: ensure that these associated utilities are also downloaded: PuTTYgen and PSCP.

Obtain the following before creating a connection:

The IP address of the compute node (host)

Note: The IP address of the host associated with the DBaaS is listed on the Oracle Database Cloud Service Overview page. See Viewing Detailed Information for a Database Deployment

The SSH private key file that matches the public key associated with the deployment.

Creating a PuTTY Connection

Creating a PuTTY connection is documented in Connecting to a Compute Node Through Secure Shell (SSH).

After creating the PuTTY connection it looks like this:

1

And

2

Determining if the GUI method is available

The GUI method requires that vnc software is available. Note: the installation of vnc software is not covered in this post. If it is not available, only the command-line method can be used.

Start a PuTTY session as the oracle user.

Run the vncserver command:

$ vncserver

If you receive an error, than the vnc software is not available and the command-line method must be used.

If successful, note the resulting service number from the response: (4 is the service number in this response).

New ‘hostname:4 (oracle)’ desktop is hostname:4
Starting applications specified in /home/oracle/.vnc/xstartup
Log file is /home/oracle/.vnc/ hostname:4.log

Creating a PuTTY SSH Tunnel

If the GUI method is desired and the vnc software is available, create a SSH tunnel in the PuTTY connection for running a vncviewer client. This post uses TigerVNC which is found here: TigerVNC Download

The tunnel looks like this (assuming the server port is 3)

3

Creating a SQL*Developer Connection

This post uses SQL*Developer to create and run the APEX_WEB_SERVICE package. Creating a SQL*Developer SSH connection is documented in Connecting Remotely to the Database by Using Oracle SQL Developer in Using Oracle Database Cloud Service.

Creating a SQL*Developer connection requires the following:

The Listener Port e.g. 1521

The SID or Service Name

The Schema Name that stores the Web Service call

The Schema password

An OpenSSH private key

Note: PuTTY has the PuTTYgen utility to create an OpenSSH private key from the standard private key associated with the DBaaS.

Run the PuTTYgen utility and click Load

4

Select your private key and enter the passphrase if necessary and see this:

5

Create a text file and copy/paste the entire OpenSSH key text. Save the file.

In SQL*Developer, create a new SSH Host using the DBaaS IP address and the OpenSSH private key file:

6

Create a new Local Port Forward for this host using the DBaaS listener port:

7

Create a new connection using the Local Port Forward, Schema Name, Schema Password and the Service Name:

8

Testing with the APEX_WEB_SERVICE Package

The APEX_WEB_SERVICE package is documented in the APEX_WEB_SERVICE Reference Guide.

Connect to the DBaaS using SQL*Developer.

Note: a Base64 encoding utility may be found here: Base64 Decoding and Encoding Testing Tool Use the utility to encode the username:password pair.

The following script runs the APEX_WEB_SERVICE.make_rest_request procedure and displays the status. The complete text may be found here.

SET serveroutput ON
DECLARE
p_status VARCHAR2(500);
l_ws_response_clob CLOB;
l_ws_url VARCHAR2(500) := ‘https://yourTrialID-yourIdentiryDomain.analytics.us2.oraclecloud.com/bimodeler/api/v1/dbcache’;
BEGIN
apex_web_service.g_request_headers(1).NAME := ‘X-ID-TENANT-NAME’;
apex_web_service.g_request_headers(1).VALUE := ‘yourIdentiryDomain’;     apex_web_service.g_request_headers(2).NAME := ‘authorization’;
apex_web_service.g_request_headers(2).VALUE := ‘Basic ZGF5bmUuJibberishJibberishGEwNjA1ODUh’;
l_ws_response_clob :=
apex_web_service.make_rest_request
(
p_url => l_ws_url,
p_http_method => ‘DELETE’
–,p_wallet_path => ‘file:/u01/app/oracle/product/11.2.0/dbhome_1/owm/wallets/oracle’
–,p_wallet_pwd => ‘Yourpass!’
);                                                                                                         p_status := apex_web_service.g_status_code;
dbms_output.put_line(‘Status:’ || dbms_lob.substr(p_status));
END;
/

When the procedure returns a Status:200 then the wallet contains the appropriate certificate.

When it returns ORA-29024: Certificate validation failure then the certificate(s) must be obtained and added to the wallet.

Extracting Trusted Certificates Using a Browser

The certificates associated with a Web Service need to be extracted and saved in files. This is best done using a browser. Note: Firefox is used here. Other browsers such as Chrome and IE may be used and their procedures for exporting certificates differ.

GUI Method

Start a PuTTY session as the oracle user to enable the associated SSH tunnel.

Start a vncviewer client session and connect using the SSH tunnel created with the DBaaS PuTTY connection. Enter the passphrase associated with the private key if prompted.

9

Click on the Firefox icon to start a browser session.

10

Command-Line Method

Start a Firefox browser.

Both Methods

Enter the URL for a page provided by the Web Service. This example uses the BICS home page after logging in: https://<trial ID>-<identity domain>.analytics.us2.oraclecloud.com/analytics

11

Click on the green lock box circled above, then secure connection>, then more information, then view certificate, then Details.

12

In most cases the lowest level certificate is not needed. In some cases, the highest level certificate is present by default.

For each of the top certificates, select/highlight the certificate and click Export. Choose a location and a type and click Save. The default format, PEM, is fine.

Importing Trusted Certificates into a Wallet

GUI Method Using Oracle Wallet Manager

Using Oracle’s Wallet Manager is documented here:

11g: Using Oracle Wallet Manager in the Database Advanced Security Administrator’s Guide

12c: Using Oracle Wallet Manager in the Database Enterprise User Security Administrator’s Guide

Opening / Creating a Wallet

Start a PuTTY session as the oracle user to enable the associated SSH tunnel.

Start a vncviewer client session and connect using the SSH tunnel created with the DBaaS PuTTY connection. Enter the passphrase associated with the private key if prompted.

Open a terminal window:

From the Applications menu, select System Tools > Terminal.

Start Oracle Wallet Manager with the owm command:

$ owm

From the Wallet menu, click Open. If you are alerted that the default directory does not exist, click No.

From the Wallet menu, click New. If you are alerted that the default directory does not exist, click Yes to create it.

Enter a wallet password, accept the default type Standard and click OK.

When prompted to create a certificate request, click No.

Importing the Certificates

From the Operations menu, click Import Trusted Certificate.

Choose Select a file that contains the certificate and click OK.

Select the file containing the highest level certificate (in this example …G5) and click OK.

Repeat if necessary.

From the Operations menu, click Import Trusted Certificate.

Choose Select a file that contains the certificate and click OK.

Select the file containing the next highest level certificate (in this example …G4) and click OK.

From the Wallet menu, click Save.

Verifying the Certificates

 From the Wallet menu, click Open.

Click OK for the default directory or change the directory.

Enter the wallet password if prompted.

Results should be like this:

14

From the Wallet menu, click Exit.

Command Line Method Using ORAPKI

This method requires the exported certificate files be copied to the DBaaS host.

A text file of the Linux commands used may be viewed here.

Documentation for orapki is here:

11g: Managing Oracle Wallets with orapki Utility

12c: Managing Oracle Wallets with the orapki Utility

Copying the Certificate Files

This post uses Secure Copy (SCP) to copy the files. The PuTTY PSCP utility is used.

Issue this command for the first certificate. It copies the file to the oracle user’s home directory.

$ pscp -i <path/file of the DBaaS private key file>  <path/file of the Certificate file> oracle@<DbaaS IP Address>:/home/oracle
For example:
$ pscp -i C:\TMP\PRIVATEKEY.PPK C:\TMP\certg5.cer oracle@129.144.28.999:/home/oracle

Repeat the command for additional certificates changing the file path of the additional certificates.

Creating a Wallet

Issue this command to determine if a wallet exists in the default location. Note: Oracle defines the default location as $ORACLE_HOME/owm/wallets/oracle

$ orapki wallet display -wallet <wallet directory>
For example:
$ orapki wallet display -wallet $ORACLE_HOME/owm/wallets/oracle

If the wallet exists, you need the wallet’s password for the next steps. If you have the wallet password then a new wallet does not need to be created.

Note: If you don’t have the wallet’s password, create the wallet in a custom location.

Issue this command to create a wallet and enter a new password when prompted:

$ orapki wallet create -wallet <wallet directory> -auto_login
For example:
$ orapki wallet create -wallet $ORACLE_HOME/owm/wallets/oracle -auto_login

Importing the Certificates

Issue this command to import (add) a certificate:

$ orapki wallet add -wallet <wallet directory>  -trusted_cert -cert <certificate file path>  -pwd <wallet password>
For example:
$ orapki wallet add -wallet $ORACLE_HOME/owm/wallets/oracle -trusted_cert -cert /home/oracle/certg5.cer -pwd Yourpass!

Repeat the command for additional certificates changing the file path of the additional certificates.

Verifying the Certificates

Issue this command again to display the contents.

$ orapki wallet display -wallet <wallet directory>
For example:
$ orapki wallet display -wallet $ORACLE_HOME/owm/wallets/oracle

Results should be like this:

15

Re-Testing with the APEX_WEB_SERVICE package

Retest using the two additional parameters:

,p_wallet_path => ‘file:<wallet directory>’

,p_wallet_pwd => ‘<wallet-password>

For example:

SET serveroutput ON
DECLARE
p_status VARCHAR2(500);
l_ws_response_clob CLOB;
l_ws_url VARCHAR2(500) := ‘https://yourTrialID-yourIdentiryDomain.analytics.us2.oraclecloud.com/bimodeler/api/v1/dbcache’;
BEGIN
apex_web_service.g_request_headers(1).NAME := ‘X-ID-TENANT-NAME’;
apex_web_service.g_request_headers(1).VALUE := ‘yourIdentiryDomain’; apex_web_service.g_request_headers(2).NAME := ‘authorization’;
apex_web_service.g_request_headers(2).VALUE := ‘Basic ZGF5bmUuJibberishJibberishGEwNjA1ODUh’;
l_ws_response_clob :=
apex_web_service.make_rest_request
(
p_url => l_ws_url,
p_http_method => ‘DELETE’
–,p_wallet_path => ‘file:/u01/app/oracle/product/11.2.0/dbhome_1/owm/wallets/oracle’
–,p_wallet_pwd => ‘Yourpass!’
); p_status := apex_web_service.g_status_code;
dbms_output.put_line(‘Status:’ || dbms_lob.substr(p_status));
END;
/

A successful test returns:

PL/SQL procedure successfully completed.

Status:200

Summary

This post documented how to add trusted TLS/SSL certificates to an Oracle Database as a Service (DBaaS) wallet. It used an example of an Oracle Business Intelligence Cloud Service (BICS) REST API call to Delete Cached Data.

A DBaaS wallet was created. BICS Certificates were extracted using a browser and imported into the wallet.

Both GUI and command-line methods were described.

For more OAC, BICS and BI best practices, tips, tricks, and guidance that the A-Team members gain from real-world experiences working with customers and partners, visit Oracle A-Team Chronicles for BICS and Oracle A-Team Chronicles for OAC

 

References

REST APIs for Oracle BI Cloud Service

Postman API Tool

cURL Download Site

cURL Online

Base64 Decoding and Encoding Testing Tool

PuTTY Download

Viewing Detailed Information for a Database Deployment

Connecting to a Compute Node Through Secure Shell (SSH)

TigerVNC Download

Connecting Remotely to the Database by Using Oracle SQL Developer in Using Oracle Database Cloud Service

APEX_WEB_SERVICE Reference Guide

11g: Using Oracle Wallet Manager in the Database Advanced Security Administrator’s Guide

12c: Using Oracle Wallet Manager in the Database Enterprise User Security Administrator’s Guide

11g: Managing Oracle Wallets with orapki Utility

12c: Managing Oracle Wallets with the orapki Utility

Oracle A-Team Chronicles for BICS

Oracle A-Team Chronicles for OAC

 

 

Add Your Comment