Sysdba/sysoper privileges
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Sysdba/sysoper privileges

  1. #1
    Join Date
    Apr 2002
    Location
    France
    Posts
    118
    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>
    If there is no solution, it is because there is no problem - Shadok -

  2. #2
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  3. #3
    Join Date
    Apr 2002
    Location
    France
    Posts
    118
    You are right Jmodic : it works.

    Thanks you.


    If there is no solution, it is because there is no problem - Shadok -

  4. #4
    Join Date
    Sep 2002
    Posts
    30
    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!

  5. #5
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  6. #6
    Join Date
    Jan 2002
    Location
    Netherlands
    Posts
    1,587
    Ly,
    connect ,but where?
    for instance in svrmgr
    Code:
    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..
    Code:
    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>
    Tarry Singh
    I'm a JOLE(JavaOracleLinuxEnthusiast)
    TarryBlogging
    --- Everything was meant to be---

  7. #7
    Join Date
    Sep 2002
    Posts
    30
    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]

  8. #8
    Join Date
    Dec 2001
    Location
    New Jersey
    Posts
    292
    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!
    Edward Haskins, OCP
    OraKnowledge, Inc.
    www.oraknowledge.com

    --"Live" Instructor-led Online Training for Oracle9i, Java and XML--

  9. #9
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  10. #10
    Join Date
    Sep 2002
    Posts
    30
    What jmodic said is precisely correct! Thanks very much!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width