Problem solve Get help with specific problems with your technologies, process and projects.

Running SQL*Plus scripts without supplying password

What's the best way to implement running SQL*Plus scripts without a password being supplied? Example:

 SQL*Plus userid/pw @script

I'm assuming you ask this question since you do not want the password to appear when you do "ps -ef|grep sqlplus" on a Unix system. This is a security hole if one can see your password when doing a process status listing. You have a couple of ways of getting around this.

One way is to use a connection "script". Create a script called "connect.sql". This script will only have one line:

connect userid/password
sqlplus /nolog @my_script
sqlplus username @my_script << password_file

In either method, your password can be seen if you don't take methods to secure any files which hold the password. So make sure that only the Oracle user and/or DBA group can access this file by protecting it with OS file permissions.

For More Information

Now have all your scripts call this script as the first line in that script. To stop SQL*Plus from prompting you for the password, start it with the "/nolog" option as follows: Another way is to redirect the password as input to the executable as follows: The file "password_file" contains just one word, the password for this user. When SQL*Plus would normally prompt you for the password, the contents of this file would be used.

Dig Deeper on Oracle database design and architecture