DB: Configuring the wallet for outbound https calls

Before making https calls from the database, a wallet has to be created and an access security list must be created to allow outbound https connection.

In addition, the root or intermediate certificates of the website being called must be stored into the wallet. With 12c, only the INTERMEDIATE certificate should be stored into the wallet. A good start to find the latest certificate is here; checkout also this tip to extract the certificate being used by a server.

Also, a database access list has to be created to grant the schema specified the privilege to access the host via the http call. When a proxy is used as a firewall, this access also must be part of this access list.

Setup the wallet

Here we create a wallet in the $ORACLE_HOME/wallet/<db> directory.

export LWALLET_DIR=$ORACLE_HOME/wallet/<db>

mkdir -p ${LWALLET_DIR}

orapki wallet create -wallet ${LWALLET_DIR} -pwd welcome1 -auto_login

# Example for www.oracle.com
orapki wallet add -wallet ${LWALLET_DIR} -trusted_cert -cert "GeoTrustSSLCA-G3.crt" -pwd welcome1
# Example for cloud.demo.com
orapki wallet add -wallet ${LWALLET_DIR} -trusted_cert -cert "SymantecClass3SecureServerCA-G4.crt" -pwd welcome1
ls -l ${LWALLET_DIR}

orapki wallet display -wallet ${LWALLET_DIR} -complete

In case of RAC, make sure to replicate the wallet over other RAC nodes.

Create the access list

connect sys/<password> as sysdba

ALTER SESSION SET CONTAINER=<pdb>;
begin
dbms_network_acl_admin.append_host_ace
 (host => '*'
 ,lower_port => 80
 ,upper_port => 443
 ,ace => xs$ace_type(privilege_list => xs$name_list('http','http_proxy')
 ,principal_name => 'schema'
 ,principal_type => xs_acl.ptype_db));
dbms_network_acl_admin.append_wallet_ace
 (wallet_path => 'file:/path to ORACLE_HOME/wallet/<db>'
 ,ace => xs$ace_type(privilege_list => xs$name_list('use_client_certificates','use_passwords')
 ,principal_name => 'schema'
 ,principal_type => xs_acl.ptype_db));

commit;
end;
/

To check the successful deployment of the wallet and certificate

sqlplus <sysman>/<password>
SQL> execute utl_http.set_wallet('file:<path to wallet>', 'welcome1');
SQL> select utl_http.request ('http://www.oracle.com') from dual;

or with a proxy:

sqlplus <sysman>/<password>
SQL> execute utl_http.set_wallet('file:<path to wallet>', 'welcome1');
SQL> select utl_http.request ('http://www.oracle.com','http://<proxy>:80') from dual;