How to hide oracle passwords - sqlldr, sqlplus, exp, imp, expdp, impdp from Unix ps command?

Option#1.
Pass the passwords through parameter file for the utilities exp, imp, expdp, impdp and sqlldr. This is the simple method.

$ vi parameter.pf
"parameter.pf" [New file]
userid=scott/tiger
directory=exp_dir
compression=all
dumpfile=withcomp.dmp
schemas=scott
logfile=withcomp.log


$ expdp parfile=parameter.pf &
[1]     28295
$
Export: Release 11.1.0.7.0 - 64bit Production on Thursday, 21 July, 2011 23:46:54

Copyright (c) 2003, 2007, Oracle.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

$ ps -ef|grep exp
  oracle 28300 27528   0 23:46:59 pts/2       0:00 grep exp
  oracle 28295 27528   0 23:46:55 pts/2       0:00 expdp parfile=parameter.pf

Option#2
Create a temporary file for username and password and use the file as the parameter file for sqlldr, export, import. mktemp is an Unix utility to create tempfiles with unique names.

pwdfile=$(mktemp)
echo "userid=scott/tiger">$pwdfile
#For sql loader
sqlldr parfile=$pwdfile  control=test.ctl silent=header,feedback
#For expdp
expdp parfile=$pwdfile directory=exp_dir dumpfile=tab.dmp logfile=tab.log tables=example &
rm $pwdfile

$ ps -ef|grep exp
  oracle 28691 28452   0 00:02:13 pts/3       0:00 grep exp
  oracle 28674 28452   0 00:01:33 pts/3       0:00 expdp parfile=/tmp/tmp.4Naq83 directory=exp_dir dumpfile=tab.dmp logfile=tab.log

$ cat /tmp/tmp.4Naq83
userid=scott/tiger
oracle@prod(4132) prod9 /home/oracle/scott
$ ls -ltr /tmp/tmp.4Naq83
-rw-------   1 oracle   dba           21 Jul 22 00:00 /tmp/tmp.4Naq83

Option#3
echo <user password> | exp <username> parfile=mypar.pf

Example:
echo tiger|exp scott parfile=exp.par

How to handle SQLPlus sessions?
We have discussed about exp, imp, datapump and sqlldr. Now let us discuss about SQLPlus. If you connect sqlplus username/password from the unix prompt, the ps command will display the password in the output.

$ sqlplus scott/tiger

SQL*Plus: Release 11.1.0.7.0 - Production on Fri Jul 22 01:29:06 2011

Copyright (c) 1982, 2008, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

ps -ef|grep sqlplus
  oracle 29912 27526   0 01:27:09 pts/2       0:00 grep sqlplus
  oracle 27526 27411   0 23:07:00 pts/2       0:00 sqlplus scott/tiger

Here are the some options to hide SQLPlus password from unix ps command.

Option#1 Use sqlplus /nolog

This is the simple and common method to hide password from ps command output. Once you enter into sqlplus  prompt use connect command to sqlplus.

$ sqlplus /nolog

SQL*Plus: Release 11.1.0.7.0 - Production on Fri Jul 22 01:31:43 2011

Copyright (c) 1982, 2008, Oracle.  All rights reserved.

SQL> connect scott/tiger
Connected.

Option#2

echo <user password>|sqlplus -s <username> @test.sql
Example:

echo tiger|sqlplus -s scott @test.sql

Option#3

Oracle has also provided another method which pads the command line to sqlplus (or any other executable) with just enough blanks to fool the unix ps command into not displaying the actual command line.  To do this, get hide.c (and os.h, if your machine doesn't have it), compile it on your system (the exact compile syntax may vary), move the original sqlplus binary to sqlplus.hide (must have a ".hide" extension), and create a link from "hide" to "sqlplus" (which will run the "hide" binary whenever "sqlplus" is entered on the command line; "hide" will then run "sqlplus.hide" with the command line padded with blanks). 

Example
cc hide.c -o hide
cp hide $ORACLE_HOME/bin
cd $ORACLE_HOME/bin
mv sqlplus sqlplus.hide
ln hide sqlplus

After doing this, any time anyone executes sqlplus with command line options, those command line options will not be visible from ps.  Be aware, though, if you apply any patches to your Oracle bin directory, you must first do a "mv sqlplus.hide sqlplus" to put back the original sqlplus binary into the Oracle bin directory.  Then, after applying the patches, you can once again run through those steps above to create a link from the hide binary to sqlplus.  (Note: I haven't tried using hide.c, since I always use the pipe method, but, I've seen this at several places on the web and listservs, so, I'm including it here in case you want to try it out.)

DBA Tips Data Pump Reference

0 comments:

Post a Comment

 

dba topics. Copyright 2011-16 All Rights Reserved | Site Map | Contact | Disclaimer | Google