Running sqlplus from a unix shell script, how do I handle passwords?

[A. Clay Stephenson · Accepted solution]

Here is one method (assuming your ORACLE_SID is already set):

S1=/tmp/cmds${$}.sql

# simply list all users and exit

cat << !EOF >> ${S1}
select * from all_users;
exit;
!EOF!

sqlplus username/topsecret < ${S1}

rm -f ${S1}

[Jeff Schussele]

Hi wvsa,

Would be handled by sending a string @ login like

username/password@ORACLESID

or something like that - depends on the type of DB accessed.

Any way it needs the string you'll have to deal with the fact that the PW will be in plain text in the script.
Only option would be to use 3rd-party SW to encrypt/decrypt the PW on either end.
Search the forum for "ecnrypted passwords" for ways others have done this.

Rgds,
Jeff

[Deepak Extross]

If you dont mind your script being a little interactive, you can prompt the user for the Username/Password when the script starts up:

echo "Enter the User Name for the DB"
read user
stty -echo
echo "Enter the Password for $user"
read pass
stty echo

sqlplus $user/$pass ......

[Mark Seaman]

Storing passwords in scripts is a problem for a couple reasons:

1. You have it change it in a number of places when your password changes.
2. It is a security risk if you forget to change the permissions on the script so no other user can read it.

Two alternatives are:

1. Use other forms of authentication: Oracle OS Authentication or Network authentication. Take a look at the Admin Guide for more info on these.

2. Store the password in a file and read it in every script:

PWD=$(cat /home/admin/passwd.txt)

Make sure the permissions on this file don't allow others to read it.

[Deepak Extross]

Have you considered using Expect?
http://expect.nist.gov/

[Douglas Arneson]

We run sqlplus from several scripts using the following:

sqlplus -s username/password @report_something.sql


Doug

[Thierry Poels_1]

Hi,

I strongly agree with Mark Seaman! Storing Oracle passwords in shell scripts is a BIG security issue!

Next to having the password visible in the script for anyone who can access the script (and the administration to keep passwords in line with the database); you also have an issue that ANYBODY can see the command "sqlplus user/password" with a simple "ps -ef"!!

My favourite solution still is OS authentication (the OPS$-logins). Personally I strongly advise against Oracle passwords in shell scripts; in fact I blow away these scripts whenever I find them, and as long as the programmers don't do the same with me, I'll continue doing so in the future ;)

regards,
Thierry.

[Hiren Patel]

Thierry - What is: OS authentication (the OPS$-logins) and how would one use it?

Thanks,
Hiren

[Thierry Poels_1]

Hi,

Create user ops$XXXXX
identified externally
default tablespace x
temporary tablespace y;

OS_AUTHENT_PREFIX in your init.ora will define the prefix of the externally authenticated users; default used to be "OPS$", but can be set to anything you want, or can even be empty.

After the user has been created and the necessary grants have been given (connect, custom roles, ...) the Unix (or other OS) user XXXXX will be able to connect to the database without supplying a password; i.e. "sqlplus /" or "runform /" will be sufficient.

regards,
Thierry.

[Suwarna]

I agree that OS Authentication is the best means of doing it.
However if I want to follow the properties file approach, how do I pass the username/password to the sqlplus command via shell script? I am doing something like -

DBURL=$(cat temp.txt)
echo $DBURL

sqlplus $DBURL << EOF
exec packageName.procName();
EOF

The sqlplus command is not recognizing the DBURL parameter. How do I pass this shell script variable to sqlplus command?
I am new to shell scripting. Please help.
Thanks.........