Oracle Database Discussions

It uses OS level authentication .

Thanks

Hi,

Its windows - authentication - that is authentication is carried out at OS level and the current is part of OS level DBA group - oracle is trusted with that.
furthe read the doc :

http://www.oracle-base.com/articles/misc/OsAuthentication.php
http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/dba.htm#i1006628

- Pavan Kumar N

Hi Pavan,

I understood a part of it.
1. Create OS user ORACLE
2. Create ops$oracle user identified externally
3.Grant sysdba to ops$oracle
4. But when we do sqlplus / as sysdba , we should have logged in as oracle but not sys---this part i am not understanding :(

After connecting sqlplus / as sysdba then execute show user and post result there

Hi,

When we connect as sysdba - to you are references the SYS schema. Which holds all of the base tables and views for the database's data dictionary are stored.

- Pavan Kumar N

oracle sqlplus / as sysdba

SQL*Plus: Release 10.2.0.3.0 - Production on Fri Jan 7 04:19:01 2011

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


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

SQL> sho user
USER is "SYS"

So you actually connected with SYS user.And what is your problem?

Refer to this thread :-

352542

Thanks.

There is no problem, but my question is if I am giving sysdba privs to ops$oracle, i should have logged on as oracle not sys...
(correct me if i am wrong :(
)

No,that is wrong.So if you use connecting */AS SYSDBA* then your user always is SYS. How can i say that is axiom ;-)

Try this :-

[timepass@adc2110341 bin]$ ./sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Fri Jan 7 04:57:28 2011

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


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

SQL> show user
USER is "SYS"

And then this :-

[timepass@adc2110341 bin]$ ./sqlplus /

SQL*Plus: Release 11.2.0.1.0 Production on Fri Jan 7 04:57:51 2011

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


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

SQL> show user
USER is "OPS$TIMEPASS"

Thanks.

>

yeah..nice comparison

Edited by: puspak on Jan 7, 2011 5:10 AM

Hi,

I have similar questions, when use OS level authentication, it only refer to the sys right? nothing to do with other normal oracle user?

Wrong, look at Garg's update, its clearly distinguishes between two types of login.

I think there are only three ways you can connect using OS level Auth :-

1. using / as sysdba ( if your OS user is part of dba group )
2. using / as sysoper ( if your OS user is part of oper group )
3. using / ( if you have created an OS user in Oracle using identified externally.

Thanks.

Hmm would you explain why the question is wrong ?

Aman....

Apart from sysdba login , there are options available to login directly such as sqlplus /

The important bit to know is that for both, either Sys or normal user, the said o/s account must be a part of the dba group and that's what was asked that whether this o/s login has anything to do with the os user oracle or not and was my question to you as well that why you think it's wrong? I hope it's clear now.

Aman....

In case of OS user , i think even though it is not a part of dba group , it is possible to connect as a normal user if the OS user is defined as " identified externally"

SQL> create user ops$timepass identified externally;
GRANT CONNECT TO ops$timepass;

User created.

SQL> 


[timepass@adc2110341 bin]$ id
uid=60000(timepass) gid=60000(timepass) groups=60000(timepass)


[timepass@adc2110341 bin]$ ./sqlplus /

SQL*Plus: Release 11.2.0.1.0 Production on Fri Jan 7 05:33:05 2011

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


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

SQL> show user
USER is "OPS$TIMEPASS"

My bad , yes you are correct. It's like , if the o/s allows you to login, oracle would allow too. Still, just to be a little fussy , the asked question is still relevant as it would indeed matter that the person who is wishing to login as o/s authenticated should be able to have a valid login from the o/s itself.

Thanks again Parul!

@Puspak,

I stand corrected about the group part, apologies for it.

Aman....

Hi, thanks, now i know more about the OS level Auth, and one more question: there is a sqlnet.ora file both on server side and client side, which one i must change to enable or disable the OS level Auth? both?

Edited by: Lukecui on Jan 7, 2011 6:48 AM

sqlnet.ora is a file where some of the parameters apply only to the client side, and some apply only to the server side. (and don't forget that some operations are client operations even though they are physically occurring on the sever box).

In this case, think about what machine you are on when you exercise os level authentication ...

Which specific parameter are you looking at? ;)

Take a look at this, from the host machine: (notice that every line in sqlnet.ora is a comment. )

[oracle@vmlnx01 admin]$ pwd
/ora00/app/oracle/product/10.2.0/db_1/network/admin
[oracle@vmlnx01 admin]$ cat sqlnet.ora
# sqlnet.ora Network Configuration File: C:\oracle\product\10.2.0\client_1\network\admin\sqlnet.ora
# Generated by Oracle configuration tools.

# This file is actually generated by netca. But if customers choose to 
# install "Software Only", this file wont exist and without the native 
# authentication, they will not be able to connect to the database on NT.

#SQLNET.AUTHENTICATION_SERVICES= (NTS)

#NAMES.DIRECTORY_PATH= (TNSNAMES)
#REMOTE_LOGIN_PASSWORDFILE=NONE
[oracle@vmlnx01 admin]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Fri Jan 7 09:30:12 2011

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

SQL> show user
USER is "SYS"
SQL>

Thanks

If you are asking about sysdba from a remote client, see http://download.oracle.com/docs/cd/E11882_01/server.112/e17120/dba006.htm#ADMIN11010 and http://download.oracle.com/docs/cd/E11882_01/server.112/e17120/dba007.htm#ADMIN11060

Note other versions may work differently, and always trust, but verify, documentation.

Hi ,

For more information on Remote_os_auth and Sqlnt.ora , refer :-

http://database-defacto.blogspot.com/

Thanks.