A colleague of mine recently asked me why I always started Oracle programs using a “/nolog” and then explicitly connecting rather than just passing in the username and password in one go.  Some years back, I worked with an Oracle guru who showed me exactly why you shouldn’t, just by using one simple command in UNIX:

ps -ef
UID   PID  PPID C    STIME TTY TIME CMD
oracle    4325  5180 0 12:00:04 ?   0:00 sqlplus system/manager
appluser  3118  3012 0 12:00:03 ?   0:00 sqlldr scott/tiger
appluser 26332 24101 0 11:00:15 ?   0:00 imp matt/matt full=y

As you can see – if you pass in the username and password, then anyone can easily find it out using ps!

So, how go you get round it?  If you can’t connect from within the tool (as you can in SQL*Plus), you can use a password file, which you should then delete once you have done the job, e.g. “sqlldr parfile=$PWDFILE….” where the file contains “userid=matt/matt”.

In order to make it even more secure, then you could use temporary files for your password file, e.g.

PWDFILE=$(mktemp)
echo "userid=matt/matt" > $PWDFILE
sqlldr parfile=$PWDFILE ....
rm $PWDFILE

mktemp is a utility which will create a temporary file with a unique name.

This entry was posted on Friday, July 24th, 2009 at 10:10 pm and is filed under Oracle, Technical. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.