Re: Interesting Exploit in PL/SQL
- From: "Stefan Knecht" <knecht.stefan@xxxxxxxxx>
- To: Jacques.Kilchoer@xxxxxxxxx
- Date: Sat, 2 Dec 2006 08:22:47 +0100
On 12/2/06, Jacques Kilchoer <Jacques.Kilchoer@xxxxxxxxx> 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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
*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@xxxxxxxxx> wrote:
>
> On 12/1/06, Stefan Knecht <knecht.stefan@xxxxxxxxx > 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@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
>
Other related posts:
