Hide Database Password in shell script

[tekpr00]

Hello All:

I have a little shell script that checks for no row. Here is the sample:

#!/bin/ksh
NAME=no rows selected
ORACLE_SID=mydtbase
export $ORACLE_SID
sqlplus -L userprod/sunny123@mydtbase <<!
@myscript.sql
exit
!
if (grep  $NAME mydtbase.txt); then
         echo
   else
         echo "There are rows in READY STATE  on `hostname` "| mailx -s Subject $DBALIST
   fi
exit

++++++++++++++++++
I will like to hide the password "sunny123". I was wondering if anyone could help.

Thanks.

 

[p5wizard]

put a connect string in the sql script file if you can lock down access to that sql script file


connect scott/tiger@stores
select count(*) from table
where status = ... ;

HTH,

p5wizard

 

[rzs0502]

Quite a few ways to do this.
I've seen some use shc (external program) to compile a shell script into an executable.
But then you have to recompile each time you change the password.
I manage mine with a Python script that encrypts the password store.

But here are another 2 options that look quite good.
The latter from Oracle seems to be the best solution.

Oracle Password Repository

http://laurentschneider.blogspot.com/2006/07/oracle-password-repository.html

SQLNet Wallet

http://laurentschneider.blogspot.com/2006/08/sqlnetwalletoverridetrue.html

One thing whith your sqlplus command, is that your password is visible on the ps output while your script is running.
There are ofcourse ways to workaround this.
hide.c from Oracle.

I mainly use Python/Perl with the respective DB API for anything database related.

"If you always do what you've always done, you will always be where you've always been."

 

[SamBones]

I had a script a long time ago that kept the password in an encrypted file using the [tt]crypt[/tt] command. Something like...

#!/bin/ksh

export ORACLE_SID=mydtbase
export ORACLE_USER=userprod
export ORACLE_PW=$(crypt `hostname` < encrypted.pwd)

sqlplus -L ${ORACLE_USER}/${ORACLE_PW}@${ORACLE_SID} <<-SQLCMDS
@myscript.sql
exit
SQLCMDS

And you create the encrypted file like this...

echo sunny123 | crypt `hostname` > encrypted.pwd

Of course that means you need to protect the "key" somehow. In the example I'm just using the hostname for the encryption key. This keeps you from having to hard code the password into the script.

 

[p5wizard]

Well, Sam, anyone that can run a [tt]ps -ef[/tt] during the execution of sqlplus still sees that password...

HTH,

p5wizard

 

[SamBones]

Agreed. You should do it more like this regardless of how you're hiding the password from the script...

#!/bin/ksh

export ORACLE_SID=mydtbase
export ORACLE_USER=userprod
export ORACLE_PW=$(crypt `hostname` < encrypted.pwd)

[b]sqlplus -S /nolog <<-SQLCMDS
connect ${ORACLE_USER}/${ORACLE_PW}@${ORACLE_SID}[/b]
@myscript.sql
exit
SQLCMDS

This keeps the username and password off of the command line.

 

[tekpr00]

thanks all for your contribution.

 

[tekpr00]

When I did it like you suggested: It works when I run the script from the command line.

However, when I schedule it from the crontab it does not work, it gives the error.

/export/home/oracle/queues/queue.sh[31]: encrypted.pwd: cannot open
SP2-0306: Invalid option.
Usage: CONN[ECT] [logon] [AS {SYSDBA|SYSOPER}]
where <logon> ::= <username>[/<password>][@<connect_identifier>] |

Please advice.

 

[tekpr00]

Note that I have actually re-created the encrypted.pwd and give the file a full permission 777. Still the job cannot see it from crontab. Again, it works from command line, but not when the job is schedule via crontgab.
5 23 * * * . /etc/profile; . $HOME/.profile; /export/home/oracle/queues/queue.sh* > /dev/null 2>&1

 

[p5wizard]

Use a full path name for the encrypted password file (and for any file referenced really). Jobs run from crontab do not necessarily have the same process environment as when you are logged in interactively.

Another option is to make the script as a first step to cd into the directory that your files are in (probably your home directory).

HTH,

p5wizard

 

[tekpr00]

Many thanks to p5wizard and everyone. This is now working perfect.