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. sqlldr parfile=$TMPFILE control=x.ctl silent=header,feedback >$LOGFILE &
    sleep 5
    rm $TMPFILE

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

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

  4. 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 ;-)

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

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

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

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

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

Leave a Reply

Your email address will not be published.


*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>