Call: +44 (0)1904 557620 Call

Pete Finnigan's Oracle Security Weblog

This is the weblog for Pete Finnigan. Pete works in the area of Oracle security and he specialises in auditing Oracle databases for security issues. This weblog is aimed squarely at those interested in the security of their Oracle databases.

[Previous entry: "Oracle 9i union flaw"] [Next entry: "which special characters can be used in Oracle database passwords"]

preventing password leakage with SQL*Loader

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

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.