Sysdba/sysoper privileges

[yanban]

Hi all,

I am trying (just for testing) to add to another user ( MYUSER) the SYSDBA or SYSOPER privileges in order to let him open the DB.
What I have :
My parameter remote_login_passwordfile is set to exclusive.
I have only one DB ( and one instance) installed locally on a NT machine.
I have successfully given the privileges to this user,( see below a query about V$PWFILE_USERS) and see this user in my pw file.
I still can not open the DB ( see below).
Anyone could help?

Connecté Ã* :
Oracle8i Release 8.1.7.0.0 - Production
JServer Release 8.1.7.0.0 - Production

SQL> select * from v$pwfile_users;

USERNAME SYSDB SYSOP
------------------------------ ----- -----
INTERNAL TRUE TRUE
SYS TRUE TRUE
MYUSER TRUE TRUE

SQL> connect myuser/myuser;
Connecté.
SQL> startup
ORA-01031: privilèges insuffisants
SQL>

[jmodic]

Originally posted by yanban
SQL> connect myuser/myuser;
Connecté.

Change that to: "connect myuser/myuser as sysdba"

"Connecting as a user" and "connecting as a user as sysdba" are two very different things. Only if you are connected as sysdba you'll be able to startup the database.

[yanban]

You are right Jmodic : it works.

Thanks you.

[ly]

Originally posted by jmodic

Originally posted by yanban
SQL> connect myuser/myuser;
Connecté.

Change that to: "connect myuser/myuser as sysdba"

But did you notice that you can use any username and password to connect as a sysdba in this way. "connect ANYuser/ANYpasswd as sysdba". So, what's the use of password file?" . Can anyone help me to answer, thanks!

[jmodic]

Originally posted by ly
But did you notice that you can use any username and password to connect as a sysdba in this way. "connect ANYuser/ANYpasswd as sysdba".

No, not on my sistems. Only the accounts that have been granted SYSDBA can connect AS SYSDBA. All others get "ORA-01031: Insuficient privileges" when they try.

[Tarry]

Ly,
connect ,but where?
for instance in svrmgr

C:\>svrmgrl

Oracle Server Manager Release 3.1.6.0.0 - Production

Copyright (c) 1997, 1999, Oracle Corporation.  All Rights Reserved.

Oracle8i Enterprise Edition Release 8.1.6.0.0 - Production
With the Partitioning option
JServer Release 8.1.6.0.0 - Production
SVRMGR> connect scott/tiger as sysdba
ORA-01031: insufficient privileges
SVRMGR> exit
Server Manager complete.

or in Sqlplus..

C:\>SQLPLUS

Enter user-name: scott/tiger as sysdba
ERROR:
ORA-01031: insufficient privileges


Enter user-name: scott/tiger@nick

Connected to:
Oracle8i Enterprise Edition Release 8.1.6.0.0 - Production
With the Partitioning option
JServer Release 8.1.6.0.0 - Production

scott@NICK.WAGENBORG.COM>

[ly]

But this do happens in my environment. It happens in both "8i on Linux" and "9i on Win".

I can connect use any typing, like aaa/kkk, and aaa is not a username, I can just type anything. See the follow:

8i on Linux
============================================================
[oracle@LYLINUX oracle]$ sqlplus

SQL*Plus: Release 8.1.7.0.0 - Production on Wed Oct 2 09:22:11 2002

(c) Copyright 2000 Oracle Corporation. All rights reserved.

Enter user-name: aaa/kkk as sysdba

Connected to:
Oracle8i Enterprise Edition Release 8.1.7.0.1 - Production
With the Partitioning option
JServer Release 8.1.7.0.1 - Production

SQL> show user
USER is "SYS"
SQL> exit
Disconnected from Oracle8i Enterprise Edition Release 8.1.7.0.1 - Production
With the Partitioning option
JServer Release 8.1.7.0.1 - Production
[oracle@LYLINUX oracle]$ exit

===========================================================

9i on win2000
***********************************************************
C:\>sqlplus

SQL*Plus: Release 9.2.0.1.0 - Production on Wed Oct 2 09:54:53 2002

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

Enter user-name: ddd/fff
ERROR:
ORA-01017: invalid username/password; logon denied


Enter user-name: ddd/fff as sysdba

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 user
USER is "SYS"
SQL> show parameter remote_login

NAME TYPE VALUE
------------------------------------ ----------- -----------
remote_login_passwordfile string EXCLUSIVE
SQL>

***********************************************************

So strange! Any idea? Thanks!

[Edited by ly on 10-01-2002 at 09:54 PM]

[ehaskins]

Yes, strange...I tested it on my Win 2000 with 9.0.1 and got the same result. I can login with scott/tiger as SYSDBA even though scott is not included in the PWDFILE.

Could be a security hole in 9i? Chances are that we are just missing something though. I'll be interested in finding out though!

[jmodic]

I don't know about Linux, but on Win$ the thing goes like this:

By default there is an entry SQLNET.AUTHENTICATION_SERVICES = (NTS) in the sqlnet.ora. This means that when connecting to the database, it will use NT native authentication for all NT users that are included in the ORA_DBA or ORA_sid_DBA group. So if you are logged on host/domain as a ORA_DBA user, the database will not try to check your identity, it will simply rely on the fact that OS has allready authenticated you. So no matter what you type for your username/password, it will simply connect you as if you have provided "/ as sysdba".

If you don't want this behavior (it's not a bug, it's a feature! ), simply remove or comment out the SQLNET.AUTHENTICATION_SERVICES = (NTS) in your sqlnet.ora.

[ly]

What jmodic said is precisely correct! Thanks very much!