I need to run a script with a connection statement. Is there any way I can "Hide" password in the script file? Or can I use OS level user/password as Oracle's user/password?
Thanks for any help.
Why not have the script prompt the user for username and password, and not display the password a la starting SQL*Plus? How secure does the environment need to be?
A script that requires hard coding of a username and password within it is a bad script. What's to prevent someone else from looking at the script? Are you on UNIX? Is the script trying to have whomever executed it log in to the database as someone else? If so, another bad design feature.
Is the user a DBA privileged user? You can hack into a user account without altering the user's password by getting the encrypted password string from dba_users and doing an alter user username identified by values 'what_the_string_is';.
How is the script being run? Exec'd on the command line by the Oracle user? You can write a shell script that will prompt for a password. That should solve your problem.
sqlplus -s << EOF
env contains username and password as variables
We are trying to create a new user in the script and want the whole process to get done automatically.
OS based authentification
Your post did say under which OS, but I will assume UNIX.
The most secure way to run scripts with SQL*Plus and hide crudentials is to use the OS based authentifiation.
Create an Oracle user OP$name, where "name" is your Unix username. For example if your script starts under the Unix user BATCH then you create an Oracle user called OPS$BATCH. Give all suffisant privileges to the new Oracle user.
Now, start SQL*Plus like this:
$ sqlplus / @yourscript.sql
/ Represents a default logon using operating system authentication and SQL*plus will automatically try to connect using the user OPS$name. Since you're logged in under Unix, Oracle assumes you're already authenticated and lets you in without password prompting.
This also has the good side of turning your script into a maintenance free and user independant process.
Hope this clears it up for ya!
Unless you create a user with O/S Authentication you have to store the Oracle password in a file. I store it in a hiden unix 700 file.
Syntax I use is (depending on password lenth)
PASS=`cut -b 12-17 /directory/.password`
Try and keep you password in a single centralised area.
OCP 8i, 9i DBA
Click Here to Expand Forum to Full Width