ORA-02030: can only select from fixed tables/views

今天在测试的过程中遇到了一个小问题，这让我产生了疑惑：为什么单独执行的时候就可以，而在创建view的时候就会提示没有权限？

SEIANG@seiang11g>select value from v$mystat, v$statname 

  2  where v$mystat.statistic# = v$statname.statistic# 

  3  and v$statname.name = 'redo size';

     VALUE

----------

     29152

SEIANG@seiang11g>create or replace view redo_size1

  2  as

  3  select value from v$mystat, v$statname 

  4  where v$mystat.statistic# = v$statname.statistic# 

  5  and v$statname.name = 'redo size';

select value from v$mystat, v$statname

                            *

ERROR at line 3:

ORA-01031: insufficient privileges

起初，还以为是没有create view的权限，于是就查询了seiang用户的去权限视图：

SEIANG@seiang11g>select * from role_sys_privs where PRIVILEGE = 'CREATE VIEW';

ROLE       PRIVILEGE                                ADM

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

DBA        CREATE VIEW                              YES

问题解决：

第一次：

SYS@seiang11g>GRANT SELECT ANY DICTIONARY to seiang;

Grant succeeded.

SEIANG@seiang11g>create or replace view redo_size

  2  as

  3  select value from v$mystat, v$statname 

  4  where v$mystat.statistic# = v$statname.statistic# 

  5  and v$statname.name = 'redo size';

View created.

回收权限，继续试验

SYS@seiang11g>revoke SELECT ANY DICTIONARY from  seiang;

Revoke succeeded.

SEIANG@seiang11g>create or replace view redo_size1

  2  as

  3  select value from v$mystat, v$statname 

  4  where v$mystat.statistic# = v$statname.statistic# 

  5  and v$statname.name = 'redo size';

select value from v$mystat, v$statname

                            *

ERROR at line 3:

ORA-01031: insufficient privileges

第二次：

SYS@seiang11g>grant select on v$statname to seiang;

grant select on v$statname to seiang

                *

ERROR at line 1:

ORA-02030: can only select from fixed tables/views

SYS@seiang11g> select * from dba_synonyms t where t.synonym_name = 'V$STATNAME';

OWNER      SYNONYM_NAME    TABLE_OWNER    TABLE_NAME                     DB_LINK

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

PUBLIC     V$STATNAME      SYS            V_$STATNAME

分析：对以v$开头的视图，不能直接grant，v$开头的视图是v_$的同义词

第三次：

SYS@seiang11g>grant select on v_$statname to seiang;

Grant succeeded.

SEIANG@seiang11g>create or replace view redo_size1

  2  as

  3  select value from v$mystat, v$statname 

  4  where v$mystat.statistic# = v$statname.statistic# 

  5  and v$statname.name = 'redo size';

select value from v$mystat, v$statname

                  *

ERROR at line 3:

ORA-01031: insufficient privileges

SYS@seiang11g>grant select on v_$mystat to  seiang;

Grant succeeded.

 SEIANG@seiang11g>create or replace view redo_size3

  2  as

  3  select value from v$mystat, v$statname 

  4  where v$mystat.statistic# = v$statname.statistic# 

  5  and v$statname.name = 'redo size';

View created.

下面是Oracle 11g官方文档的解释说明：

*******************************************************************************

To create a view in your own schema, you must have the CREATE VIEW system privilege. To create a view in another user's schema, you must have the CREATEANY VIEW system privilege.

要在自己的schema中创建视图，必须具有CREATE VIEW系统去权限。 要在其他用户的schema中创建视图，必须具有CREATE ANY VIEW系统权限。

To create a subview, you must have the UNDER ANY VIEW system privilege or the UNDER object privilege on the superview.

要创建一个子视图，必须具有UNDER ANY VIEW系统权限或者该超级视图的UNDER对象权限。

The owner of the schema containing the view must have the privileges necessary to either select, insert, update, or delete rows from all the tables or views on which the view is based. The owner must be granted these privileges directly, rather than through a role.

包含视图schema的所有者必须具有从视图（基于的所有表或视图）中选择，插入，更新或删除行所必需的权限。 所有者必须直接授予这些权限，而不是通过角色授予。

作者：SEian.G（苦练七十二变，笑对八十一难）