For v$ views you need to grant privilege to each v_$ directly
最近在看Thomas Kyte的<Expert Oracle.Database.Architecture.9i.and.10g>,在定制環境的時候，運行以下腳本，出現問題： scott@SHENZHEN> grant select any table to scott; scott@SHENZHEN> create or replace view stats
as select 'STAT...' || a.name name, b.value
from v$statname a, v$mystat b
where a.statistic# = b.statistic#
union all
select 'LATCH.' || name, gets
from v$latchlatch
union all
9 select 'STAT...Elapsed Time', hsecs from v$timer;
from v$statname a, v$mystat b
*
ERROR at line 3:
ORA-01031: insufficient privileges
[@more@]
欲將這幾個動態性能視圖的select權限單獨賦給Scott：
sys@SHENZHEN> grant select on v$mystat to scott;
grant select on v$mystat to scott
*
ERROR at line 1:
ORA-02030: can only select from fixed tables/views
查詢后發現，原來v$對象，并不是視圖，而是指向v_$視圖的同義詞，而視圖是基于真正的v$視圖創建的。
還有X$表，這一部分表是Oracle數據庫的運行基礎，在數據庫啟動時由Oracle應用程序動態創建。
這部分表對數據庫來說至關重要，所以Oracle不允許SYSDBA之外的用戶直接訪問，顯示授權不被允許。
將select權限賦給4個v_$視圖
sys@SHENZHEN> grant select on v_$mystat to scott;
Grant succeeded.
sys@SHENZHEN> grant select on v_$statname to scott;
Grant succeeded.
sys@SHENZHEN> grant select on v_$latch to scott;
Grant succeeded.
sys@SHENZHEN> grant select on v_$timer to scott;
Grant succeeded.
scott@SHENZHEN> create or replace view stats
2 as select 'STAT...' || a.name name, b.value
3 from v$statname a, v$mystat b
4 where a.statistic# = b.statistic#
5 union all
6 select 'LATCH.' || name, gets
7 from v$latch
8 union all
9 select 'STAT...Elapsed Time', hsecs from v$timer;
View created.
問題解決！
Reference:
http://space.itpub.net/9252210/viewspace-591741
http://www.orafaq.com/forum/t/72902/2/
http://zhangsolomon.itpub.net/post/39913/488842
http://blog.csdn.net/yang073402/archive/2010/04/13/5482987.aspx
http://www.alidw.com/?p=263
