[Previous entry: "Andrej Koelewijn talks about google stopping comment spam"] [Next entry: "Happy birthday to orablogs.com"]
A script to call SQL*Plus without hardcoding passwords
January 31st, 2005 by Pete
Post to del.icio.us
Post to Furl
I receive each week the DBA-Village newsletter and this week there was a link in the tips section of the newsletter for a script written by Wayne Martin that stores usernames and passwords in a central setuid file that only root can access. The script and a short description can be downloaded from the DBA-Village site. To do so though you will need to register for a free login - Brian's post "Why do I have to register?" - comes to mind..:-)
Go to the DBA-Village home page for registration. Then the script description can be found on a tip titled "Executes SQL without hard coding of passwords.". The actual script source code can be downloaded from the same page by hitting the download button.
Whilst this is an admirable solution to the age old problem of passing a username and password to SQL*Plus on the command line there is a second problem now introduced. This is that the username is still hard coded but in one file that is probably better protected than hard coding the password and username in an SQL file. This is an age old problem in Oracle and other software where usernames and passwords need to be used but not leaked. The problem still exists with the Unix ps command potentially leaking the username and password on the command line. This has to be dealt with correctly by invoking SQL*Plus so that they are not displayed. The author of the script does deal with this.
The problem then is hard code passwords in SQL scripts, or a single file, or even an environment variable (probably still in a file, the .profile) and also the issue of leakage on the command line. I have written about this issue a few times. There are many solutions and each should be considered in respect to sever access, file security, access to ps listings, the level of the user account being used and many more.
Always obey the least privilege principle for users used to run scripts. If possible use DBMS_JOB or its 10g successor DBMS_SCHEDULER as then the issue goes away. If possible use a server based external account to avoid the need for a password. This needs to be done carefully so that this user can only execute the packages needed by the process or system commands needed. If possible avoid running scripts, do it from within the database. If not consider an external user (not remote!), lastly consider solutions like this script. As I said its horses for courses though. Interesting problem!
