Re: Interesting Exploit in PL/SQL

From: Stefan Knecht <knecht.stefan_at_gmail.com>
Date: Sat, 2 Dec 2006 08:22:47 +0100
Message-ID: <486b2b610612012322q5fc3225fj4b1eb23a6bbf4ca@mail.gmail.com>

On 12/2/06, Jacques Kilchoer <Jacques.Kilchoer_at_quest.com> wrote:
>
> I agree with Mr. Litchfield. The invalid assumption here is : the
> noaudit command can only have an error if auditing wasn't turned on in the
> first place. And I have found that it's a mistake to assume "there's only
> one possible thing that can go wrong here." IMHO, it would be better to look
> for the "expected" errors (at a cursory glance, ORA-00942 and ORA-01031) and
> trap only those (see below). Or else, like Mark Farnham said yesterday, to
> publish a list of tables that still have auditing enabled once your
> procedure has run.
>

I agree with you there, the statement "the noaudit command can only have an error if auditing wasn't turned on in the first place" was wrong. But that's not actually the point. The point is that we don't care about any errors that could happen.

I remember a developer coming to me after spending half an hour trying to
> debug an the application error message "This order number already exists in
> the system." The answer: our application assumed that the only error on an
> insert would be a duplicate primary key, when in this case the error was
> "maximum number of extents reached for the table" - but the application
> reported it as a duplicate entry
>

This example is a wholly different story, though. You're absolutely right that this is no place for a when others then null;

.
>
> SQL> -- table does not exist (or insufficient privileges to "see" the
> table)
> SQL> noaudit insert on a.b ;
> noaudit insert on a.b
> *
> ERREUR à la ligne 1 :
> ORA-00942: Table ou vue inexistante
>

Yep, that can happen. And actually, we've had several occasions where you'd select objects from the dba_ audit views, and when you'd then try to use audit / noaudit on them, Oracle would say they don't exist (even though they did, and we ran it as SYS). Point being, when turning ON auditing, we need to know that this failed. When turning it off, we don't.

SQL> -- insufficient privileges
> SQL> noaudit insert on sys.tab$ ;
> noaudit insert on sys.tab$
> *
> ERREUR à la ligne 1 :
> ORA-01031: privilèges insuffisants
>
> SQL> -- turning off auditing on a table that did not have auditing turned
> on (doing it twice just to be sure)
> SQL> noaudit insert on jrk.t ;
> Commande Noaudit réussie
> SQL> noaudit insert on jrk.t ;
> Commande Noaudit réussie
>
> SQL>
>

And there's also about a dozen more errors (take a peek at grep -i audit $ORACLE_HOME/rdbms/mesg/oraus.msg) that can be raised when trying to audit / noaudit an object. We don't have a problem when the ENABLE would faild due to a nôn-handled exception. We can fix that, and include the error handler in the code. But the DISABLE cannot abort under any circumstances.

Hope I made my point clear now :-)

Stefan

---

> *De :* oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]
> *De la part de* Stefan Knecht
> *Envoyé :* vendredi, 1. décembre 2006 03:03
> *À :* Niall Litchfield
> *Cc :* oracle-l
> *Objet :* Re: Interesting Exploit in PL/SQL
>
> ORA-20001 ? :) Assuming you've created a before noaudit trigger... Could
> be several reasons why someone has such a trigger. If the reason was a
> foolish developer playing around, then we lost with our approach, and an
> unwanted audit could stay in place. If it's legitimate, and the audit has to
> stay in place no matter what (which I suppose is the purpose of such a
> trigger) then no harm done either.
>
> Aside from that, the "disable" procedure was sort of an emergency only
> thing. That would just turn off auditing wherever possible as quickly as
> possible, should problems - of performance or other nature - arise. And we
> really wouldn't want that to abort no matter what. And WHEN OTHERS THEN
> NULL; is just what we need.
>
>
>
> Stefan
>
>
> On 12/1/06, Niall Litchfield <niall.litchfield_at_gmail.com> wrote:
> >
> > On 12/1/06, Stefan Knecht <knecht.stefan_at_gmail.com > wrote:
> > > Well yeah, for the 'enable' procedure we do. But when we try to
> > disable it,
> > > and Oracle doesn't like that, we just don't care - because if it can't
> > be
> > > disabled, it couldn't have been enabled in the first place :)
> >
> > Have a think about this then.
> >
> > SQL> CONN NIALL/JASPER_at_ASDB
> > Connected.
> > SQL> AUDIT INSERT ON T;
> >
> > Audit succeeded.
> >
> > SQL> ALTER TRIGGER AUDIT_TRIGGER ENABLE;
> >
> > Trigger altered.
> >
> > SQL> NOAUDIT INSERT ON T;
> > NOAUDIT INSERT ON T
> > *
> > ERROR at line 1:
> > ORA-00604: error occurred at recursive SQL level 1
> > ORA-20001: AUDIT CHANGES NOT ALLOWED
> > ORA-06512: at line 2
> >
> >
> >
> >
> > --
> > Niall Litchfield
> > Oracle DBA
> > http://www.orawin.info
> >
>
>

--
http://www.freelists.org/webpage/oracle-l

Received on Sat Dec 02 2006 - 01:22:47 CST