Skip to content

Archive for

If O7_DICTIONARY_ACCESSIBILITY is set to TRUE, What happened?

“If you’re granted select any table to a user when O7_DICTIONARY_ACCESSIBILITY  is set to TRUE, the particular user can able to see the data dictionary tables.”

O7_DICTIONARY_ACCESSIBILITY controls restrictions on SYSTEM privileges. If the parameter is set to true, access to objects in the SYS schema is allowed (Oracle7 behavior). The default setting of false ensures that system privileges that allow access to objects in “any schema” do not allow access to objects in the SYS schema.

For example, if O7_DICTIONARY_ACCESSIBILITY is set to false, then the SELECT ANY TABLE privilege allows access to views or tables in any schema except the SYS schema (data dictionary tables cannot be accessed). The system privilege EXECUTE ANY PROCEDURE allows access on the procedures in any schema except the SYS schema.

If this parameter is set to false and you need to access objects in the SYS schema, then you must be granted explicit object privileges. The following roles, which can be granted to the database administrator, also allow access to dictionary objects:

  • SELECT_CATALOG_ROLE
  • EXECUTE_CATALOG_ROLE
  • DELETE_CATALOG_ROLE

Example :

SQL> grant select any table to scott;

Grant succeeded.

SQL> show parameter O7_DICTIONARY_ACCESSIBILITY;

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

O7_DICTIONARY_ACCESSIBILITY          boolean     FALSE

SQL> conn scott/tiger

Connected.

SQL> select * from dba_users;

select * from dba_users               *

ERROR at line 1: ORA-00942: table or view does not exist

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 1603411968 bytes

Fixed Size                  2226912 bytes

Variable Size             687867168 bytes

Database Buffers          905969664 bytes

Redo Buffers                7348224 bytes

Database mounted. Database opened.

SQL> show parameter O7_DICTIONARY_ACCESSIBILITY;

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

O7_DICTIONARY_ACCESSIBILITY          boolean     TRUE

SQL> conn scott/tiger
 Connected.

SQL> select count(username) from dba_users;

COUNT(USERNAME)

---------------

45

Ref : Oracle Document

Calling all High Rollers: Hit the Oracle PacktPot!

Calling all High Rollers: Hit the Oracle PacktPot!

Packt Publishing prepares for COLLABORATE 12 with discounts on Oracle titles all month

As a leading publisher of Oracle books/eBooks, Packt Publishing is inviting their readers to celebrate the upcoming COLLABORATE 12 conference being held in Las Vegas from 22nd-26th April.

The COLLABORATE 12 conference covers a wide array of topics across all Oracle technologies and applications, including everything from OBIEE and Siebel, to Hyperion and E-Business Suite R12. Over 6,000 attendees are expected in Las Vegas this April for an exciting week of sessions and networking, and Packt’s offer will help any Oracle professionals make the most of the conference, whether they’re attending or just taking an interest from afar.

Packt is offering a range of exciting discounts on their 60+ titles across all areas of Oracle technology including Applications, Database and Fusion Middleware:

  • 20% off all Oracle print books
  • 30% off all Oracle eBooks
  • 10% off Oracle PacktLib subscriptions

To lend a helping hand to those in attendance or professionals who are simply excited about the buzz that the glamorous Las Vegas event will bring, Packt’s discounts apply to all of their Oracle titles and formats.

For further information on Packt’s “Oracle PacktPot” offers in March, visit: Hit the Oracle packtpot 2012

Recent Oracle publications include:

Upcoming titles due in 2012:

  • Oracle E-Business Suite Financials R12: A      Functionality Guide
  • Oracle Database 11g: Data Warehousing and      Business Intelligence Solutions Cookbook
  • Oracle BAM 11gR1 Handbook

Throughout 2012, Packt will continue to build-upon the success of their vast number of Oracle titles venturing further into not only books on even more topics, but also exciting new formats and series’ like Cheat Sheets and Video.