ADWC – connect from your premises

By Franck Pachot

.
In the previous post about the Autonomous Data Warehouse Service, I’ve run queries though the Machine Learning Notebooks. But you obviously want to connect to it from your premises, with SQL*Net.

Of course the connection, going through the public internet, must be secured. If you already use a managed service like the Oracle Exadata Express Cloud Service, you already know how to do: download a .zip containing the connection string and the wallet and certificate for SQL*Net encryption.

You get it from the Service Console, logged as the ADMIN user, and Administration tab. The Download Client Credentials asks you for the wallet password. However, this is not a password to protect the .zip file and the .zip file contains an auto-login wallet, so keep it secured.

SQL Developer

The simplest use of this file is with SQL Developer because you don’t even have to unzip it. Just choose a ‘Cloud PDB’ connection type, enter the path of the .zip file as Configuration File, the password as Keystore Password and the ADMIN user (or any user you have created with the Oracle ML Users).

In the tnsnames.ora provided in the .zip file there are 3 network service names connecting to 3 different services: _low, _medium and _high. They map to the resource manager plan so that you can run your queries with different priorities.

SQLcl thin

With SQLcl you do not need to unzip the credentials file, at least when you are using thin JDBC (the default).
You just register it with:

18:53:12 SQL> set cloudconfig /media/sf_share/ADWC/wallet_ADWC.zip
Using temp directory:/tmp/oracle_cloud_config4174171941677611695

and you are ready to connect to the _low, _medium and _high services.

As you see, it unzips the file into a temporary directory so you have to do it each time you run SQLcl. You can add this to login.sql and may add some housekeeping as this temporary directory may remain. Or run all this in a docker container.

This is simple, at least if you are running the latest Java 8 which includes the Java Cryptography Extension (JCE). If it is not the case, as when you use the Java Home provided with 18c (1.8.0_152), you have to add the jars yourself. But don’t worry, all is explained:

SQL> set cloudconfig /media/sf_share/ADWC/wallet_ADWC.zip
***** JCE NOT INSTALLED ****
***** CAN NOT CONNECT TO PDB Service without it ****
  Current Java: /u01/app/oracle/product/18.0.0/dbhome_1/jdk/jre
 Follow instructions on  http://www.oracle.com/technetwork/java/javase/downloads/jce8-download-2133166.html
SQL>

Of course the alternative is to install the latest Java

$ sudo  rpm -i jre-8u171-linux-x64.rpm
Unpacking JAR files...
        plugin.jar...
        javaws.jar...
        deploy.jar...
        rt.jar...
        jsse.jar...
        charsets.jar...
        localedata.jar...

and set JAVA_HOME to it before starting SQLcl

$ export JAVA_HOME=/usr/java/jre1.8.0_171-amd64
$ SQLPATH=~/sql bash $ORACLE_HOME/sqldeveloper/sqlcl/bin/sql /nolog

Anyway, in all cases, once the credential .zip is provided you can connect with Thin JDBC (the default) with a user/password that has been created in the ADWC:

SQL> connect admin@adwc_high
Password? (**********?) ****************
AArray = [B@24959ca4
AArray = [B@10289886
AArray = [B@32115b28
AArray = [B@2ad48653
Connected.
SQL> select * from v$instance;
  INSTANCE_NUMBER INSTANCE_NAME   HOST_NAME   VERSION      STARTUP_TIME   STATUS   PARALLEL     THREAD# ARCHIVER   LOG_SWITCH_WAIT   LOGINS    SHUTDOWN_PENDING   DATABASE_STATUS   INSTANCE_ROLE      ACTIVE_STATE   BLOCKED     CON_ID INSTANCE_MODE   EDITION   FAMILY   DATABASE_TYPE
  --------------- -------------   ---------   -------      ------------   ------   --------     ------- --------   ---------------   ------    ----------------   ---------------   -------------      ------------   -------     ------ -------------   -------   ------   -------------
                6 esj1pod6                    12.2.0.1.0   13-MAY-18      OPEN     YES                6 STARTED                      ALLOWED   NO                 ACTIVE            PRIMARY_INSTANCE   NORMAL         NO               0 REGULAR         EE                 RAC

What’s in the .zip

The tnsnames.ora has entries for the low, medium, high services.

adwc_high = (description= (address=(protocol=tcps)(port=1522)(host=adwc.uscom-east-1.oraclecloud.com))(connect_data=(service_name=p7zyfbmcnl4kjy3_adwc_high.adwc.oraclecloud.com))(security=(ssl_server_cert_dn="CN=adwc.uscom-east-1.oraclecloud.com,OU=Oracle BMCS US,O=Oracle Corporation,L=Redwood City,ST=California,C=US"))   )
 
adwc_low = (description= (address=(protocol=tcps)(port=1522)(host=adwc.uscom-east-1.oraclecloud.com))(connect_data=(service_name=p7zyfbmcnl4kjy3_adwc_low.adwc.oraclecloud.com))(security=(ssl_server_cert_dn="CN=adwc.uscom-east-1.oraclecloud.com,OU=Oracle BMCS US,O=Oracle Corporation,L=Redwood City,ST=California,C=US"))   )
 
adwc_medium = (description= (address=(protocol=tcps)(port=1522)(host=adwc.uscom-east-1.oraclecloud.com))(connect_data=(service_name=p7zyfbmcnl4kjy3_adwc_medium.adwc.oraclecloud.com))(security=(ssl_server_cert_dn="CN=adwc.uscom-east-1.oraclecloud.com,OU=Oracle BMCS US,O=Oracle Corporation,L=Redwood City,ST=California,C=US"))   )

The sqlnet.ora mentions the wallet used for SQL*Net encryption:

WALLET_LOCATION = (SOURCE = (METHOD = file) (METHOD_DATA = (DIRECTORY="?/network/admin")))
SSL_SERVER_DN_MATCH=yes

Note that the directory is an absolute path and you probably want to change it to your TNS_ADMIN one where you unzip the file.

In the wallet location, you find the ewallet.p12 that contain the certificate and private keys, protected with password, and the cwallet.sso which do not need to provide the password to open it, so protect them with file permissions.

You find also keystore.jks which also contains the Self-signed certificate but in JKS truststore format. and referenced from ojdbc.properties properties:

oracle.net.wallet_location=(SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=${TNS_ADMIN})))

Once you have unzipped the credentials, you can use them to connect with OCI.

SQL*Plus or SQLcl -oci

If you want to connect with an OCI client, you have to unzip this file to your TNS_ADMIN directory. That can be the $ORACLE_HOME/network/admin, but be careful to overwrite existing files, or it can be a new directory you will use by setting the TNS_ADMIN environment variable (or registry entry) to it.

Here are some examples where I set TNS_ADMIN to the directory where I unzipped the credentials:

TNS_ADMIN=/media/sf_share/ADWC/wallet_ADWC sqlcl -oci /nolog
TNS_ADMIN=/media/sf_share/ADWC/wallet_ADWC sqlplus /nolog

Any application using OCI (the oracle client, which can be the InstantClient or a full database installation) can use this without providing any password.

Databas Link

With the unzipped credentials you can access through OCI which means that you can also have a database link to the ADWC database. The credentials must be unzipped (or merged) in the TNS_ADMIN (or default ?/rnetwork/admin) of the instance:

SQL> create database link ADWC connect to ADMIN identified by "Ach1z0#dAch1z0#d" using 'adwc_high';
Database link created.
 
SQL> select banner from v$version@ADWC;
 
BANNER
--------------------------------------------------------------------------------
Oracle Database 18c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

Client Credential password

When downloading the .zip you are asked for a password to ‘protect this file to prevent unauthorized database access‘. But that is misleading. The .zip is not password protected. Anyone can open it. And it contains an auto-login wallet, so anybody can use it. You can access the database without this password. Of course, you cannot connect if you don’t have a user/password with a create session privileges, but you access to it for user credentials verification.

So what is this password used for? We have seen that SQL Developer needs the password (or you will get a files as java.io.IOException: Keystore was tampered with, or password was incorrect). Then, you may remove the .sso auto-login wallet from the .zip file when it is used only by SQL Developer. But of course, you have to think about where the password is stored in SQL Developer. Is is more secured than the .sso ?

As long as the auto-login wallet is there, you do not need to store the wallet password. But of course, you will protect credential files.