0

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
  1. 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
1
  • Can you add an example of some lines of such external file? Not with the real data of course;)
    – Davide Madrisan
    Sep 11, 2021 at 17:26

2 Answers 2

0

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

7
  • I have elaborated the question. Added contents of the file. Can you have a look please.
    – Gaurav Gupta
    Sep 11, 2021 at 17:37
  • @Gaurav Gupta I updated the script.
    – Davide Madrisan
    Sep 11, 2021 at 17:52
  • Thank you very much Davide. It worked. I also want to learn this kinda scripting, if you can guide me from where to start.
    – Gaurav Gupta
    Sep 11, 2021 at 18:50
  • I read long times ago the Advanced Bash-Scripting Guide, but you can find lots of more updated resources. Same for sed, here's my bible.
    – Davide Madrisan
    Sep 11, 2021 at 19:09
  • 2
    @DavideMadrisan, it is user/password@tns , not user@tns/password. Besides, with your solution I would be able to run a ps in the terminal and read the passwords. It is better to encapsulate the connection inside sqlplus by using the nolog option and then using the connect command. Sep 11, 2021 at 21:18
0

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?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.