We are getting some very odd permissions errors on our development databases. Things run smoothly for a while, but when the errors appear, the appear for all new schema until we flush the shared pool.
Our environment consists of developers each creating their own schema, with each schema looking fairly identical to each other schema. On completion of work, the schema is dropped.
Every so often we see
dbms_aqadm.start_queue
*
ERROR at line 10:
ORA-06550: line 10, column 5:
PLS-00201: identifier 'DBMS_AQADM' must be declared
ORA-06550: line 10, column 5:
PL/SQL: Statement ignored
When I log into sqlplus as the user in question and run the following
SQL, I see this error
begin -- see bug Bug 54511 / Failed queued message work
for xq in
(select q.name
from user_queue_tables qt, user_queues q
where qt.queue_table = q.queue_table
...
)
loop
dbms_aqadm.start_queue
(
...
);
end loop;
end;
/
but when I run
DESC DBMS_AQADM
from the same schema, it succeeds.
A 10046 trace is similar, but not identical for these two statements.
The permissions check is recursive SQL looking like:
PARSING IN CURSOR #2 len=131 dep=1 uid=0 oct=3 lid=0
tim=1288020317613433 hv=1389591971 ad='297e5b758' sqlid='3g7sxtj9d6zd3'
select privilege#,nvl(col#,0),max(mod(nvl(option$,0),2))from objauth$
where obj#=:1 and grantee#=:2 group by privilege#,nvl(col#,0)
END OF STMTPARSE #2:c=1000,e=892,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,plh=0,tim=1288020317613429
BINDS #2: Bind#0 oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00 oacflg=08 fl2=0001 frm=00 csi=00 siz=24 off=0 kxsbbbfp=2ac09e9049c8 bln=22 avl=03 flg=05 value=8076 Bind#1 oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00 oacflg=08 fl2=0001 frm=00 csi=00 siz=24 off=0 kxsbbbfp=2ac09e904998 bln=24 avl=02 flg=05 value=16 This is run a number of times with different grantee# Object_id 8076 is dbms_aqadm The grantee# checks 5 roles and then a user_id.For the failing case the user# does not exist in user$, for the successful case it is the user# of the schema in question.
Are user or object permissions kept in an in memory linked list? Could there be more than one list and some operations check one and others check the other?
I'm thinking some memory dump or querying of x$ tables might be of help, but I'm not sure of the details. Oracle Support has been of no help so far.
Thanks. Henry -- //www.freelists.org/webpage/oracle-l
