How to Allow a User to Refresh Another User's Materialized View

I needed to find out how to allow a user to refresh another user’s materialized view. Once I had done this I decided to document it for future reference with a worked example, which I ran on an Oracle 11.2.0.2.7 database. First I created user A, who will own the materialized view:

 

SQL> create user a identified by a

  2  default tablespace users

  3  quota unlimited on users

  4  /

 

User created.

 

SQL> grant

  2  create session,

  3  create materialized view,

  4  create procedure,

  5  create table,

  6  select any dictionary to a

  7  /

 

Grant succeeded.

 

SQL>

 

I logged in as user A, created a table then created a materialized view on that table:

 

SQL> conn a/a

Connected.

SQL> create table tab1

  2  as select * from dba_tables

  3  /

 

Table created.

 

SQL> create materialized view mv1

  2  as select * from tab1

  3  /

 

Materialized view created.

 

SQL>

 

I showed that user A could refresh the materialized view as he owned it:

 

SQL> exec dbms_mview.refresh('mv1');

 

PL/SQL procedure successfully completed.

 

SQL>

 

I created user B, logged in as this user and tried to refresh the materialized view again. This failed as user B did not own the materialized view:

 

SQL> conn / as sysdba

Connected.

SQL> create user b identified by b

  2  /

 

User created.

 

SQL> grant create session to b

  2  /

 

Grant succeeded.

 

SQL> conn b/b

Connected.

SQL> exec dbms_mview.refresh('a.mv1');

BEGIN dbms_mview.refresh('a.mv1'); END;

 

*

ERROR at line 1:

ORA-01031: insufficient privileges

ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2566

ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2779

ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2748

ORA-06512: at line 1

 

SQL>

 

I granted alter any materialized view to user B and this allowed him to refresh user A’s materialized view:

 

SQL> conn / as sysdba

Connected.

SQL> grant alter any materialized view to b

  2  /

 

Grant succeeded.

 

SQL> conn b/b

Connected.

SQL> exec dbms_mview.refresh('a.mv1');

 

PL/SQL procedure successfully completed.

 

SQL>

 

I did not like this approach as it allowed user B to refresh any materialized view so I revoked the privilege:

 

SQL> conn / as sysdba

Connected.

SQL> revoke alter any materialized view from b

  2  /

 

Revoke succeeded.

 

SQL>

 

I reconnected as user A, created a procedure to refresh the materialized view then allowed user B to execute it:

 

SQL> conn a/a

Connected.

SQL> create or replace procedure pr1 as

  2  begin

  3  dbms_mview.refresh('a.mv1');

  4  end;

  5  /

 

Procedure created.

 

SQL> grant execute on pr1 to b

  2  /

 

Grant succeeded.

 

SQL>

 

Finally, I logged in as user B and was able to refresh the materialized view:

 

SQL> conn b/b

Connected.

SQL> exec a.pr1;

 

PL/SQL procedure successfully completed.

 

SQL>