Generate your Oracle Secure External Password Store wallet from your tnsnames.ora

Want to connect passwordless with SQLcl to your databases from a single location? Here is a script that creates the Secure External Password Store wallet credentials for each service declared in the tnsnames, as well as shell aliases for it (as bash does autocompletion). The idea is to put everything (wallet, sqlcl,…) in one single directory that you must protect of course because read access to the files is sufficient to connect to your databases.

Download the latest SQLcl from:

SQLcl Downloads

And install the Oracle Client if you do not have it already:

Oracle Instant Client Downloads

Now here is my script that:

• reads the tnsnames.ora (define the location)
• define sqlnet.ora and tnsnames.ora (ifile to the original one)
• creates the password wallet
• generates a script to define all aliases
• create a login.sql

All that is located in the sqlcl directory (here under my $HOME) and the aliases have everything to point here (TNS_ADMIN and SQLPATH)

# this is where your tnsnames.ora is found
TNS_ADMIN=/etc
# unzip -d ~ sqlcl-19.1.0.094.1619.zip
#
# if "Error Message = no ocijdbc18 in java.library.path" see https://martincarstenbach.wordpress.com/2019/05/20/using-the-secure-external-password-store-with-sqlcl/
#
alias sqlcl='TNS_ADMIN=~/sqlcl SQLPATH=~/sqlcl ~/sqlcl/bin/sql -L -oci'
#
cat > ~/sqlcl/sqlnet.ora <<CAT
WALLET_LOCATION=(SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY="$HOME/sqlcl")))
SQLNET.WALLET_OVERRIDE=TRUE
CAT
#
cat > ~/sqlcl/tnsnames.ora <<CAT
ifile=$TNS_ADMIN/tnsnames.ora
CAT
#
cat > ~/sqlcl/login.sql <<'CAT'
set exitcommit off pagesize 5000 linesize 300 trimspool on sqlprompt "_user'@'_connect_identifier> "
set sqlformat ansiconsole
CAT
#
read -p "Enter SYS password to store in the wallet: " -s PASSWORD
# Create the wallet
mkstore -wrl ~/sqlcl -create <<END
$PASSWORD
$PASSWORD
END
# Add services to wallet
awk -F"," '/^[^ #\t].*=/{sub(/=.*/,""); for (i=1;i<=NF;i++){print $i}}' $TNS_ADMIN/tnsnames.ora | while read service
do
echo "=== Adding $service to wallet for passwordless connection like: /@$service as sysdba"
mkstore -wrl ~/sqlcl -createCredential $service SYS <<END
$PASSWORD
$PASSWORD
$PASSWORD
END
done
# list services from wallet
{
mkstore -wrl ~/sqlcl -listCredential <<END
$PASSWORD
END
} | awk '/^[0-9]+: /{print "alias sysdba_"tolower($2)"="q"TNS_ADMIN=~/sqlcl SQLPATH=~/sqlcl ~/sqlcl/bin/sql -L -oci /@"toupper($2)" as sysdba"q}' q="'" qq='"' |
 sort | tee ~/sqlcl/services.sh
#
unset PASSWORD

Then just source the generated services.sh to create aliases for each service (like sysdba_xxx). This example creates connections as sysdba with the SYS authentication, but it is highly recommended to have your own user. Of course the idea here is that the same password is used on all databases, but that again can be customized.

When I don’t want to use an alias (from a script for example) I also have a chmod u+x script in my path to run sqlcl with this environment

TNS_ADMIN=~/sqlcl SQLPATH=~/sql ~/sqlcl/bin/sql -L -oci ${@:-/nolog}

and SQLcl has also autocompletion for the connect command (from the tnsnames.ora).

If you have a “no ocijdbc18 in java.library.path” message, then look at Martin Bach blog:

Using the Secure External Password store with sqlcl

If you have credentials to connect to the Oracle Cloud, use the downloaded wallet instead of creating one with mkstore.