[Previous entry: "Oracle 9i union flaw"] [Next entry: "which special characters can be used in Oracle database passwords"]
preventing password leakage with SQL*Loader
October 11th, 2004 by Pete
Post to del.icio.us
Post to Furl
I received an email tonight from a colleague who helped out on the SANS Oracle security step by step book when it was first written. He asked me if I knew how to hide the password used to connect to the database with SQL*Loader. He regularly audits the Unix servers under his control for password leakage on the command line by suing simple ps -ef | grep commands. This sometimes picks up users using SQL*Plus and connecting as follows:
sqlplus system/manager@orcl
The command line then shows up in a process listing using the ps command. He informs the culprit and advises them of the dangers of this and tells them to rectify the issue by using:
sqlplus /nolog
and then supplying the username/password@sid in the SQL script being used. It had come to his attention that users of the sqlldr binary were also leaking the password by supplying it on the command line. He was stumped as to how to fix this. I supplied the answer which I thought I would share tonight with others so that they can also stop this issue.
The simple answer is to use a parfile. This is a file that contains the SQL*Loader commands and could look like this:
$ cat parfile.par
userid=system/manager@orcl
control=control.ctl
errors=9999
log=logfile.log
This is then invoked as follows:
$ sqlldr parfile=parfile.par
This will prevent the leakage of the password into the process list but will present a new problem. The password is then stored in a script. This is a perennial issue. The answer is really down to levels of risk. The parfile can be protected at the file system level to prevent it being read. Ideally do not make it accessible to the owner of the oracle software or the dba group as this will prevent access from many of the functions such as UTL_FILE that allow operating system files to be read or written from within the database. Also the file could be generated or hand written just before use and then destroyed immediately after use. There are many other ideas that could be explored, as I said it really comes down to levels of risk. The particular issue here was how to prevent the process list leakage. Unfortunately this sort of problem often creates a new one.
Finally although not discussed with my colleague the same issues apply with other Oracle tools such as exp and imp and can be solved in the same way.
