Hi,

Having trouble with privleges while creating the MV that uses a dblink. I have went back and checked all the privs that were given, seems it should work.

db1
grant select on table_one to role_name.
granted role_name to user1

db2
created public dblink linking at user1
granted role_name to user2
granted create materialized view to user2
granted query rewrite to user2

as user2 - This works:
desc owner.table_one@dblink_name

as user2 - This doesn't work: insufficent privs,

create materialized view results_mv
build immediate
refresh on demand
enable query rewrite
as select * from owner.table_one@dblink_name;

Error Message:
select * from owner.table_one@dblink_name
*
ERROR at line 6:
ORA-01031: insufficient privileges

Any ideas what I'm missing here.
Thanks,
Kathy