RE: Refresh materialized view by other user then owner

>>>> This table and materialized view have been made by user System

>>What about if it's owned by a regular user? This sounds like a very
>>marginal case with a lot that could go wrong.

Hi there,

When user creates his own MView then there is no problem with refreshing it, but I still have no idea how refresh MView of other user

FOR EXAMPLE:
User "SYSTEM":

    CREATE USER User2

       IDENTIFIED BY ThisIsMySecretPassword;     GRANT CREATE SESSION TO User2;
    GRANT ALTER ANY MATERIALIZED VIEW to User2;     GRANT SELECT ANY TABLE to User2;
    GRANT CREATE ANY TABLE to User2;
    GRANT CREATE ANY MATERIALIZED VIEW to User2 ;     GRANT DROP ANY MATERIALIZED VIEW to User2;     ALTER USER "USER2" QUOTA UNLIMITED ON "SYSTEM"; -- END OF SYSTEM User "USER2"

    create table My_Table (aa integer primary key);     create materialized view My_View as select * from My_Table;     begin

        DBMS_MVIEW.REFRESH('My_View','c');     end;
--END OF USER2 This example works fine but when TABLE and MATERIALIZED VIEW are made by "SYSTEM" and user "USER2" trying to refresh it by:

    begin

        DBMS_MVIEW.REFRESH('sys.My_View','c');     end;

then some privileges are needed and error is shown:

    begin
    *
    ERROR at line 1:

    ORA-01031: insufficient privileges
    ORA-06512: at "SYS.DBMS_SNAPSHOT", line 794
    ORA-06512: at "SYS.DBMS_SNAPSHOT", line 851
    ORA-06512: at "SYS.DBMS_SNAPSHOT", line 832
    ORA-06512: at line 2

I don't have any idea what privileges are needed because I already tried almost all of then. What is a meaning of this lines in error (794, 851, 832)? Is there any way to track what privileges are missing?

It not suppose to be so difficult! What's wrong with this Oracle?

Thanks for any help

varciasz

--
http://www.freelists.org/webpage/oracle-l