创建其他用户下的物化视图报错权限不足

建立物化视图的选项很多，比如刷新方式，刷新模式和是否支持查询重写等。由此涉及到的权限也比较多。而如果在建立物化视图的时候由于缺少权限而报错，ORACLE给出的错误信息仅仅是ORA-01031: 权限不足，而没有清楚的给出是由于缺少哪种权限造成的。

打算通过几篇文章综合描述建立普通的物化视图和建立各种选项的物化视图所需的权限，这篇文章主要介绍物化视图所需的最基本的权限。主要会分为三个不同的情况：基表和物化视图都是当前用户下；基表不在当前用户下；建立其他用户下的物化视图。

相关文章：

建立物化视图所需权限（二）：http://blog.itpub.net/post/468/50707

建立物化视图所需权限（三）：http://blog.itpub.net/post/468/50838

建立物化视图所需权限（四）：http://blog.itpub.net/post/468/51163

 

首先，建立一个新的测试用户，只给CREATE SESSION（保证用户可以登陆到Oracle）和UNLIMITED TABLESPACE（保证用户有足够的空间）权限进行测试。

SQL> CONN YANGTK/YANGTK@TEST4已连接。
SQL> CREATE USER TEST IDENTIFIED BY TEST DEFAULT TABLESPACE YANGTK;

用户已创建

SQL> GRANT CREATE SESSION TO TEST;

授权成功。

SQL> GRANT UNLIMITED TABLESPACE TO TEST;

授权成功。

由于需要建立物化视图的基表，因此先给TEST用户CREATE TABLE的权限。

SQL> GRANT CREATE TABLE TO TEST;

授权成功。

SQL> CONN TEST/TEST@TEST4已连接。
SQL> CREATE TABLE T (ID NUMBER PRIMARY KEY, NAME VARCHAR2(30));

表已创建。

SQL> CREATE MATERIALIZED VIEW MV_T AS SELECT * FROM T;
CREATE MATERIALIZED VIEW MV_T AS SELECT * FROM T
*
ERROR 位于第 1 行:
ORA-01031: 权限不足

SQL> CONN YANGTK/YANGTK@TEST4已连接。
SQL> GRANT CREATE MATERIALIZED VIEW TO TEST;

授权成功。

SQL> CONN TEST/TEST@TEST4已连接。
SQL> CREATE MATERIALIZED VIEW MV_T AS SELECT * FROM T;

实体化视图已创建。

建立物化视图必然需要CREATE MATERIALIZED VIEW权限（在8i及以前版本中，权限的名称是CREATE SNAPSHOT，9i中SNAPSHOT和MATERIALIZED VIEW是同义词）。在上面的例子中，通过授予了CREATE MATERIALIZED VIEW权限，已经建立了最简单的物化视图。但是，是不是仅仅包含CREATE MATERIALIZED VIEW权限就足够了？我们注意到，在开始的时候为了建立基表，还给了CREATE TABLE权限，下面回收这个权限，然后重建物化视图：

SQL> CONN YANGTK/YANGTK@TEST4已连接。
SQL> REVOKE CREATE TABLE FROM TEST;

撤销成功。

SQL> CONN TEST/TEST@TEST4已连接。
SQL> CREATE MATERIALIZED VIEW MV_T1 AS SELECT * FROM T;
CREATE MATERIALIZED VIEW MV_T1 AS SELECT * FROM T
*
ERROR 位于第 1 行:
ORA-01031: 权限不足

通过测试可以看到，建立物化视图，除了CREATE MATERIALIZED VIEW权限，还需要CREATE TABLE权限。其实，这个并不奇怪，查询一下USER_OBJECTS就明白了。

SQL> COL OBJECT_NAME FORMAT A30
SQL> SELECT OBJECT_NAME, OBJECT_TYPE FROM USER_OBJECTS;

OBJECT_NAME OBJECT_TYPE
------------------------------ ------------------
MV_T TABLE
MV_T MATERIALIZED VIEW
SYS_C003877 INDEX
SYS_C003878 INDEX
T TABLE

Oracle建立了同名的表和物化视图MV_T，这就是需要CREATE TABLE权限的原因。

第二种情况基表不在当前用户下：这种情况比较简单，除了第一种情况需要的权限以外，还需要对基表的SELECT权限（当然SELECT ANY TABLE权限也可以，不过这里的原则是讨论最小权限）

SQL> CONN YANGTK/YANGTK@TEST4已连接。
SQL> GRANT CREATE TABLE TO TEST;

授权成功。

SQL> CREATE TABLE T (ID NUMBER PRIMARY KEY, NAME VARCHAR2(30));

表已创建。

SQL> CONN TEST/TEST@TEST4已连接。
SQL> CREATE MATERIALIZED VIEW MV_T1 AS SELECT * FROM YANGTK.T;
CREATE MATERIALIZED VIEW MV_T1 AS SELECT * FROM YANGTK.T
*
ERROR 位于第 1 行:
ORA-00942: 表或视图不存在

SQL> CONN YANGTK/YANGTK@TEST4已连接。
SQL> GRANT SELECT ON T TO TEST;

授权成功。

SQL> CONN TEST/TEST@TEST4已连接。
SQL> CREATE MATERIALIZED VIEW MV_T1 AS SELECT * FROM YANGTK.T;

实体化视图已创建。

对于第三种情况，创建用户由于要在别的方案中创建物化视图，因此很明显需要CREATE ANY MATERIALIZED VIEW权限，另外物化视图的拥有者必须具有CREATE TABLE权限。当然，如果基表不在物化视图拥有者方案内，则物化视图的拥有者必须有基表的查询权限。

SQL> CONN YANGTK/YANGTK@TEST4已连接。
SQL> CREATE USER MVIEW_OWNER IDENTIFIED BY MVIEW_OWNER;

用户已创建

SQL> GRANT CREATE SESSION, UNLIMITED TABLESPACE, CREATE TABLE TO MVIEW_OWNER;

授权成功。

SQL> GRANT CREATE ANY MATERIALIZED VIEW TO TEST;

授权成功。

SQL> GRANT SELECT ON T TO MVIEW_OWNER;

授权成功。

SQL> CONN TEST/TEST@TEST4已连接。
SQL> CREATE MATERIALIZED VIEW MVIEW_OWNER.MV_T AS SELECT * FROM YANGTK.T;

实体化视图已创建。

上面列出了需要成功创建这种类型的物化视图的基本权限，需要说明的是，TEST用户的CREATE TABLE, CREATE MATERIALIZED和对基表的查询权限是不必要的。

SQL> CONN YANGTK/YANGTK@TEST4已连接。
SQL> REVOKE SELECT ON T FROM TEST;

撤销成功。

SQL> CONN TEST/TEST@TEST4已连接。
SQL> CREATE MATERIALIZED VIEW MVIEW_OWNER.MV_T1 AS SELECT * FROM YANGTK.T;

实体化视图已创建。

SQL> CONN YANGTK/YANGTK@TEST4已连接。
SQL> REVOKE CREATE TABLE, CREATE MATERIALIZED VIEW FROM TEST;

撤销成功。

SQL> CONN TEST/TEST@TEST4已连接。
SQL> CREATE MATERIALIZED VIEW MVIEW_OWNER.MV_T2 AS SELECT * FROM YANGTK.T;

实体化视图已创建。

根据上面的测试可以看出，第三种情况其实是前两种情况的组合。作为物化视图的创建者需要CREATE ANY MATERIALIZED VIEW权限，而物化视图的拥有者需要CREATE TABLE和对基表的查询权限。

 

转自： http://yangtingkun.itpub.net/post/468/50672