Our internal audit team has raised an issue on some scripts exposing database user, password and tns. You see, all you need is an access to unix shell and run ps command and you’ll be able to get the user id and password of the database. On the otherhand, there’s not a lot of people who have access to the unix shell. But regardless, it’s still a security vulnerability and must be rectified. Here’s how.

Basically, the steps are as follows.

  1. Write the connection string to a temp file. This would be your parameter file. The format is as below.
    userid=scott/tiger@
    As it is a parameter file, you can actually put all the other sqlldr parameters in there.
  2. Call sqlldr with parfile parameter.
    Example : sqlldr parfile=paramfile.txt data=mydata.csv control=mytable.ctl log=mydata101.log
  3. Delete the temp file

To be more clear, here’s the actual code I used.

umask 0077
PWDFILE=$(mktemp)
echo "userid=${SYS_JOB_BSS_ORACLE_UID}/${PASWD}@${SYS_JOB_BSS_ORACLE_SID}" > $PWDFILE
sqlldr parfile=$PWDFILE data=${data_file} control=${APP_CTL_FILE_NAME} log=${data_file}.log rows=100 errors=100
retCode=`echo $?`
rm $PWDFILE

I would like to explain it a bit but I realized that you won’t be here if you don’t understand what it does. If anything, the umask there just sets the permission of the temp file to be created as 077. The rest are pretty much self explanatory.

http://laurentschneider.com/wordpress/2009/05/hide-password-from-ps-output-sql-loader.html

Comments are closed.

Post Navigation