Re: DDL Trigger question

Hi Paul and all,

This is a scheme level trigger. This only fires if the operation is performed within the schema in which the trigger was created. That's why anyone with the drop any table privilege can drop this table and the trigger has no effect (it has nothing to do with sysdba or DBA role privileges).

Note that schema level triggers work for all accounts (except SYS). Not SYS is affected by database level triggers (except logon).

To get around this problem, you can either disable the dropping of all tables in the database via a database level trigger (perhaps overkill and not what you really want) or you be really really careful who you assign the "any" system privileges to (as of course you should).

You can protect a user from themselves but you can't protect the database from a poor DBA !!

Regards

Richard

"Paul Brewer" <paul_at_paul.brewers.org.uk> wrote in message news:3d497f1b$1_3_at_mk-nntp-1.news.uk.worldonline.com...
> "Rudy Susanto" <rsusanto_at_atlas-sp.com> wrote in message
> news:1a928d0b.0207300030.4d450a18_at_posting.google.com...
> > Hi,
> >
> > How to prevent my objects from deletion? I think I can use DDL trigger
> > but i found a problem. Here is the script.
> >
> > SQL>connect scott/tiger
> > Connected.
> >
> > SQL> create trigger tr_drop
> > 2 before drop on schema
> > 3 begin
> > 4 raise_application_error(-20000,'drop object may not allow...');
> > 5 end;
> > 6 /
> > Trigger created.
> >
> > SQL> create table emp2 as select * from emp where 1=2;
> > Table created.
> >
> > SQL> drop table emp2;
> > drop table emp2
> > *
> > ERROR at line 1:
> > ORA-00604: error occurred at recursive SQL level 1
> > ORA-20000: drop object may not allow...
> > ORA-06512: at line 2
> >
> > SQL> conn system/manager
> > Connected.
> >
> > SQL> drop table scott.emp2;
> > Table dropped.
> > SQL>
> >
> > When I connect as SCOTT, I can't drop the table but when I connect as
> > SYSTEM, I can drop it easily. Why this trigger was't running as i
> > expected?
> >
> > This trigger works on schema level, is it possible if i want to know
> > which object that user want to delete?
> >
> > I am using Oracle 8.1.7 NT.
> >
> >
> > Thank in advance,
> >
> > Rudy Susanto
>
> As I understand it, Oracle have decided that database triggers must not
fail
> if the user has the 'burned in' DBA role. This, apparently, is to protect
us
> from ourselves, as otherwise we could create, for example, a logon trigger
> which would lock everybody out from the database forever.
> They may have good reason, but if it were up to me I would exempt only SYS
> as SYSDBA.
>
> My 2c.
>
> Regards,
> Paul
>
>
>
Received on Fri Aug 02 2002 - 01:03:01 CDT