Home / server / Questions / 85462
Accepted
Aaron Digulla
Asked: 2009-11-18 01:37:29 +0800 CST

How do you use Oracle's tools from scripts?

  • 772

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?

security

2 Answers

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

    See: Advanced Security Administrator's Guide

    for details.

    • 5
  2. Best Answer

    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
    • 4