dblink materialized view
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.
grant select on table_one to role_name.
granted role_name to user1
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:
as user2 - This doesn't work: insufficent privs,
create materialized view results_mv
refresh on demand
enable query rewrite
as select * from owner.table_one@dblink_name;
select * from owner.table_one@dblink_name
ERROR at line 6:
ORA-01031: insufficient privileges
Any ideas what I'm missing here.
I needed to grant global query rewrite, since using remote db.