Hide and retrieve database credentials

Shell Programming and Scripting
1

Hi Everyone,
I am new to shell scripting and I have a requirement to fire a sql statement to Oracle database and create a out file in Unix server.
This will be plugged in to Autosys as a batch job.

I have the database credentials saved in a configuration file in the below format.

dbdata.cfg:

GN1 dbname dbuser dbpwd

The actual script shown below:
miscript.sh:

DBNAME= `awk '{print $2}' /home/mireport/dbdata.cfg`
DBPWD= `awk '{print $4}' /home/mireport/dbdata.cfg`
DBUSER= `awk '{print $3}' /home/mireport/dbdata.cfg`
OUTFILE= /home/mireport/testmi.csv

sqlplus -S ${DBUSER}/${DBPWD}@${DBNAME}" <<SQLEND >> ${OUTFILE}
 set pages 0 feed off head off
 alter session set current_schema=Schema_Owner;
select column1 from testfile;

exit
sqlend

Getting the below error when exceuting the script, can you provide some help on this?

$ sh miscript.sh
test.sh: dbpwd:  not found.
test.sh[2]: dbname^M:  not found.
test.sh[3]: dbuser^M:  not found.
test.sh[4]: /home/mireport/testmi.csv^M:  not found.
test.sh[5]: ^M:  not found.
test.sh[6]: sqlplus:  not found.
2

You will need to remove the paces after the = in your variable declarations, otherwise you will be executing the contents of the files you are awking, not assigning them.

You also don't need to use awk or command substitution (``) to read the files in simple cases like this. You could do something like:

read x DBNAME < /home/mireport/dbdata.cfg

for example.

It is also more secure to put the connect string inside the SQLPlus command, not as an argument to it. As an argument, anyone running a ps on the system will see the username and password.

3

Thanks Scott, as the dbname, password and user delimited by a space in the file, could you please show me an example how to read assign the values to variables?

4

On top of what Scott already said, you seem to have DOS line terminators (^M / \r / 0x0D / <CR>) in your files, mayhap due to editing with an non-*nix tool.
What is your OS and editor?

Instead of using three command substitutions for the variable assignments, how about

read X DBNAME DBPWD DBUSER X /home/mireport/dbdata.cfg

assuming - as you do - the data file has one line only?

EDIT: Please see correction by Scott below...

5 
read x DBNAME DBUSER DBPWD junk < /home/mireport/dbdata.cfg
6

Hi Rudic, OS: AIX and using notepad to edit the scripts.
I ll modify and test the scripts and update the outcome.

Thanks,

7

If you can't avoid notepad, make sure to save files in *nix style.

8

Of course, anyone able to read the script will be able to follow it to find the credentials. How do you plan to hide the credentials away? Can you make the file with credentials only readable by the id that runs the Autosys job?

Indeed, anyone able to use ps will also be able to see the credentials because they are on the command line to sqlplus. Can you move the credentials inside the here document that is driving the sqlplus? process?

Just a few thoughts,
Robin