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

今天在測試的過程中遇到了一個小問題，這讓我產(chǎn)生了疑惑：為什么單獨執(zhí)行的時候就可以，而在創(chuàng)建view的時候就會提示沒有權(quán)限？

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的權(quán)限，于是就查詢了seiang用戶的去權(quán)限視圖：

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.

回收權(quán)限，繼續(xù)試驗

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中創(chuàng)建視圖，必須具有CREATE VIEW系統(tǒng)去權(quán)限。 要在其他用戶的schema中創(chuàng)建視圖，必須具有CREATE ANY VIEW系統(tǒng)權(quán)限。

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

要創(chuàng)建一個子視圖，必須具有UNDER ANY VIEW系統(tǒng)權(quán)限或者該超級視圖的UNDER對象權(quán)限。

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的所有者必須具有從視圖（基于的所有表或視圖）中選擇，插入，更新或刪除行所必需的權(quán)限。 所有者必須直接授予這些權(quán)限，而不是通過角色授予。

作者：SEian.G（苦練七十二變，笑對八十一難）