=====Overview===== 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 :) =====Setup===== First we have to setup a user: ====User Creation==== SQL> create user test identified by Welcome1; User created. SQL> grant connect,resource to test; Grant succeeded. ====Create ACL==== Then we need to create an ACL which will allow us to make requests outside the server and assign it to that user. 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. ====Create and Config Oracle Wallet==== Now, we need to create wallet and add the certificate, we can do all that using the following script: # 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 /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. ====Test==== Now we can finally test all 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 -------------------------------------------------------------------------------- Tain | Online Gaming Platforms RESP -------------------------------------------------------------------------------- RESP -------------------------------------------------------------------------------- RESP -------------------------------------------------------------------------------- RESP --------------------------------------------------------------------------------