PL/SQL: ORA-00942: table or view does not exist

Two users, jytest and jytest1, both have dba permissions. In jytest1, a user creates a table named 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

You can directly query the jytest1.test table in jytest but create a process in jytest. During this process, the jytest1.test table cannot be compiled. other user objects referenced in the process cannot be accessed through the role permission. Only authorized objects can be accessed during the process.

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

 

The record information displayed in its trace file is as follows: 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 = 1355777810000085 BINDS #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 the operation above shows that when user jytest executes the select * from jytest1.test query, it will query whether the user jytest has the select permission on the jytest1.test table.

 

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 = 1355777810001339 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 = b7ecffb8 bln = 22 avl = 04 flg = 05

Value = 52603

 

The above query is to query the column definitions in the jytest1.test table.

1 2 3 4 5 Next Page