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
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.

umask 0077
echo "userid=scott/tiger" >$TMPFILE
sqlldr parfile=$TMPFILE control=x.ctl silent=header,feedback

mktemp is an Unix utility that creates temp files with unique names.

By Laurent Schneider

Oracle Certified Master

14 replies on “Hide password from ps output : sql loader”

sqlldr parfile=$TMPFILE control=x.ctl silent=header,feedback >$LOGFILE &
sleep 5

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 I recall using hide.c back in the version 7 days to deal with this issue.

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.

Also very annoying is the output Username: when you pass username thru standard input.

$ echo tgpowner/tgpowner@dzheats1|sqlldr control=x.ctl silent=header,feedback

We could still grep -v

$ echo tgpowner/tgpowner@dzheats1|sqlldr control=x.ctl silent=header,feedback|grep -v 'Username:'

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.

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

Leave a Reply

Your email address will not be published.