有两个用户jytest,jytest1都拥有dba权限在jytest1用户创建了一个表test
create user jytest identified by "jytest"
default tablespace users temporary tablespace temp;
grant dba to jytest;
grant resource to jytest;
grant connect to jytest;
create user jytest1 identified by "jytest1"
default tablespace users temporary tablespace temp;
grant dba to jytest1;
grant resource to jytest1;
grant connect to jytest1;
create table jytest1.test(userid number(20));
SQL> select a.owner,a.object_name,a.object_type from dba_objects a where a.object_id=52603;
OWNER OBJECT_NAME OBJECT_TYPE
------------------------------ ----------------------------------- -------------------
JYTEST1 TEST TABLE
在用户jytest下可以直接查询jytest1.test表但是在jytest下创建一个过程,在过程中引用jytest1.test表编译不通过.在过程中引用别的用户对象不能通过role权限来访问,要通过对象授权才能在过程中访问
SQL> alter session set events '10046 trace name context forever,level 12';
Session altered
SQL> select * from jytest1.test;
USERID---------------------
SQL> alter session set events '10046 trace name context off';
Session altered
它的跟踪文件中显示的记录部分信息如下:select col#, grantee#, privilege#,max(mod(nvl(option$,0),2)) from objauth$ where obj#=:1 and col# is not null group by privilege#, col#, grantee# order by col#, grantee#END OF STMTPARSE #2:c=0,e=86,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1355777810000085BINDS #2:kkscoacd Bind#0
acdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
acflg=08 fl2=0001 frm=00 csi=00 siz=24 ff=0
kxsbbbfp=b7f47498 bln=22 avl=04 flg=05
value=52603上面的操作显示了用户jytest在执行select * from jytest1.test查询时会查询jytest用户对jytest1.test表是否有select权限
select con#,obj#,rcon#,enabled,nvl(defer,0) from cdef$ where robj#=:1END OF STMTPARSE #4:c=0,e=55,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1355777810001339BINDS #4:kkscoacd Bind#0
acdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
acflg=08 fl2=0001 frm=00 csi=00 siz=24 ff=0
kxsbbbfp=b7ecffb8 bln=22 avl=04 flg=05
value=52603
上面的查询是查询jytest1.test表中的列的定义
但是在用户jytest创建mytest过程时编译时会报错
SQL> create or replace procedure mytest
2 as
3 hj number;
4 begin
5 select count(*) into hj from jytest1.test;
6
7 end ;
8 /
SQL> alter session set events '10046 trace name context forever,level 12';
Session altered
SQL> alter procedure mytest compile;
Warning: Procedure altered with compilation errors
SQL> alter session set events '10046 trace name context off';
Session altered
Warning: Procedure created with compilation errors 而这个错误的真实信息是Error: PL/SQL: ORA-00942: table or view does not exist
SQL> select a.owner,a.object_name,a.object_type from dba_objects a where a.object_id=52604;
OWNER OBJECT_NAME OBJECT_TYPE
------------------------------ ----------------------------------- -------------------
JYTEST MYTEST PROCEDURE
SQL> select a.owner,a.object_name,a.object_type from dba_objects a where a.object_id=52603;
OWNER OBJECT_NAME OBJECT_TYPE
------------------------------ ----------------------------------- -------------------
JYTEST1 TEST TABLE
下面是alter procedure mytest compile操作的跟踪文件的部分信息
在jytest.mytest过程进行编译时,oracle会查找与mytest过程所引用
的对对象.下面向dependency$表中插入了mytest和test两个对象
insert into dependency$(d_obj#,d_timestamp,order#,p_obj#,p_timestamp, property, d_attrs)values (:1,:2,:3,:4,:5,:6, :7)
END OF STMT
PARSE #5:c=999,e=367,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,tim=1355777875297551
BINDS #5:
kkscoacd
Bind#0
acdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
acflg=08 fl2=0001 frm=00 csi=00 siz=24 ff=0
kxsbbbfp=b7dd9b88 bln=22 avl=04 flg=05
value=52604
Bind#1
acdty=12 mxl=07(07) mxlc=00 mal=00 scl=00 pre=00
acflg=18 fl2=0001 frm=00 csi=00 siz=8 ff=0
kxsbbbfp=29a5cae5 bln=08 avl=07 flg=09
value="12/29/2013 18:50:54"
Bind#2
acdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
acflg=08 fl2=0001 frm=00 csi=00 siz=24 ff=0
kxsbbbfp=b7dd9b64 bln=24 avl=01 flg=05
value=0
Bind#3
acdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
acflg=08 fl2=0001 frm=00 csi=00 siz=24 ff=0
kxsbbbfp=b7dd9b40 bln=24 avl=03 flg=05
value=887
Bind#4
acdty=12 mxl=07(07) mxlc=00 mal=00 scl=00 pre=00
acflg=18 fl2=0001 frm=00 csi=00 siz=8 ff=0
kxsbbbfp=29aca975 bln=08 avl=07 flg=09
value="4/18/2003 0:0:0"
Bind#5
acdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
acflg=08 fl2=0001 frm=00 csi=00 siz=24 ff=0
kxsbbbfp=b7dd9b1c bln=24 avl=02 flg=05
value=1
Bind#6
acdty=23 mxl=32(00) mxlc=00 mal=00 scl=00 pre=00
acflg=18 fl2=0001 frm=00 csi=00 siz=32 ff=0
kxsbbbfp=00000000 bln=32 avl=00 flg=09
WAIT #5: nam='db file sequential read' ela= 28 file#=1 block#=689 blocks=1 obj#=-1 tim=1355777875298765
EXEC #5:c=1000,e=1358,p=1,cr=1,cu=7,mis=1,r=1,dep=1,og=4,tim=1355777875299060
BINDS #5:
kkscoacd
Bind#0
acdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
acflg=08 fl2=0001 frm=00 csi=00 siz=24 ff=0
kxsbbbfp=b7dd9b88 bln=22 avl=04 flg=05
value=52604
Bind#1
acdty=12 mxl=07(07) mxlc=00 mal=00 scl=00 pre=00
acflg=18 fl2=0001 frm=00 csi=00 siz=8 ff=0
kxsbbbfp=29a5cae5 bln=08 avl=07 flg=09
value="12/29/2013 18:50:54"
Bind#2
acdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
acflg=08 fl2=0001 frm=00 csi=00 siz=24 ff=0
kxsbbbfp=b7dd9b64 bln=24 avl=02 flg=05
value=1
Bind#3
acdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
acflg=08 fl2=0001 frm=00 csi=00 siz=24 ff=0
kxsbbbfp=b7dd9b40 bln=24 avl=04 flg=05
value=52603
Bind#4
acdty=12 mxl=07(07) mxlc=00 mal=00 scl=00 pre=00
acflg=18 fl2=0001 frm=00 csi=00 siz=8 ff=0
kxsbbbfp=29a522d1 bln=08 avl=07 flg=09
value="12/29/2013 18:44:2"
Bind#5
acdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
acflg=08 fl2=0001 frm=00 csi=00 siz=24 ff=0
kxsbbbfp=b7dd9b1c bln=24 avl=02 flg=05
value=1
Bind#6
acdty=23 mxl=32(00) mxlc=00 mal=00 scl=00 pre=00
acflg=18 fl2=0001 frm=00 csi=00 siz=32 ff=0
kxsbbbfp=00000000 bln=32 avl=00 flg=09
EXEC #5:c=1000,e=741,p=0,cr=0,cu=7,mis=0,r=1,dep=1,og=4,tim=1355777875299853
=====================
PARSING IN CURSOR #5 len=69 dep=1 uid=0 ct=2 lid=0 tim=1355777875301888 hv=544200346 ad='29a537d8'
insert into access$(d_obj#,order#,columns,types) values (:1,:2,:3,:4)
END OF STMT
PARSE #5:c=1000,e=1363,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,tim=1355777875301883
BINDS #5:
kkscoacd
Bind#0
acdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
acflg=08 fl2=0001 frm=00 csi=00 siz=24 ff=0
kxsbbbfp=b7dd9b88 bln=22 avl=04 flg=05
value=52604
Bind#1
acdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
acflg=08 fl2=0001 frm=00 csi=00 siz=24 ff=0
kxsbbbfp=b7dd9b64 bln=24 avl=02 flg=05
value=1
Bind#2
acdty=23 mxl=32(00) mxlc=00 mal=00 scl=00 pre=00
acflg=18 fl2=0001 frm=00 csi=00 siz=32 ff=0
kxsbbbfp=00000000 bln=32 avl=00 flg=09
Bind#3
acdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
acflg=08 fl2=0001 frm=00 csi=00 siz=24 ff=0
kxsbbbfp=b7dd9b40 bln=24 avl=02 flg=05
value=9
WAIT #5: nam='db file sequential read' ela= 72 file#=1 block#=697 blocks=1 obj#=-1 tim=1355777875303656
EXEC #5:c=2999,e=2589,p=1,cr=1,cu=3,mis=1,r=1,dep=1,og=4,tim=1355777875304577
BINDS #3:
kkscoacd
Bind#0
acdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
acflg=08 fl2=0001 frm=00 csi=00 siz=24 ff=0
kxsbbbfp=b7ddf88c bln=22 avl=02 flg=05
value=18
而当我将jytest1.test的select权限授予给jytest
grant select on jytest1.test to jytest;
再进行编译时就成功了
SQL> alter session set events '10046 trace name context forever,level 12';
Session altered
SQL> alter procedure mytest compile;
Procedure altered
SQL> alter session set events '10046 trace name context off';
Session altered
跟踪文件的部分信息如下
insert into dependency$(d_obj#,d_timestamp,order#,p_obj#,p_timestamp, property, d_attrs)values (:1,:2,:3,:4,:5,:6, :7)
END OF STMT
PARSE #4:c=0,e=537,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,tim=1355782171491739
BINDS #4:
kkscoacd
Bind#0
acdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
acflg=08 fl2=0001 frm=00 csi=00 siz=24 ff=0
kxsbbbfp=b7ee5c44 bln=22 avl=04 flg=05
value=52604
Bind#1
acdty=12 mxl=07(07) mxlc=00 mal=00 scl=00 pre=00
acflg=18 fl2=0001 frm=00 csi=00 siz=8 ff=0
kxsbbbfp=29a5cae5 bln=08 avl=07 flg=09
value="12/29/2013 20:22:13"
Bind#2
acdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
acflg=08 fl2=0001 frm=00 csi=00 siz=24 ff=0
kxsbbbfp=b7ee5c20 bln=24 avl=01 flg=05
value=0
Bind#3
acdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
acflg=08 fl2=0001 frm=00 csi=00 siz=24 ff=0
kxsbbbfp=b7ee5bfc bln=24 avl=03 flg=05
value=887
Bind#4
acdty=12 mxl=07(07) mxlc=00 mal=00 scl=00 pre=00
acflg=18 fl2=0001 frm=00 csi=00 siz=8 ff=0
kxsbbbfp=29aca975 bln=08 avl=07 flg=09
value="4/18/2003 0:0:0"
Bind#5
acdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
acflg=08 fl2=0001 frm=00 csi=00 siz=24 ff=0
kxsbbbfp=b7ee5bd8 bln=24 avl=02 flg=05
value=1
Bind#6
acdty=23 mxl=32(00) mxlc=00 mal=00 scl=00 pre=00
acflg=18 fl2=0001 frm=00 csi=00 siz=32 ff=0
kxsbbbfp=00000000 bln=32 avl=00 flg=09
EXEC #4:c=2000,e=1910,p=0,cr=1,cu=7,mis=1,r=1,dep=1,og=4,tim=1355782171493656
BINDS #4:
kkscoacd
Bind#0
acdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
acflg=08 fl2=0001 frm=00 csi=00 siz=24 ff=0
kxsbbbfp=b7ee5c44 bln=22 avl=04 flg=05
value=52604
Bind#1
acdty=12 mxl=07(07) mxlc=00 mal=00 scl=00 pre=00
acflg=18 fl2=0001 frm=00 csi=00 siz=8 ff=0
kxsbbbfp=29a5cae5 bln=08 avl=07 flg=09
value="12/29/2013 20:22:13"
Bind#2
acdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
acflg=08 fl2=0001 frm=00 csi=00 siz=24 ff=0
kxsbbbfp=b7ee5c20 bln=24 avl=02 flg=05
value=1
Bind#3
acdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
acflg=08 fl2=0001 frm=00 csi=00 siz=24 ff=0
kxsbbbfp=b7ee5bfc bln=24 avl=04 flg=05
value=52603
Bind#4
acdty=12 mxl=07(07) mxlc=00 mal=00 scl=00 pre=00
acflg=18 fl2=0001 frm=00 csi=00 siz=8 ff=0
kxsbbbfp=29a522d1 bln=08 avl=07 flg=09
value="12/29/2013 18:44:2"
Bind#5
acdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
acflg=08 fl2=0001 frm=00 csi=00 siz=24 ff=0
kxsbbbfp=b7ee5bd8 bln=24 avl=02 flg=05
value=1
Bind#6
acdty=23 mxl=32(00) mxlc=00 mal=00 scl=00 pre=00
acflg=18 fl2=0001 frm=00 csi=00 siz=32 ff=0
kxsbbbfp=00000000 bln=32 avl=00 flg=09
EXEC #4:c=2000,e=1717,p=0,cr=0,cu=7,mis=0,r=1,dep=1,og=4,tim=1355782171495379
BINDS #4:
kkscoacd
Bind#0
acdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
acflg=08 fl2=0001 frm=00 csi=00 siz=24 ff=0
kxsbbbfp=b7ee5c44 bln=22 avl=04 flg=05
value=52604
Bind#1
acdty=12 mxl=07(07) mxlc=00 mal=00 scl=00 pre=00
acflg=18 fl2=0001 frm=00 csi=00 siz=8 ff=0
kxsbbbfp=29a5cae5 bln=08 avl=07 flg=09
value="12/29/2013 20:22:13"
Bind#2
acdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
acflg=08 fl2=0001 frm=00 csi=00 siz=24 ff=0
kxsbbbfp=b7ee5c20 bln=24 avl=02 flg=05
value=2
Bind#3
acdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
acflg=08 fl2=0001 frm=00 csi=00 siz=24 ff=0
kxsbbbfp=b7ee5bfc bln=24 avl=03 flg=05
value=3984
Bind#4
acdty=12 mxl=07(07) mxlc=00 mal=00 scl=00 pre=00
acflg=18 fl2=0001 frm=00 csi=00 siz=8 ff=0
kxsbbbfp=298cc271 bln=08 avl=07 flg=09
value="6/30/2005 19:12:15"
Bind#5
acdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
acflg=08 fl2=0001 frm=00 csi=00 siz=24 ff=0
kxsbbbfp=b7ee5bd8 bln=24 avl=02 flg=05
value=1
Bind#6
acdty=23 mxl=32(00) mxlc=00 mal=00 scl=00 pre=00
acflg=18 fl2=0001 frm=00 csi=00 siz=32 ff=0
kxsbbbfp=00000000 bln=32 avl=00 flg=09
EXEC #4:c=1000,e=984,p=0,cr=0,cu=7,mis=0,r=1,dep=1,og=4,tim=1355782171496437
=====================
多插入了一条d_obj#=52604 p_obj#=3984的记录
SQL> select * from dependency$ a where a.d_obj#=52604;
D_OBJ# D_TIMESTAMP ORDER# P_OBJ# P_TIMESTAMP D_OWNER# PROPERTY D_ATTRS D_REASON
---------- ----------- ---------- ---------- ----------- ---------- ---------- -------------------------------------------------------------------------------- --------------------------------------------------------------------------------
52604 2013-12-29 0 887 2003-4-18 1
52604 2013-12-29 1 52603 2013-12-29 1
52604 2013-12-29 2 3984 2005-6-30 1 1
PARSING IN CURSOR #4 len=69 dep=1 uid=0 ct=2 lid=0 tim=1355782171496998 hv=544200346 ad='298e30f4'
insert into access$(d_obj#,order#,columns,types) values (:1,:2,:3,:4)
END OF STMT
PARSE #4:c=1000,e=486,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,tim=1355782171496991
BINDS #4:
kkscoacd
Bind#0
acdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
acflg=08 fl2=0001 frm=00 csi=00 siz=24 ff=0
kxsbbbfp=b7ef15ec bln=22 avl=04 flg=05
value=52604
Bind#1
acdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
acflg=08 fl2=0001 frm=00 csi=00 siz=24 ff=0
kxsbbbfp=b7ef15c8 bln=24 avl=02 flg=05
value=1
Bind#2
acdty=23 mxl=32(00) mxlc=00 mal=00 scl=00 pre=00
acflg=18 fl2=0001 frm=00 csi=00 siz=32 ff=0
kxsbbbfp=00000000 bln=32 avl=00 flg=09
Bind#3
acdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
acflg=08 fl2=0001 frm=00 csi=00 siz=24 ff=0
kxsbbbfp=b7ef15a4 bln=24 avl=02 flg=05
value=9
EXEC #4:c=3000,e=2183,p=0,cr=1,cu=3,mis=1,r=1,dep=1,og=4,tim=1355782171499507
=====================
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/26015009/viewspace-751883/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/26015009/viewspace-751883/