Easy Oracle Cloud wallet location in the JDBC connection string

I wrote about the 19c easy-connect string recently and the possibility to use a wallet with it (and no need for a tnsnames.ora then):

19c EZCONNECT and Wallet (Easy Connect and External Password File)

That was with sqlplus and setting TNS_ADMIN and still requires sqlnet.ora to set the wallet location directory. This post adds two things:

• TNS_NAMES parameter in the JDBC URL with no need for
  the java -Doracle.net.tns_admin
• We can add our password to the cloud wallet downloaded from the Autonomous Database (ATP/ADW)

Oracle Cloud user

For this test I’ve created a new user in my Autonomous Transaction Processing cloud service.

The click-path is:

• Autonomous Database
• Autonomous Database Details
• Service Console
• Administration
• Manage ML Users (yes, this is the Machine Learning interface)
• Create User

I’ve created the user Franck with password T1s1s@UserPassword (with an at-sign on purpose to show you that it is annoying but manageable).

A side note here because this is the most hidden part of these autonomous services. You are in the Machine Learning interface and if you click on the Home button on top right you can log with this user and access a Zeppelin Notebook to run some SQL statements.

I’m opening one ‘SQL Script Scratchpad’ here to see my roles. Actually, a user created from this Oracle ML interface is a developer user with the role ‘OML_DEVELOPER’ and the role ‘DWROLE’ (even it this is ATP and not ADW). This differs from the ADMIN user which has many more roles.

You can create the same user from the sql command line or SQL Developer connected as ADMIN. But, for this, we need to the credentials wallet first.

Oracle Cloud wallet

I will connect with this user from my on-premises (aka laptop;) and then I need to download the credential wallet which contains everything I need to connect to the service remotely.

The web path is:

• Autonomous Database
• Autonomous Database Details
• Service Console
• Download Client Credentials (Wallet)

I enter a password for this wallet: T1s1s@WalletPassword and get a .zip file. This is not a password-protected ZIP. The password is the wallet password. Note that this is common to all my compartment database services and I’ll see all of them in the tnsnames.ora. There’s one wallet per compartment. Finer security is done by each database with user authentication. But the wallet has the name of the service you downloaded from

Oracle JDBC client

I unzip the wallet:

[oracle@db193]$ unzip -d /home/oracle/mywallet /tmp/wallet_MYATP.zip
Archive:  /tmp/wallet_MYATP.zip
  inflating: /home/oracle/mywallet/cwallet.sso
  inflating: /home/oracle/mywallet/tnsnames.ora
  inflating: /home/oracle/mywallet/truststore.jks
  inflating: /home/oracle/mywallet/ojdbc.properties
  inflating: /home/oracle/mywallet/sqlnet.ora
  inflating: /home/oracle/mywallet/ewallet.p12
  inflating: /home/oracle/mywallet/keystore.jks

It contains 3 entries for each of my database services, with low/medium/high alternatives for resource management.

Here I’m using myatp_low:

[oracle@db193]$ grep myatp /home/oracle/mywallet/tnsnames.ora
myatp_low = (description= (address=(protocol=tcps)(port=1522)(host=adb.eu-frankfurt-1.oraclecloud.com))(connect_data=(service_name=vavxrlxx2llql7m_myatp_low.atp.oraclecloud.com))(security=(ssl_server_cert_dn="CN=adwc.eucom-central-1.oraclecloud.com,OU=Oracle BMCS FRANKFURT,O=Oracle Corporation,L=Redwood City,ST=California,C=US"))   )

If I specify only the TNS_ADMIN, the tnsnames.ora is found but not the wallet (because the directory in sqlnet.ora is not setup with the wallet location):

[oracle@db193]$ TNS_ADMIN=/home/oracle/mywallet tnsping myatp_low
TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 08-MAY-2019 20:21:20
Copyright (c) 1997, 2019, Oracle.  All rights reserved.
Used parameter files:
/home/oracle/mywallet/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (description= (address=(protocol=tcps)(port=1522)(host=adb.eu-frankfurt-1.oraclecloud.com))(connect_data=(service_name=vavxrlxx2llql7m_myatp_low.atp.oraclecloud.com))(security=(ssl_server_cert_dn= CN=adwc.eucom-central-1.oraclecloud.com,OU=Oracle BMCS FRANKFURT,O=Oracle Corporation,L=Redwood City,ST=California,C=US)))
TNS-12560: TNS:protocol adapter error

jdbc:oracle:thin:…?TNS_ADMIN=…

I don’t care about sqlnet.ora here and I’ll use SQLcl with the thin JDBC connection. And since 18.3 the driver supports a TNS_ADMIN parameter in the URL to mention the TNS_ADMIN. And this one is used to find the tnsnames.ora but also the credential files:

[oracle@db193]$ sql Franck/'"T1s1s@UserPassword"'@myatp_low?TNS_ADMIN=/home/oracle/mywallet
SQLcl: Release 19.1 Production on Wed May 08 20:23:38 2019
Copyright (c) 1982, 2019, Oracle.  All rights reserved.
Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.4.0.0.0
SQL> show user
USER is "FRANCK"
SQL> show jdbc-- Database Info --
Database Product Name: Oracle
Database Product Version: Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.4.0.0.0
Database Major Version: 18
Database Minor Version: 0
-- Driver Info --
Driver Name: Oracle JDBC driver
Driver Version: 19.3.0.0.0
Driver Major Version: 19
Driver Minor Version: 3
Driver URL: jdbc:oracle:thin:@myatp_low
Driver Location:
resource: oracle/jdbc/OracleDriver.class
jar: /u01/app/oracle/product/DB193/jdbc/lib/ojdbc8.jar
JarSize: 4210517
JarDate: Fri Apr 05 03:38:42 GMT 2019
resourceSize: 2604
resourceDate: Thu Apr 04 20:38:40 GMT 2019

This is awesome because in previous JDBC driver versions you had to set this by adding a parameter in Java, like -Doracle.net.tns_admin or OracleConnection.CONNECTION_PROPERTY_TNS_ADMIN (or SET CLOUDCONFIG in SQLcl).

The 18.3 version of the driver is available for JDK 8 (ojdbc8.jar) and JDK 10 (ojdbc10.jar):

JDBC and UCP Downloads page

Note: it seems that JDBC does not understand comma separated tnsnames.ora entries (convenient, but maybe not documented).

Passwordless connection

And there is more. You have an easy way to set the wallet location. You have a wallet. It is used to store the SSL/TLS certificate and key.

But you can use the same to store your passwords:

[oracle@db193]$ mkstore -wrl . -createCredential myatp_low Franck
Oracle Secret Store Tool Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
Copyright (c) 2004, 2019, Oracle and/or its affiliates. All rights reserved.
Your secret/Password is missing in the command line
Enter your secret/Password: T1s1s@UserPassword
Re-enter your secret/Password: T1s1s@UserPassword
Enter wallet password:

Important note: this is easy for passwordless connection, but then the protection of those files are critical. Before, you needed the wallet and the user password to connect to your service. The wallet to reach the database, and the user password to connect to it. Now, anybody that has read access to the wallet can connect to your service with the stored credentials.

Here is the list of credentials stored there:

[oracle@db193]$ mkstore -wrl . -listCredential
Oracle Secret Store Tool Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
Copyright (c) 2004, 2019, Oracle and/or its affiliates. All rights reserved.
Enter wallet password: T1s1s@WalletPassword
List credential (index: connect_string username)
1: myatp_low Franck

And even the stored password is easily visible when you provide the wallet password:

[oracle@db193]$ mkstore -wrl . -viewEntry oracle.security.client.password1
Oracle Secret Store Tool Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
Copyright (c) 2004, 2019, Oracle and/or its affiliates. All rights reserved.
Enter wallet password: T1s1s@WalletPassword
oracle.security.client.password1 = T1s1s@UserPassword

There’s no magic. This is still password authentication and the passwords needs to be read. But that’s better than having it hardcoded in scripts and command lines.

So, now it is easy to connect without mentioning the user and password but only the service and the location of the wallet:

[oracle@db193]$ sql /@myatp_low?TNS_ADMIN=/home/oracle/mywallet
SQLcl: Release 19.1 Production on Wed May 08 20:27:25 2019
Copyright (c) 1982, 2019, Oracle.  All rights reserved.
Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.4.0.0.0
SQL> show user
USER is "FRANCK"
SQL>

the service has been found in the tnsnames.ora and the password in the credentials (sqlnet.ora was not used there, so no need to configure it).