Re: Logging in "AS SYSDBA" vs. without it ("NORMAL")

"ohaya" <ohaya_at_cox.net> wrote in message news:41E8E069.B7BAE0B3_at_cox.net...
>
>
> Michel Cadot wrote:
> >
> > "ohaya" <ohaya_at_cox.net> a écrit dans le message de
news:41E881D0.12873BA7_at_cox.net...
> > | Hi,
> > |
> > | Suppose that I have created a user ("foo") in the database instance
> > | "foodb" using sqlplus. I have also granted the user "foo" several
> > | roles, including "CONNECT", "DBA", and "RESOURCE". The user password
> > | was assigned in the "CREATE USER", as "foopwd".
> > |
> > | When I connect as user "foo", I can enter either "foopwd" or "foopwd
as
> > | sysdba" as the password.
> > |
> > | I was wondering if someone could tell me, in general, exactly what the
> > | difference is between using the different password forms?
> > |
> > | For example, if I login using the password "foopwd as sysdba", do I
get
> > | more of the roles that were granted to the user vs. if I login using
the
> > | password "foopwd"?
> > |
> > | How exactly does this work?
> > |
> > | Thanks,
> > | Jim
> >
> > 2 sections to read:
> >
> > User Authentication:
> >

http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96524/c23acces.htm#CNCPT323
> >
> > Database Administrator Authentication:
> >

http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96521/dba.htm#1064
> >
> > Regards
> > Michel Cadot
>
>
> Michel,
>
> Thanks for the pointers.
>
> I'm still unclear about:
>
> 1) I did a "select * from v$pwfile_users;". According to one of those
> pages, this is suppose to show users with the SYSDBA privilege, and only
> the "SYS" user was displayed. I thought that only users with the SYSDBA
> privilege could login with "AS SYSDBA", but I can login as my test user
> "foo" with "AS SYSDBA". According toe the "select * from
> v$pwfile_users;", shouldn't only "SYS" be able to login with "AS
> SYSDBA"?
>
> 2) I read through those pages, but am still not 100% clear about what is
> different when I login with "AS SYSDBA" and without "AS SYSDBA". Is the
> only difference that when I login with "AS SYSDBA" that I then have
> access to the "SYS" tables?
>
> Thanks,
> Jim

Jim,

Keep in mind that "as sysdba" is not granted within the database, don't confuse it with the DBA role. You need to be able to connect with "as sysdba" when the database is shut down (or hasn't even been created yet!)

IMO, regarding your item 2 above, the URL below offers the best explanation. Clear, concise and it even has a little table of exactly what you can do "with SYSDBA".

http://oraclesvca2.oracle.com/docs/cd/B10501_01/server.920/a96521/dba.htm#10431

HTH,
Randy Received on Sat Jan 15 2005 - 09:24:54 CST