topic Re: sqlplus string to connect with out showing in ps command in Operating System - HP-UX

sqlplus string to connect with out showing in ps command

Ratzie — Thu, 30 Jun 2005 12:40:20 GMT

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 ::= [/][@] | /

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

TwoProc — Thu, 30 Jun 2005 12:46:58 GMT

sqplus /nolog <connect user1/userpw
SELECT tn from TN_TABLE WHERE TN = '$i';
yada yada...
exit
EOF

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

Ratzie — Thu, 30 Jun 2005 13:02:12 GMT

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

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

TwoProc — Thu, 30 Jun 2005 13:24:12 GMT

You already had that part...

sqplus -s /nolog <connect user1/userpw
SELECT tn from TN_TABLE WHERE TN = '$i';
yada yada...
exit
EOF

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

Ratzie — Thu, 30 Jun 2005 13:43:31 GMT

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

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

TwoProc — Thu, 30 Jun 2005 13:59:14 GMT

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?

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

Ratzie — Thu, 30 Jun 2005 14:14:39 GMT

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

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

Ratzie — Thu, 30 Jun 2005 14:15:25 GMT

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

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

Yogeeraj_1 — Thu, 30 Jun 2005 23:41:07 GMT

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

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

Yogeeraj_1 — Thu, 30 Jun 2005 23:45:45 GMT

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

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

Ariel Cary — Fri, 01 Jul 2005 09:27:57 GMT

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

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

Ariel Cary — Fri, 01 Jul 2005 09:39:10 GMT

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

ARC