ORA-28009: connection as SYS should be as SYSDBA or SYSOPER

when you don't need as sysdba or as sysoper at connect time
=============================================================

ORA-28009: connection as SYS should be as SYSDBA or SYSOPER
============================================================



SQL*Plus: Release 10.2.0.1.0 - Production on Wed Apr 14 21:32:22 2010

Copyright (c) 1982, 2005, Oracle. All rights reserved.
SQL>
SQL> conn sys/sys
ERROR:
ORA-28009: connection as SYS should be as SYSDBA or SYSOPER


Warning: You are no longer connected to ORACLE.
SQL>
SQL>
SQL> conn sys/sys as sysdba
Connected.
SQL>


SQL> alter system set O7_DICTIONARY_ACCESSIBILITY=true scope=spfile;

System altered.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 591396864 bytes
Fixed Size 1250308 bytes
Variable Size 171969532 bytes
Database Buffers 411041792 bytes
Redo Buffers 7135232 bytes
Database mounted.
Database opened.
SQL>
SQL>
SQL> conn sys/sys
Connected.
SQL>
SQL>
QL> shutdown immediate
RA-01031: insufficient privileges
QL>
QL>

note: if you not connect as sysdba or sysoper you can't shutdown
or startup the database.


Thats why
if O7_DICTIONARY_ACCESSIBILITY=FALSE
you can't select any Dictionary table without connecting as sysdba or as sysoper
then you need to grant privilige "grant select any dictionary to user"

IF O7_DICTIONARY_ACCESSIBILITY=FALSE you can't need this privilige.



There are two side effects of this parameter being set to FALSE:
-----------------------------------------------------------------

1) that "sys/password" will not work without sysoper/sysdba, is
not clearly documented anywhere. The fact that the O7_DICTIONARY_ACCESSIBILITY init.ora
parameter causes this is somewhat hard to track down -- but that is what is causing this.


2)Access to the "real" data dictionary owned by SYS will not be available to users, even
if they have the SELECT ANY TABLE privelege. These are not the data dictionary views
like ALL_OBJECTS but rather the base tables like SYS.OBJ$ that will be unaccessible.