Help with SQL*Plus Connection

[danielle]

I "inherited" these scripts which are used to access an Oracle DB on a test envoiroment. Originaly someone from our security team would add an system user and password to the otherwise finished script and encrypt the whole thing. The encrypted file (script.x) would then be scheduled with Control-M.

Recently we decided to test new ways to connect to the DB and managed the following solution:

Code: Select all

${ORACLE_HOME}/bin/sqlplus "`decrypt -a 3des -k ${DIR_CHAVES}/key.3des.aga -i ${DIR_CHAVES}/login.aga`"@orahmp01 <<EOF

Where login.aga is an encrypted file with DB user and password, key.3des.aga the key file for decryption, ${DIR_CHAVES} stores the path to the key diractory and orahmp01 is the DB instance.

It does work, however the system user and password are visible as clear text on the job sysout in Control-M.

Does anyone know how I can hide the decrypt output (user and password) and still logon to the database?

Thanks in advance.

[hipikll]

I hope, I will be right and helpful:

In agent configuration (ctmunixcfg) you can set the -n flag as parameter for running specified shell.
This parameter causes, that no commands will be included in jobs sysout. In this case - no post processing is possible (as manual says).

Or try to set this parameter in the script:

#!/bin/sh -n
#!/bin/ksh -n

But i havent tried it yet.

OR
can you use the CTM for Databases?
If you are running version 6.4, you can use it for free, as it is in bundle. From this addon, you can do a DB jobs very nicely with no additional password need.

[danielle]

Hi, hipikll, thank you for your answer.

Unfortunately

Code: Select all

 #!/bin/sh -n

didn't work.

We have Control-M version 6.3.01 and I'm really new to this, so I don't know how to go about setting the -n flag as parameter.

[hipikll]

The -n flag for shell is available via the ctm agent utility ctmunicfg.
You have to login to the agent machine under agent account and run it.

Maybe the most easy way for you is to:
In post processing of your job, there is a Sysout handling field.
Choose to delete the sysout (noone will see it after job finished) or move it to location, where only administrator (oracle:dba) can read it and from CTM it will appear as non existing sysout. If you will be using the Move, then you need to remember have correct rights over the destination folder (or your job need to have the right owner).

[danielle]

Maybe the most easy way for you is to:
In post processing of your job, there is a Sysout handling field.
Choose to delete the sysout (noone will see it after job finished) or move it to location, where only administrator (oracle:dba) can read it and from CTM it will appear as non existing sysout. If you will be using the Move, then you need to remember have correct rights over the destination folder (or your job need to have the right owner).

Hi, hipikll, thanks again

Since the script creates its own log without writing sensible information in it I used the delete option of sysout handling as you recomended and now everything is working fine.

[rahulsehgal]

Hi,

Have you tried running SQL command in Silent mode?

like :-

${ORACLE_HOME}/bin/sqlplus -s "`decrypt -a 3des -k ${DIR_CHAVES}/key.3des.aga -i ${DIR_CHAVES}/login.aga`"@orahmp01 <<EOF


Regards,
Rahul Sehgal

[baralem]

Another way for hiding password on linux/unix is using "set -x" and "set +x" between the lines where a password is used. Also, if you connect to database using: sqlplus user/pass @file.sql you'll see the username and password on unix process list (ps -ef | grep sqlplus). You can avoid this using "/nolog" option and putting connect data in .sql file.


set +x
PASSWD=`cat pwd.file`
set -x
................
................
set +x
sqlplus /nolog @file.sql
set -x

regards
martin

[danielle]

Hi, all!

"-s" didn't help (I don't know why, I thought it would), but set +x .......... set -x
was perfect as the test team really wants the sysout even though all the relevant data is provided in a log file created by the script

Thank you so much!