DBAsupport.com Forums - Powered by vBulletin
Results 1 to 10 of 10

Thread: prevent drop of table

  1. #1
    Join Date
    Aug 2000
    Posts
    132
    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.

  2. #2
    Join Date
    Apr 2001
    Posts
    20
    Use schema trigger to prevent dropping table.

  3. #3
    Join Date
    Aug 2000
    Posts
    132
    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.

  4. #4
    Join Date
    Jan 2002
    Location
    Up s**t creek
    Posts
    1,525
    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
    Jim
    Oracle Certified Professional
    "Build your reputation by helping other people build theirs."

    "Sarcasm may be the lowest form of wit but its still funny"

    Click HERE to vist my website!

  5. #5
    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
    ------------------------------------------
    Shyami.S.Seelan, OCP 8i, 9i DBA
    Dublin, Ireland.
    http://www.geocities.com/shyamiseelan/OCP
    ------------------------------------------

  6. #6
    Join Date
    May 2002
    Posts
    2,645
    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?

  7. #7
    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
    ------------------------------------------
    Shyami.S.Seelan, OCP 8i, 9i DBA
    Dublin, Ireland.
    http://www.geocities.com/shyamiseelan/OCP
    ------------------------------------------

  8. #8
    Join Date
    Aug 2000
    Posts
    132
    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?

  9. #9
    Join Date
    Apr 2001
    Location
    Louisville KY
    Posts
    295
    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'

  10. #10
    Join Date
    Aug 2000
    Posts
    132
    so event publication triggers like the one above can be placed at the schema level in addition to the database level? Thanks.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width