Oracle用户权限的管理 12/31 2008 安装配置 | Oracle 3658 次查看
上一条：开机启动oracle的脚本 下一条：RedHatLinuxAS3U2安装oracle9204
Oracle系统中用户权限的赋予,查看和管理.
在Oracle数据库中,用户的权限分为两种(在这里我们不讨论dba或dbopr的权限,只考虑普通用户的权限),分别是System Privilege系统权限 和User Table Privilege用户数据表权限.
1.首先,创建用户,以下几条命令可以创建一个用户,前提是必须以DBA的身份登录(如果你不是DBA,不要看下去了):
create user DB_USER identified by DB_USER_PW
'创建用户DB_USER,密码为DB_USER_PW
grant create session to DB_USER
'给用户创建会话的权限
grant resource to DB_USER
2.当用户建立后,会自动在Oracle数据库系统中生成属于该用户的Scheme (可以理解为所有属于该用户的表,视图....等对象的集合).
该用户可以将对这些对象的访问权限赋予其它的系统用户.
3.该用户用sqlplus登录后,以下命令可以看到该用户的权限(该部分取自于CNOUG网站):
本用户读取其他用户对象的权限:
select * from user_tab_privs;
本用户所拥有的系统权限:
select * from user_sys_privs;
4. System Privilege列表
PRIVILEGE
NAME
PROPERTY
创建其他用户下的物化视图报错权限不足
作者：佚名 出处：中国自学编程网收集整理 发布日期：2008-05-26
在创建一个其他用户的物化视图时报错，错误信息为：ORA-01031: 权限不足。
下面看看问题的重现：
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已连接。
SQL> CREATE TABLE T (ID NUMBER PRIMARY KEY, NAME VARCHAR2(30));
表已创建。
SQL> CREATE MATERIALIZED VIEW LOG ON T;
实体化视图日志已创建。
SQL> CREATE MATERIALIZED VIEW MV_T REFRESH FAST
2 AS SELECT * FROM T;
实体化视图已创建。
用户MV创建物化视图并不会报错，下面尝试使用USER_DBA用户来创建MV用户下的物化视图：
SQL> CONN USER_DBA输入口令: 已连接。
SQL> CREATE MATERIALIZED VIEW MV_T REFRESH FAST
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 FAST
2 AS SELECT * FROM MV.T;
AS SELECT * FROM MV.T
*第 2 行出现错误:
ORA-01031: 权限不足
可以看到，USER_DBA可以在当自己的SCHEMA建立物化视图，但是无法在MV用户下建立物化视图。
由于Oracle报错缺少权限，首先感觉的问题是出在USER_DBA用户上，因为MV用户自己可以建立物化视图。
尝试使用SYS来建立物化视图：
SQL> CONN / AS SYSDBA已连接。
SQL> CREATE MATERIALIZED VIEW MV.MV_T REFRESH FAST
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_DBA已连接。
SQL> CREATE MATERIALIZED VIEW MV.MV_T REFRESH FAST
2 AS SELECT * FROM MV.T;
AS SELECT * FROM MV.T
*第 2 行出现错误:
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 FAST
2 AS SELECT * FROM MV.T;
AS SELECT * FROM MV.T
*第 2 行出现错误:
ORA-01031: 权限不足
SQL> ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT OFF';
会话已更改。
下面查看一下trace中，错误出现在哪里：
SQL> SELECT SPID FROM V$PROCESS P, V$SESSION S
2 WHERE P.ADDR = S.PADDR
3 AND SID IN (SELECT SID FROM V$MYSTAT WHERE ROWNUM = 1);
SPID
------------
13940
SQL> HOST
[oracle@testzone ~]$ more $ORACLE_BASE/admin/testzj/udump/testzj_ora_13940.trc
/opt/ora10g/admin/testzj/udump/testzj_ora_13940.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
ORACLE_HOME = /opt/ora10g/product/10.2.0/db_1
System name: Linux
Node name: testzone
Release: 2.6.18-8.el5xen
Version: #1 SMP Tue Jun 5 23:53:34 EDT 2007
Machine: x86_64
Instance name: testzj
Redo thread mounted by this instance: 1
Oracle process number: 20
Unix process pid: 13940, image: oracle@testzone (TNS V1-V3)
*** 2008-05-08 16:05:49.580
*** ACTION NAME:() 2008-05-08 16:05:49.580
*** MODULE NAME:(SQL*Plus) 2008-05-08 16:05:49.580
*** SERVICE NAME:(SYS$USERS) 2008-05-08 16:05:49.580
*** SESSION ID:(148.437) 2008-05-08 16:05:49.580
WAIT #6: nam='SQL*Net message to client' ela= 3 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1181869091387111
*** 2008-05-08 16:06:11.329
WAIT #6: nam='SQL*Net message from client' ela= 21238676 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1181869112626106
XCTEND rlbk=0, rd_only=1
=====================
.
.
.
=====================
PARSE ERROR #3:len=57 dep=1 uid=71 oct=3 lid=71 tim=1181869112672434 err=10980
SELECT "T"."ID" "ID","T"."NAME" "NAME" FROM "MV"."T" "T"
=====================
PARSING IN CURSOR #1 len=108 dep=2 uid=0 oct=3 lid=0 tim=1181869112673324 hv=1873661484 ad='a6554dd0'
select 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
END OF STMT
PARSE #1:c=0,e=512,p=0,cr=0,cu=0,mis=1,r=0,dep=2,og=4,tim=1181869112673319
BINDS #1:
kkscoacd
Bind#0
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=0001 frm=00 csi=00 siz=24 off=0
kxsbbbfp=2aaaacaa0a40 bln=22 avl=04 flg=05
value=81507
EXEC #1:c=0,e=1407,p=0,cr=0,cu=0,mis=1,r=0,dep=2,og=4,tim=1181869112674805
FETCH #1:c=8000,e=301,p=0,cr=3,cu=0,mis=0,r=0,dep=2,og=4,tim=1181869112675138
STAT #1 id=1 cnt=0 pid=0 pos=1 obj=0 op='HASH JOIN (cr=3 pr=0 pw=0 time=315 us)'
STAT #1 id=2 cnt=0 pid=1 pos=1 obj=31 op='TABLE ACCESS CLUSTER CDEF$ (cr=3 pr=0 pw=0 time=43 us)'
STAT #1 id=3 cnt=1 pid=2 pos=1 obj=30 op='INDEX UNIQUE SCAN I_COBJ# (cr=2 pr=0 pw=0 time=19 us)'
STAT #1 id=4 cnt=0 pid=1 pos=2 obj=32 op='TABLE ACCESS CLUSTER CCOL$ (cr=0 pr=0 pw=0 time=0 us)'
STAT #1 id=5 cnt=0 pid=4 pos=1 obj=30 op='INDEX UNIQUE SCAN I_COBJ# (cr=0 pr=0 pw=0 time=0 us)'
=====================
PARSING IN CURSOR #2 len=87 dep=1 uid=71 oct=1 lid=71 tim=1181869112675587 hv=2894365893 ad='a0abf708'
CREATE TABLE "MV"."MV_T" ("ID","NAME") AS SELECT "T"."ID","T"."NAME" FROM "MV"."T" "T"
END OF STMT
PARSE #2:c=8000,e=3005,p=0,cr=4,cu=0,mis=1,r=0,dep=1,og=1,tim=1181869112675583
BINDS #2:
EXEC #2:c=0,e=129,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,tim=1181869112675776
ERROR #2:err=1031 tim=1740669629
EXEC #4:c=40002,e=34575,p=0,cr=78,cu=22,mis=0,r=0,dep=0,og=1,tim=1181869112675848
ERROR #4:err=1031 tim=1740669629
STAT #5 id=1 cnt=0 pid=0 pos=1 obj=178 op='TABLE ACCESS BY INDEX ROWID SNAP$ (cr=1 pr=0 pw=0 time=22 us)'
STAT #5 id=2 cnt=0 pid=1 pos=1 obj=181 op='INDEX UNIQUE SCAN I_SNAP1 (cr=1 pr=0 pw=0 time=12 us)'
WAIT #4: nam='log file sync' ela= 95 buffer#=1861 p2=0 p3=0 obj#=-1 tim=1181869112676218
WAIT #4: nam='SQL*Net break/reset to client' ela= 4 driver id=1650815232 break?=1 p3=0 obj#=-1 tim=1181869112676272
WAIT #4: nam='SQL*Net break/reset to client' ela= 68 driver id=1650815232 break?=0 p3=0 obj#=-1 tim=1181869112676360
WAIT #4: nam='SQL*Net message to client' ela= 3 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1181869112676382
*** 2008-05-08 16:06:24.825
WAIT #4: nam='SQL*Net message from client' ela= 13130207 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1181869125806625
=====================
PARSING IN CURSOR #7 len=55 dep=0 uid=72 oct=42 lid=72 tim=1181869125806851 hv=524428051 ad='0'
ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT OFF'
END OF STMT
PARSE #7:c=0,e=111,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,tim=1181869125806846
EXEC #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
exit
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 ADM
------------------------------ ---------------------------------------- ---
MV CREATE MATERIALIZED VIEW NO
MV UNLIMITED TABLESPACE NO
SQL> GRANT CREATE TABLE TO MV;
授权成功。
SQL> CREATE MATERIALIZED VIEW MV.MV_T REFRESH FAST
2 AS SELECT * FROM MV.T;
实体化视图已创建。
问题居然是出在MV用户下，创建其他用户下的物化视图，不仅需要这个用户拥有CREATE TABLE权限，而且要求这个权限不能是通过角色获得，必须直接授权。
从这个要求可以推测，当建立其他用户下的物化视图，多半是通过存储过程实现的，因为这个授权的要求很显然是符合存储过程授权规则。
至于SYS用户为什么不需要目标用户直接授权CREATE TABLE，根据Tom的描述，SYS用户是特殊的，普通的规则在SYS身上并不适用。
