Jason -- Thanks for the question regarding "Hiding the password", version

Submitted on 2-May-2000 13:21 Central time zone

Tom's latest followup | Bookmark | Bottom

Last updated 30-Nov-2009 15:18

You Asked

I usually write shell scripts that
invoke SQL*Plus and friends from the
command line.  I put the username and
password on the command line since that
info is kept in an environment variable,
since it can potentially change.  However,
I don't like the fact that the "ps"
command can display the command line of
the process to other users, since it
will reveal the username and password
for this account!  How can I mask this
information from "ps" without having
to resort to keeping the username and
password in the SQL script?

and we said...

Well, my favorite solution to this is to use an "identified externally" account.  For 
example, I've set:


NAME                           TYPE    VALUE
------------------------------ ------- --------------------
os_authent_prefix              string  ops$

in my init.ora.  I then:

create user ops$tkyte identified externally;

This lets me:

$ id       
uid=12997(tkyte) gid=1(other)

$ sqlplus /
SQL*Plus: Release 8.1.5.0.0 - Production on Fri Mar 10 19:28:46 2000
(c) Copyright 1999 Oracle Corporation.  All rights reserved.
Connected to:
Oracle8i Enterprise Edition Release 8.1.5.0.0 - Production
With the Partitioning and Java options
PL/SQL Release 8.1.5.0.0 - Production

ops$tkyte@8i> show user
USER is "OPS$TKYTE"
ops$tkyte@8i> 


I do not need a username password anymore (i can still use them but I can always use / to 
log in as my).  This is perfect for cron jobs, at jobs and the like.  You have to be 
logged into unix to become that account.


see

http://www.peachnet.edu/oiit/support/oracle/General/Unix/HideArgs.html

for another solution.

Reviews

How about for NT? April 5, 2001 - 9am Central time zone

Bookmark | Bottom | Top

Reviewer: Victor Lobaton from The Hague, The Netherlands

I was happy to know that there is one already for UNIX but how about for NT?  Could you help me how 
to do this in NT?  Since we use a lot of logfiles in our batch jobs the password is always 
dynamically coded in the logfiles as well as the scripts.  Thanks in advance and more power!

Other tricks... August 30, 2001 - 12pm Central time zone

Bookmark | Bottom | Top

Reviewer: andrew from CA, USA

http://www.orafaq.com/faqunix.htm#HIDEPSW

Need more help June 14, 2002 - 1pm Central time zone

Bookmark | Bottom | Top

Reviewer: Nitin from India

The solution which u have given is not of more use for me because I am working in a production 
server and I can not make the user as identified externlly also I need to know how can I prevent to 
echo the password while taking EXPORT not connectiong thru SQLPLUS. If u give me a solution then I 
will be really very thankfut to you.

Followup June 14, 2002 - 2pm Central time zone:

Yes you can -- and yes you should.  

The solution -- os authenticated accounts, period.  We've emailed -- so I know you are on unix -- 
it is trivial, it is very very secure (way more secure then your approach of storing passwords in 
files), and it matters not if you are on production, test or development.  You have a problem 
"passwords available via ps", the solution is "use os authenticated accounts -- no more passwords 
on command lines"

ksh options June 14, 2002 - 5pm Central time zone

Bookmark | Bottom | Top

Reviewer: Connor from UK

on korn shell you can issue

print PASS | oracletool

where 'oracletool' is most of the common oracle ones - exp, imp, sqlplus etc

Followup June 14, 2002 - 8pm Central time zone:

care to expand on that?  give a full example?  (i couldn't get it to work -- I'm a csh guy so maybe 
just more info is needed...)

I think I can help elaborate on the ksh options June 15, 2002 - 1am Central time zone

Bookmark | Bottom | Top

Reviewer: Doug from Danbury, CT USA

Something like this seems to work:
print systempassword | exp full=Y userid=system

An export takes place and with the ps command the password is not visible.

Followup June 15, 2002 - 8am Central time zone:

yup, that appears to work

I still like the os authenticated accounts as you never need to store a password anywhere.  then it 
just becomes 

exe userid=/ full=y

no muss, no fuss -- more secure, easier to manage.

thanks for the followup

Another way to hide password on UNIX June 15, 2002 - 10pm Central time zone

Bookmark | Bottom | Top

Reviewer: TJ Struckus from USA

The following shows a way to hide sqlplus and sqlldr username password from the PS command.  
Granted you still need to have the username and password somewhere because it is not externally 
identified.
 
Let this be you env variables.
---------VARIABLES --------------
export APP_USER=something
export APP_PASS=somepass
export APP_SID=sid


Here is the script with a execute permission.

--------------SCRIPT TO RUN SQL----------
#!/usr/ksh
sqlplus << END_OF_SQL
$APP_USER/$APP_PASS@APP_SID

select * from dual;
END_OF_SQL
exit $?
----------END SCRIPT----------


Here is another for SQLLOAD steps
--------------SCRIPT TO SQLLDR----------
#!/usr/ksh
sqlldr control=contril.ctl data=data.dat << END_OF_SQLLDR
$APP_USER/$APP_PASS@APP_SID
END_OF_SQLLDR
----------END SCRIPT----------

Followup June 17, 2002 - 7am Central time zone:

well, if you are on solaris, fire up your script and then go:

/usr/ucb/ps -auxwwee | grep APP_PASS

and see what you see ;)     

ps can dump the ENVIRONMENT as well.  (very handy trick to know, can be useful to see a processes 
environment -- but for you -- it exposes the password)


EXTERNALLY AUTHENTICATE ACCOUNTS!!!!!

OPS$ accounts and REMOTE_OS_AUTHENT secure? September 30, 2002 - 5pm Central time zone

Bookmark | Bottom | Top

Reviewer: Cynthia from Denver, USA

If I have OPS$ accounts and enable REMOTE_OS_AUTHENT, 
is there a security vulnerability?  
 
Do I need REMOTE_OS_AUTHENT enabled to use db links 
from another database, where the db link does not specify 
the user/password?

Followup October 1, 2002 - 9am Central time zone:

YES.

say you have ops$cynthia setup and it has DBA.  I'm on your network with my linux laptop and 
(having root) create an OS user cynthia.  Hmmm, am I in or am I in?

You do not need remote_os_authent (and in fact that in general WOULDN'T work -- the OS process 
connecting is typically owned by ORACLE, not by you!), you do need password based authentication 
however.

But what about...... October 1, 2002 - 9am Central time zone

Bookmark | Bottom | Top

Reviewer: Robert from Memphis, USA

Tom,

I see that using the externally identified account is a great thing. But how can we connect via a 
client app with these. Don't you have to be on the server in order to use these externally 
identified accounts?

Thanks,

Robert

Followup October 1, 2002 - 10am Central time zone:

Typically yes.  I do not use them for "end users" personally (mostly I guess cause I haven't used 
anything client server for many many many many years).


I use them for ADMINS who are running on the machine.

In windoze -- you can use this safely on a network -- see the NT OS admin guide for details.

October 1, 2002 - 10am Central time zone

Bookmark | Bottom | Top

Reviewer: A reader

"say you have ops$cynthia setup and it has DBA.  I'm on your network with my linux laptop and 
(having root) create an OS user cynthia.  Hmmm, am I in or am I in?"

Please, would you elaborate this? I tried to test connect this way and I couldn't. Can you please 
demonstrate what you say here? Thanks a lot.

Followup October 2, 2002 - 9am Central time zone:

if you have remote_os_authent set to true (says to trust the os credentials forwarded from the 
remote host, sort of like the r* programs -- rlogin, rsh, rexec)

and you are in a unix environment

and you drop a machine into that environment upon which you have root

and you create a user that matches one in the remote database in your OS -- by definition you are 
able to remotely login in using OS authentication to that host.

October 1, 2002 - 11am Central time zone

Bookmark | Bottom | Top

Reviewer: A reader from Hungary

$sqlplus /nolog
SQL>connect username/passwd@connect_string
$ps -ef |grep sqlplus 
end than

Followup October 2, 2002 - 9am Central time zone:

so tell me "reader".

How does that answer the question:

"How can I mask this
information from "ps" without having
to resort to keeping the username and
password in the SQL script?"

(rhetorical question, it doesn't -- your approach implies encoding the username/password into a 
script something they say "we don't want to do")

OS authentication is the RIGHT CHOICE for automated scripts that run on the server like this.

Followup for "ksh options" (Jun 2002) October 1, 2002 - 5pm Central time zone

Bookmark | Bottom | Top

Reviewer: Connor from UK

In korn shell, you can use (for example):

print my_pass | exp userid=system file=...

or

print my_pass | sqlplus system @my_script

Or for my lengthy scripts

print "
connect user/pass
select ...
exit" | sqlplus /nolog

etc etc

Cheers
Connor

AskTom.unixguru.com October 8, 2002 - 2pm Central time zone

Bookmark | Bottom | Top

Reviewer: Juan Guascarancas Pena from Mexico

<quote>
  /usr/ucb/ps -auxwwee
</quote>

You are da man Tom!!!
Almost 4 years as a Unix sysadmin and I didn't know this...

question to implemenet remote security March 10, 2003 - 5pm Central time zone

Bookmark | Bottom | Top

Reviewer: A reader

Hi

My English is not very good.... so I dont understand very much what you said about 
remote_os_authent... is it secure?

I have a question, we have an DBA Admin database which is a centralized repository for all our 
production instances (over 60 production instances), what we normally do is run shell scripts from 
the host where this admin db resides and script loops a instances.ini file which contains all 
production instances SID and for each database it runs several SQL scripts and spool the results to 
plain text files then load the results into the admin db. The password is set in shell variables 
inside shell scripts (we have a .env file which loads the variables at beggining of each shell 
script, all KSH btw), I feel this is not very secure, the unix admin can see my files and therefore 
my passwords which I am not happy so I am thinking of two approaches

1. store password encrypted in a table using dbms_obfuscation but this has a problem, I still need 
to store the seed somewhere so in my scripts can use it to unencrypt the password and use it

2. use remote_os_authent but I have never used this and wondering if it has any security issues, 
you have explained a couple of examples but I dont fully understand :(

Followup March 10, 2003 - 6pm Central time zone:

remote_os_authent is not a very secure setting, no.


or 3) use database links instead of connecting directly to the remote database.


create a schema for each remote database.
create a database link from that user to the remote database.
create synonyms for the remote tables.

How many external users? March 26, 2003 - 12pm Central time zone

Bookmark | Bottom | Top

Reviewer: Marcio from Brazil

Tom, I did not understand this stuff enough.

1. Have I got to set up init parameter os_authent_prefix such 'ops$' always for use externally 
authentication? or any other like 'bla$' or 'bla' either.

2. how many end users with authentication externally could be exists? And, all of them are going to 
use os_authent_prefix, so I gotta set their names like bla<username>?

cheers,
Marcio

Followup March 26, 2003 - 4pm Central time zone:

1) no, you don't have to.  I think you should, but it is not mandatory.

2) 2 Billion.  and yes, if you user bla as the os_authent_prefix, you would need to add that to 
their username.

Thanks for the idea September 22, 2003 - 9am Central time zone

Bookmark | Bottom | Top

Reviewer: Sanjaya Balasuriya from Colombo, Sri Lanka

And Tom,

With OS authentication how can you login to a database as different users ? 
Say I want to login as appuser@mydb as well as system@mydb.
How can I work out such a situation with OS authentication ?

Followup September 22, 2003 - 10am Central time zone:

you can either:


o use a username/password (eg: i can either log in as tkyte to the OS and 

connect /

or i can log in as whomever and 

connect ops$tkyte/password

since I created the user identified by password and set up the ops$ to signify OS authentication.  
you can do both)


o log into the OS as the right person

Passing ops$ password on command line September 23, 2003 - 9am Central time zone

Bookmark | Bottom | Top

Reviewer: Jerry from Washington, DC USA

If I have user mary identified by her pass, and user ops$oracle identified externally, and by 
mypass, mary seems to be able to login by passing her password on the command line, but ops$oracle 
doesn't seem to be able to.  

Is this a restriction placed on externally identified accouunts?

oracle@hp:~> sqlplus mary/herpass

SQL*Plus: Release 9.2.0.1.0 - Production on Tue Sep 23 09:48:08 2003

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


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

SQL> quit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
oracle@hp:~> sqlplus ops$oracle/mypass

SQL*Plus: Release 9.2.0.1.0 - Production on Tue Sep 23 09:48:19 2003

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

ERROR:
ORA-01017: invalid username/password; logon denied


Enter user-name: ops$oracle
Enter password:

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

SQL>

Followup September 23, 2003 - 11am Central time zone:

alter user ops$oracle identified by mypass;


it'll be both.

Can't Get It To Work on Linux September 23, 2003 - 11am Central time zone

Bookmark | Bottom | Top

Reviewer: Jerry from Washington, DC USA

It's not real important, but for some reason, I can't get this to work as I had expected on SuSE 
Linux 8.2 and 9iR2.



oracle@hp:~> sqlplus /

SQL*Plus: Release 9.2.0.1.0 - Production on Tue Sep 23 11:53:02 2003

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


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

SQL> alter user ops$oracle identified by mypass;

User altered.

SQL> quit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
oracle@hp:~> sqlplus ops$oracle/mypass

SQL*Plus: Release 9.2.0.1.0 - Production on Tue Sep 23 11:53:28 2003

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

ERROR:
ORA-01017: invalid username/password; logon denied


Enter user-name: ops$oracle
Enter password:

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

SQL>
SQL> show parameter os_authent

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
os_authent_prefix                    string      ops$
remote_os_authent                    boolean     FALSE

Followup September 23, 2003 - 1pm Central time zone:

think about what $oracle means in "shell" :)


try

sqlplus 'ops$oracle/mypass'


else, you are just getting

sqlplus ops$oracle/mypass -> sqlplus ops/mypass

Is there anything you don't know? September 23, 2003 - 3pm Central time zone

Bookmark | Bottom | Top

Reviewer: Jerry from Washington, DC USA

Followup September 23, 2003 - 5pm Central time zone:

i don't know...

i guess that means -- yes, there is.

I learn something new about Oracle every day.  I learn something new about stuff every day....

Does this work in window NT March 30, 2004 - 8pm Central time zone

Bookmark | Bottom | Top

Reviewer: Jennifer Chen from Viena, VA USA

Hi Tom,

Thank you for teaching us new things everyday. I have a batch job invoke sqlldr on windows, which 
loads data into DB every month, and would like to use your solution.

Does it work in window platform? I modified init.ora:

SQL> show parameter os_authent_prefix

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
os_authent_prefix                    string      ops$

SQL> CREATE USER ops$chenje IDENTIFIED externally;

User created.

SQL> grant dba to ops$chenje;

Grant succeeded.

'chenje' is my NT login account.

When I tried:

C:\Documents and Settings\chenje>set ORACLE_SID=aliasdev

C:\Documents and Settings\chenje>sqlplus /

SQL*Plus: Release 9.2.0.4.0 - Production on Tue Mar 30 19:41:32 2004

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

ERROR:
ORA-01017: invalid username/password; logon denied


Enter user-name:

Did I miss something here?

Thanks again for your time and help.

Followup March 31, 2004 - 8am Central time zone:

http://download-west.oracle.com/docs/cd/B10501_01/win.920/a95492/authen.htm#1004903

windows is not as easy as unix/linux

(i love saying that)

sqlplus / March 31, 2004 - 9am Central time zone

Bookmark | Bottom | Top

Reviewer: Jennifer Chen from Viena, VA USA

Hi Tom,

I followed metalink Note:60634.1 (WIN: Setup O/S Authentication) to be able to:

C:\sqlplus /nolog
SQL>connect / as sysdba
connected.
SQL> 

But what I really need is to be able to:

sqlplus /

OR

C:\sqlldr userid=/ as sysdba@aliasdev control='L:\fbifiles\FBIBILLING.CTL' 
log='L:\fbifiles\FBIBILLING.LOG'

Do you know how from there?

Thank you.

Followup March 31, 2004 - 9am Central time zone:

did you read through the above link i supplied?

Thank You March 31, 2004 - 11am Central time zone

Bookmark | Bottom | Top

Reviewer: Jennifer Chen

Yeah. It's operating systems specific. I tried this and worked:

sqlldr '/ AS SYSDBA' control='L:\fbifiles\FBIBILLING.CTL' log='L:\fbifiles\FBIBILLING.LOG'

After I got your idea, I am pretty much able to accomplish what I need.

Really appreciate your time and help.

Need comments June 16, 2004 - 10am Central time zone

Bookmark | Bottom | Top

Reviewer: Yogesh B from Pune, India

After reading the thread above I concluded following .. 

my init.ora
os_authent_prefix = ""

I have unix OS account as testuser. Oracle user with same name created by following SQL 

create user testuser identified externally;
alter user testuser identified by testpass;

So I login to OS with testuser/pass

and now for connecting to oracle user I say 

connect /

and it works

Scenario 2

I don't have OS user with the same name as of oracle user. So in this scenario I don't have any 
other option but to connect using 

sqlplus testuser/testpass 

or 

connect as some user which has oracle equivalent user

sqlplus / 

followed by

connect testuser/testpass 

Precisely if I don't have OS and oracle user with same name I can't use connect /
Please correct me if Im wrong

Followup June 16, 2004 - 1pm Central time zone:

if you do not have an oracle user named after the OS user, then yes, you obviously cannot use OS 
authentication.

Object Permissions June 16, 2004 - 3pm Central time zone

Bookmark | Bottom | Top

Reviewer: Al from Kenosha, WI

How timely to have found this topic, as we've been in process of implementing this solution!

There is one part about trying to retrofit this solution into existing applications that seems a 
bit messy, though, is the need to grant select/execute or whatever to every ops$ user that will be 
accessing a given schema object; manual grants to objects just leaves too much room for error!  
Even with a generic user to run jobs, still opens the door for error.  Does this just go with the 
territory, or is there a standard/recommended way of doing this that bypasses the need for 
individual object grants?

Also, I'm not able to follow the link provided for peachnet ... is there another resource for this?

Thanks in advance ... asktom.com is my favorite resource!

Al

Followup June 16, 2004 - 4pm Central time zone:

google hideargs, many places...



have you looked at ROLES?  you do not need to grant to each enduser....

RE: Object Permissions June 17, 2004 - 11am Central time zone

Bookmark | Bottom | Top

Reviewer: Al from Kenosha, WI

Right ... but DO need to grant to each object individually; no help for that, right?

Followup June 17, 2004 - 1pm Central time zone:

correct, trivial to automate this

begin
  for x in ( select object_name from user_objects where object_type = 'PACKAGE' )
  loop
     execute immediate 'grant execute on ' || x.object_name || ' to some_role';
  end loop;
end;

sqlldr problem July 23, 2004 - 10am Central time zone

Bookmark | Bottom | Top

Reviewer: Tommy from Toronto Canada

Hi Tom,

I am trying to setup an external user with tables access granted by ROLE instead of by USER level. 
When I run the sqlldr command, it said it could not find the table being accessed. However, if I 
grant the tables directly to the user instead of by the role, it works fine. My system info is as 
follows:-

Red Hat Linux release 9 (Shrike)
Kernel 2.4.20-6smp on an i686
SQL*Loader: Release 9.2.0.4.0

Followup July 23, 2004 - 3pm Central time zone:

please show me step by step how to reproduce, here is my example showing it seems to work for me 
under the same configuration:


sys@ORA9IR2> @connect "/ as sysdba"

sys@ORA9IR2> drop role a_role;
Role dropped.
 
sys@ORA9IR2> create role a_role;
Role created.
 
sys@ORA9IR2> @connect scott/tiger

scott@ORA9IR2> grant all on dept to a_role;
Grant succeeded.
 
scott@ORA9IR2> @connect "/ as sysdba"

sys@ORA9IR2> drop user ops$tkyte cascade;
User dropped.
 
sys@ORA9IR2> create user ops$tkyte identified externally;
User created.
 
sys@ORA9IR2> grant create session to ops$tkyte;
Grant succeeded.
 
sys@ORA9IR2> grant a_role to ops$tkyte;
Grant succeeded.
 
sys@ORA9IR2> !cat dept.ctl
LOAD DATA
INFILE *
INTO TABLE scott.dept
REPLACE
FIELDS TERMINATED BY '|'
(
deptno
,dname
,loc
)
BEGINDATA
10|ACCOUNTING|NEW YORK
20|RESEARCH|DALLAS
30|SALES|CHICAGO
40|OPERATIONS|BOSTON
 
sys@ORA9IR2> !sqlldr / dept.ctl
 
SQL*Loader: Release 9.2.0.4.0 - Production on Fri Jul 23 15:11:37 2004
 
Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.
 
Commit point reached - logical record count 4
 
sys@ORA9IR2> !cat dept.log
 
SQL*Loader: Release 9.2.0.4.0 - Production on Fri Jul 23 15:11:37 2004
 
Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.
 
Control File:   dept.ctl
Data File:      dept.ctl
  Bad File:     dept.bad
  Discard File:  none specified
  
 (Allow all discards)
 
Number to load: ALL
Number to skip: 0
Errors allowed: 50
Bind array:     64 rows, maximum of 256000 bytes
Continuation:    none specified
Path used:      Conventional
 
Table SCOTT.DEPT, loaded from every logical record.
Insert option in effect for this table: REPLACE
 
   Column Name                  Position   Len  Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
DEPTNO                              FIRST     *   |       CHARACTER
DNAME                                NEXT     *   |       CHARACTER
LOC                                  NEXT     *   |       CHARACTER
 
 
Table SCOTT.DEPT:
  4 Rows successfully loaded.
  0 Rows not loaded due to data errors.
  0 Rows not loaded because all WHEN clauses were failed.
  0 Rows not loaded because all fields were null.
 
 
Space allocated for bind array:                  49536 bytes(64 rows)
Read   buffer bytes: 1048576
 
Total logical records skipped:          0
Total logical records read:             4
Total logical records rejected:         0
Total logical records discarded:        0
 
Run began on Fri Jul 23 15:11:37 2004
Run ended on Fri Jul 23 15:11:38 2004
 
Elapsed time was:     00:00:00.14
CPU time was:         00:00:00.01
 
sys@ORA9IR2> !id
uid=500(tkyte) gid=500(tkyte) 
groups=500(tkyte),502(ora9ir2),503(ora9ir1),504(ora8ir3),505(dba),501(ora10g)

More on sqlldr problem July 23, 2004 - 1pm Central time zone

Bookmark | Bottom | Top

Reviewer: Tommy from Toronto, Canada

I forgot to mention the problem arises when direct=yes is specified when running sqlldr.

Followup July 23, 2004 - 4pm Central time zone:

so, like I said, give me a test case ok, here is mine again:

ops$tkyte@ORA9IR2> @connect "/ as sysdba"
ops$tkyte@ORA9IR2> set termout off
sys@ORA9IR2> set termout on
sys@ORA9IR2> drop role a_role;
 
Role dropped.
 
sys@ORA9IR2> create role a_role;
 
Role created.
 
sys@ORA9IR2> @connect scott/tiger
sys@ORA9IR2> set termout off
scott@ORA9IR2> set termout on
scott@ORA9IR2> grant all on dept to a_role;
 
Grant succeeded.
 
scott@ORA9IR2> @connect "/ as sysdba"
scott@ORA9IR2> set termout off
sys@ORA9IR2> set termout on
sys@ORA9IR2> drop user ops$tkyte cascade;
 
User dropped.
 
sys@ORA9IR2> create user ops$tkyte identified externally;
 
User created.
 
sys@ORA9IR2> grant create session to ops$tkyte;
 
Grant succeeded.
 
sys@ORA9IR2> grant a_role to ops$tkyte;
 
Grant succeeded.
 
sys@ORA9IR2> !cat dept.ctl
LOAD DATA
INFILE *
INTO TABLE scott.dept
REPLACE
FIELDS TERMINATED BY '|'
(
deptno
,dname
,loc
)
BEGINDATA
10|ACCOUNTING|NEW YORK
20|RESEARCH|DALLAS
30|SALES|CHICAGO
40|OPERATIONS|BOSTON
 
sys@ORA9IR2> !sqlldr / dept.ctl direct=true
 
SQL*Loader: Release 9.2.0.4.0 - Production on Fri Jul 23 16:40:47 2004
 
Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.
 
 
Load completed - logical record count 4.
 
sys@ORA9IR2> !id
uid=500(tkyte) gid=500(tkyte) 
groups=500(tkyte),502(ora9ir2),503(ora9ir1),504(ora8ir3),505(dba),501(ora10g)
 
sys@ORA9IR2> !cat dept.log
 
SQL*Loader: Release 9.2.0.4.0 - Production on Fri Jul 23 16:40:47 2004
 
Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.
 
Control File:   dept.ctl
Data File:      dept.ctl
  Bad File:     dept.bad
  Discard File:  none specified
  
 (Allow all discards)
 
Number to load: ALL
Number to skip: 0
Errors allowed: 50
Continuation:    none specified
Path used:      Direct
 
Table SCOTT.DEPT, loaded from every logical record.
Insert option in effect for this table: REPLACE
 
   Column Name                  Position   Len  Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
DEPTNO                              FIRST     *   |       CHARACTER
DNAME                                NEXT     *   |       CHARACTER
LOC                                  NEXT     *   |       CHARACTER
 
 
Table SCOTT.DEPT:
  4 Rows successfully loaded.
  0 Rows not loaded due to data errors.
  0 Rows not loaded because all WHEN clauses were failed.
  0 Rows not loaded because all fields were null.
 
Bind array size not used in direct path.
Column array  rows :    5000
Stream buffer bytes:  256000
Read   buffer bytes: 1048576
 
Total logical records skipped:          0
Total logical records read:             4
Total logical records rejected:         0
Total logical records discarded:        0
Total stream buffers loaded by SQL*Loader main thread:        1
Total stream buffers loaded by SQL*Loader load thread:        0
 
Run began on Fri Jul 23 16:40:47 2004
Run ended on Fri Jul 23 16:40:48 2004
 
Elapsed time was:     00:00:00.39
CPU time was:         00:00:00.01

Busted link... August 3, 2004 - 11am Central time zone

Bookmark | Bottom | Top

Reviewer: J from Vancouver

Hi Tom.

FYI (from the original response):

...
see

http://www.peachnet.edu/oiit/support/oracle/General/Unix/HideArgs.html

for another solution.
...

Apparently, the link is now restricted. Bummer.

Regards,
- J

Followup August 3, 2004 - 11am Central time zone:

google hideargs, you'll find it.

sqlldr / @host_string slower than sqlldr / to load data in same box August 27, 2004 - 6pm Central time zone

Bookmark | Bottom | Top

Reviewer: Ray Cheng from Toronto, ON Canada

We have an AIX box running 2 instances of Oracle for 2 different systems. For my system, I just use 
sqlldr / control=... data=.... For the other system, they are using sqlldr /@host_string, although 
their data file is in the same box. We had an AIX upgrade recently to 5.2. Since then, their sqlldr 
run 10 times slower than before, ours are just fine. After they removed the host string from the 
sqlldr command in their scripts, their loading jobs go back to normal.

I have a feeling that because they specify the host string, even with the external authentication, 
Oracle somehow still tries to go through sqlnet while loading into the same box, therefore slow 
down a lot. There may be something to do with AIX version, before 5.2, Oracle might be able to 
figure out the network can be bypassed even if a hoststring is specific.

Followup August 27, 2004 - 7pm Central time zone:

@hostname is going over the net -- absolutely.

and means they have remote_os_authent set -- meaning they have the least secure system on the 
planet.  you must set that false.

sqlldr / @host_string slower than sqlldr / to load data in same box August 27, 2004 - 6pm Central time zone

Bookmark | Bottom | Top

Reviewer: Ray Cheng from Toronto, ON Canada

We have an AIX box running 2 instances of Oracle for 2 different systems. For my system, I just use 
sqlldr / control=... data=.... For the other system, they are using sqlldr /@host_string, although 
their data file is in the same box. We had an AIX upgrade recently to 5.2. Since then, their sqlldr 
run 10 times slower than before, ours are just fine. After they removed the host string from the 
sqlldr command in their scripts, their loading jobs go back to normal.

I have a feeling that because they specify the host string, even with the external authentication, 
Oracle somehow still tries to go through sqlnet while loading into the same box, therefore slow 
down a lot. There may be something to do with AIX version, before 5.2, Oracle might be able to 
figure out the network can be bypassed even if a hoststring is specific.

It turns out to be a setup problem with the AIX. August 30, 2004 - 11am Central time zone

Bookmark | Bottom | Top

Reviewer: Ray Cheng from Toronto, ON Canada

It took 2 SECONDS to ping the localhost!! No wonder why simply add @hostname can slow down so much. 


Thank a lot for the alert on remote_os_authen. I was shocked to find out actually every database in 
my organization has this thing on.

DB is in unix and User connects thro NT November 11, 2004 - 4pm Central time zone

Bookmark | Bottom | Top

Reviewer: dav from USA

How does OS_AUTHENTICATION work if the database is in UNIX Server and user are connecting from NT 
Client. 
Also we've 8i database.

Followup November 11, 2004 - 4pm Central time zone:

insecurely, don't use it. (remote os authent)

Is it not possible OR it's possible but not secure November 11, 2004 - 5pm Central time zone

Bookmark | Bottom | Top

Reviewer: dav from usa

Is it not possible OR it's possible but not secure?

Followup November 12, 2004 - 6am Central time zone:

possible, not secure.

Is it bad idea? April 18, 2005 - 4pm Central time zone

Bookmark | Bottom | Top

Reviewer: Vinayak from NJ, USA

Is it a bad practice to use '/ as sysdba' to run the export scripts etc.

eg. exp userid=\'/ as sysdba\' <other parameters here>

That way, I am not exposing any passwords to the ps command.

Followup April 18, 2005 - 6pm Central time zone:

well, i would not use "as sysdba" UNLESS I HAD TO.

I'd rather setup an OS account "the_dba" or something and use that with just connect /

as sysdba is rarely needed.

Password, ps and unix. May 31, 2005 - 12pm Central time zone

Bookmark | Bottom | Top

Reviewer: Paul Joyce from Ireland

/usr/ucb/ps -auxwwee | grep APP_PASS

I believe that this will only expose exported environment 
variables.

Try this:
 
vi dblogin.sh
#!/usr/bin/ksh
ORA_LOGIN=user/pass

$chmod u+x dblogin.sh

vi test.sh
#!/usr/bin/ksh
/home/pjoyce/dblogin.sh

sqlplus << EOF
$ORA_LOGIN
select 'Hello world' from dual;
exit
EOF
sleep 30         #  While running go to another window and try ps

$chmod u+x test.sh

then

$./test.sh

and try ps now. (well within 30 seconds!)

Using this in combination with something like sudo will get around the problem.

Also, even with 

sqlplus << EOF >> $logfile
it will not show up in the logfile.

Paul

Followup June 1, 2005 - 7am Central time zone:

you still have passwords in files.  

And it might appear to work on your OS but not their OS or my OS and so on.

I'll stick with OS authentication, no passwords in files.

What about when using JDBC connections July 14, 2005 - 10am Central time zone

Bookmark | Bottom | Top

Reviewer: Orlando Reyes from Minnetonka, MN

Tom,

We have this application that needs to connect to more than one Oracle DB, so what it does is it 
reads a property file to get the right user/password combination (stored in the clear) and then 
it builds the appropriate JDBC connection, indicating host, port and user/pwd combination.

Question is how can we use the identified externally option to solve this issue? Could the JDBC 
connection be creating just indicating the user if this was identified externally? What would be 
the syntax to dynamically create the 2-3 DB connections we might need on a given run? (this java 
application is running on batch, for the online one we use data sources and password is not an 
issue).

Any comments/ideas would be much appreciated.

As usual, thanks a lot.

Orlando

Followup July 14, 2005 - 11am Central time zone:

if you are doing this over the network, you do NOT want to use OS authentication.


If you do, I'll just drop my linux box into your network, get in as root, create a user.......

What's the way to go then? July 14, 2005 - 11am Central time zone

Bookmark | Bottom | Top

Reviewer: Orlando Reyes from Minnetonka, MN

Yes we are on the network, so I won't use that option, but, what would be a good (or best) solution 
then? 

The problem I have is that sometimes for trouble shooting, developers might have access to the file 
with the passwords in it, even though it is not the normal situation.

Thanks,

Orlando

Followup July 14, 2005 - 12pm Central time zone:

database links come into play?  instead of java connecting here, there and everywhere, it connects 
to one thing and runs a stored procedure.

I need Jave developers to buy into it :-) July 14, 2005 - 1pm Central time zone

Bookmark | Bottom | Top

Reviewer: Orlando Reyes from Minnetonka, MN

Definitely that would work, the problem is to go against the Java Paradigm and their Best 
Practices, which are We dont want Oracle (PL/SQL) stored procedure/functions at all; we know 
better.

Thanks for you input though and if you can think of any other solutions please let me know.

Orlando

December 16, 2005 - 10am Central time zone

Bookmark | Bottom | Top