Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.0k views
in Technique[技术] by (71.8m points)

sql - 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 ?


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

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)


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...