Results 1 to 9 of 9
  1. #1
    Join Date
    Sep 2008
    Posts
    18

    Unanswered: Why "connect SYS/xxx AS SYSDBA" always TRUE ?

    No matter what I typed to the SYS password, my connections were always TRUE and show user always said : USER is "SYS".
    Did my connections really succeed ?

    SQL> connect sys/abc as sysdba;
    Connected.
    SQL> show user;
    USER is "SYS"
    SQL> connect sys/cde as sysdba;
    Connected.
    SQL> show user;
    USER is "SYS"

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Research "OS authentication". Your operating system user is a member of ORA_DBA group, so password doesn't matter. You could even
    Code:
    C:\>sqlplus mickey/mouse@ora10 as sysdba
    
    SQL*Plus: Release 10.2.0.1.0 - Production on ╚et Sij 21 12:06:17 2010
    
    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
    
    SQL> show user
    USER is "SYS"
    SQL>
    and there's no user "mickey" on my system.

  3. #3
    Join Date
    Mar 2007
    Posts
    623
    Quote Originally Posted by cpchan View Post
    No matter what I typed to the SYS password, my connections were always TRUE and show user always said : USER is "SYS".
    Did my connections really succeed ?
    Yes, if you are connected in OS as user oracle. See the difference:
    Code:
    SQL> conn sys/abc as sysdba
    Connected.
    SQL> conn sys/def as sysdba
    Connected.
    SQL> exit
    Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
    With the Partitioning option
    $ whoami
    oracle
    $
    Code:
    SQL> conn sys/abc as sysdba
    ERROR:
    ORA-01031: insufficient privileges
    
    
    SQL> conn sys/def as sysdba
    ERROR:
    ORA-01031: insufficient privileges
    
    
    SQL> exit
    Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64 bit Production
    With the Partitioning option
    # whoami
    root
    #

  4. #4
    Join Date
    Aug 2009
    Posts
    262
    and the solution for it ?

  5. #5
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Solution for what? Is there a problem? Security is what it is and should be handled by someone who knows what he/she is doing.

  6. #6
    Join Date
    Aug 2009
    Posts
    262
    my respect to you ,

    i am not asking for the brief intro on security concept .
    i am asking to be pointed to to the precise component of 11g / 10gxe
    where i can change it not to log in with any other password except the real one.

    excluding the user from dba group is not logical .

    audit
    security
    any parameter.
    and flag
    any component .
    em
    repository

    give me ur idea so i can compare it with mine

  7. #7
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    This behavior of the oracle database can not be disabled so the dba can never be locked out of the database. No one but the dba should have the password for the oracle user on the database server.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  8. #8
    Join Date
    Jul 2003
    Posts
    2,296
    I agree. Owner of oracle binaries needs to be able to start/stop the database services, etc. SOMEONE has to be the owner right?

    Usually only the the "senior dba" has this password and other users are created with separate privileges. Not everyone should be logging in as "oracle" (actually only one person). This is how you would solve your situation. Create other groups with different privileges (ie: junoir_dba, whatever).

    To be honest, if you have someone logging in as oracle who shouldn't be then you have bigger problems on your hands. They could just delete your whole directory structure so I wouldn't worry about them logging into the database as sys.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  9. #9
    Join Date
    Aug 2009
    Posts
    262
    Administrator Authentication
    One question that you may be asking is whether you already use OS authentication when you
    connect “/ as sysdba”? â e answer is that this is indeed a form of Oracle trusting a user that has
    been authenticated with the OS but this mode is called administrator authentication and is quite
    diff erent. You have special handling for administrator authentication to solve the chicken-and-egg
    problem of starting a database. If the normal authentication method is O3/O5 LOGON and the
    database does the authentication using usernames and passwords stored in the database, how do
    you authenticate the person who needs to start the database? If the database is down, how is it
    going to authenticate this administrator and check their password? To solve this, Oracle authen-
    ticates administrators (users who have the sysdba or sysoper system privileges) using the OS sepa-
    rately from IDENTIFIED EXTERNALLY.
    In almost all installations of Oracle, if you are logged onto the OS a s the Oracle instance
    account you can connect as the SYS user using sysdba or sysoper privileges without supplying a
    password:
    oracle10% sqlplus "/ as sysdba"
    SQL*Plus: Release 10.2.0.1.0 - Production on Fri Jan 25 11:59:41 2008
    Copyright (c) 1982, 2005, Oracle. All rights reser ved.
    Connected to:
    Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
    With the Partitioning, OLAP, and Data Mining options
    SQL> show user
    USER is "SYS"
    To understand how this works, let’s look at Oracle on Unix. When you install Oracle on Unix there
    are two OS groups, OSDBA and OSOPER, that map into two Unix user groups—dba and oper.
    When an OS user tries to log onto Oracle as /, Oracle checks the Unix user group for which the
    user belongs to. If the user belongs to the Unix user group dba and the connection is being done as
    sysdba then Oracle will allow the connection and log the user on as SYS with the sysdba privileges.
    If the user belongs to the Unix user group oper and the connection is being done as sysoper, then
    Oracle will allow the connection and log the user on as PUBLIC with the sysoper privileges:
    oracle10% sqlplus "/ as sysoper"
    SQL*Plus: Release 10.2.0.1.0 - Production on Fri Jan 25 12:04:39 2008
    Copyright (c) 1982, 2005, Oracle. All rights reser ved.
    Connected to:
    Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
    With the Partitioning, OLAP, and Data Mining options
    SQL> show user
    USER is "PUBLIC"
    In both these cases there is no explicit username or password. â is is a form of OS authentication—
    but it requires you to be logged in locally to the host where the database is installed and it relies
    on Unix user groups. It is not a general-purpose authentication scheme that you can use for any
    user. Also, it cannot be used for connecting as sysdba or sysoper over the network—for that you
    need to set up password fi les.

Posting Permissions

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