north hunter

纸上得来终觉浅,绝知此事要躬行

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;


【2】

虽然“授人以鱼,不如授之以渔”出自中国古语,但却不影响oracle精之其髓。oracle中授权使用:

grant create session to testuser;
  • 1

如果说这里被授予的权限“create session”是鱼,那“testuser”只能说是饿不死,还不能说吃得饱,或者说只修了身还没有养家经营的手段。

oracle授权中也有“渔”,这个渔决定了被授权用户是否能将权限继续授权给其他用户。只不过这里的oracle把“渔”细化了,分为两个:

with admin option

使用with admin option,被授权用户可将所获得的权限再次授予其它用户或角色,而且取消授权时不级联。例如:

grant create session to user_a with admin option;
  • 1

则用户user_a用户拥有了“create session”权限,然后用户user_a操作:

grant create session to user_b;
  • 1

则user_b也拥有了“create session”权限。 
如果系统管理员要回收user_a的权限,则user_b的权限仍然保留,但管理员可以显式回收user_b的权限:

revoke create session from user_b;
  • 1

with grant option

使用with grant option。被授权用户可将所获得的权限再次授予其它用户或角色,并且权限的取消是级联的。级联的意思是,如果user_a使用“with grant option”语句将权限又授予了user_b,当管理员回收user_a的权限时,则user_b的权限也会被回收。但管理员不可以显式回收用户user_b的权限。


阅读更多
想对作者说点什么? 我来说一句

没有更多推荐了,返回首页

不良信息举报

Oracle视图授权(with grant option)

最多只允许输入30个字

加入CSDN,享受更精准的内容推荐,与500万程序员共同成长!
关闭
关闭