普通用户授予select any table 权限

原创

chinadm123 2012-12-20 21:42:49 博主文章分类：oracle 基础 ©著作权

文章标签 Oracle 的 where version p 文章分类 Oracle 数据库

©著作权归作者所有：来自51CTO博客作者chinadm123的原创作品，请联系作者获取转载授权，否则将追究法律责任

基于应用的需要，让普通用户有访问sys表的权限，于是就想到了select any table 的权限，可是当授权以后发现还是不能访问sys的表，经过查一系列资料，发现select any table不是真正的any table。下面做这个实验：

SQL> select * from v$version where rownum<2;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

SQL> show user;
USER is "SYS"

SQL> create table baby(name varchar2(10),sex char(5));

Table created.

SQL> insert into baby values('keren','nv');

1 row created.

SQL> commit;

Commit complete.

SQL> grant select any table to mdu;

Grant succeeded.

SQL> conn mdu/oracle
Connected.
SQL> select * from user_sys_privs;

USERNAME                       PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
MDU                            UNLIMITED TABLESPACE                     NO
MDU                            SELECT ANY TABLE                         NO

SQL> select * from sys.baby;
select * from sys.baby
*
ERROR at line 1:
ORA-00942: table or view does not exist

这是为什么呢？经过google,发现是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 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

原来在oracle7及之前版本中，此参数默认设置为true，也就说只要普通用户被授予了select any table的权限，就可以访问任意一个表了，包括sys的表；也正是因为这样，给系统带来不少安全隐患，所以自从oracle8i开始此参数被默认设置为false，也就是即使普通用户被授予了select any table的权限，但sys用户的表仍然不能被访问（其它用户的表是可以访问的）。

那么现在非常清楚了，如果非要访问sys的表，就要把这个参数设置为true，通过上面引用的官方文档可以看出，此参数是静态的，也就是修改参数值需要重启database，也可以通过下面方法判断修改此参数是需要重启数据库的：

SQL> select name,ISSYS_MODIFIABLE from v$parameter where name='O7_DICTIONARY_ACCESSIBILITY';

NAME ISSYS_MOD
------------------------------ ---------
O7_DICTIONARY_ACCESSIBILITY FALSE #####false 代表修改值后重启才生效#####

SQL> conn /as sysdba
Connected.
SQL> show parameter O7_DICTIONARY_ACCESSIBILITY

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

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 730714112 bytes
Fixed Size                  2216944 bytes
Variable Size             515902480 bytes
Database Buffers          209715200 bytes
Redo Buffers                2879488 bytes
Database mounted.
Database opened.

SQL> show parameter O7_DICTIONARY_ACCESSIBILITY

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

SQL> conn mdu/oracle
Connected.
SQL> select * from sys.baby;

NAME SEX
------------------------------ -----
keren nv

SQL> desc v$instance;
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
INSTANCE_NUMBER                                    NUMBER
INSTANCE_NAME                                      VARCHAR2(16)
HOST_NAME                                          VARCHAR2(64)
VERSION                                            VARCHAR2(17)
STARTUP_TIME                                       DATE
STATUS                                             VARCHAR2(12)
PARALLEL                                           VARCHAR2(3)
THREAD#                                            NUMBER
ARCHIVER                                           VARCHAR2(7)
LOG_SWITCH_WAIT                                    VARCHAR2(15)
LOGINS                                             VARCHAR2(10)
SHUTDOWN_PENDING                                   VARCHAR2(3)
DATABASE_STATUS                                    VARCHAR2(17)
INSTANCE_ROLE                                      VARCHAR2(18)
ACTIVE_STATE                                       VARCHAR2(9)
BLOCKED                                            VARCHAR2(3)