Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question.

Asked: December 20, 2004 - 5:37 pm UTC

Last updated: April 07, 2020 - 2:26 pm UTC

Version: 9

Viewed 1000+ times

You Asked

could u please explain the difference between os authentication and database authentication and how it is done, briefly...i have read it but unable to understand it completely(w.r.t oracle 9i)

and Tom said...

[tkyte@localhost tkyte]$ sqlplus scott/tiger


this is database authentication, the database is accepting a username/password from us, it will validate it for us, the database will decide whether to let us in -- whether we are really "scott"

SQL*Plus: Release 9.2.0.4.0 - Production on Mon Dec 20 18:05:14 2004
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
scott@ORA9IR2> Disconnected from Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production



[tkyte@localhost tkyte]$ sqlplus /

this is OS authentication -- we tell Oracle -- ASK THE OS who we are, if that is a username you are familar with, let us in. We need no password, we already gave it to the OS



SQL*Plus: Release 9.2.0.4.0 - Production on Mon Dec 20 18:05:17 2004

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


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

ops$tkyte@ORA9IR2>


Rating

  (11 ratings)

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

Comments

authentication

nitin, December 20, 2004 - 6:17 pm UTC

please answer this wrt windows and as well as linux..i shall be thankfull to u

1)is there anything special to do to enable os authentication
2) how can we disable os authentication so that user has to enter username and password everytime

Tom Kyte
December 20, 2004 - 6:53 pm UTC

1) on linux, nope -- easy as pie.

on windows
http://otn.oracle.com/pls/db92/db92.show_toc?partno=a95492&remark=drilldown&word=os+authentication


2) let the os_autent_prefix default to ops$ and don't create any accounts that start with ops$

notice how my account name in my prompt is ops$tkyte?  my OS account name is tkyte, ops$tkyte is my database name.  If you leave the os authent prefix at OPS$ and don't create any ops$ accounts, no one can use OS authentication (on unix).  You'll have to read the windows docs for information on that platform 

A reader, September 19, 2005 - 11:47 am UTC

i have oracle 9i on redhat linux 9.i have read that in order for os authentication you have to alter /etc/passwd and /etc/groups files.what i do is i log on to the linux as username :sac and password: 123456 this is the user on linux which i created while installing linux.it is a normal user. i added the "scott", user of oracle in the /etc/passwd file and added him in the dba group.now according to RULES whenever i do
sqlplus /nolog
connect scott
password:

it should not ask for a password ..but it is asking

now why it is asking for the password.should i make scott a user of linux and also add him in the dba group and then log in to linux as scott and then su - oracle and then

sqlplus /nolog
connect scott


Tom Kyte
September 19, 2005 - 12:01 pm UTC

you do not, where did you read that?


you need an OS user named scott.
you need to be logged into the OS as that user scott
and then sqlplus / will work for scott (assuming your os_authent_prefix isn't set)


if you are not logged in as scott, you cannot have the os authenticate them!!!

A reader, September 19, 2005 - 12:09 pm UTC

hi

so this scott "MUST" be both a os user and a Oracle user(mean it should be a oralce user like users scott or sys or any other user we make in oraclenot known to os)..

2) i logged on as sys and do
grant sysdba to scott....this is succesfull but whenever i try to shutdown oracle as user scott it is not letting me though scott is a sysdba user now...

Tom Kyte
September 19, 2005 - 1:29 pm UTC

did you connect "as sysdba" when connecting as scott.



reader

A reader, September 19, 2005 - 1:56 pm UTC

Thanks for answering all the basic questions for me....i did not connect scott as sysdba. i just did connect scott/tiger after granting him sysdba...

regarding first question..
so this scott "MUST" be both an os user and an Oracle user(mean it should be in oracle database)..

Tom Kyte
September 19, 2005 - 4:47 pm UTC

yes, if you want the OS to authenticate, there better be an OS user scott and you better have let the OS authenticate him (you logged into the OS as scott)

reader

A reader, September 28, 2005 - 12:09 pm UTC

hi Tom
i made a user judy which is identified externally....and made that user in os also and made him a member of dba group...now when i am logged in from that user and give sqlplus "/ as sysdba"..it say command not found ..why is that...but it launches fine from oracle user

thanks
sachin


Tom Kyte
September 28, 2005 - 12:36 pm UTC

you are missing $ORACLE_HOME/bin in the path it sounds like.

reader

A reader, September 28, 2005 - 12:10 pm UTC

hi Tom
i made a user judy which is identified externally....and made that user in os also and made him a member of dba group...now when i am logged in from that user and give sqlplus "/ as sysdba"..it say command not found ..why is that...but it launches fine from oracle user......i have oracle 9i on linux

thanks
sachin

sachin

A reader, September 29, 2005 - 1:30 pm UTC

Hi Tom,

Thanks it worked fine,but here i got stuck once again...Please guide....


i have RHlinux and oracle 9i ..i tried to do os authentication by

making a user judy externally in oracle and in linux made him a member

of a dba group....and did the entries(export ORACLE_HOME=/opt/ora9/

product/9.2 AND PATH=$ORACLE_HOME/bin) in the .bash_profile or /.bashrc

file for that user ,it(sqlplus) launched but when i restarted the system

in that username it did not login and show this error message

[there is some installation problem or you maybe out of disk space.try

loggin in with some failsafe session to see if you can fix the problem

..(view details ~/.xsession-errors file)]

and this file( ~/.xsession-errors) has entries(some 7 to 8 lines) like:

/etc/x11/xinit/xclients:line45:rm:command not found
/usr/x11r6/bin/runwm:line1:tr:command not found
....

...but instead of putting the entries in the .bash_profile or /.bashrc

when i export these entries in the comand prompt itself before launching

the sqlplus..it workes fine..why is it so....

i have tried the above with 3 different users..same thing

2)and may i know what is the difference between .bash_profile and /.

bashrc

thanks
sachin

Tom Kyte
September 30, 2005 - 8:34 am UTC

1) this is a "red hat configuration" question? it doesn't seem to have anything to do with oracle at all?


but you mucked up your path, you probably meant something like:

# .bashrc
# User specific aliases and functions
# Source global definitions
if [ -f /etc/bashrc ]; then
. /etc/bashrc
fi
export ORACLE_HOME=/x/y/z
export PATH=$PATH:$ORACLE_HOME/bin


......


you seem to have wiped out the path and have it just be the oracle home/bin

2) .bash_profile is a file.
/. is a directory (and a famous website)

I still don't get it

ed, November 08, 2005 - 5:29 am UTC

I have log in my OS name as "ed2231", and I've created a database user name "ed223l" as well. 

SQL> CREATE USER ops$ed2231 identified by manager

I've set the REMOTE_LOGIN_PASSWORDFILE to NONE, as shown by the oracle guidelines, but i still couldn't get it done...
 

Tom Kyte
November 08, 2005 - 10:03 pm UTC

you don't say what os.

the remote login paswordfile isn't relevant here. this is just OS authentication.

little confusion

Radoslav Golian, January 08, 2007 - 3:47 pm UTC

Hi Tom,
I'm playing with OS authentication (on Linux). I discovered that, if and only if OS_AUTHENT_PREFIX=ops$, you should be able to use OS and DB authentication at the same time, of course when db username is ops$os_user (with password set - not externally identified) and there is system user os_user.
Short script will demonstrate why I'm confused:
sys@DB10GR2>host su -c "/usr/sbin/useradd -G oinstall user2;passwd user2"
Password:
Changing password for user user2.
New UNIX password:
BAD PASSWORD: it is too short
Retype new UNIX password:
passwd: all authentication tokens updated successfully.

sys@DB10GR2>show parameter os_authent_prefix

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

sys@DB10GR2>create user ops$user2 identified by password;

User created.

sys@DB10GR2>grant create session to ops$user2;

Grant succeeded.
sys@DB10GR2>host su -c "sqlplus /" user2
Password:

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Jan 8 21:19:27 2007

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


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


GLOBAL_NAME
---------------------------------------------------
ops$user2@DB10GR2

ops$user2@DB10GR2>exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

sys@DB10GR2>conn ops$user2/password
Connected.

GLOBAL_NAME
----------------------------------------------------
ops$user2@DB10GR2

ops$user2@DB10GR2>conn / as sysdba
Connected.

GLOBAL_NAME
----------------------------------------------------
sys@DB10GR2

<b>BUT I realy do not understand this:</b>

sys@DB10GR2>host su -c "sqlplus ops$user2/password" user2
Password:

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Jan 8 21:22:31 2007

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

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

<b>I got the same error in bash:</b>
$ sqlplus ops$user2/password
SQL*Plus: Release 10.2.0.1.0 - Production on Mon Jan 8 21:24:55 2007

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

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


So, my question is: why sqlplus ops$user2/password is not working for me, while for Arup Nanda is: ;)
http://www.oracle.com/technology/pub/articles/project_lockdown/phase2.html#2.2
[qoute_begin]
In that case, the user can log into the database in either manner:
sqlplus /
sqlplus ops$ananda/oracle
[qoute_end]

Would you use OS authentication in production DB on Linux ? In your opinion, which authentication is the best one (on Linux)?

Thank you.

Tom Kyte
January 08, 2007 - 6:04 pm UTC

echo ops$user

and ask yourself what $user might be in a shell?


[tkyte@localhost ~]$ sqlplus ops$tkyte/foobar

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Jan 8 18:01:33 2007

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

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


Enter user-name:
[tkyte@localhost ~]$ sqlplus 'ops$tkyte'/foobar

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Jan 8 18:01:42 2007

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


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

ops$tkyte%ORA10GR2>

thank you

A reader, January 09, 2007 - 1:46 am UTC


19C

moh, April 06, 2020 - 12:37 pm UTC

dbt-05509 failed to connect to the specified database oracle 19C
i can not create any data base ??????????????
Chris Saxon
April 07, 2020 - 2:26 pm UTC

And how exactly are you trying to connect?

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.