Oracle视图授权(with grant option)

今天领导让我给一个用户授权几个视图的查询权限
遇到了一点小意外.还是基本功不扎实啊

模拟如下
create user a identified by a;
create user b identified by b;
create user c identified by c;

grant connect,resource to a;
grant connect,resource to b;
grant connect,resource to c;
grant create view to b;

conn a/a
create table t1 as select rownum rn from dual connect by level<10;
grant select on t1 to b;

conn b/b
create table t2 as select rownum rn from dual connect by level<10;
create view v1 as select * from a.t1 union all select * from t2;
grant select on v1 to c;

conn c/c
select * from b.v1;

用户a有一个基表,
用户b有用户a基表的查询权限,并创建了一个视图,
现在需要把用户b创建的视图,授权给用户c.

实际上grant select on v1 to c;命令执行会报错(ORA-01720).
那是因为a.t1的查询授权不能传递.

解决这个问题也很容易,在给b用户a.t1的授权中增加with grant option
create user a identified by a;
create user b identified by b;
create user c identified by c;

grant connect,resource to a;
grant connect,resource to b;
grant connect,resource to c;
grant create view to b;

conn a/a
create table t1 as select rownum rn from dual connect by level<10;
grant select on t1 to b with grant option;

conn b/b
create table t2 as select rownum rn from dual connect by level<10;
create view v1 as select * from a.t1 union all select * from t2;
grant select on v1 to c;

conn c/c
select * from b.v1;

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

转载于:http://blog.itpub.net/29254281/viewspace-1299022/