Simpler Navigation for Servers and Operating Systems
Completed: a much simpler Servers and Operating Systems section of the Community. We combined many of the older boards, so you won't have to click through so many levels to get at the information you need. Check the consolidated boards here as many sub-forums are now single boards.
General
cancel
Showing results for 
Search instead for 
Did you mean: 

sqlplus string to connect with out showing in ps command

SOLVED
Go to solution
Ratzie
Super Advisor

sqlplus string to connect with out showing in ps command

I am trying to reconfigure our scripts to prevent the showing of user name and password in the ps command.
But when I try I get an error:
Original:
sqlplus -s user1/userpw < SELECT tn from TN_TABLE WHERE TN = '$i';
yada yada...

What I am trying to do:
sqlplus -s < connect user1/userpw
yada, yada

ERROR:
SP2-0306: Invalid option.
Usage: CONN[ECT] [logon] [AS {SYSDBA|SYSOPER}] | [INTERNAL]
where ::= [/][@] | /
11 REPLIES
TwoProc
Honored Contributor
Solution

Re: sqlplus string to connect with out showing in ps command

sqplus /nolog <connect user1/userpw
SELECT tn from TN_TABLE WHERE TN = '$i';
yada yada...
exit
EOF
We are the people our parents warned us about --Jimmy Buffett
Ratzie
Super Advisor

Re: sqlplus string to connect with out showing in ps command

Great works!
Is there a way to get rid of the extra fluff on feed back. I am piping to a file:

Extra fluff:
SQL*Plus: Release 8.1.7.0.0 - Production on Thu Jun 30 13:00:25 2005

(c) Copyright 2000 Oracle Corporation. All rights reserved.

SQL> Connected.
SQL>
no rows selected

SQL> Disconnected from Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production
With the Partitioning option
JServer Release 8.1.7.4.0 - Production
TwoProc
Honored Contributor

Re: sqlplus string to connect with out showing in ps command

You already had that part...

sqplus -s /nolog <connect user1/userpw
SELECT tn from TN_TABLE WHERE TN = '$i';
yada yada...
exit
EOF
We are the people our parents warned us about --Jimmy Buffett
Ratzie
Super Advisor

Re: sqlplus string to connect with out showing in ps command

yes but it still returns information on every disconnect, if I have 7000 records to check...

SQL> Disconnected from Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production
With the Partitioning option
JServer Release 8.1.7.4.0 - Production
TwoProc
Honored Contributor

Re: sqlplus string to connect with out showing in ps command

Wow, I'm on 9i, and when I use the "-s" flag, it doesn't show what you're showing. But, while we're at it, why would disconnect and rec onnect 7000 times? Wouldn't just one connection with 7000 subsequent commands and then a single exit do it for you?
We are the people our parents warned us about --Jimmy Buffett
Ratzie
Super Advisor

Re: sqlplus string to connect with out showing in ps command

But I am running a while loop to a huge file.

cat file |while read i
do
echo $i
sqlplus /nolog < CONNECT $LOGONID
SELECT tn from tn_table WHERE TN = '$i';
EOF
done
Ratzie
Super Advisor

Re: sqlplus string to connect with out showing in ps command

This is on 8.1.4 if there is a better way to check this OI am all open to suggestions.

Yogeeraj_1
Honored Contributor

Re: sqlplus string to connect with out showing in ps command

hi,

one work around would be to spool to a file in the sqlplus then use another file which you would stuff all the outputs from sqlplus, i.e. something like

cat /dev/null > myresults.lis
cat file |while read i
do
echo $i
sqlplus /nolog <CONNECT $LOGONID
spool tmpfile.lis
SELECT tn from tn_table WHERE TN = '$i';
spool off
EOF
cat tmpfile.lis >> myresults.lis
done

hope this helps!
regards
yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Yogeeraj_1
Honored Contributor

Re: sqlplus string to connect with out showing in ps command

hi again,

at the sqlplus level, you can also use the SQLPLUS commands like "set heading off" etc.
to remove any other unimportant info which of course you can also do at script level.

good luck

regards
yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Ariel Cary
Frequent Advisor

Re: sqlplus string to connect with out showing in ps command

The best way to prevent ps from showing your password is to user externally authenticated users in the database. Basically, the OS authenticates you not the database. Take a look at http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96521/users.htm#17308.

Then you do

acary@srv> sqlplus /

SQL*Plus: Release 9.2.0.6.0 - Production on Fri Jul 1 10:22:44 2005

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production

SQL> sho user
USER is "OPS$ACARY"
SQL>

It also works on 8i.

Regards,

ARC
Ariel Cary
Frequent Advisor

Re: sqlplus string to connect with out showing in ps command

be sure to set remote_os_authent=false (which is the default in 8i, 9i) to prevent undesired access from remote clients.

ARC