Re: Connecting as SYSDBA/Normal

You haven't received very enlightening replies, so let me help out a bit.

In Oracle 7, when I gave you the (for example) 'select ANY table' privilege, it meant you could exercise that privilege in ANY schema -including SYS's. Which meant that you had full access to my data dictionary tables, even though all I wanted to do was quickly give you the ability to select from Scott's EMP, Brenda's SALES, Fred's CUSTOMERS and Friedrich Marien-Battenburg's CAKE_RECIPES. Letting you waltz all through my data dictionary was not what I wanted at all!

So in Oracle 8.0, they split things. A grant of an "ANY" privilege now meant 'rights in anybody's schema EXCEPT SYS's'. Problem solved. But the problem was that a rather large number of badly-written applications had been written in Version 7 days that *needed* access to the data dictionary to work properly... so leaving things like this would have broken them all. So, a fix was needed in the form of a new init.ora parameter called o7_dictionary_accessibility. That could be set to TRUE, meaning that thing worked just like Oracle 7: the grant of an ANY privilege implied access to the data dictionary. Set it to false, and the new 8.0 idea of 'any schema BUT SYS's' became true. The default value for this parameter was TRUE, so in this respect, by default, Oracle 8.0 behaved exactly as Oracle 7 had done.

And so things stayed in Oracle 8i.

But in 9i, given that many moons had passed, they decided to change the default, so the same parameter now defaults to FALSE.

And it's this that explains the behaviour you report. Trouble is, with the default of FALSE, only privileged users can view the data dictionary tables. Yet those tables are owned by SYS. Therefore, SYS *must* be a privileged user to look at his own tables. Therefore, the login process demands that SYS connect 'as sysdba'.

The behaviour is reversible. If you take the trouble to set o7_dictionary_accessibility back to a setting of TRUE, you'll find that 'connect sys/oracle' -without the 'as sysdba'- works as it always did.

However, there was a good reason for inventing the split between 'any' and 'sys' in the first place, and I wouldn't recommend non-privileged SYS connections.

Regards
HJR "Stjepan Brbot" <stjepan.brbot_at_zg.hinet.hr> wrote in message news:ai1p0r$fer6$2_at_as201.hinet.hr...
> There is a lot of question people asking why they cannot connect as
> SYSDBA. There is really opposite question; I can connect SYS as SYSDBA
> but not as Normal. Every time I try start SQL*Plus as SYS as Normal I
> get the error message telling me that I'm not allowed to connect on DB
> as SYS without SYSDBA privilege! What's going on here?
>
> (O9i)
> --
>
> Stjepan Brbot
>
>
>
>
Received on Mon Jul 29 2002 - 04:13:55 CDT