plsql_rest_api_call [My wiki]

Calling Restful API from outside the server is quite challanging in Oracle, especially from 11.2 First, you need to create an ACL to allow requests for outside, then you have to creata wallet to store the certificate and add all the certificate up to the chain to the root certificate to that wallet. So let's get going.

So let's get going :)

First we have to setup a user:

Create User

SQL> create user test identified by Welcome1;

User created.

SQL> grant connect,resource to test;

Grant succeeded.

Then we need to create an ACL which will allow us to make requests outside the server and assign it to that user.

Create ACL

begin
  dbms_network_acl_admin.create_acl (
    acl         => 'utl_http.xml',
    description => 'HTTP Access',
    principal   => 'TEST',
    is_grant    => TRUE,
    privilege   => 'connect',
    start_date  => null,
    end_date    => null);
 
  dbms_network_acl_admin.add_privilege (
    acl        => 'utl_http.xml',
    principal  => 'TEST',
    is_grant   => TRUE,
    privilege  => 'resolve',
    start_date => null,
    end_date   => null
  );
 
  dbms_network_acl_admin.assign_acl (
    acl        => 'utl_http.xml',
    host       => 'www.oracle.com',
    lower_port => 443,
    upper_port => 443
  );
end;
/

In our example, we want to make requests to Oracle.com via port 443.

Now, we need to create wallet and add the certificate, we can do all that using the following script:

Create and Config Oracle Wallet

# sequence to generate wallet
#
# tested with a single (1) chain certificate; 
# the process is equal where multiple chains certificates are involved: add all the chains
#

# define the server we like to use
myserverCN=www.oracle.com
myWalletLocation=$ORACLE_HOME/wallet/$myserverCN
myWalletPassword=Welcome1

# fetch server cer
openssl s_client -connect $myserverCN:443 -showcerts </dev/null &> /tmp/request

# extract chain cer
beginCer=$( grep -n 'BEGIN CERTIFICATE' /tmp/request |tail -1|awk -F: '{print $1}' )
endCer=$( grep -n 'END CERTIFICATE' /tmp/request |tail -1|awk -F: '{print $1}' )
cerLength=$[ $endCer - $beginCer + 1 ]
head -$endCer /tmp/request |tail -$cerLength > /tmp/chain.cer

# resolve root cer
rootIssuer=$( head -1 /tmp/request |awk -F'CN = ' '{print "CN="$2}' )
# our stored root certs
openssl crl2pkcs7 -nocrl -certfile /etc/pki/tls/certs/ca-bundle.crt | openssl pkcs7 -print_certs -text -noout|grep 'Issuer:' > /tmp/issuers.out
# our root cer is in the bundle the Xth certificate
nthIssuerInBundle=$( grep -n "$rootIssuer" /tmp/issuers.out | awk -F: '{print $1}' )
beginCer=$( grep -n  'BEGIN CERTIFICATE' /etc/pki/tls/certs/ca-bundle.crt | head -$nthIssuerInBundle |tail -1|awk -F: '{print $1}' )
endCer=$( grep -n  'END CERTIFICATE' /etc/pki/tls/certs/ca-bundle.crt | head -$nthIssuerInBundle |tail -1 |awk -F: '{print $1}' )
cerLength=$[ $endCer - $beginCer + 1 ]
head -$endCer /etc/pki/tls/certs/ca-bundle.crt |tail -$cerLength > /tmp/root.cer

# create the wallet
orapki wallet create -wallet $myWalletLocation -auto_login -pwd $myWalletPassword
# add the chain
orapki wallet add -wallet $myWalletLocation -trusted_cert -cert /tmp/chain.cer -pwd $myWalletPassword
# add the root
orapki wallet add -wallet $myWalletLocation -trusted_cert -cert /tmp/root.cer -pwd $myWalletPassword
# display the resulting wallet contents
orapki wallet display -wallet $myWalletLocation -pwd Welcome1

That will create us a wallet and it will import the certificate as trusted.

Now we can finally test all

Test

SQL> select utl_http.request(url => 'https://www.tain.com',
wallet_path => 'file:/app/oracle/product/12.2.0/dbhome_1/wallet/www.oracle.com',
wallet_password => 'Welcome1',
https_host => 'https://www.tain.com'
) resp from dual;  2    3    4    5

RESP
--------------------------------------------------------------------------------
<!DOCTYPE html>
<html lang="en-GB" class=" html_stretched responsive av-preloader-disabled av-de
fault-lightbox  html_header_top html_logo_left html_main_nav_header html_menu_ri
ght html_custom html_header_sticky html_header_shrinking html_mobile_menu_phone
html_disabled html_header_searchicon html_content_align_center html_header_unsti
ck_top_disabled html_header_stretch_disabled html_entry_id_2046 ">
<head>
<meta charset="UTF-8" />

<!-- page title, displayed in your browser bar -->
<title>Tain | Online Gaming Platforms</title>

RESP
--------------------------------------------------------------------------------

<!-- ALL GLORY TO THE HYPNOTOAD! -->

<meta name="robots" content="index, follow" />
<link rel="icon" href="https://www.tain.com/wp-content/uploads/2015/12/favicon.i
co" type="image/x-icon">

<!-- mobile setting -->
<meta name="viewport" content="width=device-width, initial-scale=1, maximum-scal
e=1">


RESP
--------------------------------------------------------------------------------
<!-- Scripts/CSS and wp_head hook -->
<!-- Typekit -->
<!--
<script src="https://use.typekit.net/rsb8abb.js"></script>
<script>try{Typekit.load({ async: true });}catch(e){}</script>
-->
<link href='https://fonts.googleapis.com/css?family=Oswald:400,700' rel='stylesh
eet' type='text/css'>
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/font-awesome/4.5.0/
css/font-awesome.min.css">
<link rel="dns-prefetch" href="//connect.facebook.net" />

RESP
--------------------------------------------------------------------------------
<link rel='dns-prefetch' href='//connect.facebook.net' />
<link rel='dns-prefetch' href='//fonts.googleapis.com' />
<link rel='dns-prefetch' href='//s.w.org' />
<link rel="alternate" type="application/rss+xml" title="Tain &raquo; Feed" href=
"https://www.tain.com/feed/" />
<link rel="alternate" type="application/rss+xml" title="Tain &raquo; Comments Fe
ed" href="https://www.tain.com/comments/feed/" />

<!-- google webfont font replacement -->
<link rel='stylesheet' id='avia-google-webfont' href='//fonts.googleapis.com/css
?family=Open+Sans:400,600%7CBangers' type='text/css' media='all'/>

RESP
--------------------------------------------------------------------------------
                <script type="text/javascript">
                        window._wpemojiSettings = {"baseUrl":"https:\/\/s.w.org\/images\/core\/emoji\
/11.


SQL>

After that we can drop the ACL and thus preventing outgoing access:

Drop ACL

sqlplus / as sysdba

begin
  DBMS_NETWORK_ACL_ADMIN.DROP_ACL (
    acl         => 'utl_http.xml');
end;
/

Overview

Setup

User Creation

Create ACL

Create and Config Oracle Wallet

Test

Disable

My wiki