Re: Oracle security vulnerability, nuisance, or paranoia?

In article <1105459396.790983.13840_at_f14g2000cwb.googlegroups.com>, casey.kirkpatrick_at_gmail.com says...
>
>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;
>
>

I don't know your data processing logic, but that seems to defy any rules of data processing I know.

"process anything you might be able to get your hands on and skip other stuff willy nilly"

:)

Not sure that it would be "a good idea(tm)". In a general sense, I'm not at all sure how it would work. Maybe in your specific case, with your requirements -- perhaps.

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

we have such a device, we use it in out queueing software. You would use it by using a message queue to process messages, instead of a database table.

If you really do have a queue there -- skipping the locked rows would probably actually "work" if the messages don't need be processed in order.

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

-- 
Thomas Kyte
Oracle Public Sector
http://asktom.oracle.com/
opinions are my own and may not reflect those of Oracle Corporation