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 $TMPFILE

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

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

  1. Boris

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

  3. Laurent Schneider Post author

    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

  4. Greg Rahn

    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.

  6. Laurent Schneider

    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 😉

  7. Brian Tkatch

    IIRC, you can also pass the parameters with <<EOF, by specifying user and pass on the following lines.

  8. Laurent Schneider Post author

    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.

  11. Laurent Schneider Post author

    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
    Username:$

    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 🙂

  12. Pingback: Laurent Schneider » return code before grep

  13. joel garry

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

  14. Laurent Schneider

    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

Comments are closed.