Re: sys and system in 9i

"Ed Wong" <ewong_at_mail.com> wrote in message news:a5ae1554.0307041427.1ae6d623_at_posting.google.com...
> I've migrated an instance from 8.1.7 to 9.2.0. After the migration, I
> can connect to system by connection string "system/password_at_tnsnames"
> but can't do that as sys. I got an error ORA-28009: connection to sys
> should be as sysdba or sysoper. Is it a normal behavior?
>
> If I need to do "connect / as sysdba" instead of connecting
> sys/password, why oracle doesn't make "system" account the same way?

Because SYS owns the data dictionary tables and SYSTEM doesn't.

The init.ora parameter O7_DICTIONARY_ACCESSIBILITY used to default to true in 8.0 and 8i. That meant anyone could access the data dictionary tables if they'd been granted the 'select any table' privilege. In 9i, the default has changed to be false, which means the only person who can access those tables is their owner -ie, SYS.

And to enforce that, logging on as SYS in 'normal user' mode (ie, without the 'as sysdba') is no longer permitted.

That's a good thing, by the way: you shouldn't be logging on as SYS on a regular basis. But if you insist on it, change the parameter back to true, and connect sys/xxxx_at_tnsnames will work again.

~QM

>
> Thanks,
> ewong
Received on Fri Jul 04 2003 - 18:22:57 CDT