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

在创建一个其他用户的物化视图时报错，错误信息为：ORA-01031:权限不足。

以前写过几篇文章，介绍物化视图创建所需要的权限：

建立物化视图所需权限（一）：http://yangtingkun.itpub.net/post/468/50672

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

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

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

但是目前碰到的问题显然和上面的普通问题有所区别。

下面看看问题的重现：

SQL> CREATE USER MV IDENTIFIED BY MV DEFAULT TABLESPACE USERS;

用户已创建。

SQL> GRANT CONNECT, RESOURCE TO MV;

授权成功。

SQL> GRANT CREATE MATERIALIZED VIEW TO MV;

授权成功。

SQL> CREATE USER USER_DBA IDENTIFIED BY USER_DBA; 

用户已创建。

SQL> GRANT DBA TO USER_DBA;

授权成功。

SQL> CONN MV/MV
$l3K2ws6W/N(KY2@7R0已连接。
V PE;a4KV0}Q0SQL> CREATE TABLE T (ID NUMBER PRIMARY KEY, NAME VARCHAR2(30));

表已创建。

SQL> CREATE MATERIALIZED VIEW LOG ON T;

实体化视图日志已创建。

SQL> CREATE MATERIALIZED VIEW MV_T REFRESH FASTITPUB个人空间2w[1}{1W$fP
  2  AS SELECT * FROM T;

实体化视图已创建。

用户MV创建物化视图并不会报错，下面尝试使用USER_DBA用户来创建MV用户下的物化视图：

SQL> CONN USER_DBA
h_7l`.]sb#G"g ~+N+c0输入口令:ITPUB个人空间&Ngpr
N5S|.u+Bx
已连接。ITPUB个人空间xG5}}/`9T
SQL> CREATE MATERIALIZED VIEW MV_T REFRESH FAST         
q;Iez&OBos^.h0  2  AS SELECT * FROM MV.T;

实体化视图已创建。

SQL> DROP MATERIALIZED VIEW MV_T;

实体化视图已删除。

SQL> DROP MATERIALIZED VIEW MV.MV_T;

实体化视图已删除。

SQL> CREATE MATERIALIZED VIEW MV.MV_T REFRESH FASTITPUB个人空间Rx#[(K,H;KT|8W
  2  AS SELECT * FROM MV.T;
(?
F6O M@K0AS SELECT * FROM MV.T
,K7n5S I7t*j Nm{0                    *
4AxED&I{(]J$n0第2行出现错误:ITPUB个人空间3F'B~%[2m |;f
ORA-01031:权限不足

可以看到，USER_DBA可以在当自己的SCHEMA建立物化视图，但是无法在MV用户下建立物化视图。

由于Oracle报错缺少权限，首先感觉的问题是出在USER_DBA用户上，因为MV用户自己可以建立物化视图。

尝试使用SYS来建立物化视图：

SQL> CONN / AS SYSDBAITPUB个人空间5JV2KF"UC8KC1n
已连接。

t RiGq'U0SQL> CREATE MATERIALIZED VIEW MV.MV_T REFRESH FAST
D9[u?\zs~r0  2  AS SELECT * FROM MV.T;

实体化视图已创建。

SQL> DROP MATERIALIZED VIEW MV.MV_T;

实体化视图已删除。

SYS用户的创建成功，更加确信问题是出在USER_DBA身上，开始怀疑USER_DBA是缺少了什么权限。

可是USER_DBA已经授予了DBA角色，按道理将，一般不会缺少什么权限，难道权限还需要直接授权，而不能通过角色：

SQL> GRANT CREATE ANY TABLE TO USER_DBA;

授权成功。

SQL> GRANT SELECT ANY TABLE TO USER_DBA;

授权成功。

SQL> GRANT COMMENT ANY TABLE TO USER_DBA;

授权成功。

SQL> GRANT LOCK ANY TABLE TO USER_DBA;

授权成功。

SQL> GRANT SELECT ANY DICTIONARY TO USER_DBA;

授权成功。

SQL> CONN USER_DBA/USER_DBAITPUB个人空间y9@ewfX
已连接。ITPUB个人空间L:NoJy/M&CX
SQL> CREATE MATERIALIZED VIEW MV.MV_T REFRESH FASTITPUB个人空间5fC;g6hM0u0h
F*N
  2  AS SELECT * FROM MV.T;ITPUB个人空间i0i!M h4s ]3f5Z
AS SELECT * FROM MV.T
F'@/}d&vv W_0                    *
H8af,JT"U@Q
_ V0第2行出现错误:ITPUB个人空间xusyq4Q9G
ORA-01031:权限不足

将一堆的ANY权限直接授权给USER_DBA，可是创建物化视图视图的时候仍然报错，缺少权限，这就没有什么道理了，难道是个bug。

查询了metalink，并未发现什么类似的描述。看来只好通过trace的方式分析一下错误了：

SQL> ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT FOREVER, LEVEL 12';

会话已更改。

SQL> CREATE MATERIALIZED VIEW MV.MV_T REFRESH FASTITPUB个人空间T5[({ m e"Vp
  2  AS SELECT * FROM MV.T;
8o|Yj+~Mf0AS SELECT * FROM MV.TITPUB个人空间xG!E
B,z
                    *
,kr~H"y!nG0第2行出现错误:
3HO DuB!@d g0ORA-01031:权限不足

ITPUB个人空间~?Xz6m$WIRk
SQL> ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT OFF';

会话已更改。

下面查看一下trace中，错误出现在哪里：

SQL> SELECT SPID FROM V$PROCESS P, V$SESSION S
Ld|HS7w/@0  2  WHERE P.ADDR = S.PADDRITPUB个人空间S6c2i#]6e;E
  3  AND SID IN (SELECT SID FROM V$MYSTAT WHERE ROWNUM = 1);

SPIDITPUB个人空间.Z,D$} eX'd-[ W
------------ITPUB个人空间(EqJ4k]
f]
13940

SQL> HOST
EKx~)u#p!Y_9Al0[oracle@testzone ~]$ more $ORACLE_BASE/admin/testzj/udump/testzj_ora_13940.trc
`5Wl+t@/T(]&c0/opt/ora10g/admin/testzj/udump/testzj_ora_13940.trc
d I)vE }cb0Oracle Database10gEnterprise Edition Release 10.2.0.3.0 - 64bit Production

db;v:gJg)u d0With the Partitioning, OLAP and Data Mining options
qb.uO/zZZh
\0ORACLE_HOME = /opt/ora10g/product/10.2.0/db_1
!oK2} I
L]~4w/jI0System name:    Linux
F@UE8{~1[!P'g&U0Node name:      testzone

^"} s;X1w\0Release:        2.6.18-8.el5xenITPUB个人空间0Rx!hd EO/b7s
Version:        #1 SMP Tue Jun 5 23:53:34 EDT 2007ITPUB个人空间cUL$tPh*kym
Machine:        x86_64
9gHL5[\d0Instance name: testzj
9tY[ Go{(_@8m+}N0Redo thread mounted by this instance: 1
`2g&u_-euj0Oracle process number: 20ITPUB个人空间C#v/Z XTn[
Unix process pid: 13940, image: oracle@testzone (TNS V1-V3)

*** 2008-05-08 16:05:49.580
8R1Hd7y5n!A7b0*** ACTION NAME:() 2008-05-08 16:05:49.580ITPUB个人空间#dd;C!FIO{L
*** MODULE NAME:(SQL*Plus) 2008-05-08 16:05:49.580ITPUB个人空间&a_1uUI1R X
*** SERVICE NAME:(SYS$USERS) 2008-05-08 16:05:49.580ITPUB个人空间x` }fR%zP
*** SESSION ID:(148.437) 2008-05-08 16:05:49.580
WT7I3A,hj7T:n#s~.D0WAIT #6: nam='SQL*Net message to client' ela= 3 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1181869091387111
9?7GZ$O(a0g9^0*** 2008-05-08 16:06:11.329ITPUB个人空间U,q?4yQ Irab
WAIT #6: nam='SQL*Net message from client' ela= 21238676 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1181869112626106
/R"N3_,jd,b{C0z]0XCTEND rlbk=0, rd_only=1
5i,[C*MJ:v0=====================ITPUB个人空间(qNVu%I6l%r%T-F
.
wL$k+As&_)Y"w\/b0.
V+VD}6|`L0.
} CQN.V Q0=====================
'TV9k?R e0PARSE ERROR #3:len=57 dep=1 uid=71 ct=3 lid=71 tim=1181869112672434 err=10980
[5Kp XuTvj$n0SELECT "T"."ID" "ID","T"."NAME" "NAME" FROM "MV"."T" "T"ITPUB个人空间 kU#`wz]/C
=====================ITPUB个人空间#~8M&vf8fm%R
PARSING IN CURSOR #1 len=108 dep=2 uid=0 ct=3 lid=0 tim=1181869112673324 hv=1873661484 ad='a6554dd0'
sE;_1s(f8Fna0select cc.intcol# from cdef$ c, ccol$ cc where c.obj#=cc.obj# and c.con#=cc.con# and c.type#=7 and c.obj#=:1
-A:uz[cdiS0END OF STMTITPUB个人空间2o4PFCpf
PARSE #1:c=0,e=512,p=0,cr=0,cu=0,mis=1,r=0,dep=2,og=4,tim=1181869112673319
B dP+\vS+c([0BINDS #1:ITPUB个人空间M5u2Zh
Ba-~Lwt
kkscoacd
-of}6e7YA6Ye0 Bind#0ITPUB个人空间 H9f4]v+q
T.w#p2_+F
  acdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00ITPUB个人空间:o/V2R4FP6[!Cs
  acflg=00 fl2=0001 frm=00 csi=00 siz=24 ff=0ITPUB个人空间 oHp3fJ&r[ e.K
  kxsbbbfp=2aaaacaa0a40  bln=22  avl=04  flg=05ITPUB个人空间!v%Kg}!JGK [A
  value=81507ITPUB个人空间sye.I B[
EXEC #1:c=0,e=1407,p=0,cr=0,cu=0,mis=1,r=0,dep=2,og=4,tim=1181869112674805
7Ey
K'| vd0FETCH #1:c=8000,e=301,p=0,cr=3,cu=0,mis=0,r=0,dep=2,og=4,tim=1181869112675138ITPUB个人空间:J+ps8MA1S%N
STAT #1 id=1 cnt=0 pid=0 pos=1 bj=0 p='HASH JOIN  (cr=3 pr=0 pw=0 time=315 us)'ITPUB个人空间SziK"`Ki
STAT #1 id=2 cnt=0 pid=1 pos=1 bj=31 p='TABLE ACCESS CLUSTER CDEF$ (cr=3 pr=0 pw=0 time=43 us)'
'p)M~O7TNHv ?,I0STAT #1 id=3 cnt=1 pid=2 pos=1 bj=30 p='INDEX UNIQUE SCAN I_COBJ# (cr=2 pr=0 pw=0 time=19 us)'
&gn&\1ErR#t+x0STAT #1 id=4 cnt=0 pid=1 pos=2 bj=32 p='TABLE ACCESS CLUSTER CCOL$ (cr=0 pr=0 pw=0 time=0 us)'ITPUB个人空间 x
RW8F7V
STAT #1 id=5 cnt=0 pid=4 pos=1 bj=30 p='INDEX UNIQUE SCAN I_COBJ# (cr=0 pr=0 pw=0 time=0 us)'ITPUB个人空间}
z6f0z{5a-Nv
=====================
u^ ?3lF6a#^s;ZU0PARSING IN CURSOR #2 len=87 dep=1 uid=71 ct=1 lid=71 tim=1181869112675587 hv=2894365893 ad='a0abf708'
Fq$s%]Et
^0CREATE TABLE "MV"."MV_T" ("ID","NAME")  AS SELECT "T"."ID","T"."NAME" FROM "MV"."T" "T"ITPUB个人空间 W8_B!khHi
END OF STMT
FPH2HFEU0PARSE #2:c=8000,e=3005,p=0,cr=4,cu=0,mis=1,r=0,dep=1,og=1,tim=1181869112675583ITPUB个人空间1_$J1ZL0ouF"o
BINDS #2:
li#_TCdZ&x0EXEC #2:c=0,e=129,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,tim=1181869112675776
d3c!Z4O_b0ERROR #2:err=1031 tim=1740669629ITPUB个人空间%}pxD}
EXEC #4:c=40002,e=34575,p=0,cr=78,cu=22,mis=0,r=0,dep=0,og=1,tim=1181869112675848ITPUB个人空间@8^i7g@r
ERROR #4:err=1031 tim=1740669629
V ~V EqQ0STAT #5 id=1 cnt=0 pid=0 pos=1 bj=178 p='TABLE ACCESS BY INDEX ROWID SNAP$ (cr=1 pr=0 pw=0 time=22 us)'ITPUB个人空间y(G+Z9~.M!zb
STAT #5 id=2 cnt=0 pid=1 pos=1 bj=181 p='INDEX UNIQUE SCAN I_SNAP1 (cr=1 pr=0 pw=0 time=12 us)'
g2m;ia4NV$vv:x ^ u0WAIT #4: nam='log file sync' ela= 95 buffer#=1861 p2=0 p3=0 obj#=-1 tim=1181869112676218ITPUB个人空间2h4H@7q!Y[
WAIT #4: nam='SQL*Net break/reset to client' ela= 4 driver id=1650815232 break?=1 p3=0 obj#=-1 tim=1181869112676272ITPUB个人空间"`$Jt#T3w
I'E
WAIT #4: nam='SQL*Net break/reset to client' ela= 68 driver id=1650815232 break?=0 p3=0 obj#=-1 tim=1181869112676360ITPUB个人空间vl"l]U1v;Y
WAIT #4: nam='SQL*Net message to client' ela= 3 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1181869112676382
LR9f"C,p0*** 2008-05-08 16:06:24.825ITPUB个人空间:g`TR#I'Vot1D
WAIT #4: nam='SQL*Net message from client' ela= 13130207 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1181869125806625ITPUB个人空间Y&r2` pj3}4m:]
=====================
o%zg(KQ(A Zo$m!h0PARSING IN CURSOR #7 len=55 dep=0 uid=72 ct=42 lid=72 tim=1181869125806851 hv=524428051 ad='0'ITPUB个人空间?qG,h O%m-g.s+ak)Y
ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT OFF'
Xks_$],Dr0END OF STMTITPUB个人空间.ekp;C;}6S4m
PARSE #7:c=0,e=111,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,tim=1181869125806846
He4ErI:M e*x7z0EXEC #7:c=0,e=56,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,tim=1181869125806968

从上面的trace信息文件，错误出现在建表过程中，可是直接尝试上面的建表语句是不报错的：

[oracle@testzone ~]$ exit
3_K&FO:J,t2h3@6HW0exit

SQL> CREATE TABLE "MV"."MV_T" ("ID","NAME")  AS SELECT "T"."ID","T"."NAME" FROM "MV"."T" "T";

表已创建。

SQL> DROP TABLE MV.MV_T;

表已删除。

难道是因为MV用户的权限必须直接授权，而不能通过角色获取：

SQL> SELECT * FROM DBA_SYS_PRIVS WHERE GRANTEE = 'MV';

GRANTEE                        PRIVILEGE                                ADMITPUB个人空间?-Jd/A|t/A3i
------------------------------ ---------------------------------------- ---
A8g{Z0e)}"zY0MV                             CREATE MATERIALIZED VIEW                 NO
)I U0I,T)NI)e&\-Z0MV                             UNLIMITED TABLESPACE                     NO

SQL> GRANT CREATE TABLE TO MV;

授权成功。

SQL> CREATE MATERIALIZED VIEW MV.MV_T REFRESH FAST
lez4bf&vh3q(C*u4z0  2  AS SELECT * FROM MV.T;

实体化视图已创建。

问题居然是出在MV用户下，创建其他用户下的物化视图，不仅需要这个用户拥有CREATE TABLE权限，而且要求这个权限不能是通过角色获得，必须直接授权。

从这个要求可以推测，当建立其他用户下的物化视图，多半是通过存储过程实现的，因为这个授权的要求很显然是符合存储过程授权规则。

至于SYS用户为什么不需要目标用户直接授权CREATE TABLE，根据Tom的描述，SYS用户是特殊的，普通的规则在SYS身上并不适用。