Connecting to multiple Oracle databases hosted on different machines - Username as common and different password for each database

Question

Environment:

We have ~100 databases hosted on multiple machines. All databases have common username but password is different on every database.

Issue:

How can I read the password and TNS (Service name) from an external file one by one and connect to the each database and run the sql query and pass the output to a logfile.

What is done so far:

1. Created pass file that contains password and Service_Name and delimiter ":"

cat pass
Loufr#123:PROD
Brinj#6523:TEST

2. Another file dblist is created with all the dbnames (that matches the service names) in step 1

cat dblist
PROD
TEST
DEV
QA
Quality

---

Goal:

read name of the database from dblist and find the password from pass file for that database.

Use dbname and password to connect to database in following string

sqlplus -s dbsnmp@${TNS}/${Password}
spool output.log
@query.sql
exit

Answer 1

This script should do the trick:

while read TNS; do
    Password="$(sed -n "s/\(.*\):${TNS}$/\1/p" pass)"
    [ "$Password" ] && sqlplus -s dbsnmp@${TNS}/${Password} << EOF
whenever sqlerror exit sql.sqlcode;
set echo off 
set heading off

spool output.log
@query.sql

exit;
EOF
done < dblist

If you just ask for a script parsing the files containing the credentials. You can see also: Connect to sqlplus in a shell script and run SQL scripts

Answer 2

There are many ways to get what you want but ‘solutions’ that store plaintext passwords or that show them on the command line are wrong. There is a solution for storing passwords since many versions, called oracle wallet. https://docs.oracle.com/database/121/DBIMI/walet.htm#DBIMI10236 Using this makes scripting a lot easier because you can limit your logon code to something like: “sqlplus /@tns” this will dig up the credentials in the wallet using tns as the key and make a connection with it, without even showing the username.

There are many security problems in the world. Why not use the tools to make applications a bit more secure?