Does anyone know of a way to prevent the drop of a table by all, including the owner? I know I could set up referential integrity that would throw an error on drop, but, I'd like to know if there is a way just to only allow the dba to dropp the table. Thanks.
Thank you for the reply. Could I trouble you to elaborate. I've looked at the documentation and I'm guessing you are suggesting triggering and event publication, that is to say, a trigger that would fire upon the event of the drop table_x DDL statement being issued. Unfortunately, the documentation doesn't really give examples. Thanks.
I am sorry if this sounds silly, but does this trigger actually prevent dropping of a table? Will it not just insert into the log table the details of the person who dropped it as opposed to preventing anyone from dropping it? Please clarify!!!
12 types of triggers exist: before & after; row or table; insert, update, delete (2x2x3=12). You can record the drop as provided in previous post. You can prevent non-owners from dropping objects belonging to another owner. How do you prevent a user from dropping something he/she owns?
Sorry!!! I just did not read the message completely before posting a message!!! Will try to correct myself... and not do the same mistake again. Jovery has specifically mentioned that the trigger is just for keeping track of statistics!!
I sure do not know how you can stop someone from dropping objects that they own :-(
I've been advised that we could use an before drop on database trigger (8i and > only) something like
tkyte@ORACLE8I> create or replace trigger ddl_trigger
2 before create or alter or drop on DATABASE
4 if ( user in ( 'SCOTT', 'HOTEL', 'PEOPLE' ) )
6 raise_application_error( -20001,
'You are not allowed to DROP' );
7 end if;
but I don't to enforce this across the whole database only a specific schema. Any thoughts?
You may not be able to stop a schema owner, but you can slow them down with the trigger. THat is, raise an application error in a before trigger. The owner will at least have to drop the trigger first, but the error in the original trigger can be sent to the alert log and trapped.
The error would stop accidental drops.
Joseph R.P. Maloney, CSP,CDP,CCP
'The answer is 42'