Top 60 Oracle Blogs

Recent comments

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
# unzip -d ~
# if "Error Message = no ocijdbc18 in java.library.path" see
alias sqlcl='TNS_ADMIN=~/sqlcl SQLPATH=~/sqlcl ~/sqlcl/bin/sql -L -oci'
cat > ~/sqlcl/tnsnames.ora <ifile=$TNS_ADMIN/tnsnames.ora
cat > ~/sqlcl/login.sql <<'CAT'
set exitcommit off pagesize 5000 linesize 300 trimspool on sqlprompt "_user'@'_connect_identifier> "
set sqlformat ansiconsole
read -p "Enter SYS password to store in the wallet: " -s PASSWORD
# Create the wallet
mkstore -wrl ~/sqlcl -create <$PASSWORD
# Add services to wallet
awk -F"," '/^[^ #\t].*=/{sub(/=.*/,""); for (i=1;i<=NF;i++){print $i}}' $TNS_ADMIN/tnsnames.ora | while read service
echo "=== Adding $service to wallet for passwordless connection like: /@$service as sysdba"
mkstore -wrl ~/sqlcl -createCredential $service SYS <$PASSWORD
# list services from wallet
mkstore -wrl ~/sqlcl -listCredential <$PASSWORD
} | 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/

Then just source the generated 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.