Materialized Views (Immediate Help requiredd.)
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Materialized Views (Immediate Help requiredd.)

  1. #1
    Join Date
    Sep 2001
    Posts
    24

    Angry

    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.



    Shyla

  2. #2
    Join Date
    Sep 2001
    Posts
    24

    Angry

    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
    Shyla

  3. #3
    Join Date
    Apr 2001
    Location
    UK
    Posts
    137
    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.


  4. #4
    Join Date
    Sep 2001
    Location
    SWEDEN
    Posts
    70
    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.
    Regards/
    Magnus

  5. #5
    Join Date
    Sep 2001
    Posts
    24

    Talking

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


    Regards
    Shyla
    Shyla

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width