ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 视图创建时的权限问题

视图创建时的权限问题

原创 Linux操作系统 作者:物理狂人 时间:2012-02-02 19:52:43 0 删除 编辑

阅读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/,如需转载,请注明出处,否则将追究法律责任。

上一篇: Oracle rowid 详解
请登录后发表评论 登录
全部评论

注册时间:2010-06-16

  • 博文量
    80
  • 访问量
    140182