Shell Programming and Scripting

Hi,
We have a unix shell script which tries login to database. The user name and password to connect to database is stored in a file connection.sql.

Now connection.sql has contents

Code:

def ora_user =&1

CONNECT A_PROXY[&ora_user]/abc123@DEV01

When on UNIX server we connect to database and set spool on and invoke connection.sql .. in the output spooled file we see the password. We want the password to be in encrypted form .. how can we achieve that?

Code:

SQL> set echo on
SQL> set feed on
SQL> set termout on
SQL> set sqlblanklines on
SQL> @GTBIR_DW/dbscripts/connection.sql BIR
SQL> def ora_user =&1
SQL> 
SQL> CONNECT A_PROXY[&ora_user]/abc123@DEV01
Connected.

SQL> spool off

In the spooled file we want something like this instead of displaying the password.

Code:

SQL> CONNECT A_PROXY[&ora_user]/******@DEV01

Thanks,
GR

Are we allowed to know if this is Oracle sql*plus? Connect before spool on? Remove in postprocessing? Access by UNIX login?

Yes, this is Oracle sql*plus. The connect is after spool on. And accessed by UNIX login. One thing that we could do is set echo off .. which will not display the sql statements which are running. But we want to display all the sql statements running.Any way to encrypt/hide the password?

Does it help if you put it on the command line?
Starting SQL*Plus

To connect to a remote database when you start command-line SQL*Plus, include the Oracle Net service name in your SQLPLUS command in the following form:
SQLPLUS HR/your_password@connect_identifierCan you spool on after connect?

We connect after spool. There are statements which have to run with diff login's and hence we keeep on switching user's .. that's when the password is spooled in log file.

Run each different login and query/report in a new or parallel sqlplus to a different scrolling file. It's easier to maintain.

The trick with very flexible tools, and UNIX is that and more, is, you 'can', but sometimes you 'should not'! Features get put in by committee, demanded by rich idiots, installed by money grubbing developers like me, but nobody says you have to use them!

Oracle's sqlplus utility does not put asterisks like that. If you use it interactively, and enter your password when prompted, it will hide all characters, but never put asterisks.
So, if you do want to see those asterisks, then you'll have to process your log file after you're done with your task with sqlplus. I am assuming you do not want to do that. Log files are not supposed to be touched. They are used for auditing and trouble-shooting; you don't tamper with the evidence.

You could avoid entering the password entirely if you configure your client use the external password store. Oracle stores the login credentials in a "wallet" in the client machine in that case. You could then use the "connect" statement like so -

Code:

CONNECT /@db_connect_string

and Oracle determines the login credentials from the wallet itself. More details are in the Oracle Database Security Guide in the documentation.

A simpler technique, however, would be to sandwich the "connect" statement between echo off and on -

Code:

def ora_user =&1
set echo off
CONNECT A_PROXY[&ora_user]/abc123@DEV01
set echo on
....
... rest of the stuff
...

If you feel like adding a diagnostic message at that point, then use the "prompt" command to print something relevant -

Code:

def ora_user =&1
set echo off
prompt Connecting to A_PROXY[&ora_user]@DEV01 now...
CONNECT A_PROXY[&ora_user]/abc123@DEV01
set echo on
....
... rest of the stuff
...