Materialized Views (Immediate Help requiredd.)

Hi,
I have created some materialized views with DBA role and i want to give the refresh role / privilege alone to another user
for refreshing the same views who doesn't have create materialized view role .


Thanks
Shyla.

The base table and the materialized view is in one tablespace and created by a user having DBA role and for the created materialized view a public synonym has been created .So the developer (without dba role) can query the materialized view and given the create materialized view now. But when the refresh is done by the developer the view doesn't get refreshed .Pl help me out.

Thanks
Shyla

One option might be to create a stored procedure under the user who owns the materialized views. In the procedure, you could refresh the views using dbms_mview.refresh(). The user then just calls this procedure.

If the procedure is created with definer rather than invoker rights (the default), then it should be executed under the privileges of the procedure owner rather than the user invoking it.

Quote:

---

Originally posted by nealh
One option might be to create a stored procedure under the user who owns the materialized views. In the procedure, you could refresh the views using dbms_mview.refresh(). The user then just calls this procedure.

If the procedure is created with definer rather than invoker rights (the default), then it should be executed under the privileges of the procedure owner rather than the user invoking it.

---

Hi,

You can grant the system privilege ALTER ANY SNAPSHOT to that user who will refresh the view. But be care, thats a power privilege which your DBA don't like.

THANKS A LOT MAGERI , NEALH FOR THE TIMELY HELP.
It is indeed helpful.


Regards
Shyla