Paul wrote:
> Hi
>
> I have created a materialized view but I am having issues executing the
> DBMS_MVIEW.REFRESH procedure against it as a user who does not own the
> view.
>
> Here are the details:
>
> (logged in as SYS)
>
> DEFINE &USER1 = USER1
> DEFINE &USER2 = USER2
>
> CREATE USER &USER1 ...
> CREATE USER &USER2...
>
> GRANT CREATE SESSION TO &USER1;
> GRANT CONNECT TO &USER1;
> GRANT CREATE TRIGGER TO &USER1; *
> GRANT CREATE PROCEDURE TO &USER1; *
> GRANT SELECT ON DBA_OBJECTS TO &USER1; *
> GRANT SELECT ON DBA_SYNONYMS TO &USER1;*
> GRANT SELECT ON ALL_OBJECTS TO &USER1;*
> GRANT CREATE MATERIALIZED VIEW TO &USER1;
>
> GRANT CREATE SESSION TO &USER2;
> GRANT CONNECT TO &USER2;
> GRANT ALTER ANY TABLE TO &USER2;*
> GRANT SELECT ON DBA_SYNONYMS TO &USER2;*
> GRANT SELECT ON ALL_OBJECTS TO &USER2;*
> GRANT EXECUTE ON DBMS_MVIEW TO &USER2;
>
>
> * I don't think these are relevant but have included to try to include
> as much info as possible
>
> (Logged in as &USER1)
>
> CREATE MATERIALIZED VIEW TEST1
> AS ...
>
> GRANT SELECT, INSERT, UPDATE, DELETE ON TEST1 TO &USER2;
>
> (logged in as &USER2)
>
> CREATE SYNONYM TEST1 FOR &USER1..TEST1;
>
> Then executing the following statement:
>
> EXECUTE DBMS_MVIEW.REFRESH('TEST1');
>
> fails with the error:
>
> 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 1
>
> Does anybody know whether or not I can do this and if so what
> additional privileges are required?
>
> Thanks in advance
>
> Paul
This must be the single most underappreciated aspect of Oracle. We
answer this time-after-time, month-after-month. The privilege must
be granted explicitly to the end-user ... not through a role.
--
Daniel A. Morgan
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond)
Received on Fri Oct 14 2005 - 15:58:16 CDT
