Thanks for the question, Jason .

Asked: May 02, 2000 - 1:21 pm UTC

Last updated: July 01, 2013 - 4:48 pm UTC

Version:

Viewed 50K+ times! This question is

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 Tom 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 </code> http://www.peachnet.edu/oiit/support/oracle/General/Unix/HideArgs.html <code>for another solution.

Rating

(91 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

How about for NT?

Victor Lobaton, April 05, 2001 - 9:37 am UTC

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...

andrew, August 30, 2001 - 12:31 pm UTC

</code> http://www.orafaq.com/faqunix.htm#HIDEPSW <code>

Need more help

Nitin, June 14, 2002 - 1:48 pm UTC

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.

June 14, 2002 - 2:27 pm UTC

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

Connor, June 14, 2002 - 5:40 pm UTC

on korn shell you can issue

print PASS | oracletool

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

June 14, 2002 - 8:47 pm UTC

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

Doug, June 15, 2002 - 1:24 am UTC

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.

June 15, 2002 - 8:08 am UTC

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

TJ Struckus, June 15, 2002 - 10:04 pm UTC

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----------

June 17, 2002 - 7:25 am UTC

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?

Cynthia, September 30, 2002 - 5:02 pm UTC

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?

October 01, 2002 - 9:38 am UTC

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......

Robert, October 01, 2002 - 9:58 am UTC

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

October 01, 2002 - 10:12 am UTC

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.

A reader, October 01, 2002 - 10:47 am UTC

"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.

October 02, 2002 - 9:23 am UTC

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.

A reader, October 01, 2002 - 11:16 am UTC

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

October 02, 2002 - 9:31 am UTC

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)

Connor, October 01, 2002 - 5:11 pm UTC

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

Juan Guascarancas Pena, October 08, 2002 - 2:44 pm UTC

<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

A reader, March 10, 2003 - 5:23 pm UTC

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 :(

March 10, 2003 - 6:22 pm UTC

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?

Marcio, March 26, 2003 - 12:25 pm UTC

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

March 26, 2003 - 4:30 pm UTC

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

Sanjaya Balasuriya, September 22, 2003 - 9:55 am UTC

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 ?

September 22, 2003 - 10:59 am UTC

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

Jerry, September 23, 2003 - 9:52 am UTC

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>

September 23, 2003 - 11:08 am UTC

alter user ops$oracle identified by mypass;


it'll be both.

Can't Get It To Work on Linux

Jerry, September 23, 2003 - 11:58 am UTC

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

September 23, 2003 - 1:52 pm UTC

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?

Jerry, September 23, 2003 - 3:04 pm UTC

September 23, 2003 - 5:38 pm UTC

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

Jennifer Chen, March 30, 2004 - 8:02 pm UTC

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.

March 31, 2004 - 8:17 am UTC

</code> http://docs.oracle.com/docs/cd/B10501_01/win.920/a95492/authen.htm#1004903 <code>

windows is not as easy as unix/linux

(i love saying that)

sqlplus /

Jennifer Chen, March 31, 2004 - 9:37 am UTC

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.

March 31, 2004 - 9:48 am UTC

did you read through the above link i supplied?

Thank You

Jennifer Chen, March 31, 2004 - 11:46 am UTC

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

Yogesh B, June 16, 2004 - 10:05 am UTC

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

June 16, 2004 - 1:02 pm UTC

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

Object Permissions

Al, June 16, 2004 - 3:30 pm UTC

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

June 16, 2004 - 4:03 pm UTC

google hideargs, many places...

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

RE: Object Permissions

Al, June 17, 2004 - 11:11 am UTC

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

June 17, 2004 - 1:43 pm UTC

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

Tommy, July 23, 2004 - 10:09 am UTC

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

July 23, 2004 - 3:18 pm UTC

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)

Busted link...

J, August 03, 2004 - 11:47 am UTC

Hi Tom.

FYI (from the original response):

...
see
</code> http://www.peachnet.edu/oiit/support/oracle/General/Unix/HideArgs.html <code>
for another solution.
...

Apparently, the link is now restricted. Bummer.

Regards,
- J

August 03, 2004 - 11:56 am UTC

google hideargs, you'll find it.

sqlldr / @host_string slower than sqlldr / to load data in same box

Ray Cheng, August 27, 2004 - 6:00 pm UTC

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.

August 27, 2004 - 7:27 pm UTC

@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

Ray Cheng, August 27, 2004 - 6:02 pm UTC

It turns out to be a setup problem with the AIX.

Ray Cheng, August 30, 2004 - 11:35 am UTC

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

dav, November 11, 2004 - 4:09 pm UTC

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

November 11, 2004 - 4:29 pm UTC

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

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

dav, November 11, 2004 - 5:16 pm UTC

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

November 12, 2004 - 6:52 am UTC

possible, not secure.

Is it bad idea?

Vinayak, April 18, 2005 - 4:16 pm UTC

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.

April 18, 2005 - 6:39 pm UTC

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.

Paul Joyce, May 31, 2005 - 12:57 pm UTC

/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

June 01, 2005 - 7:46 am UTC

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

Orlando Reyes, July 14, 2005 - 10:57 am UTC

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

July 14, 2005 - 11:22 am UTC

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?

Orlando Reyes, July 14, 2005 - 11:47 am UTC

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

July 14, 2005 - 12:43 pm UTC

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 :-)

Orlando Reyes, July 14, 2005 - 1:02 pm UTC

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

A reader, December 16, 2005 - 10:13 am UTC

Tom,

We are having a sticky situation with exteranl accounts.
We are running Oracle 9.2 on AIX 5.3 unix box. We have tons of scripts, where password is hardcoded.
We decided have external user authentication mechanism.
When we change the user to external user, it works, but we are not able to log in as "username@dbname/password"
from sqlplus. Our DBA altered the user and added the password, now we cannot connect as external user.
Is there is something we are missing?? Please advise.

December 16, 2005 - 12:58 pm UTC

if you use os_authent_prefix, you can do this

I can

connect /
connect OPS$tkyte/foobar
connect OPS$tkyte/foobar@remote


but if you just use "tkyte", it cannot work. It doesn't know you want to use OS authentication for that account anymore.

A reader, December 16, 2005 - 11:32 am UTC

Hi Tom,

Let me rephase my last posting.
Can we have an password set on external accounts?
We need password for couple of reasons. One is, we have tons of scripts. We would like to make changes in phases, when all scripts are modified, then change the password.
Other reason is to have db links on the account.

Is this is possible? Please advise.

December 16, 2005 - 1:01 pm UTC

see above, with os_authent_prefix in use - sure.

A reader, December 16, 2005 - 3:55 pm UTC

Hi Tom,

We did use os_authent_prefix, but still didn't work.
We can have the accout as external or with the password, not the both.

Our DBA opened a TAR with Oracle and this is the response from the support team:
"This is expected behavior. You cannot have an externally identified user and also have a database
password for that same user. As soon as you alter the user to have a password, you convert the user
from being os authenticated (external) to a database authenticated account.

There is no way to have the same userid authenticated both externally and with a password. It has to be one or the other."

I'm confused. Please clarify.

December 16, 2005 - 4:37 pm UTC

they are wrong.


show me your os_authent_prefix.

Consider:


ops$tkyte@ORA9IR2> connect /
Connected.
ops$tkyte@ORA9IR2> show user
USER is "OPS$TKYTE"
ops$tkyte@ORA9IR2> connect ops$tkyte/foobar
Connected.
ops$tkyte@ORA9IR2> connect ops$tkyte/not_my_password
ERROR:
ORA-01017: invalid username/password; logon denied


Warning: You are no longer connected to ORACLE.


It certainly works - as LONG as you are using the os authent prefix.  This works in 7.0, 7.1, 7.2, 7.3, 8.0, 8ir1, ........ all of them.

A reader, December 19, 2005 - 3:35 pm UTC

Hi Tom,

Any suggestions would be appreciated.
Thank you.

SQL> show parameter os_authent

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
os_authent_prefix                    string      BI


SQL>
SQL> alter user bijboli0 identified externally;

User altered.

SQL> exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.5.0 - 64bit Producti
on
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.5.0 - Production


/home/jboli0 >id
uid=300(jboli0) gid=1(staff) groups=200(mqm),730(seckan)
/home/jboli0 >sqlplus /

SQL*Plus: Release 9.2.0.5.0 - Production on Mon Dec 19 14:26:29 2005

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


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.5.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.5.0 - Production
SQL> 
SQL> alter user bijboli0 identified by jason;

User altered.

SQL>exit

Disconnected from Oracle9i Enterprise Edition Release 9.2.0.5.0 - 64bit Producti
on
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.5.0 - Production
/home/jboli0 >sqlplus /

SQL*Plus: Release 9.2.0.5.0 - Production on Mon Dec 19 14:27:37 2005

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

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


Enter user-name:

December 19, 2005 - 4:09 pm UTC

ops$ - try it with that, as my examples have been.

A reader, December 19, 2005 - 4:42 pm UTC

Hi Tom,

It worked with OPS$. Thanks a lot.
Is there any reason that we have to use OPS$?

December 19, 2005 - 4:48 pm UTC

ops$ is sort of "special", used to be the only game in town - ops is short for OPerating System and ops$ is "known"

A reader, December 21, 2005 - 9:29 am UTC

Hi Tom,

Need your suggestion on this scenario.
In our production database all ETL scripts runs on schema "abc". I have created a new external account "ops$abc" for operators to run the scripts. Userid/password has been removed from the scripts.
I have created synonyms on all objects in the schema abc and granted appropriate privileges to ops$abc. Its working fine. Is this is the correct approach?
This is the first time I am using the OS authentication mechanism. Please advise.

December 21, 2005 - 7:28 pm UTC

private synonyms - great, except for "truncate" all will be OK (I cannot think of other synonym issues off of the top of my head... private synonyms are pretty efficient)

A reader, December 21, 2005 - 10:43 pm UTC

Hi Tom,

Thanks for the advise. Appreciate it a lot.

Script logs into database multiple times as a different user

Jack Mulhollan, April 25, 2006 - 8:09 pm UTC

The previous discussion implies that OS authentication can't solve the case in which a script needs to log into Oracle as a user other than the Unix user. I have some existing shell scripts that log into the database multiple times as a different user. For example:

---------- SCRIPT 01 ----------
#!/usr/bin/ksh

sqlplus bob/DMNWS6PQ @bob_01.sql
sqlplus sam/NUFDRXF5 @sam_01.sql
sqlplus bob/DMNWS6PQ @bob_02.sql

exit
---------- END ----------

The obvious flaw is:

(1) The passwords are visible with "ps".

We have seen one way to solve problem 1:

---------- SCRIPT 02 ----------
#!/usr/bin/ksh

echo "DMNWS6PQ" | sqlplus bob @bob_01.sql
echo "NUFDRXF5" | sqlplus sam @sam_01.sql
echo "DMNWS6PQ" | sqlplus bob @bob_02.sql

exit
---------- END ----------

That makes the passwords invisible to "ps".

However, the approach still has some flaws:

(2) The passwords are hard-coded in plain text in the script.

(2a) If I change the Oracle passwords, I must change (multiple) scripts. This administrative overhead probably leads to a reluctance to change the passwords.

(2b) "Storing the passwords in plain text is a security risk."

That leads to my first question (A): Why is storing a password in plain text a security risk?

I would argue that this script connects to the database as the privileged users bob and sam. Therefore the script must be kept secure. If the script is secure, another user cannot see bob and sam's plain-text passwords. If the script is not secure, then another user can connect to the database as bob or sam (with or without knowing the passwords).

One attempt to solve 2a is to put bob and sam's passwords in environment variables in a login script like this:

---------- LOGIN SCRIPT ----------

export BOBS_PASS="DMNWS6PQ"
export SAMS_PASS="NUFDRXF5"

---------- END ----------

The environment variables may then be used like this:

---------- SCRIPT 03 ----------
#!/usr/bin/ksh

echo "$BOBS_PASS" | sqlplus bob @bob_01.sql
echo "$SAMS_PASS" | sqlplus sam @sam_01.sql
echo "$BOBS_PASS" | sqlplus bob @bob_02.sql

exit
---------- END ----------

This greatly simplifies the administrative issue of changing passwords.

However, you have stated that the environment variable technique is not secure because the environment variables may be visible to other unix commands such as "/usr/ucb/ps -auxwwee". I do not find this to be true on my system. I see some environment variables such as LOGNAME, PATH, and SHELL, but I do not see BOBS_PASS.

My second question is (B): Since "/usr/ucb/ps -auxwwee" does not reveal BOBS_PASS on my system, is the environment variable approach acceptable?

This approach is attractive because it requires the least amount of change. However, it doesn't quite feel right, because of "/usr/ucb/ps -auxwwee", or some other "super ps" that someone may know about.

Here is another idea:

---------- SCRIPT 04 ----------
#!/usr/bin/ksh

echo $(get_password bob) | sqlplus bob @bob_01.sql
echo $(get_password sam) | sqlplus sam @sam_01.sql
echo $(get_password bob) | sqlplus bob @bob_02.sql

exit
---------- END ----------

Here, get_password is a program (or function) that retrieves the passwords from an encrypted file and presents them in plain text.

Question (C): What is the flaw in that plan?

And here is my final idea, which goes full circle back to OS authentication:

1) make the bob and sam Oracle accounts "identified externally" (ops$bob and ops$sam)
2) create corresponding unix accounts (bob and sam)
3) set up /etc/sudoers to allow specific users to run "sudo su - bob" and "sudo su - sam" without a password

Then I could modify the scripts in question to be like this:

---------- SCRIPT 05 ----------
#!/usr/bin/ksh

sudo su - bob << EOF
sqlplus / @bob_01.sql
EOF

sudo su - sam << EOF
sqlplus / @sam_01.sq
EOF

sudo su - bob << EOF
sqlplus / @bob_02.sql
EOF

exit
---------- END ----------

Sam and Bob's passwords are stored nowhere (hashed in Unix). No one even needs to know sam and bob's passwords. Specific Unix users are allowed to connect as sam and bob based on their own Unix credentials.

Question (D): is this idea viable and secure?

Please provide your thoughts, and any alternative ideas. Thanks.

- Jack

April 26, 2006 - 12:04 am UTC

I scanned this quickly - pretty long for a followup/review.

I will comment thusly:

o storing passwords in a script is a bad idea.
o storing passwords in environment variables is a bad idea - because that implies
you are storing them in a script somewhere as well and you might not have been
able to figure out how to see the entire environment, but I bet someone that
really wanted to would.
o using os authentication allows you to not store them in scripts
o storing them encrypted implies there is a program (your get_password) that
someone could use to retrieve them

So, I would opt for using os authentication. to sudo approach IF NECESSARY (you could just have the account bob, sam, whatever run the scripts using cron themselves without making this complicated)

not true

Sokrates, April 26, 2006 - 5:12 am UTC

"storing them encrypted implies there is a program (your get_password) that
someone could use to retrieve them"

not true, why do you think that ?

as far as I know, Oracle stores encrypted passwords in
DBA_USERS and doesn't need either a program to retrieve them

standard authentication works as follows:
- Oracle encrypts password gotten to authenticate
and checks if result equals encrypted pw, right ?

April 26, 2006 - 8:07 am UTC

because the example, ummm, sort of implied it. The example sort of said "we'll run a program that returns the password for us"

no, Oracle does not store encrypted passwords. We store a hashed digest. A user supplies us a user/password and we hash them and compare them to the hash.

So, no, Oracle does not do what you suggest.

You cannnot decrypt the hashed password value you see in dba_users, it is not encrypted.

Socrates ...

Greg, April 26, 2006 - 8:09 am UTC

There's a big difference between Encryption and Hashing ...

passwords are hashed .. not encrypted ... (well, they should be, anyway .. )
:p

Thanks

Jack Mulhollan, April 26, 2006 - 11:18 am UTC

Tom,

Thank you very much for you response. I fully expected you to stick to your guns about OS authentication. I am sincere (albeit longwinded) in seeking the correct solution to my problem.

I believe the root of my problem is that prior to 10g you had to connect as the owner of the objects in order to grant permissions. Some existing scripts create objects in multiple schemas (and grant permissions), thus the scripts were developed to log in and out as different users.

The only other solution (besides sudo) that I can think of is to have a single OS authenticated user with the power to create objects in multiple schemas. The scripts could connect as that user (no one needs to connect as bob and sam). That would require fully qualifying the object names in all the sql scripts. And if I'm not mistaken, it would require 10g in order to do the grants.

Thanks again.

- Jack

April 27, 2006 - 4:14 am UTC

I don't know why those scripts would be run 'in the background' like that then - doesn't seem like you would have a recurring job that would grant all of the time.

Password In Tables

A reader, April 27, 2006 - 12:54 am UTC

Hi Tom,

How to save password in tables in encrypted or in other manner where by key is required to decrypt??

April 27, 2006 - 2:55 pm UTC

I don't do that, it is not "smart".

You are on your own for that. Key management is hard, in 10gR2 we transaparently encrypt/decrypt data for you - but before that, you manage the keys and how you do that - up to you and your needs.

Password In Tables

A reader, April 27, 2006 - 12:55 am UTC

Hi Tom,

How to save password in tables in encrypted or in other manner where by key is required to decrypt??

script to hide password

Robert James Hanrahan, July 04, 2006 - 9:24 am UTC

I found this at www.orafaq.com:

you can pass the username in clear (on terminal)
and then you give the password without seeing it.

</code> http://rjh.keybit.net/oracle/sql_hide_password.sh <code>

Robert Hanrahan

But I would prefer using the parameter set like tom says:

os_authent_prefix string ops$

July 07, 2006 - 8:46 pm UTC

Oh, that won't work on some systems....

ps will show you the command line, INCLUSIVE OF THE ARGUMENTS, of which the username and password are included in this example!!!

if they used

sqlplus /nolog <<EOF
connect $.../$....
blah blah blah
EOF

that would be different.

hiding password

Robert Hanrahan, July 10, 2006 - 9:09 am UTC

In which systems ps would show the parameters passed to the script?

Do you mean solaris?
/usr/ucb/ps -auxwwee | grep Password

I work on Tru64 and HPUX and from what I see there are no
clear password showing with the ps command...

anyway, I change the script:

--
#!/bin/ksh

# script to hide password from sqlplus

export ORACLE_SID=FMS_C
export ORACLE_HOME=/app/oracle/product/9.2.0

echo "Name : \c"
read Name
echo "Password : \c"
stty -echo
read Password
stty echo

sqlplus /nolog <<EOF

connect $Name/$Password

select entity_id from event_archive;

EOF
--

now there is no way I can read what I pass to the script, right?

cheers

Robert Hanrahan

July 10, 2006 - 9:20 am UTC

it varies from OS to OS - as stated.  Yes, Solaris would be one of them.


But, if you have the /usr/ucb/ps and use the wwe (weally wide environment - that is what I call the wwe option), you can see the next problem no?  the environment may be visible.  Probably won't show the "small" environment - the one set by the read commands.

I still personally believe (since this seems to be host based - not over a network), OPS$ accounts rock and roll, no passwords.

passwords with funny characters

Robert Hanrahan, July 11, 2006 - 8:43 am UTC

hi Tom,

let's say I have an account on production who has to import
some data with the following script:

-- start script

#!/bin/ksh

export ORACLE_SID=BKP
export ORACLE_HOME=/app/oracle/product/9.2.0

echo "Name : \c"
read Name
echo "Password : \c"
stty -echo
read Password
stty echo
sqlplus /nolog <<EOF

connect $Name/$Password

truncate table t;

host imp userid=$Name/$Password parfile=whatever.par

EOF

-- end script

if the username is "my_team" and password "team_my" the script Rock and Rolls.
But if the password is something like "team,my_6" the script goes crazy and says:

LRM-00108: invalid positional parameter value 'my'

If I use the "\" in the line:
sys@BKP>host exp 'tim_team/team\,my_6' file=bo.dmp tables=t
it works, but only for exp...

sqlplus does not like the "\"
sys@BKP>connect 'tim_team/team\,my_6'
Enter password:

sqlplus likes this:
sys@BKP> connect tim_team/team,my_6
Connected.

any idea?

July 11, 2006 - 8:01 pm UTC

script doesn't go crazy, script responds as you would fully expect it to.

not sure you should use a "," like that - how about that?

guess you would have to use sed to escape special characters you encounter to escape them and use two different passwords - but since you put the password on the COMMAND LINE AGAIN ANYWAY WITH THE HOST COMMAND - who cares, we can see it :)

sort of hiding password

Robert Hanrahan, July 14, 2006 - 11:06 am UTC

ok,

I see what you mean.

Thanks Tom, you Rock

Robert Hanrahan

rlogin asking for pass

A reader, September 11, 2006 - 12:02 pm UTC

Hi Tom

I am trying to install Rac on solaris 10
here rlogin is asking for password.
Do you know to resolve this.

September 11, 2006 - 1:25 pm UTC

supply password?

A reader, September 11, 2006 - 3:34 pm UTC

>>supply password?

ROTFL...

trying to setup rac

A reader, September 11, 2006 - 4:01 pm UTC

rlogin : supply pasword

the first rule for RAC IS

RLOGIN, RSH/SSH and rcp/scp should work seemless without requiring passwords

September 11, 2006 - 4:12 pm UTC

ok, ok ;)

</code> http://docs.oracle.com/docs/cd/B19306_01/install.102/b14201/racinstl.htm#sthref962 <code>

it just seemed to easy to find out...

Password management

A reader, January 22, 2009 - 8:12 am UTC

Dear Thomas,

thanks like always,

when i use external password i cannot manage passwords from the profile, i do not want to manage it from the os i need to manage it from database, any suggestion?

Thanks

January 22, 2009 - 9:26 am UTC

Let's see

you want to use an OS password
you want to use the database to manage that password

sort of a conundrum. The database has no clue what the password is, when it was last changed, nothing. The "password" might not even be a password - it could be a certificate, kerberos, whatever.

If you use identified externally, you have to use the "external" thing to manage the password, think about it - how can Oracle 'expire' your OS credentials?

JDBC external authentication and remote_os_authent=false

Carmen, April 15, 2009 - 4:19 am UTC

Our developers have coded a java program to generate XML documents from an external source and insert them in the database. This will be executed periodically on the database box (it's a batch job).
They say is quicker to generate the XML in memory from java and then insert in the table than doing it from PL/SQL (they didn't know how to generate XML in PL/SQL) which would avoid the problem we have.

I would like the JDBC (thin or OCI) connection were opened by an user OS authenticated, and keeping remote_os_authent=false (really it's a local account), but JDBC connection fails with ORA-01017.

By parallelism, with the batch user:
sqlplus /@connection_string => ORA-01017 (I suppose TCP connections always "become remote" though it be done on the same server)
but sqlplus / => OK

Is there a way to bypass this not being to set remote_os_authent=true? I mean, something like configure the JDBC connection as if it were local.
If finally it weren't possible, what would be your suggestion having in mind security risks?
1. JDBC connection opened by user authenticated by database
2. JDBC connection opened by user OS authenticated + remote_os_authent=true
3. coding it in PL/SQL and execute from sqlplus /

Thank you very much for this site.

April 15, 2009 - 9:35 am UTC

... They say is quicker to generate the XML in memory from java and then insert in
the table than doing it from PL/SQL (they didn't know how to generate XML in
PL/SQL) ...

how could they say that - if they don't know how to do it, how could they have evaluated anything???? confusing - isn't it?

[tkyte@dellpe j]$ cat AuthTest.java
import java.sql.*;
import oracle.sql.*;

public class AuthTest
{
  public static void main(String[] args) throws SQLException
  {
    DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());

    Connection conn = DriverManager.getConnection("jdbc:oracle:oci:@");

    PreparedStatement pstmt = conn.prepareCall( "select user from dual" );
    ResultSet rset = pstmt.executeQuery();

    while( rset.next() )
        System.out.println( "user = " + rset.getString(1) );

    rset.close();
    pstmt.close();

    conn.close();
  }
}
[tkyte@dellpe j]$ java AuthTest
user = OPS$TKYTE
[tkyte@dellpe j]$ id
uid=500(tkyte) gid=500(tkyte) groups=500(tkyte),501(ora9ir2),502(ora10gr1),503(ora10gr2),504(ora9ir1),505(ora11gr1)


[tkyte@dellpe j]$ plus

SQL*Plus: Release 10.2.0.4.0 - Production on Wed Apr 15 09:20:59 2009

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options


ops$tkyte%ORA10GR2> show parameter remote_os_auth

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
remote_os_authent                    boolean     FALSE

JDBC external authentication and remote_os_authent=false

Carmen, April 21, 2009 - 3:57 am UTC

Great!
Thank you, Tom.

"how could they say that - if they don't know how to do it, how could they have evaluated anything???? confusing - isn't it? "
About the throughput of java versus PL/SQL, I didn't explain it well. In fact the source is an XML file (about 30000 long records), and they have to generate 30000 PDFs and insert them in a table. At this point, they say it is easier and quicker to generate PDF from java than from PL/SQL, and with me ignoring this, (and though I think like you), I must accept their decision.

Thanks again for your time.

Issue with mkstore -wrl

Sachin, November 25, 2009 - 11:25 am UTC

Hi Tom,

I have a requirement to hide the username and password, to server this motive, I tried to use Secure External Password Store feature of Orcle 10g, Every thing goes fine till

mkstore -wrl "C:\oracle\product\10.2.0\db_1\NETWORK\ADMIN" -createCredential db10g scott tiger
Enter password:

Create credential oracle.security.client.connect_string1

C:\>

After that When tring to login into SQLPLUS as
c:\SQLPLUS /@orcl

SQL*Plus: Release 10.2.0.1.0 - Production on Wed Nov 25 22:31:05 2009

Copyright (c) 1982, 2005, Oracle. All rights reserved.

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

Getting Error ORA-01017
I have a limitation that I can not use OS Authentication for this purpose.

November 28, 2009 - 10:28 am UTC

You do not give us the step by steps you went through, this does:

http://docs.oracle.com/docs/cd/B19306_01/network.102/b14266/cnctslsh.htm

Please make sure you've done them all (eg: you created a db10g scott tiger credential, but you later use @orcl? One of the big notes in that link is:

Note:
The db_connect_string used in the CONNECT /@db_connect_string statement must be identical to the db_connect_string specified in the -createCredential command.

did you do the sqlnet.ora set up that is described?

do the documented step by steps and verify everything three times and if you cannot get it going then - post again here CUT AND PASTES from a command window proving that you've done all of the setup and are using the correct tnsnames and so on.

Issue with Secure external Password Store

Sachin, November 30, 2009 - 5:17 am UTC

Hi Tom,

please review below code and share your valuable comments.

######## sqlnet.ora

WALLET_LOCATION =
(SOURCE = (METHOD = FILE)
(METHOD_DATA =
(DIRECTORY = C:\oracle\product\10.2.0\client_1\NETWORK\ADMIN)))

SQLNET.WALLET_OVERRIDE = TRUE
SSL_CLIENT_AUTHENTICATION = FALSE
SSL_VERSION = 0

SQLNET.AUTHENTICATION_SERVICES= (NONE)

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

## Wallet Creation
C:\>mkstore -wrl "C:\oracle\product\10.2.0\client_1\network\admin" -create
Enter password:

Enter password again:

C:\>mkstore -wrl "C:\oracle\product\10.2.0\client_1\network\admin" -createCredential rep_tool <username> <password>
Enter password:

Create credential oracle.security.client.connect_string1

C:\>mkstore -wrl "C:\oracle\product\10.2.0\client_1\network\admin" -listCredential
Enter password:

List credential (index: connect_string username)
1: rep_tool <username>

C:\>sqlplus /@rep_tool

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Nov 30 10:42:45 2009

Copyright (c) 1982, 2005, Oracle. All rights reserved.

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

Enter user-name:

I gone thru shared link but could not find any thing extra.

November 30, 2009 - 3:17 pm UTC

contact support, reference Bug 4395883

as a workaround, use a tns connect string that is not 4, 8 or 12 bytes in length.

OS authentication

Hash, September 24, 2010 - 7:52 am UTC

Sir Please consider this:
at windows XP command prompt, i execute a batch script (called backup.bat) with the following line:
C:\oracle\ora92\bin\sqlplus / @c:\backup\backup.txt
the script executes fine
but when I submit the same to task schedular by issuing the command:
AT 15:49 c:\backup\backup.bat ^>c:\backup\b.txt
the command fails with the following error
ERROR:
ORA-01017: invalid username/password; logon denied

Enter user-name:

could you please shed some light?

Thanks

September 27, 2010 - 10:19 am UTC

http://support.microsoft.com/kb/313565

... Note When you use the at command, the scheduled task is run by using the credentials of the system account. ...

AT does not run "as you"

if you were in 10g or above, you could use the dbms_scheduler package to run things in the background.

Tom is also a Microsoft-Expert

Sokrates, September 27, 2010 - 10:42 am UTC

didn't know that !
:-)

September 27, 2010 - 12:22 pm UTC

Nope. The power of google. I just asked it "who does the at job run as" and it pointed me there - which made it obvious.

I just know that Windows isn't unix so I don't assume it would work rationally.

The entire services thing drives me nuts - network drives, even more so. It is just a big single user OS pretending to be multi-user.

external user's password in dba_users

Patrick Lelie, December 08, 2010 - 4:52 am UTC

Tom,
for scripting we use an su.sql which get's the password from dba_users and resets it's using the hashvalue of the field password.
Now I notice that on some databases I get a value "EXTERNAL" in this collumn and on others I get an hash value. Both users are externally identified and both databases are in 10.2.0.4.
Is this caused by the parameter remote_os_authent ?

December 08, 2010 - 10:34 am UTC

... Is this caused by the parameter remote_os_authent ? ...

no, not even remotely.

You can create a user:

create user ops$foo identified externally;

or you can create a user:

create user ops$foo identified by bar;

In the former case, they do not have a password and can only connect via OS authentication.

In the latter case, they can connect via OS authentication if they want to - OR - they can connect with ops$foo/bar

Like this:

[tkyte@localhost ~]$ <b>sqlplus /
</b>
SQL*Plus: Release 11.2.0.2.0 Production on Wed Dec 8 11:29:34 2010

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
<b>
ops$tkyte%ORA11GR2> show user
USER is "OPS$TKYTE"
</b>ops$tkyte%ORA11GR2> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

[tkyte@localhost ~]$ su - ora11gr2
Password: 


<b>[ora11gr2@localhost ~]$ sqlplus 'ops$tkyte/foobar'
</b>
SQL*Plus: Release 11.2.0.2.0 Production on Wed Dec 8 11:29:55 2010

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
<b>
ops$tkyte%ORA11GR2> show user
USER is "OPS$TKYTE"
</b>ops$tkyte%ORA11GR2>

oh, and be aware - password is going away...

ops$tkyte%ORA11GR2> select password from dba_users;

PASSWORD
------------------------------


...

38 rows selected.

You'll want to use proxy users instead:

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:83012348058#408884400346292168

Patrick, December 14, 2010 - 6:39 am UTC

Thanks Tom for the info
As always, most usefull and appreciated

Patrick, December 14, 2010 - 7:00 am UTC

An extra thought on this
We often "refresh databases" from production environment to acceptace environment for instance
This is done through a backup restore procedure with a rename of the database (recreate controlfile, ...)
Off course, we do not allow the same passwords on those 2 environments.
Untill now we extracted all the passwords from the acceptance databes (password field in dba_users), restored the production database, renamed it and finally (I left some things out cause they are not relevant to the topic) put back the old passwçords.
How can we solve this now (in 11) ?
Can we find the password somewhere else ?

December 14, 2010 - 7:33 am UTC

sys.user$

expdp ... include=user

would get them into a dmp file for impdp as well.

Patrick, December 15, 2010 - 2:08 am UTC

A warm than you from Belgium, Tom
Merry Christmas and a great 2011

A reader, March 30, 2012 - 10:18 am UTC

We have some scripts like gather statistics, backups which connect as sys/system to the database. Instead of hardcoding the passwords in the scripts, we take them from hidden password files. But when we get a sysout of the jobs we see that the password is shown as clear text. Our databases are 10.2.0.4 and 11.2.0.2. Is the externally identified users still the way to go ? Do you have any clear documentation on how to use them ? This is the first time I will be using them.

March 30, 2012 - 10:21 am UTC

if you are on the local machine with these scripts, externally identified is the way to go.

avoid using sys and system unless you really really really REALLY have to.

You have also:

http://docs.oracle.com/cd/E11882_01/network.112/e16543/authentication.htm#DBSEG99816

those passwords will not show up anywhere.

A reader, March 30, 2012 - 10:32 am UTC

"if you are on the local machine with these scripts, externally identified is the way to go. "

The scripts are on the database server but are called from a different application server. Will the externally identified authentication still work ?

March 30, 2012 - 11:20 am UTC

the scripts have to be run by a user logged into the database machine to use externally identified in general (unix).

read up on the secure password store, that'll work for you.

A reader, April 12, 2012 - 12:18 pm UTC

Thank you.. I used this and setup some scripts to use the Oracle wallet. Works great on the local server. But we have one issue. We copied the wallet files to a remote server and we were able to connect to the database user from the remote server without knowing the password. How can this be prevented ?

April 13, 2012 - 3:18 pm UTC

If you use a local auto-open wallet, it can only be opened on the machine that created it.

http://www.oracle.com/technetwork/database/focus-areas/security/tde-faq-093689.html#A13016

$ orapki wallet create-wallet-<wallet_location> auto_login_local

A reader, April 17, 2012 - 10:25 am UTC

Is the setting up of the local auto open wallet similar to the wallet creation using mkstore ? Do we create the credentials with the TNS just like with mkstore ?

April 18, 2012 - 2:53 am UTC

you use orapki to create/update the wallet

I don't know what you mean by your last sentence there. TNS is network, what does it have to do with creating actual credentials?

A reader, April 23, 2012 - 11:19 am UTC

To clarify, this is what I did to test the Oracle wallet

mkstore -wrl /orahome/wallets -create
Enter password:

Enter password again:

mkstore -wrl /orahome/wallets -createCredential <TNSvalue> system <password>

Following entries in sqlnet.ora file
-------------------------------------

WALLET_LOCATION =
(SOURCE =
(METHOD = FILE)
(METHOD_DATA =
(DIRECTORY = /orahome/wallets)
)
)

SQLNET.WALLET_OVERRIDE=TRUE

We changed the backup script as follows

sqlplus -s /@<TNSvalue>

How can we achieve this with the local auto-login wallet ? I am not able to find enough documentation on that.

Thanks for your help.

April 24, 2012 - 7:19 am UTC

Create the PKCS#12 wallet and the auto-open wallet using 'mkstore',
and then override the auto-open wallet with a local auto-open wallet
with:

$ orapki wallet create –wallet <wallet_location> -auto_login_local;

that will of course ask for the wallet password.

A reader, April 24, 2012 - 11:40 am UTC

So are these the steps I have to follow ?

mkstore -wrl /orahome/wallets -create
Enter password:

Enter password again:

mkstore -wrl /orahome/wallets -createCredential <TNSvalue> system <password>

orapki command to override to the local auto wallet

Following entries in sqlnet.ora file
-------------------------------------

WALLET_LOCATION =
(SOURCE =
(METHOD = FILE)
(METHOD_DATA =
(DIRECTORY = /orahome/wallets)
)
)

SQLNET.WALLET_OVERRIDE=TRUE

Would I still change my backup script for the sqlplys connection as follows :

sqlplus -s /@<TNSvalue>

So effectively I am doing the same set of steps to setup the wallet but instead of making it retrievable / usable I am making it usable only on the local server so that even if I copy the wallet to a different server I will not be able to use the wallet. Is my understanding correct ?

April 24, 2012 - 12:20 pm UTC

So effectively I am doing the same set of steps to setup the wallet but instead
of making it retrievable / usable I am making it usable only on the local
server so that even if I copy the wallet to a different server I will not be
able to use the wallet. Is my understanding correct ?

correct, you are making it a "local" only wallet, it cannot be moved to another machine.

Concerning Oracle Wallets

olerag, April 25, 2012 - 3:40 pm UTC

Using Oracle 10.2.0.3, our dba's have gotten this to work in a particular instance and connection thru SQL Plus works fine. However, I cannot get straight answers about the following questions for this paticular version of Oracle and thought you probably at least knew the answers to include:

Can you connect using Oracle wallets with a Java thin-client adapter, specfically provided in the "ojdbc14.jar" file?

If the answer is no....fine. If the answer is yes, do you want to see the Java source I am using and, perhaps, the appropriate adjustments could be offered?

Thanx for any info along these lines.

April 25, 2012 - 5:32 pm UTC

CONNECTION_PROPERTY_WALLET_LOCATION was added for thin support in 11.1

http://docs.oracle.com/cd/E18283_01/appdev.112/e13995/oracle/jdbc/OracleConnection.html

so, if you use the 11g thin drivers - yes, it would be supported, but not before then.

Working in 11g (11.2.0)

olerag, May 04, 2012 - 10:29 am UTC

I still cannot connect with the wallet in an 11g instance, referencing the "ojdbc6.jar" file, although much closer to sucess. The print stack trace is now reporting the rather lengthy error message....

Encountered a problem with the Secret Store. Check the wallet location for the presence of an open wallet (cwallet.sso) and ensure that this wallet contains the correct credentials using the mkstore utility.

The wallet works from SQL Plus and I am also setting both the tnsnames and wallet location path in my Java source.

Do you want to see the Java source or do you know of a decent link that provides a snippet example?

May 06, 2012 - 2:42 pm UTC

can you post the error stack and I'll ask someone to peek at it.

I'm not much of a java guy :)

Print Stack Trace results

olerag, May 06, 2012 - 4:44 pm UTC

Thanx - here's the complete print stack trace that is caught when connection is attempted from the OracleDataStore.getConnection() method:

java.sql.SQLException: encountered a problem with the Secret Store. Check the wa
llet location for the presence of an open wallet (cwallet.sso) and ensure that t
his wallet contains the correct credentials using the mkstore utility: oracle.ne
t.ns.NetException: Unable to parse the wallet location supplied.
at oracle.jdbc.driver.PhysicalConnection.getSecretStoreCredentials(Physi
calConnection.java:2819)
at oracle.jdbc.driver.PhysicalConnection.parseUrl(PhysicalConnection.jav
a:2568)
at oracle.jdbc.driver.PhysicalConnection.readConnectionProperties(Physic
alConnection.java:2106)
at oracle.jdbc.driver.PhysicalConnection.<init>(PhysicalConnection.java:
512)
at oracle.jdbc.driver.T4CConnection.<init>(T4CConnection.java:221)
at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtensio
n.java:32)
at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:503)
at oracle.jdbc.pool.OracleDataSource.getPhysicalConnection(OracleDataSou
rce.java:280)
at oracle.jdbc.pool.OracleDataSource.getConnection(OracleDataSource.java
:207)
at oracle.jdbc.pool.OracleDataSource.getConnection(OracleDataSource.java
:157)
at Test.main(Test.java:35)

Again, the wallet credential works when connecting to "SQL Plus" so providing the snippets from the tnsnames.ora and sqlnet.ora aren't probably necessary.

The parameter to connect to SQL Plus and the "setURL" method are using the same parameters and I am setting both the "oracle.net.wallet_location" and "oracle.net.tns_admin" properties with the System.setProperty method from where the path of the wallet and tnsnames files reside.

I can find virtually no examples (source snippets) on the web that provide any useful meaning to accomplish this and no help whatsoever from any Oracle links other than the fact that it can be done.

If you want the Java source, let me know.

Very frustrating, to say the least.

A reader, May 07, 2012 - 9:40 am UTC

I am trying this command to change my wallet to a local wallet but I am getting the below error

orapki wallet create -wallet /orahome/wallets -auto_login_local
Invalid command: -auto_login_local

What am I missing ?

May 07, 2012 - 10:49 am UTC

what version are you using

A reader, May 07, 2012 - 11:02 am UTC

I tested this in 10.2.0.3

May 07, 2012 - 11:34 am UTC

local wallets are new in 11g R2

http://www.oracle.com/technetwork/database/focus-areas/security/tde-faq-093689.html#A13016

11g thin client with wallets

olerag, May 07, 2012 - 11:53 am UTC

OK - we got this working. Our dba initially made a wallet and put it in a "hidden" path (.wallets). This worked for SQL Plus but the thin client, for whatever reason, didn't like that path.

We made a new path ("myWallets") with new credentials, adjusted the tnsnames.ora to support the new credential, and connection was successful.

Sorry if I made you do stuff that wasn't necessary.

May 07, 2012 - 2:05 pm UTC

Thanks for the followup!

A reader, May 09, 2012 - 10:38 am UTC

If I create the wallet in 11.2.0.2 as 'oracle' OS user and if I run the job as another OS user 'batch' then I get the following error

ORA-12578: TNS:wallet open failed.

If the job needs to run as 'batch' should the wallet also be created by 'batch' ?

May 10, 2012 - 7:45 am UTC

what are the permissions on the wallet - group and owner - and what group is batch in?

A reader, May 10, 2012 - 8:45 am UTC

The permissions on the wallet is 755 (as well as the directory under which the wallets are created) and it is owned by user oracle and group dba. The batch user is part of an apps group and then part of the dba group as well.

May 10, 2012 - 9:03 am UTC

can you make sure the batch user can see that - they'd need execute at least on all of the directories leading to it.

A reader, May 10, 2012 - 9:21 am UTC

I am able to browse to the directory of the wallets as the batch user and all the directories leading up to the wallet are 755

May 10, 2012 - 3:10 pm UTC

"browse" - what does that mean?

can you do a simple 'cat' of the file from the same prompt you try to run the program from ?

A reader, May 11, 2012 - 9:36 am UTC

Yes I am able to do a "cat" from the batch user's prompt of the wallet files ewallet.p12 and cwallet.sso

May 11, 2012 - 11:50 am UTC

have you tried turning on tracing to see if anything useful comes out of that (it should be OK if you can see the contents of the wallet)

A reader, May 11, 2012 - 11:52 am UTC

No, I havent, how do I do that ?

May 11, 2012 - 11:59 am UTC

http://docs.oracle.com/cd/E11882_01/java.112/e16548/apxtblsh.htm#JJDBC28987

A reader, May 17, 2012 - 8:41 am UTC

I have been reading about the local oracle wallets and I saw a note that said that the oracle wallets are local to the server and the OS user that created the wallet. Does that mean that if I create the wallet as 'oracle' OS user, only that user will be able to use that ?

Issue with JDBC Driver ojdbc6 OS Authentication

Bruce, August 09, 2012 - 11:22 am UTC

Hi Tom.

We are trying to make an app work with OS Authentication using the ojdbc6.jar but we are getting an ORA-01017 error.

My machine is an WinXP and the Oracle is an 10g (10.2.0.4.0) running at Solaris.

- I already created a domain user and the same user is created at oracle. Access using sqlplus / works.
- Remote Authent is True, os_authent_prefix is "" and Authentication Services in sqlnet.ora is NTS.
- I am using the Oracle Jdbc thin driver ojdbc6.jar. I already read at this post that OS Auth is available at this version.

The jdbc log says:
"GRAVE: 193385D Throwing SQLException: ORA-01017: invalid username/password; logon denied
1017"

Could you help?

August 17, 2012 - 1:33 pm UTC

Remote Authent is True

fix that right now, set it to FALSE, you do not want nor need that.

I'll have to refer you to otn.oracle.com, the discussion forums for that - I don't really use java/jdbc. I do remember way back when I was forced to be on windows for a database - there was something about including the domain name or something in there - but it was way too long ago to remember fully. sorry.

EXTERNAL AUTHENTICATION

Syed, June 21, 2013 - 12:03 pm UTC

Dear Tom,

This is good idea to have external login for backup script but the problem is we'll have to create another os user for the same bkz there might be multiple devloper aur dba who uses other application or os admins.
my question is if we create a separate user for backup how could i allow it to use all required access to oracle executable to complete the task(e.g. chmode 755 exe ,chmod 755 backup_location etc)

July 01, 2013 - 4:48 pm UTC

I don't know what you mean - there would be no change to the installed oracle software.

you would make this account part of the "dba" group or whatever group you wanted to use and everything should be good to go.

Syed Safi, July 02, 2013 - 9:31 am UTC

Thanks Tom,

This is fixed now actually the group wasnt same with both the users so backup wanst starting then giving executing permission to oracle backup exe (rman,expdp) as well as backup location were fixed the issue but wasnt perfectly resolved as exe are unsafe then. so just providing the oracle group i.e. dba to that user has perfectly done the same. Thanks again

canot run sql loader using external account via java

Btidba, September 27, 2013 - 11:03 am UTC

Hi Tom,

after creating an external account i try to use the autentication using '/' , in msdos the following commands work :
- sqlplus /
- sqlplus /@bdd
- c:\Windows\System32\cmd.exe /y /c C:\app\user\product\11.2.0\dbhome_1\BIN\sqlldr /  control=file.ctl LOG=log DIRECT=TRUE

but when i use this last command in java code it does'nt work it says: invalid user/password , the '/' is'nt considered as the userid parameter. 
Below the part of java code that run the sql loader:

CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED PROD."SqlldrCommand" AS
import java.io.*;

  public class SqlldrCommand {

    public static String executeCommand(String ctrlfile,String sqlldrpath,String cmdpath,String uselogin ,String usepass,String aimalias) {
      StringBuffer sb = new StringBuffer();
      try {
        String[] finalCommand;
          finalCommand = new String[4];
          finalCommand[0] = cmdpath;
          finalCommand[1] = "/y";
          finalCommand[2] = "/c";
          finalCommand[3] = " " + sqlldrpath+"\\sqlldr u/ control= "+ ctrlfile + " LOG="+ ctrlfile + ".log DIRECT=TRUE ";

        // Execute the command...
        final Process pr = Runtime.getRuntime().exec(finalCommand);

I'll apreciate your help as always :)!

Many thanks and regards

wallet can't work in 12.1.0.2 by auto_login_local

powell, September 03, 2014 - 4:37 am UTC

Tom,

I try to implement wallet with auto_login_local option, the same steps work in 12.1.0.1 but failed in 12.1.0.2 with the message: ORA-12578 TNS:wallet open failed

and when I change auto_login_local to auto_login, it works in 12.1.0.2, so I doubt that there is a bug in 12.1.0.2?

Regards,
Powell

More to Explore

VLDB

If you are new to partitioning, check out Connor McDonald's introduction series here.

VLDB

Documentation set on VLDB and Partitioning.

Hiding the password

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

How about for NT?

Other tricks...

Need more help

ksh options

I think I can help elaborate on the ksh options

Another way to hide password on UNIX

OPS$ accounts and REMOTE_OS_AUTHENT secure?

But what about......

Followup for "ksh options" (Jun 2002)

AskTom.unixguru.com

question to implemenet remote security

How many external users?

Thanks for the idea

Passing ops$ password on command line

Can't Get It To Work on Linux

Is there anything you don't know?

Does this work in window NT

sqlplus /

Thank You

Need comments

Object Permissions

RE: Object Permissions

sqlldr problem

More on sqlldr problem

Busted link...

sqlldr / @host_string slower than sqlldr / to load data in same box

sqlldr / @host_string slower than sqlldr / to load data in same box

It turns out to be a setup problem with the AIX.

DB is in unix and User connects thro NT

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

Is it bad idea?

Password, ps and unix.

What about when using JDBC connections

What's the way to go then?

I need Jave developers to buy into it :-)

Script logs into database multiple times as a different user

not true

Socrates ...

Thanks

Password In Tables

Password In Tables

script to hide password

hiding password

passwords with funny characters

sort of hiding password

rlogin asking for pass

trying to setup rac

Password management

JDBC external authentication and remote_os_authent=false

JDBC external authentication and remote_os_authent=false

Issue with mkstore -wrl

Issue with Secure external Password Store

OS authentication

Tom is also a Microsoft-Expert

external user's password in dba_users

Concerning Oracle Wallets

Working in 11g (11.2.0)

Print Stack Trace results

11g thin client with wallets

Issue with JDBC Driver ojdbc6 OS Authentication

EXTERNAL AUTHENTICATION

canot run sql loader using external account via java

wallet can't work in 12.1.0.2 by auto_login_local

More to Explore

VLDB

VLDB