,在定制環(huán)境的時候，運行以下腳本，出現(xiàn)問題： 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@]欲將這幾個動態(tài)性能視圖的select權(quán)限單獨賦給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
查詢后發(fā)現(xiàn)，原來v$對象，并不是視圖，而是指向v_$視圖的同義詞，而視圖是基于真正的v$視圖創(chuàng)建的。
還有X$表，這一部分表是Oracle數(shù)據(jù)庫的運行基礎(chǔ)，在數(shù)據(jù)庫啟動時由Oracle應用程序動態(tài)創(chuàng)建。
這部分表對數(shù)據(jù)庫來說至關(guān)重要，所以O(shè)racle不允許SYSDBA之外的用戶直接訪問，顯示授權(quán)不被允許。
將select權(quán)限賦給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
分享題目：Forv$viewsyouneedtograntprivilegetoeachv_$directly
當前URL：http://weahome.cn/article/igpsis.html
重慶分公司
重慶分公司
