Re: ORA-01031 error message

Internal is not a real user, and adding "it" into the Operating System ORA_DBA group is not going to acheive anything.

Once more, therefore, for old times' sake....

When you wish to connect as a privileged user, you must connect as the User SYS in his Superhero role. To differentiate Superhero mode from ordinary User mode, you use the magic words 'as sysdba'. Hence, the correct string to connect as a Privileged User (one able to startup and shutdown the Instance) is: 'connect sys/password AS SYSDBA'

When Oracle sees the two magic words, it first checks the ORA_DBA group to see whether you, Rodney, the Windows User, has an account there. If it finds you there, it will actually ignore the 'sys' and 'password' bits that you typed, and let you on anyway by virtue of you being Rodney on Windows. That's called Operating System authentication. If it doesn't find you there, and assuming your init.ora says 'remote_login_passwordfile=exclusive' (or shared), then it will check what SYS's password is IN THE PASSWORD FILE (which might be -and is, to start with- quite different from SYS's password in the Data Dictionary). And that's called Password File authentication.

Now, "Internal" is just a rather old-fashioned, Oracle 7, way of typing "SYS as SYSDBA". In other words, "connect internal" is the same as typing "connect sys/password as sysdba". It saves you a few keystrokes, but otherwise Oracle behaves exactly the same way on seing the "Internal" magic word as it would if it saw the "as sysdba" magic words. There is thus no real User called Internal (and he certainly isn't a User on Windows!) -it's just a keyword that prompts Oracle into checking the ORA_DBA group, or the password file. Using "Internal" these days is a bad thing to do, because the keyword has been dropped in Oracle 9i, and there will be an awful lot of bad habits to unlearn whenever you eventually make the upgrade move.

If the magic words "as sysdba" are not present in your connect string (or "internal" if you insist), then Oracle checks the DATA DICTIONARY for matching passwords. SYS *does* have a data dictionary entry (change_on_install, by default), but when SYS connects using that, he ceases to be a Superhero that can startup Instances, and merely connects as a very powerful, but ordinary User.

Now the one nasty is trying to fit a tnsnames alias (your '@SID' thingy) into a connect string. It's quite hard to do using the 'sys/password as sysdba' syntax, because it's not entirely clear where the '@' bit should go. So let's keep things simple, and fall into bad habits, at least to start with...

Connect internal_at_SID should work (I'd get rid of the Internal account in the ORA_DBA group, though), provided that whoever you log on to Windows as has an entry in the ORA_DBA group (O/S authentication). There should be no need to specify a password -Oracle is trusting you to be a Privileged User because you've clearly already managed to convince Windows who you are.

Connect internal/password_at_SID should also work, if there isn't an ORA_DBA group, or if the account you log onto Windows as is not a member of the ORA_DBA group, *provided* that there is a password file, and you supply the password which is contained within that password (it starts off as 'oracle' by default). Also, get out of the habit of only typing the Username, and letting it prompt you for the password. It can cause problems, especially in sqlplus -I'd still be using server manager for privileged actions, if I were you.

Connect sys/password_at_SID will work, provided the password there is SYS's normal data dictionary password, and not the one in the password file. There's no 'as sysdba' magic word, so it's data dictionary authentication that's being used. The starter password is 'change_on_install'. And if it works, and you try to issue a startup or shutdown command, expect to see the 'insufficient privileges' error messages -this is ordinary SYS at work, not superhero SYS.

The tricky one is using the correct syntax for getting on as a Privileged User. You could try 'connect sys/password_at_SID as sysdba'. I think that's worked for me in the past. If not, it will be 'connect sys/password as sysdba_at_SID'. Or, assuming you are doing this all locally on the Server, you could take the path of least resistance, and make sure ORACLE_SID is set as an environment variable before you start.

HTH
Regards
HJR "Rodney Li" <rodney.li_at_stelco.ca> wrote in message news:a4qt6.157996$Z2.1991043_at_nnrp1.uunet.ca...
> Hi Sybrand:
> Thx for the reply.
> What I meant by writing username_at_SID/password was that I entered
> username_at_SID first and then I entered password when prompted. This seems
 to
> work.
> Anyway, there was no user called internal in the ORA_DBA group and I
 created
> it. However, when i log on from a client, I still have the same problem.
> Logging on as a user first works fine but when i try
 internal_at_SID/password,
> I get the insufficient privileges error message. However, when I open
> SQLplus and I enter internal as username, password and SID, it works!!
> Hope to hear from you soon,
> Rodney
> Sybrand Bakker wrote in message ...
> >
> >"Rodney Li" <rodney.li_at_stelco.ca> wrote in message
> >news:7rus6.155604$Z2.1967710_at_nnrp1.uunet.ca...
> >> Hi All,
> >> I just set up this Oracle Server 8.1.6 Enterprise Ed. Now, I can
 connect
> >> using sys_at_SID/password fine, same thing with any other user. However,
 when
 I
> >> try connecting as internal_at_SID/password, I get the error:
> >> ORA-01031: insufficient privileges.
> >> Anyone experienced that?
> >> Thx,
> >> Rodney
> >>
> >>
> >
> >First of all: try using the *proper* syntax, as documented in your
 manuals:
> ><username>/<password>@service
> >Secondly it looks like your user is not a member of the dba group (Unix)
 or
> >the local ora_dba group (NT)
> >
> >Hth,
> >
> >Sybrand Bakker, Oracle DBA
> >
> >
> >

>
> Received on Mon Mar 19 2001 - 13:57:12 CST