Sqlldr Password visible at Unix level

anupam · Post by **anupam** » Wed Feb 15, 2006 1:35 am

Hi,

I am executing sqlldr and do not want my password to be displayed.
I have defined an environment variable and passing it to 1 routine and call dsloginfo. My password is not getting displayed anywhere in DataStage.

My problem is to hide the password from unix level also. When my Job is running, if that point in time, i execute
ps -ef | grep sqlldr

then the whole sqlldr statement is displayed with the password. So doing all the stuff at DataStage level is wasted.

Pls let me know is there any way of restricting the way of displaying the sqlldr command at unix level.

roy · Post by **roy** » Wed Feb 15, 2006 10:42 am

Hi,
What does your DBAs say?
It is not really a DS issue!

Please post your findings,

I_Server_Whale · Post by **I_Server_Whale** » Wed Feb 15, 2006 11:06 am

Yes. Roy is Right. It is not really a datastage problem. See if you can encrypt this password by writing a script and overwrite the parameter file with this encrypted password.

ray.wurlod · Post by **ray.wurlod** » Wed Feb 15, 2006 3:59 pm

Plus I know for a fact that this was solved at RIL last time I was there. You will be able to find examples where the password is not visible. Think about environment variables, think about "here scripts" that actually prompt for passwords, where the value can be provided from the environment variable.

anupam · Post by **anupam** » Wed Feb 15, 2006 11:18 pm

It was not solved fully. If somebody execute 'ps -ef | grep sqlldr' then the password was visible.....

Anyways, now i have solved it.

Thanks all of you all....

ray.wurlod · Post by **ray.wurlod** » Wed Feb 15, 2006 11:42 pm

Does your "solution" involve the -s switch? Please let future searchers know how you solved it.

anupam · Post by **anupam** » Thu Feb 16, 2006 12:04 am

-s does not work with sqlldr, it works with sqlplus.

Definately i will share the code to anyone who wants but can not share in public. Who so ever wants the logic should send PM to me. I hope you guys understand, its security issues....

ray.wurlod · Post by **ray.wurlod** » Thu Feb 16, 2006 3:39 am

Surely the TECHNIQUE doesn't violate any security?!! The sqlldr program is invoked with various command line options, but can prompt for user id and password if required. In that case a "here script" can be used to supply responses to the prompts, and environment variables can be used to supply the actual values. For example

Code: Select all

# Invoke sqlldr but prompt for user ID and password
sqlldr ...options... << EOT
$DBUSER        # response to user ID prompt
$DBPASSWORD    # response to password prompt

EOT

Where's the security breach in that?

rleishman · Post by **rleishman** » Sat Feb 18, 2006 12:23 am

Most Oracle DBAs know about this one. There is a solution that works with all flavours of Unix I know, and exploits the fact that PS shows only the first 255 chars of the command. Some versions of Oracle actually ship with it included I think (at least they do with sqlplus and sqlforms) - but my current 10g still has the problem on Linux.

Here's what you do:
- Rename the sqlldr executable to sqlldr.exe (or whatever..)
- Write a C program sqlldr.c that simply runs an exec() of sqlldr.exe using the same positional arguments it was passed. It has to strip the path name from ARGV[0] in case the Oracle bin is not on the user's path.
- Between the sqlldr.exe and the first argument in the exec() call, insert 255 spaces.
- Compile sqlldr.c and move the executable into the Oracle BIN directory.

rleishman · Post by **rleishman** » Sun Feb 19, 2006 10:07 pm

Damn Linux has ruined it for everyone! Just tried the above solution on Linux (SuSE SLES

and ps returns heaps more than 255 chars. I tried up to 1000 and I couldn't break it.

Use Ray's solution. Or you could also use the PARFILE option to stick the userid/password in a file.