Before you can connect to a website you need to setup the oracle wallet. Start by going to the website you are going to be connecting to. In this case I am just using my own site. I am also using Safari on OSX, the steps differ slightly depending on operating system and browser.
After clicking the padlock icon, and show certificate, we represented with the certificate chain. The root certificate is trusted by the browser, which then is used to validate the intermediary certificate which belongs to the encryption authority, in this case it’s Let’s Encrypt. Lets Encrypt in turn validates the certificate for my site. When oracle tries to connect to balddba.com it will need the intermediary and the root certificate to validate the server we are connecting to.
When I download the certificate we need to convert it to base-64 encoding so that we can handle it like a text file.
Convert the certificates to base-64
amyers$ openssl x509 -inform DER -in root.cer > root.crt
amyers$ openssl x509 -inform DER -in intermediate.cer > intermediate.crt
On the database server, create a folder to hold the wallet. Since I am doing this on the oracle cloud, and I only have one database SID, and the database is not RAC, I am not worrying about putting the wallet into a dynamic location. If you want to have a wallet per sid, you can use the $QNAME variable in the sqlnet.ora
[oracle@testdb db_wallet]$ pwd
/u01/app/oracle/admin/ORCL/db_wallet
[oracle@testdb db_wallet]$ ls -tlr
total 0
Modify the sqlnet.ora to point to the new wallet directory
WALLET_LOCATION = (SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=/u01/app/oracle/admin/ORCL/db_wallet)))
Create a new auto login wallet using orapki
orapki wallet create -wallet /u01/app/oracle/admin/ORCL/db_wallet -pwd WallPass2 -auto_login
Add the root and intermediate certificates to the wallet
[oracle@testdb certs]$ cd /home/oracle/certs
[oracle@testdb certs]$ ls -tlr
total 8
-rw-r--r-- 1 oracle oinstall 1200 Dec 4 16:53 root.crt
-rw-r--r-- 1 oracle oinstall 1648 Dec 4 16:53 intermediate.crt
[oracle@testdb certs]$ orapki wallet add -wallet /u01/app/oracle/admin/ORCL/db_wallet -trusted_cert -cert "/home/oracle/certs/root.crt" -pwd WallPass2
Oracle PKI Tool : Version 12.2.0.1.0
Copyright (c) 2004, 2016, Oracle and/or its affiliates. All rights reserved.
Operation is successfully completed.
[oracle@testdb certs]$ orapki wallet add -wallet /u01/app/oracle/admin/ORCL/db_wallet -trusted_cert -cert "/home/oracle/certs/intermediate.crt" -pwd WallPass2
Oracle PKI Tool : Version 12.2.0.1.0
Copyright (c) 2004, 2016, Oracle and/or its affiliates. All rights reserved.
Operation is successfully completed.
Then validate the certificates are in the wallet
[oracle@testdb db_wallet]$ orapki wallet display -wallet /u01/app/oracle/admin/ORCL/db_wallet
Oracle PKI Tool : Version 12.2.0.1.0
Copyright (c) 2004, 2016, Oracle and/or its affiliates. All rights reserved.
Requested Certificates:
User Certificates:
Trusted Certificates:
Subject: CN=Let's Encrypt Authority X3,O=Let's Encrypt,C=US
Subject: CN=DST Root CA X3,O=Digital Signature Trust Co.
Now that the wallet is setup with our certificates,
I am starting off by creating myself a new user, so that I am starting from scratch and not using the dba privileges.
SQL> create user amyers identified by MyPassword
2 default tablespace users
3 temporary tablespace temp
4 /
User created.
Give the user access to connect and to use the url_http package
SQL> grant connect to amyers;
Grant succeeded.
SQL> grant resource to amyers;
Grant succeeded.
SQL> grant execute on utl_http to amyers;
Grant succeeded.
We create a new ACL for the host, and for the wallet
begin
DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
host => 'www.balddba.com',
ace => xs$ace_type(privilege_list => xs$name_list('connect', 'resolve'),principal_name => 'AMYERS',principal_type => xs_acl.ptype_db));
end;
/
begin
dbms_network_acl_admin.append_wallet_ace(
wallet_path => 'file:/u01/app/oracle/admin/ORCL/db_wallet',
ace => xs$ace_type(privilege_list => xs$name_list('use_client_certificates'),principal_name => 'AMYERS',principal_type => xs_acl.ptype_db));
end;
/
Now we can test the connection
SQL> select utl_http.request('https://www.balddba.com',null,'file:/u01/app/oracle/admin/ORCL/db_wallet','WallPass2','balddba.com') from dual;
UTL_HTTP.REQUEST('HTTPS://WWW.BALDDBA.COM',NULL,'FILE:/U01/APP/ORACLE/ADMIN/ORCL
--------------------------------------------------------------------------------
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/x
{more html output}
There is something I think should be pointed out, there is a change from 12.1 to 12.2. utl_http.request takes in a new argument called https_host.
I was getting the following error
SQL> select utl_http.request('https://www.balddba.com',null,'file:/u01/app/oracle/admin/ORCL/db_wallet','WallPass2') from dual;
select utl_http.request('https://www.balddba.com',null,'file:/u01/app/oracle/admin/ORCL/db_wallet','WallPass2') from dual
*
ERROR at line 1:
ORA-29273: HTTP request failed
ORA-06512: at "SYS.UTL_HTTP", line 1501
ORA-24263: Certificate of the remote server does not match the target address.
ORA-06512: at "SYS.UTL_HTTP", line 380
ORA-06512: at "SYS.UTL_HTTP", line 1441
ORA-06512: at line 1
There is now another parameter that needs to be set, the https_host has to match the common name in the certificate.
LEN BINARY_INTEGER IN DEFAULT
FUNCTION REQUEST RETURNS VARCHAR2
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
URL VARCHAR2 IN
PROXY VARCHAR2 IN DEFAULT
WALLET_PATH VARCHAR2 IN DEFAULT
WALLET_PASSWORD VARCHAR2 IN DEFAULT
HTTPS_HOST VARCHAR2 IN DEFAULT
Here is my certificate
So after adding the arguemnt I am able to get to the page
SQL> select utl_http.request('https://www.balddba.com',null,'file:/u01/app/oracle/admin/ORCL/db_wallet','WallPass2','balddba.com') from dual;
UTL_HTTP.REQUEST('HTTPS://WWW.BALDDBA.COM',NULL,'FILE:/U01/APP/ORACLE/ADMIN/ORCL
--------------------------------------------------------------------------------
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/x
html1/DTD/xhtml1-strict.dtd">