视图创建时的权限问题

阅读tom的书时，实验环境需要建立一个收集状态信息的视图。

test@ORCL> create or replace view stats as
2 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 /

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$latch

ORA-01031: insufficient privileges

尝试给用户xezf赋权限

sys@SQL> grant select on v$statname to xezf;
grant select on v$statname to xezf
                *
ERROR at line 1:
ORA-02030: can only select from fixed tables/views

[oracle@station3 ~]$ oerr ora 2030
02030, 00000, "can only select from fixed tables/views"
// *Cause: An attempt is being made to perform. an operation other than
//         a retrieval from a fixed table/view.
// *Action: You may only select rows from fixed tables/views.

对于fixed tables/view 只能检索数据，不能进行其他的操s@作。怎么办呢？

我们知道v$是v_$的同义词，试试对v_$赋权限怎么样？

sys@ORCL> grant select on v_$statname to xezf;

Grant succeeded.

sys@ORCL> grant select on v_$mystat to xezf;

Grant succeeded.

sys@ORCL> grant select on v_$latch to xezf;

Grant succeeded.

OK，可以grant。 再尝试建立视图。

test@ORCL> create or replace view stats as
2 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 ;

View created

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/24104518/viewspace-715547/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/24104518/viewspace-715547/