Refresh materialized view by other user then owner

[varciasz]

Hello,

I'm trying to refresh materialized view (Oracle 9i) and I have error
that some pirivileges are needed but I dont have idea what more can be
needed

I'm trying to execute from User2:

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

and Oracle gives me back error:

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

Table, materialized view and privileges are made in this way:

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

CREATE USER User2;
IDENTIFIED BY ThisIsMySecretPassword;
GRANT ALL ON My_View TO User2;
GRANT SELECT ON My_Table TO User2;
GRANT CREATE SESSION TO User2;
GRANT ALTER ANY MATERIALIZED VIEW to User2;
GRANT SELECT ANY TABLE to User2;
GRANT CREATE ANY MATERIALIZED VIEW to User2
GRANT DROP ANY MATERIALIZED VIEW to User2

--END

This table and materialized view have been made by user System

I also try to give the same Roles that have System but this is still
the same error.
Refresh works fine executed by owner of this View

What privileges are missing?

Thanks for any help

varciasz

[alek]

Hi,

Storing database objects into the SYS schema is unusual and
unadvised... The SYS schema is protected and having SELECT ANY TABLE
for a user doesn't affect the accesibility against the SYS objects. Of
course there are a lot of other privileges which might be needed in
order to perform a materialized view refresh. For example, because your
sys.my_table has no materialized view log the Oracle server simply
chooses to perform a complet refresh which implies, first of all, a
truncate against the mv object therefore a drop any table privilege
might be needed. However, trying the same scenario using SYSTEM instead
SYS works without problems. If your business logic dictates that the
materialized view should be contained by the SYS schema then the key
for solving your problem is the O7_DICTIONARY_ACCESSIBILITY. By default
this is set to false and, for security reasons, should have this value
for almost all databases. Pay atention to the fact that this parameter
is static and will imply an instance restart. After changing it you
will be able to perform the refresh from the user2 user without any
problems.

alec.