Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

Most Oracle tools and scripts request that you pass them the password via the command line - where everyone on the same machine can see them. Example:

exp <user>/<password> ...

Is there a way to invoke those commands (like sqlplus, imp, exp) from a script without compromising security?

share|improve this question

2 Answers 2

up vote 4 down vote accepted

For imp and exp you can supply parameters in a file. You can change permissions on this file in the file system to make it readable only by the people you choose. Check the documentation for the PARFILE command line option. The option you will want to include in the PARFILE is the USERID option.

For SQLPLUS you can specify /nolog option and supply connection command as the first line of the sql script you are executing.

sqlplus /nolog @myscript.sql

And then in myscript.sql:

connect user/pass@tnsname
...

For SQLPlus I usually recommend creating a file or using a "here" document so you can protect the username/password from being viewed with "ps -ef" command in Unix/Linux. If the username/password is contained in a script file or sql file you can protect using appropriate user/group read permissions. Then you can keep the user/pass inside the file like this in a shell script:

sqlplus -s /nolog <<EOF 
connect user/pass 
select blah from blah; 
quit 
EOF
share|improve this answer
1  
+1, the best way if you're not using OS authentication and you can't require users to enter input at runtime. –  DCookie Nov 17 '09 at 15:59
    
+1 And I can do $(grep "$USER:" ~/.orapass | cut -d: -f2) in the here document! –  Aaron Digulla Nov 18 '09 at 9:01
1  
See this question for a way to safely create a parfile: stackoverflow.com/questions/687014/… –  Aaron Digulla Nov 18 '09 at 9:33
    
Aaron, that is a great way to keep your passwords in a centrally located (and locked down) area. –  David Mann Nov 18 '09 at 15:33

You can use "identified externally" to have authentication done at the operating system level.

See: Advanced Security Administrator's Guide

for details.

share|improve this answer
    
+1, This is a very good way, but it could be a large change in the way a shop does business. –  DCookie Nov 17 '09 at 15:59
    
Excellent suggestion. Unfortunately, this is out of scope for our admins :( –  Aaron Digulla Nov 18 '09 at 9:00

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.