Re: Refresh materialized view by other user then owner

varciasz_at_gmail.com wrote:
> Hello,
>
> I have problem. I'm trying to refresh materialized view 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

Should I understand from your example that you built a materialized view as SYS? What schema are you in when you execute the REFRESH? Does that schema have EXECUTE on DBMS_MVIEW? Granted how?

Daniel Morgan
www.psoug.org Received on Wed May 03 2006 - 11:14:55 CDT