Re: Best approach to retrieve Connect String

From: Mark Bole <makbo_at_pacbell.net>
Date: Sun, 15 Aug 2004 20:34:36 GMT
Message-ID: <wNPTc.6601$Qr6.5282@newssvr29.news.prodigy.com>

Daniel Morgan wrote:

> Wes Brooks wrote:
> 

>> Hello expert,
>>
>> My environment has 10 Oracle databases. The job is run called up by
>> UNIX script. The job may need to sign on with different User IDs to
>> different Oracle database instance in order to get the batch job
>> running. To avoid to logon with a wrong database instance, the
>> current practice is to hard-code the logon ID, password, and the
>> Connect String on the UNIX script.
>>
>> If I want to remove the hard-code of the logon information, would you
>> mind to share your experience how to handle this change? How to set
>> up the UNIX User environment based on the job?

> 
> 
> I agree with Hans but also suggest you explore the possibility of using
> externally authenticated accounts such as OPS$DEV, OPS$TEST, OPS$PROD1.

And lastly, if the previous two good suggestions won't work, here's an answer to your original question.

Assuming the unix user 'oracle' and group 'dba' are suitably secure (which they should be anyway), create one or more "password" files with file names matching the user and connect string.

1. One time set-up of "hidden" password for each user and connect:

[as user oracle, your directory structure may vary]

% echo "my_password" > ${ORACLE_BASE}/local/mysid.world.myuser.passwd % chmod ug+r,o-rwx ${ORACLE_BASE}/local/mysid.world.myuser.passwd

[the above makes the file readable only by owner and group]

2. Skeleton shell script for Solaris or Linux, line wrapping may need fixing:

------[cut here]--------------
#!/bin/bash
# connect information passed as args
CONNECT=$1
USER=$2
if [ ! -e ${ORACLE_BASE}/local/${CONNECT}.${USER}.passwd ] then

     echo "Cannot find file
          ${ORACLE_BASE}/local/${CONNECT}.${USER}.passwd, exiting..."
     exit

fi

/bin/cat ${ORACLE_BASE}/local/${CONNECT}.${USER}.passwd \

      | ${ORACLE_HOME}/bin/sqlplus ${USER}@${CONNECT} \
        @${ORACLE_BASE}/sql/my_script \
      2>&1

------[cut here]--------------

A heck of lot better than hardcoding -- now you can change your passwords on a regular basis without editing every script each time, just the one-line "password" file(s).

For more security, flexibility, and control in scripting, Perl with DBI is highly recommended.

--Mark Bole Received on Sun Aug 15 2004 - 15:34:36 CDT