OS authentication and remote_login_passwordfile
jianhui, June 11, 2003 - 4:28 pm UTC
Tom,
My database is 8.1.7.2. The oracle document says the remote_login_passwordfile MUST be NONE if using OS authentication. Below is what i got and it seems the document is not true, because i can still use OS authentication while the parameter is set to EXCLUSIVE can you explain it. Does setting it to EXCLUSIVE allow both OS and network authentication?
Regards,
quote from oracle document:
{
Operating system authentication
You must have SYSDBA privileges on your operating system, and the initialization parameter REMOTE_LOGIN_PASSWORDFILE must be set to NONE.
See Also: Oracle9i Database Administrator's Guide to learn about authentication, and your operating system specific documentation to learn about adding SYSDBA privileges
}
my test screen shoot:
[dbaweb@wbapps12 dbaweb]$ id
uid=511(dbaweb) gid=511(dbaweb) groups=511(dbaweb),509(dba)
[dbaweb@wbapps12 dbaweb]$ echo $ORACLE_HOME $ORACLE_SID
/orasrc/product/8.1.7 ISPT
[dbaweb@wbapps12 dbaweb]$ sqlplus '/ as sysdba'
SQL*Plus: Release 8.1.7.0.0 - Production on Wed Jun 11 16:19:22 2003
(c) Copyright 2000 Oracle Corporation. All rights reserved.
Connected to:
Oracle8i Enterprise Edition Release 8.1.7.2.0 - Production
JServer Release 8.1.7.2.0 - Production
SQL> show parameter remote_lo
NAME TYPE VALUE
------------------------------------ ------- ------------------------
remote_login_passwordfile string EXCLUSIVE
SQL>
June 12, 2003 - 8:24 am UTC
well, you don't tell me what document and apparently you are using 9i docs for 8i?
but in 9i
sqlplus '/ as sysdba'
SQL*Plus: Release 9.2.0.3.0 - Production on Thu Jun 12 08:23:16 2003
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.3.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.3.0 - Production
sys@ORA920> show parameter remote_log
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
remote_login_passwordfile string EXCLUSIVE
further question about the password file
jianhui, June 11, 2003 - 4:40 pm UTC
Tom,
A futher question is when i connect to an idle instance usgin sqlplus '/ as sysdba' before starting the instance, how does oracle know which parameter file should be used to locate the remote_login_passwordfile parameter???
For example, i have 2 instances in one machine, instance1 uses password file and set the parameter to exclusive yet instance2 uses OS authentication. After i login to the OS and set $ORACLE_HOME $ORACLE_SID, then i issue sqlplus '/ as sysdba', how does oracle know whether to use OS authentication or PASSWORD file? Since the setting is in the parameter file, the only way oracle can know is to locate the parameter file by the default location derived by the $ORACLE_HOME $ORACLE_SID, is it right? What if the initSID.ora is not in the default location?
June 12, 2003 - 8:26 am UTC
your oracle sid and oracle home determine that.
but if you use /, it is moot, that is using OS auth. "dba group" is what gets you in (or not)
OS authentication
Jianhui, July 14, 2003 - 9:49 am UTC
Tom,
Why cannt i login as sysdba when i use oracle OS user account? Below is the copy & paste. Oracle version is 9.2.0.1.0
SQL> conn /
Connected.
SQL> show user
USER is "OPS$ORACLE" (-- OS authentication is working for non sysdba connection)
SQL> conn / as sysdba
ERROR:
ORA-01031: insufficient privileges
(-- OS authentication is NOT working for sysdba connection)
Warning: You are no longer connected to ORACLE.
SQL> conn sys as sysdba
Enter password:
Connected.
SQL> select * from v$pwfile_users;
USERNAME SYSDB SYSOP
------------------------------ ----- -----
SYS TRUE TRUE
OPS$ORACLE TRUE FALSE
SQL> show parameter remote
NAME TYPE VALUE
------------------------------------ ----------- ---------------
remote_archive_enable string true
remote_dependencies_mode string TIMESTAMP
remote_listener string
remote_login_passwordfile string EXCLUSIVE
remote_os_authent boolean FALSE
remote_os_roles boolean FALSE
SQL> show parameter os
NAME TYPE VALUE
------------------------------------ ----------- ---------------
optimizer_index_cost_adj integer 100
os_authent_prefix string ops$
os_roles boolean FALSE
remote_os_authent boolean FALSE
remote_os_roles boolean FALSE
timed_os_statistics integer 0
SQL>
July 14, 2003 - 10:45 am UTC
well, you are not using the pw file when you do that and ORACLE must not be in the "dba" group or whatever group.
do this to test:
alter user ops$oracle identified by pw;
Now, both:
connect /
connect ops$oracle/pw
will work. try both "as sysdba" - if the second works, then v$pwfile is being used for that. check the groups oracle is in, make sure it is in the right ones.
why shouldn't oracle belong to DBA group?
jianhui, July 14, 2003 - 4:29 pm UTC
Tom,
I tested it again as you said without making any change. Why shouldnt oracle belong to DBA group in order to use OS authentication to connect as sysdba? Thanks!
SQL> conn ops$oracle/pw
Connected.
SQL> conn /
Connected.
SQL> conn ops$oracle/pw as sysdba
Connected.
SQL> conn / as sysdba
ERROR:
ORA-01031: insufficient privileges
Warning: You are no longer connected to ORACLE.
SQL> host id
uid=1000(oracle) gid=1000(dba)
July 15, 2003 - 1:05 am UTC
that means whomever installed your software used a group other then DBA to be the sysdba (internal) group. You'll need to find out what they used. DBA is the default, but you can override that during install
a mistake
jianhui, July 15, 2003 - 9:48 am UTC
Tom,
Could you please remove my latest message, because i dont want to disclose the machine name and SID in the public website, the last line of my message has that information.
But the question remains the same!
Many thanks!
July 15, 2003 - 10:09 am UTC
open a tar, contact support, I don't know your environment -- all I know is "i do not believe the dba group is the group"
connect as sysdba
David, February 04, 2004 - 2:20 pm UTC
Hi Tom,
connect as sysdba is confusing me. My OS is Solaris8. My OS user account is in dba group. We are using Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
JServer Release 9.2.0.4.0 - Production
Here is my problem:
I connect as sysdba,
Oracle asked me Enter user-name: and I typed sys
Oracle asked me to Enter password: I entered whatever I liked to enter and Oracle let me in.
Here is my question:
Why Oracle asked user name and password but not really care about what the password I entered?
Example:
dli@test> connect as sysdba
Enter user-name: sys
Enter password:
Connected.
dli@test> show user
USER is "SYS"
Best Regards,
David
February 04, 2004 - 5:46 pm UTC
Oracle isn't asking.
sqlplus is
ops$tkyte@ORA10G> connect as sysdba
Enter user-name: foobar
Enter password:
Connected.
sys@ORA10G> show user
USER is "SYS"
I do not have a foobar user -- you are just making sqlplus "happy". You don't need a username/password to connect locally "as sysdba" but sqlplus doesn't realize that.
connecting as sysdba always means you are SYS, just as connecting as SYSOPER always means you are PUBLIC.
question reg sysdba
reader, March 03, 2004 - 11:04 am UTC
I am on NT
If I create user & give connect grant , I can still connect to oracle 'as sysdba'
Is this b'cos I am using NT ??
How will be the behaviour on Unix ??
March 03, 2004 - 3:21 pm UTC
it is because of the OS account you are using, nothing to do with any existing Oracle database accounts (please see the other place you asked this same thing)
remote_login_passwordfile = NONE
Kanwar Plaha, June 09, 2004 - 1:17 am UTC
Hi Tom:
I have a peculiar issue: as the title says, my r_l_p is set to NONE. Now, how do I grant permissions to a given user (OPS$ user) to connect as sysdba. My server is Windows 2003 and oracle version is 8.1.7.4.
To give you some background, i have tried to connect as OPS$<localdomain>\ORACLE and tried the following:
SQLPLUS> grant sysdba to OPS$<localdomain>\some_other_user;
and oracle screams:
ORA-01994: GRANT failed: cannot add users to public password file
Confused.
June 09, 2004 - 7:15 am UTC
if you want to use the OS authentication, that is where you would manage the role sysdba (OSDBA).
problem is, if you don't want us to use a password file, someone else must do all of the authentication and authorization, that would be the OS.
You would not be granting in Oracle in this case, you'd be doing it at the OS level.
Password file overriden by OS authentication - Security Issue?
Arindom, September 15, 2004 - 10:52 am UTC
Hi,
In 9i; it seems that OS authentication overides password file authentication.
The password file is defined in both environments below.
I have logged in as the user oracle.
8i -> Password is authenticated in the password file
----------------------------------------------------
sqlplus /nolog
SQL*Plus: Release 8.1.7.0.0 - Production on Wed Sep 15 10:19:27 2004
(c) Copyright 2000 Oracle Corporation. All rights reserved.
SQL> connect sys as sysdba
Enter password:
ERROR:
ORA-01017: invalid username/password; logon denied
SQL> connect sys as sysdba
Enter password:
Connected.
SQL> show parameter remote_login_password
NAME TYPE VALUE
------------------------------------ ------- ------------------------------
remote_login_passwordfile string EXCLUSIVE
9i -> Password is OS authenticated. I can connect to sys
with any password like junk or asafgsadgh ...
sqlplus /nolog
SQL*Plus: Release 9.2.0.5.0 - Production on Wed Sep 15 10:24:30 2004
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
SQL> connect sys as sysdba
Enter password:
Connected.
SQL> show parameter remote_login_password
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
remote_login_passwordfile string EXCLUSIVE
My question is:
Whta's your take on this apparent security issue?
Is it possible in 9i to 'enforce password authentication'?
Thanks.
September 15, 2004 - 11:32 am UTC
in a local (no network) connection, OS authentication trumps everything.
No security issue -- you let them into this OS account/group. If you don't want them to be able to do that, remove them from that group, don't allow that account.
ORA-01031: insufficient privileges
reader, October 16, 2004 - 3:44 pm UTC
I have created a password file.
The password is eg oracle.
the sid is aux1 on host A
SQL*Plus: Release 9.2.0.4.0 - Production on Sat Oct 16 14:13:32 2004
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
SQL> connect sys/oracle@aux1 as sysdba
ERROR:
ORA-01031: insufficient privileges
However I can connect as
sqlplus "/ as sysdba"
sqlplus "/ as sysdba"
Connected to an idle instance.
SQL>
I have done a startup nomount and checked that the v$pwfile ...has one entry .
My ultimate purpose was to duplicate a db but it is not allowing a successful connection to the auxiliary db.
thanks
October 16, 2004 - 4:29 pm UTC
the "sid" is not used in user/pass@connect_string
aux1 in
connect sys/oracle@aux1 as sysdba
is NOT a sid, it is a tns connect string that is connecting over the network to some database -- perhaps not the one you are thinking of.
ORA-01031: insufficient privileges
Reader, October 16, 2004 - 10:53 pm UTC
my sid and connect string are the same .
tnsping shows sucess (OK)
Thanks for your help ,
Reader
October 17, 2004 - 9:48 am UTC
tnsping shows "listener up, listener ok, the hostname and port you put in the connect string are dandy"
tnsping shows nothing else.
your connect string might be the same set of characters as your sid, but that means nothing.
ORA-01994- 10g R2
Yogesh, January 27, 2006 - 1:52 pm UTC
I'm consistantly getting ORA-01994 error in one of the recently upgraded (8174 to 10201) database.
SQLPLUS SYS AS SYSDBA
password: oracle
grant sysdba to system;
ERROR at line 1:
ORA-01994: GRANT failed: password file missing or disabled
orapwd file=orapwdtest.ora password=oracle entries=5 force=y
SQL> show parameter remote
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
remote_login_passwordfile string EXCLUSIVE
SQL> show parameter os
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
os_authent_prefix string
remote_os_authent boolean FALSE
Similarly, when I try to connect to database using EM,
u:sys p:oracle, connect as: sysdba, I'm getting "Your username and/or password are invalid".
For testing I created one small Db using 10g DBCA, and I can login to EM as well can use
grant sysdba to system;
Can you please explain what is wrong with the DB?
January 28, 2006 - 12:37 pm UTC
you are missing your password file
your password file permissions are wrong
your password file is in the wrong place
your password file is named wrong
could be any of those.
looks like the name is wrong, unless your sid is dtest
sort excerpt from my current book:
<quote src=Expert Oracle: Database Architecture>
... to create and populate the initial password file. The command well use is
$ orapwd file=orapw$ORACLE_SID password=bar entries=20
That created a password file named orapwora10g in my case (my ORACLE_SID is ora10g). That is the naming convention for this file on most UNIX platforms (see your installation/OS admin guide for details on the naming of this file on your platform), and it resides in the $ORACLE_HOME/dbs directory. On Windows, this file is named PW%ORACLE_SID%.ora and is located in the %ORACLE_HOME%\database directory.
</quote>
Exclusive deprecated?
Elaine H, June 29, 2006 - 5:00 pm UTC
I am very confused. The 10gR2 documentation (Database Reference) says that:
"The value EXCLUSIVE is supported for backward compatibility. It now has the same behavior as the value SHARED."
But several sources say that the sys password cannot be changed if the value is "SHARED".
Can you please clarify?
June 29, 2006 - 5:18 pm UTC
that is confusing and looks wrong (the documentation in this case).
I'll file a documentation bug. exclusive works differently than shared.
Permission
A reader, June 05, 2007 - 6:14 am UTC
Tom,
orapw<sid> file is having the permission -rwSr---- and I want to change it to -rw-r----. I did a chamod and bounced the database. But once I make any connections using password file (sysdba connections) the permission is reverted back to -rwSr----. Can you explain this behaviour.
Regards,
June 06, 2007 - 12:58 pm UTC
it is our file, please leave it alone.
Significance of remote_os_authent=TRUE with "sqlplus /"
deepak, September 05, 2008 - 2:50 am UTC
What is the significance of remote_os_authent=TRUE with "sqlplus /".
September 05, 2008 - 9:02 am UTC
it is a whopping huge security issue.
do not do it.
it allows the remote os to tell the database "who" is on the other end of the connection.
If the user on the remote os has the ability to create accounts, they can create any account they want to get in to your database.
Login as sysdba - happening from one account not from other
Umesh Kasturi, February 25, 2010 - 11:19 pm UTC
The DB is installed on Linux
Oracle is installed from a oracle account on Linux
There are 2 OS accounts on Linux . ( Acc_1, Acc_2) from where we access the sqlplus
Acc_1 can login as sysdba to a DB (even without specifying a password)
Acc_2 CANNOT login as sysdba to the same DBA How is this ?
I get a ora 00031 : insuffecient privileges error
Thanks
How do I make my Acc_2 to work like Acc_1 ?
Please let me know what could be the reason
March 01, 2010 - 11:19 am UTC
get in touch with your DBA and System Administrator, they control this.
sysdba is granted to users by the operating system, you need to be in a 'special group', of which your DBA would have setup upon installation.
the OS group Acc_1 is in is part of the sysdba group, Acc_2 is not in that OS group and hence cannot connect locally using OS authentication like Acc_1 can.
Login as sysdba - happening from one account not from other
Umesh Kastuti, February 28, 2010 - 11:56 pm UTC
Adding to my above post.. There is no OS authentication initiated on the Server ( means there is no group called OSOPER or OSDBA)
Thanks
March 01, 2010 - 12:03 pm UTC
sure there is, they do not need to be called osoper or osdba.
If you are connecting without a password, using connect / as sysdba - or even connect santa/claus as sysdba (if / works, so will santa/claus) - you are using os authentication
Login as sysdba - happening from one account not from other
A reader, March 01, 2010 - 11:36 pm UTC
Ho Tom
How do I grant the sysdba from operating system to a particular OS user account
The DBA has created a password file using ORAPWD utility and there are 2 entries for the ACC_1 and ACC_2
March 02, 2010 - 7:22 am UTC
it would depend on the OS, please work with your sysadm or your DBA or support to configure your system.
If you are using a password file, you will be using SQLNET
connect u/p@data as sysdba
that does NOT use OS authentication. If you want to use OS authentication - get together with the people that installed the product - they'll know what to do (if not, time to break out your OS install guide and read it)
ORAPWD link
Gautham, October 18, 2010 - 10:31 am UTC
October 25, 2010 - 9:05 am UTC
rizwan, February 09, 2011 - 6:17 am UTC
Hi tom ,
i changed remote_login_passwordfile=none in my spfile (Oracle 10g version) and then tried to restart database so that parameter should take effect . Database shutdown successfully but its not starting ..
C:\Documents and Settings\rghadiya>sqlplus sys/rizwan as sysdba
SQL*Plus: Release 10.2.0.5.0 - Production on Wed Feb 9 17:40:01 2011
Copyright (c) 1982, 2010, Oracle. All Rights Reserved.
Connected to an idle instance.
select lower(user) || '@' || substr( global_name,1,
*
ERROR at line 1:
ORA-01034: ORACLE not available
idle> startup nomount;
ORA-01031: insufficient privileges
idle>
My question why is the database not starting up even though i have been connected as sysdba .
i have disabled os authentication in sqlnet.ora
does oracle check remote_login_passwordfile upon startup and if its NONE and os authentication is off it does not allow to startup the database ?
kindly explain
February 10, 2011 - 4:34 pm UTC
you need os authentication for a local sysdba connection.
you need to use "/ as sysdba" - the user/password doesn't matter - it will use os authentication only
ops$tkyte%ORA11GR2> connect santa/claus as sysdba;
Connected.
sys%ORA11GR2> connect santa/foobar as sysdba;
Connected.
sys%ORA11GR2> connect barfoo/foobar as sysdba;
Connected.
sys%ORA11GR2>
for a local sysdba connection - the user/password are not relevant, you are not connected as sysdba