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.
|