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（苦練七十二變，笑對八十一難）