Databases
cancel
Showing results for 
Search instead for 
Did you mean: 

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

SOLVED
Go to solution
wvsa
Regular Advisor

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

 
10 REPLIES
A. Clay Stephenson
Acclaimed Contributor
Solution

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

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}

If it ain't broke, I can fix that.
Jeff Schussele
Honored Contributor

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

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
PERSEVERANCE -- Remember, whatever does not kill you only makes you stronger!
Deepak Extross
Honored Contributor

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

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
Advisor

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

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
Honored Contributor

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

Have you considered using Expect?
http://expect.nist.gov/
Douglas Arneson
Occasional Advisor

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

We run sqlplus from several scripts using the following:

sqlplus -s username/password @report_something.sql


Doug
Thierry Poels_1
Honored Contributor

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

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.
All unix flavours are exactly the same . . . . . . . . . . for end users anyway.
Hiren Patel
Occasional Visitor

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

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

Thanks,
Hiren
Thierry Poels_1
Honored Contributor

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

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.
All unix flavours are exactly the same . . . . . . . . . . for end users anyway.
Suwarna
Occasional Visitor

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

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