Just this morning i was asked to investigate why a simple insert was failing . The error the users were getting was
INSERT INTO "ABC" ( "ABC1", "ABC2", "ABC3", "ABC4" ) VALUES
( 'ABC1', 1234, 'N', 'NN' )
ORA-20009: Cannot drop table Drop Not Authorized
ORA-06512: at line 14)
While we knew this was a error generated by our custom trigger that we have implemented for securing our databases from malicious application table drops. We were a little puzzled as to why a simple insert would invoke our table drop prevention trigger.
After a little dose of 10046 this became apparent to us
PARSING IN CURSOR #3 len=62 dep=1 uid=0 oct=12 lid=0 This was going on
tim=48271684044651 hv=0 ad='1045a248'
drop table "APP_NAME"."BIN$YQPns5G0IL7gQzwEPI0gvg==$0" purge
END OF STMT
PARSE #3:c=0,e=450,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,tim=48271684044648
The row insert required the table to extend to a segment that was previously owned by a dropped object . To clear space Oracle actually issues a drop table purge when it needs to extend an existing object.
We are now modifying our security trigger to exclude BIN$ objects .
Edait: after a question in the comments i have now validated that an implicit commit will happen if an insert and purge recycle bin are executed . i am going to test creating an outof space and reuse situation to see if that would cause an implicit commit too. Thanks to Chen Shapiro to point ing out the implicit commits in DDL statements.
Update: Oracle manages the space management operation outside of a transaction and a commit will not happen to the operations that have happened in the outlying transaction. it seems purge dba_recyclebin and an internal drop table purge for space management by oracle are handled differently
INSERT INTO "ABC" ( "ABC1", "ABC2", "ABC3", "ABC4" ) VALUES
( 'ABC1', 1234, 'N', 'NN' )
ORA-20009: Cannot drop table Drop Not Authorized
ORA-06512: at line 14)
While we knew this was a error generated by our custom trigger that we have implemented for securing our databases from malicious application table drops. We were a little puzzled as to why a simple insert would invoke our table drop prevention trigger.
After a little dose of 10046 this became apparent to us
PARSING IN CURSOR #3 len=62 dep=1 uid=0 oct=12 lid=0 This was going on
tim=48271684044651 hv=0 ad='1045a248'
drop table "APP_NAME"."BIN$YQPns5G0IL7gQzwEPI0gvg==$0" purge
END OF STMT
PARSE #3:c=0,e=450,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,tim=48271684044648
The row insert required the table to extend to a segment that was previously owned by a dropped object . To clear space Oracle actually issues a drop table purge when it needs to extend an existing object.
We are now modifying our security trigger to exclude BIN$ objects .
Edait: after a question in the comments i have now validated that an implicit commit will happen if an insert and purge recycle bin are executed . i am going to test creating an outof space and reuse situation to see if that would cause an implicit commit too. Thanks to Chen Shapiro to point ing out the implicit commits in DDL statements.
Update: Oracle manages the space management operation outside of a transaction and a commit will not happen to the operations that have happened in the outlying transaction. it seems purge dba_recyclebin and an internal drop table purge for space management by oracle are handled differently
Any way of telling whether that implicit drop table actually causes a commit in the primary transaction, or is it done in the same way as an autonomous transaction ?
ReplyDeleteas far as what i drop this transaction did not commit probably becuase the drop trigger failed. good point though and chen shapiro has a post has implicit DDL commits
ReplyDeletehttp://prodlife.wordpress.com/2009/02/11/ddl-implicit-commit/
for a DDL before and after and in this case because of the drop table trigger the drop never happened and the insert never committed. i will have to create a testcase and see if a implicit drop table will cause a commit and this will be very concerning if it does.
Gary I just validated that a space management operation does not cause an implicit commit on the transaction. It seems oracle is calling an autonomous transaction to complete the drop which would not impact the transaction.
ReplyDelete