Some time ago I was contacted by one of our developers (smile and wave, yes, you!) with very interesting issue on (fortunately) development database. The next is almost a quote:
insert into SOMETABLE (CODE,...) values ('CODEVALUE', ...); insert into SOMETABLE (CODE, * ERROR at line 1: ORA-00604: error occurred at recursive SQL level 2 ORA-20033: USR has no privileges to DDL any object in this schema...
Ok, 20033 is user-defined exception at recursive level 2… this might be a trigger. And that’s it, I’ve found a ‘DDL prevention trigger’ which I have installed several months ago just for testing. The trigger was:
create or replace trigger system.ddl_prevent_tr before create or alter or drop or rename on database when ( sys.dictionary_obj_owner in ('APPOWN') ) begin if ora_sysevent in ('CREATE','DROP') and sys.dictionary_obj_owner in ('APPOWN') and upper(user) not in ('APPOWN') and upper(sys.dictionary_obj_type) in ('PROCEDURE','PACKAGE','PACKAGE BODY','FUNCTION','PROCEDURE','TRIGGER','TABLE') then raise_application_error(-20033,Upper(USER)||' has no privileges to DDL any object in this schema...'); end if; end; /
That’s it but… the trigger is defined as ‘before create or alter or drop or rename’, where DDL came from into our insert? The table SOMETABLE doesn’t belong to APPOWN schema, and calling user isn’t APPOWN also.
After several minutes of stupor I’ve get an idea. To prove it I did the same insert and got the same error, but under 10046 trace. The tracefile produced was a bit huge.
Search backward from the end of the file shows some piece of cake:
EXEC #47408502285272:c=3000,e=2397,p=0,cr=32,cu=0,mis=0,r=0,dep=2,og=1,plh=0,tim=1343647204457842 ERROR #1:err=20033 tim=1343647204457853 CLOSE #47408502285272:c=0,e=10,dep=2,type=0,tim=1343647204457898 EXEC #47408502569656:c=174973,e=174353,p=299,cr=3161,cu=4,mis=0,r=0,dep=1,og=4,plh=0,tim=1343647204457919 ERROR #11:err=604 tim=1343647204457930 kdpurtab: err=604 goc1=604 goc2=20033 goc3=6512 CLOSE #47408502569656:c=0,e=2,dep=1,type=0,tim=1343647204457981
6th line is our error. Some additional search through the tracefile shows that cursor #47408502285272 is our trigger, and #47408502569656 is (tadaaa!)
drop table "APPOWN"."BIN$sdbtwxw2q6DgQAEKFjxyWw==$0" purge
Futher ‘reading’ of the tracefile showed the story:
- During insert Oracle didn’t find free space in the table segments and made a decision to allocate a next extent.
- Then it didn’t find enough contiguous set of blocks for the new extent and tried to free up some space from recyclebin and this time it was an object “APPOWN”.”BIN$sdbtwxw2q6DgQAEKFjxyWw==$0″
- It issued a drop operation for it which is definitely a DDL and object is owned by APPOWN and therefore has been caught by our trigger. That’s it.
The trigger has been fixed by adding additional condition: ora_dict_obj_name not like ‘BIN$%’