刷新物化视图需要的权限

Problem Summary:

================

Privileges To Refresh A Snapshot Or Materialized View

 

Problem Description:

===================

From Oracle 8i materialized views are synonymous with snapshots. Any reference to snapshot

can be replaced with materialized view instead.

 

You are attempting to refresh a snapshot that someone else owns, manually or

automatically, and you are receiving ORA-1031 or ORA-23406

 

SQLDBA> execute dbms_snapshot.refresh(SCOTT.SNAP_TEST);

ORA-01031: insufficient privileges

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

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

ORA-06512: at line 1

 

OR

 

SQLDBA> execute dbms_refresh.refresh(SCOTT.SNAP_TEST);

ORA-23406: insufficient privileges on user "JOHN"

ORA-06512: at "SYS.DBMS_SYS_ERROR", line 83

ORA-06512: at "SYS.DBMS_IREFRESH", line 25

ORA-06512: at "SYS.DBMS_REFRESH", line 21

ORA-06512: at "SYS.DBMS_REFRESH", line 171

ORA-06512: at line 1

 

OR

 

SQL> exec DBMS_MVIEW.REFRESH('scott.snap_test','C', '',TRUE,FALSE,0,0,0, FALSE);

BEGIN DBMS_MVIEW.REFRESH('scott.snap_test','C', '',TRUE,FALSE,0,0,0, FALSE); END;

 

*

ERROR at line 1:

ORA-01031: insufficient privileges

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

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

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

ORA-06512: at line 1

 

 

For example, user JOHN tries to refresh SCOTT's snapshot and receives the

above errors.  However, user JOHN has the DBA role granted to him  

as well as privileges to the master table and the snapshot log on the

master site. Why can't he refresh SCOTT's snaphshot?

 

 

Problem Explanation:

====================

Being granted the DBA role is NOT sufficient to refresh another

user's snapshot.   You have to explicitly grant the user:

ALTER ANY SNAPSHOT

or

ALTER ANY MATERIALIZED VIEW

and

SELECT ANY TABLE

system privilege.

 注意：如果job是在system用户下的，那么删除了其他普通用户再重建用户后，需要给system显式的授于这几个权限才能刷新其他用户的MV及snapshot！

 

Solution Summary:

=================

GRANT ALTER ANY SNAPSHOT

GRANT ALTER ANY MATERIALIZED VIEW

GRANT SELECT ANY TABLE

 

Solution Description:

=====================

connect system/manager

grant ALTER ANY SNAPSHOT to ;

or

grant ALTER ANY MATERIALIZED VIEW to ;

grant SELECT ANY TABLE to ;

 

Solution Explanation:

=====================

 

To refresh a snapshot, you must meet the following criteria:

 

o  You must own the snapshot or have the ALTER ANY SNAPSHOT or

   ALTER ANY MATERIALIZED VIEW and

   SELECT ANY TABLE privilege

 

o  The snapshot owner (or the user that you have connected as, if you are

   using a database link) must have SELECT privileges on the master table

   and, for fast refreshes, on the snapshot log.

 

 

 

The reason why the DBA role will not work is because when Oracle goes to

refresh, internally, it specifically checks the requirements mentioned above. 

Oracle checks if the snapshot is owned by the user refreshing OR checks for

the user to have ALTER ANY SNAPSHOT or ALTER ANY MATERIALIZED VIEW and

SELECT ANY TABLE privilege. 

It does NOT check for the privileges granted through the DBA role.