connect rene/rene
create user tq84_a identified by tq84_a quota unlimited on users;
create user tq84_b identified by tq84_b quota unlimited on users;
create user tq84_c identified by tq84_c quota unlimited on users;
grant create session, create table, create view to tq84_a;
grant create session, create table, create view, create procedure to tq84_b;
grant create session, create table, create view to tq84_c;
User A creates a table …
connect tq84_a/tq84_a
create table t (a number, b varchar2(10));
… and grants select to tq84_bwith …
grant select on t to tq84_b with grant option;
… and to tq84_cwithout grant option:
grant select on t to tq84_c;
User tq84_b creates a table and a view that selects from from tq84_a's table as well as from its own table:
connect tq84_b/tq84_b
create table t (a number, b varchar2(10));
create view v as
select * from tq84_a.t union all
select * from tq84_b.t;
Because tq84_b granted selectwith grant option, he can «pass on» selectability:
grant select on v to tq84_c;
User tq84_c does the same thing…
connect tq84_c/tq84_c
create table t (a number, b varchar2(10));
create view v as
select * from tq84_a.t union all
select * from tq84_c.t;
… but the following grant throws ORA-01720: grant option does not exist for TQ84_A.T:
grant select on v to tq84_b;
However, it is possible for tq84_c to create a PL/SQL object that selects from the view and provides its data to tq84_b:
create or replace function sel_cnt return number
authid definer
as
cnt number;
begin
select count(*) into cnt from v;
return cnt;
end;
/
grant execute on sel_cnt to tq84_b;
connect tq84_b/tq84_b
select tq84_c.sel_cnt from dual;
Cleaning up:
connect rene/rene
drop user tq84_a cascade;
drop user tq84_b cascade;
drop user tq84_c cascade;