Privilege to call DBMS_MVIEW.REFRESH for a materialized view of an other schema

I’m currently trying to refresh a materialized view of a schema MYSCHEMA with an other schema (MYSCHEMA_DML). When I try to call

BEGIN DBMS_MVIEW.REFRESH('MYSCHEMA.MV_MYVIEW');END;

I’ve got the following erreor in my output :

ORA-06512: à "SYS.DBMS_SNAPSHOT_KKXRCA", ligne 3020
ORA-06512: à "SYS.DBMS_SNAPSHOT_KKXRCA", ligne 2432
ORA-06512: à "SYS.DBMS_SNAPSHOT_KKXRCA", ligne 88
ORA-06512: à "SYS.DBMS_SNAPSHOT_KKXRCA", ligne 253
ORA-06512: à "SYS.DBMS_SNAPSHOT_KKXRCA", ligne 2413
ORA-06512: à "SYS.DBMS_SNAPSHOT_KKXRCA", ligne 2976
ORA-06512: à "SYS.DBMS_SNAPSHOT_KKXRCA", ligne 3263
ORA-06512: à "SYS.DBMS_SNAPSHOT_KKXRCA", ligne 3295
ORA-06512: à "SYS.DBMS_SNAPSHOT", ligne 16
ORA-06512: à ligne 1
01031. 00000 -  "insufficient privileges"
*Cause:    An attempt was made to perform a database operation without
           the necessary privileges.
*Action:   Ask your database administrator or designated security
           administrator to grant you the necessary privileges

My user has the following privilege on the view :

SELECT
ALTER
DELETE
INSERT
UPDATE

Obvously, this isn’t enough 🙁 I’ve done some researches and the only privilege I’ve found to add in order to do the refresh seems to be

GRANT ALTER ANY MATERIALIZED VIEW to MYSCHEMA_DML;

Which seems a little bit to much. Is it any other privilege that I can grant to my user in order to refresh my view ?

Answer

Issue fixed, thanks to @MarmiteBomber :

I’ve create a procedure into my first schema MYSCHEMA

create or replace procedure REFRESH_MV_MYVIEW
 as
 begin
 DBMS_MVIEW.REFRESH('MYSCHEMA.MV_MYVIEW');
 end;

and then grant execute for MYSCHEMA2.

BUT. As the query of the materialized view was making a select on another schema, I had to grant SELECT to the user MYSCHEMA explicitly (he only had the rights with a role)

Advertisement

Answer