This discussion is archived
4 Replies Latest reply: Dec 7, 2012 4:28 AM by EdStevens RSS

Why do users connect as SYSDBA if they are locked?

978434 Newbie
Currently Being Moderated
Hi All, I am new to Oracle. I noticed that if i use SQL*PLUS to connect ot a dba using "Scott" and "tiger", i get a notice that the account is locked out. Interestingly, if i connect with "Scott as SYSDBA" and "tiger" I get a session. Can someone explain why this is?
  • 1. Re: Why do users connect as SYSDBA if they are locked?
    Srini Chavali-Oracle Oracle ACE Director
    Currently Being Moderated
    SYSDBA is a special privilege that allows you to connect without authentication.

    http://docs.oracle.com/cd/E11882_01/server.112/e10897/users_secure.htm#ADMQS12004

    Try this command - "sqlplus foobar as SYSDBA" and enter anything for the password - what do you see ?

    This privilege only works if the OS account you are using to log in is in a special group called "dba"

    HTH
    Srini
  • 2. Re: Why do users connect as SYSDBA if they are locked?
    Osama_Mustafa Oracle ACE
    Currently Being Moderated
    The SYSDBA and SYSOPER system privileges allow access to a database instance even when the database is not open. Control of these privileges is totally outside of the database itself.

    The SYSDBA and SYSOPER privileges can also be thought of as types of connections that enable you to perform certain database operations for which privileges cannot be granted in any other fashion. For example, you if you have the SYSDBA privilege, you can connect to the database by specifying CONNECT AS SYSDBA.

    http://docs.oracle.com/cd/B28359_01/server.111/b28310/dba006.htm
  • 3. Re: Why do users connect as SYSDBA if they are locked?
    Aman.... Oracle ACE
    Currently Being Moderated
    975431 wrote:
    Hi All, I am new to Oracle. I noticed that if i use SQL*PLUS to connect ot a dba using "Scott" and "tiger", i get a notice that the account is locked out. Interestingly, if i connect with "Scott as SYSDBA" and "tiger" I get a session. Can someone explain why this is?
    This command "sys / as sysdba" actually uses the o/s authentication along with the role Sysdba which means, the username that you are entering won't be honored but every user is going to be turned into the Sys account at the end. This means, even if you enter "scott / as sysdba" , the username that would be finally connected would be still SYS . The password is also not looked upon as the o/s authentication takes the precedence .

    HTH
    Aman....
  • 4. Re: Why do users connect as SYSDBA if they are locked?
    EdStevens Guru
    Currently Being Moderated
    975431 wrote:
    Hi All, I am new to Oracle. I noticed that if i use SQL*PLUS to connect ot a dba using "Scott" and "tiger", i get a notice that the account is locked out. Interestingly, if i connect with "Scott as SYSDBA" and "tiger" I get a session. Can someone explain why this is?
    Note what the others said about OS authentication and the 'dba' OS group. Now, realize the only a very limited number of people -- DBAs only -- should be members of that group and thus have that special privilege.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points