1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Effect of O7_DICTIONARY_ACCESSIBILITY

Discussion in 'Server Administration and Options' started by Arju, Nov 7, 2008.

  1. Arju

    Arju Forum Expert

    Messages:
    107
    Likes Received:
    5
    Trophy Points:
    240
    Location:
    Bangladesh
    Let's see current value of O7_DICTIONARY_ACCESSIBILITY .
    Code (Text):
    SQL> show parameter O7_

    NAME                                 TYPE        VALUE
    ------------------------------------ ----------- ------------------------------
    O7_DICTIONARY_ACCESSIBILITY          boolean     FALSE
    Now connecting to database as a SYS user will fail.
    Code (Text):
    SQL> conn sys/a
    ERROR:
    ORA-28009: connection as SYS should be as SYSDBA or SYSOPER


    Warning: You are no longer connected to ORACLE.
    Let's change the value to TRUE and see the effect.
    Code (Text):
    SQL> conn / as sysdba
    Connected.
    SQL> alter system set O7_dictionary_accessibility=TRUE scope=spfile;

    System altered.
     
    Since it is static parameter so need to bounce the database.
    Code (Text):
    SQL> startup force
    ORACLE instance started.

    Total System Global Area  104857600 bytes
    Fixed Size                  1247516 bytes
    Variable Size              62916324 bytes
    Database Buffers           33554432 bytes
    Redo Buffers                7139328 bytes
    Database mounted.
    Database opened.
    Now connection as SYS is successful without SYSDBA privilege.
    Code (Text):
    SQL> conn sys/a
    Connected.
    Note: Setting O7_DICTIONARY_ACCESSIBILITY to TRUE is dangerous.
     
  2. prashant

    prashant Forum Advisor

    Messages:
    85
    Likes Received:
    7
    Trophy Points:
    160
    Location:
    New Delhi, India
    Yes that is right Arju!

    This behaviour is because sys objects can be accessed only by user who is logged in as sysdba. EVEN SYS cannot access its own objects unless during logon he authenticate himself as sysdba. After we set O7_DICTIONARY_ACCESSIBILITY=True, this check is no more there and even guys with 'select any table' priv can select tables like user$, obj$, seq$ etc.

    Regards,
    Prashant