prevent drop of table

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.

Use schema trigger to prevent dropping table.

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.

Hi,

I'm not sure of the code you would use to stop the drop statement but we use this trigger to keep track of all objects that we drop:

create or replace trigger dropobj_trig
after drop on database
begin
insert into del_log values (
user,
sysdate,
ora_dict_obj_name,
ora_dict_obj_owner,
ora_dict_obj_type);
end;

Hope it helps

Regards

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!!!

Thanks,
-Shyami

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 :-(

Thanks & Sorry,
-Shyami

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
3 begin
4 if ( user in ( 'SCOTT', 'HOTEL', 'PEOPLE' ) )
5 then
6 raise_application_error( -20001,
'You are not allowed to DROP' );
7 end if;
8 end;
9 /

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.

so event publication triggers like the one above can be placed at the schema level in addition to the database level? Thanks.