topic Re: Running sqlplus from a unix shell script, how do I handle passwords? in Operating System - HP-UX

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

wvsa — Mon, 29 Apr 2002 21:04:24 GMT

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

A. Clay Stephenson — Mon, 29 Apr 2002 21:14:16 GMT

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}

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

Jeff Schussele — Mon, 29 Apr 2002 21:15:57 GMT

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

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

Deepak Extross — Tue, 30 Apr 2002 00:40:38 GMT

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 ......

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

Mark Seaman — Tue, 30 Apr 2002 03:44:18 GMT

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.

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

Deepak Extross — Tue, 30 Apr 2002 07:16:17 GMT

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

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

Douglas Arneson — Tue, 30 Apr 2002 10:59:27 GMT

We run sqlplus from several scripts using the following:

sqlplus -s username/password @report_something.sql

Doug

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

Thierry Poels_1 — Tue, 30 Apr 2002 11:20:52 GMT

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.

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

Hiren Patel — Tue, 30 Apr 2002 12:36:51 GMT

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

Thanks,
Hiren

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

Thierry Poels_1 — Tue, 30 Apr 2002 13:53:55 GMT

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.

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

Suwarna — Mon, 10 Jul 2006 11:09:58 GMT

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.........