Re: Oracle security vulnerability, nuisance, or paranoia?

From: <casey.kirkpatrick_at_gmail.com>
Date: 11 Jan 2005 08:03:16 -0800
Message-ID: <1105459396.790983.13840@f14g2000cwb.googlegroups.com>

Thomas Kyte wrote:
...
>
> So, I can simplify your example into
>
> SQL> lock table T1;
>
>
> However, that aside, if your primary goal is to prevent select for
updates --
> you might be able to do something similar to this:
...
> ops$tkyte_at_ORA9IR2> create or replace view v
> 2 as
> 3 select x from t order by x;
>
> Thomas Kyte
> Oracle Public Sector
> http://asktom.oracle.com/
> opinions are my own and may not reflect those of Oracle Corporation

Casey:

Tom,

Thanks - very clear... and your LOCK TABLE point pretty much sealed the deal (my only remaining *sheepish* objection is semantic; I'd prefer the "FOR UPDATE" token were instead "LOCKING ROWS" or some such).

Your VIEW suggestion also hits the nail on the head for a solution - however, this is a large enough table (1E6 rows) that if a lot of users were doing table scans, the sorting overhead could quickly become a problem. Also, my manager (who honest-to-god feels that relational databases are largely convenient storage/access devices for flat files) "fears" DDL (and the notion of table renames, insert triggers, and transaparent views are new and frightening).

Since my management does not "fear" simple software changes to our code, I'm considering an approach of changing our code from a

• OLD VERSION CURSOR C1_CUR IS SELECT DUMMY FROM T1 WHERE <conditions met> FOR UPDATE; ... BEGIN FOR C1 IN C1_CUR LOOP -- this statement blocked by users <row processing/update logic> END LOOP; END;

approach (in which the open cursor can get blocked) into a new approach, which basically says, "If you want to lock a row, it's your problem if it doesn't get processed":

• NEW LOGIC CURSOR C1_CUR IS SELECT ROWID ROW_ID FROM T1 WHERE <conditions met>; EXCEPTION ROW_UNAVAILABLE; PRAGMA EXCEPTION_INIT (ROW_UNAVAILABLE, -00054); ... BEGIN FOR C1 IN C1_CUR LOOP BEGIN SELECT DUMMY INTO C1_DUMMY FROM T1 WHERE ROWID = C1.ROW_ID AND <conditions met> FOR UPDATE NOWAIT; <row processing/update logic> EXCEPTION WHEN ROW_UNAVAILABLE THEN NULL; END; END LOOP; END;

This leads me to another thought - wouldn't it be nice if Oracle offered another token "UNLOCKED ONLY", to be used like:

CURSOR C1_CUR IS
SELECT * FROM T1
WHERE <conditions>
FOR UPDATE
UNLOCKED ONLY; where "UNLOCKED ONLY" is like a sister command to "NOWAIT" which simply ignores any rows which cannot be acquired without a NOWAIT violation? Or would there be data integrity violations down that road? Received on Tue Jan 11 2005 - 10:03:16 CST