潛水珽的異想世界

ref: http://hi.baidu.com/zhaozhe521/blog/item/bf6c9b2f0d8353321f3089cd.html

1. User A have table A1.
User B have table B1.
User C create a view C1. View C based on A.A1 and B.B1.
Grant select on C.View C1 to user D.
Select C.C1 from D report error ORA-1031.

2.Solution:
A. Grant select on A.A1 and B.B1 WITH GRANT OPTION to C.
B. Grant select an table privilege to D.
C. Create a new MView on C based on View C1. Then grant select on C.MView to D.

Test:
1.Grant select on A.A1 and B.B1 WITH GRANT OPTION to C.

conn A/A
create table A1 (nn number);
insert into A1 values (1);
insert into A1 values (2);

conn B/B
create table B1 (nn number);
insert into B1 values (1);

conn / as sysdba
grant select on A.A1 to C WITH GRANT OPTION;
grant select on B.B1 to C WITH GRANT OPTION;

conn C/C
create view C1 as select * from A.A1 x, B.B1 y where x.nn=y.nn;

conn / as sysdba
grant select on C.C1 to D;

conn D/D
select * from C.C1;

2. Grant select an table privilege to D.

conn A/A
create table A1 (nn number);
insert into A1 values (1);
insert into A1 values (2);

conn B/B
create table B1 (nn number);
insert into B1 values (1);

conn / as sysdba
grant select on A.A1 to D;
grant select on B.B1 to D;
grant select on A.A1 to C;
grant select on B.B1 to C;

conn C/C
create view C1 as select * from A.A1 x, B.B1 y where x.nn=y.nn;

conn / as sysdba
grant select on C.C1 to D;

conn D/D
select * from C.C1;

3. Create a new MView on C based on View C1. Then grant select on C.MView to D.

conn A/A
create table A1 (nn number);
insert into A1 values (1);
insert into A1 values (2);

conn B/B
create table B1 (nn number);
insert into B1 values (1);

conn / as sysdba
grant select on A.A1 to C;
grant select on B.B1 to C;

conn C/C
create view C1 as select * from A.A1 x, B.B1 y where x.nn=y.nn;
create materialized view MC1 parallel 3 nologging refresh complete start with sysdate next SYSDATE+1/24 as select * from C1;

conn / as sysdba
grant select on C.MC1 to D;

conn D/D
select * from C.MC1;