O7_DICTIONARY_ACCESSIBILITY

先来看官方文档

O7_DICTIONARY_ACCESSIBILITY

Property	Description
Parameter type	Boolean
Default value	false
Modifiable	No
Range of values	true | false

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 SYSschema.

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 SYSschema (data dictionary tables cannot be accessed). The system privilege EXECUTE ANY PROCEDURE allows access on the procedures in any schema except theSYS 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

测试：

会话A
[root@yjgocp ~]# su - oracle
[oracle@yjgocp ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Tue May 21 16:08:24 2013
Copyright (c) 1982, 2009, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> show parameter o7

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
O7_DICTIONARY_ACCESSIBILITY          boolean     FALSE

会话B

SQL> conn u_1/u_1
Connected.

SQL> select * from user_role_privs;

USERNAME                       GRANTED_ROLE                   ADM DEF OS_
------------------------------ ------------------------------ --- --- ---
U_1                            CONNECT                        NO  YES NO
U_1                            RESOURCE                       NO  YES NO


SQL> select * from session_privs;

PRIVILEGE
----------------------------------------
CREATE SESSION
UNLIMITED TABLESPACE
CREATE TABLE

SELECT ANY TABLE

CREATE CLUSTER
CREATE SEQUENCE
CREATE PROCEDURE
CREATE TRIGGER
CREATE TYPE
CREATE OPERATOR
CREATE INDEXTYPE

10 rows selected.

SQL> select * from v$log;
select * from v$log
              *
ERROR at line 1:
ORA-00942: table or view does not exist

说明访问不了数据字典；

会话A

SQL> show parameter spfile;


NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      /u01/app/oracle/product/11g/db
                                                 s/spfileocpyjg.ora

SQL> alter system set O7_DICTIONARY_ACCESSIBILITY=TRUE SCOPE=SPFILE;


System altered.


SQL> show parameter o7


NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
O7_DICTIONARY_ACCESSIBILITY          boolean     FALSE

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

SQL> startup
ORACLE instance started.


Total System Global Area  422670336 bytes
Fixed Size                  1336960 bytes
Variable Size             348129664 bytes
Database Buffers           67108864 bytes
Redo Buffers                6094848 bytes
Database mounted.
Database opened.

SQL> show parameter o7


NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
O7_DICTIONARY_ACCESSIBILITY          boolean     TRUE

会话B

SQL> select * from v$log;


    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC
---------- ---------- ---------- ---------- ---------- ---------- ---
STATUS           FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------------- ------------- --------- ------------ ---------
         1          1         10   52428800        512          1 NO
CURRENT                1828058 21-MAY-13   2.8147E+14


         2          1          8   52428800        512          1 YES
INACTIVE               1771597 15-MAY-13      1800119 19-MAY-13


         3          1          9   52428800        512          1 YES
INACTIVE               1800119 19-MAY-13      1828058 21-MAY-13


说明已经可以访问数据字典；

注：OCP 有一道题是：

Some non-DBA users in your database have been granted ANY TABLE system privileges and
they are able to access data dictionary base tables. You decide to restrict their access to data
dictionary objects.

Which method would you adopt to achieve this objective?

A.Revoke the RESOURCE role from the users.
B.Set the value of the OS_ROLES parameter to TRUE.
C.Use Database Resource Manager to restrict user access to objects.
D.Grant ANY TABLE system privileges again without ADMIN OPTION.
E.Set the value of the O7_DICTIONARY_ACCESSIBILITY parameter to FALSE.

正是考察该知识点；