Wednesday, June 12, 2013

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>

If you have an Oracle book on Amazon, which you would like to advertise here for free, please write to me at international_dba@yahoo.co.uk.

No comments: