基于应用的需要,让普通用户有访问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)

发现修改完此参数后,select any table是真正意义上的any table了。

当然如果你的需求是普通用户只访问sys的某一个表或者几个表,你完全没必须费这么大的周折和冒这么大的安全风险来改这个参数,你完全可以授予普通用户对象权限来实现查询某张表。