RE: Interesting Exploit in PL/SQL

From: Jacques Kilchoer <Jacques.Kilchoer_at_quest.com>
Date: Fri, 1 Dec 2006 15:25:19 -0800
Message-ID: <22EC50E60931494FA666A8CF8260C45B9E6073@ALVMBXW05.prod.quest.corp>

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 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.  

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  

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>

---

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 Fri Dec 01 2006 - 17:25:19 CST