Hi I have a following scenario to create materialized view (MV) and refresh it accordingly:
scenario 1: Successful
user1: all base tables of MV lie under user1.
user2: MV is created under user2.creates a procedure refresh_mv_t1 to refresh MV.
user3: calls proc1 to refresh MV.
scenario 2: Failed
user1: all base tables of MV lie under user1 and MV is created under user1.
user2: creates a procedure refresh_mv_t1 to refresh MV.
user3: calls proc1 to refresh MV.
NOTE: User1 and User2 are having DBA privileges.
User3 is a regular user a/c, which is given privs to execute proc, all DML privs on the base tables of MV. + select on MV.
Successful Attempt:
connect as user1:
SQL> create table t1 as select 1 col1 from dual;
Table created
connect as user2:
SQL> create materialized view mv_t1 as select col1 from t1;
Materialized view created
SQL> grant select on test.mv_t1 to user3;
Grant succeeded
SQL> create or replace procedure refresh_mv_t1 as
2 begin
3 execute immediate 'begin dbms_snapshot.refresh(''user2.MV_T1''); end;';
4 end;
5 /
Procedure created
SQL> grant execute on test.refresh_mv_t1 to user3;
Grant succeeded
SQL> insert into t1 values (5);
1 row inserted
SQL> commit;
Commit complete
SQL> -- refresh with user3 user
SQL> conn user3/test@ORCL;
Connected as user3:
SQL> exec user2.refresh_mv_t1;
PL/SQL procedure successfully completed
SQL> select * from user2.mv_t1;
COL1
----------
1
5
unsuccessful Attempt:
Running Proc refresh_mv_t1 as USER3
ORA-01031: insufficient privileges
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2563
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2776
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2745
ORA-06512: at line 1
ORA-06512: at "PLSQLDBA.REFRESH_MV", line 8
ORA-06512: at line 1
PS: I am using Oracle 11.2.0.3 DB