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
Create User
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.
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.
Create and Config Oracle Wallet
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.
Test
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 » Feed" href= "https://www.tain.com/feed/" /> <link rel="alternate" type="application/rss+xml" title="Tain » 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>
Disable
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; /