Saturday, April 17, 2010

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 - Production on Wed Apr 14 21:32:22 2010

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

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

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> conn sys/sys
QL> shutdown immediate
RA-01031: insufficient privileges

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

Thats why
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.

1 comment:

Anonymous said...

may the blessing be with you.........................................