Re: DDL Trigger question

"Richard Foote" <richard.foote_at_bigpond.com> wrote in message news:Q8p29.51046$Hj3.152066_at_newsfeeds.bigpond.com...
> 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
> >
> >
> >
>

Thanks, Richard.
I missed that one.

Paul Received on Fri Aug 02 2002 - 12:35:27 CDT