Hide password from ps output : sql loader
By reporting the process status with ps, any Unix user will see the command line arguments
ps -ef
UID PID PPID C STIME TTY TIME CMD
lsc 13837 13825 0 May 11 pts/17 0:01 -ksh
oracle 4698 6294 0 12:00:40 ? 0:00 sqlplus -s system/manager
appluser 4229 4062 0 12:00:03 ? 0:00 sqlldr scott/tiger
applrun0 28445 28281 0 11:54:03 ? 0:00 imp king/gold full=y
...What you see here above is definitely a security issue. For sqlplus, the trick is to use sqlplus /nolog and then pass connect system/manager as input or script.
For sqlldr (and exp/imp etc…), the trick is to use a parameter file.
To make it as safe as possible, the file must be unique, readable only for owner and removed after usage.
Ex:
umask 0077
TMPFILE=$(mktemp)
echo "userid=scott/tiger" >$TMPFILE
sqlldr parfile=$TMPFILE control=x.ctl silent=header,feedback
rm $TMPFILEmktemp is an Unix utility that creates temp files with unique names.
sqlldr parfile=$TMPFILE control=x.ctl silent=header,feedback >$LOGFILE &
sleep 5
rm $TMPFILE
yes!
and to get the return code
wait $!But sending a job in the background may have side effects, for instance if you interrupt the script during the “sleep 5″, the job will still be running in the background, but groundly yes, it is best to keep the tempfile the shortest possible time
What version of Oracle was this? I believe that in 10g this was addressed for all tools (sqlplus/sqlldr/imp/exp). It does not reproduce in 10.2.0.4. I recall using hide.c back in the version 7 days to deal with this issue.
Great Job!
Just don’t use password on command…
Greg, this depends on your OS. Output above is 10.2.0.4 Solaris sparc. Even hide.c is just a hack and does not hide that well… Thanks for the link, nice memories
IIRC, you can also pass the parameters with <<EOF, by specifying user and pass on the following lines.
For some reason, I had <<EOF and it did not work in one of my server. I can reproduce it at will, but only under one particular environment. As all the os, the third party scheduler and the database are end of live since ages, I did not want to invest to much time on the issue.
(I mean I invested only hours
)
Laurent, interesting.
Also very annoying is the output Username: when you pass username thru standard input.
We could still grep -v
But then we lose the return code. I will make a new post about how to handle this
@Laurent Schneider
I’ve run into this a few times, inevitably it is extra white space somewhere. Do a global substitution for end of line and see
(in vi, something like :g/$/s//asdf/ and look for a space before asdf). If I’m understanding your issue properly. Oftentimes it manifests as scripts mysteriously not performing the last few lines.
Joel,
no, exactly the same script works when started from the command line, but not when started thru the Redwood Cronacle Job Scheduler. And only for one environment
PS: I missed my own math challenge LOL