Manage Wallets for Multiple Oracle Autonomous Databases

A database is no good if you can’t connect and Oracle’s Autonomous Database is no exception. But it’s also important to secure database connections and ATP/ADW databases are protected by wallets. Autonomous Database wallets are available in the OCI interface under each database’s detail page via the DB Connection button.

The file includes all the credentials necessary to connect to your database, along with tnsnames.ora and sqlnet.ora files. A user typically copies these files to their $TNS_ADMIN directory where clients like SQL*Plus or SQLcl will read the TNS and SQLnet entries, locate the appropriate credentials, and connect.

What if you have multiple Autonomous Databases? If they’re in the same tenant and region, choose to download a Regional Wallet.

But… what about tenants that span regions or managing databases for multiple tenancies? Wallet files have singular names—cwallet.sso, ewallet.p12, keystore.jks, truststore.jks, sqlnet.ora—and the files can’t be merged or appended into one another. They must inhabit separate directories. Entries in the TNS file, on the other hand, can be merged. A dilemma!

Can this be solved with multiple TNS_ADMIN paths? It works but it’s not really a solution. Users shouldn’t need to update their environment each time they want to make connections to a different database. (I’m embarrassed to admit this was my initial solution, a quick, dirty fix that used aliases to activate separate profiles.)

The sqlnet.ora file provides Oracle with the location of the wallet:

WALLET_LOCATION=(SOURCE=(METHOD=file)(METHOD_DATA=(DIRECTORY="/path/to/wallet")))

On a database server multiple databases in a shared home can access their own wallets by using an environment variable in the ENCRYPTION_WALLET_LOCATION specification:

ENCRYPTION_WALLET_LOCATION=(SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY="/base/wallet/$ORACLE_SID")))

That’s not a practical solution for a client. Clients may not set ORACLE_SID at all and it still requires changing the environment between connections.

The one file all connections share is tnsnames.ora. Is there a way to include wallet information for individual connections?

MY_WALLET_DIRECTORY to the rescue

TNS has an option for including wallet and credential information in each connection’s SECURITY section. In fact, the tnsnames.ora files generated for Autonomous Databases already have a security entry:

atp_high = (description= (retry_count=20)(retry_delay=3)(address=(protocol=tcps)(port=1522)(host=oraclecloud.com))(connect_data=(service_name=atp_high.oraclecloud.com))(security=(ssl_server_cert_dn="CN=oraclecloud.com,OU=Oracle BMCS US,O=Oracle Corporation,L=Redwood City,ST=California,C=US")))

The security section has an option for MY_WALLET_DIRECTORY for defining a wallet location used by the connection:

(MY_WALLET_DIRECTORY=/base/wallet/tenant/region)

Once connections aren’t relying on the sqlnet.ora file for wallet information there’s no need for any environmental change. Separate directories for each wallet are still necessary but this is desirable. It’s easier to manage credentials for multiple databases when they’re rotated or expire when they’re confined to their own directories, rather than trying to modify entries in a single, consolidated wallet.

An example connection string for an Autonomous Database, with its wallet included, looks like this:

atp_high = (description= (retry_count=20)(retry_delay=3)(address=(protocol=tcps)(port=1522)(host=oraclecloud.com))(connect_data=(service_name=atp_high.oraclecloud.com))(security=(ssl_server_cert_dn="CN=oraclecloud.com,OU=Oracle BMCS US,O=Oracle Corporation,L=Redwood City,ST=California,C=US")(MY_WALLET_DIRECTORY=/base/wallet/tenant/region)))

This method works for EZ Connect and JDBC connection strings, too:

$ORACLE_HOME/bin/sqlplus 'username@(description=(retry_count=20)(retry_delay=3)(address=(protocol=tcps)(port=1522)(host=oraclecloud.com))(connect_data=(service_name=atp_high.oraclecloud.com))(security=(ssl_server_cert_dn="CN=oraclecloud.com,OU=Oracle BMCS US,O=Oracle Corporation,L=Redwood City,ST=California,C=US")(MY_WALLET_DIRECTORY=/base/wallet/tenant/region)))'

The TNS entry, condensed onto a single line, is the connection string.