Re: Oracle security vulnerability, nuisance, or paranoia?

casey.kirkpatrick_at_gmail.com wrote:

> There is a code demo below, for those who'd prefer to skip the
> dialogue.
>
> I will be the first to admit that the application design sucks. I'm
> new to the group, and not the person who suggest an "interface" (note
> the quotation marks) with external applications consisting of a
> database link to a table into which the the external apps insert their
> data.
>
> Sadly, my management has already shot down my proposal to do away with
> the staging table "interface" (introducing a called procedure in its
> place). My secret theory is that my managers "fear" the idea of asking
> the developers of the external apps to change their code.
>
> But I digress... The problem is that there are about 20 applications
> that I *only* want to be able to read and insert into this table. Some
> number of these external applications are doing reads, using "FOR
> UPDATE" cursors, and then taking lunch breaks before COMMITTING.
>
> I strongly feel that a user with read access to a table *should not* be
> able to block an application with write access to that table from
> acquiring a write lock. Am I the only one here who thinks an exception
> should be thrown if a user without UPDATE access to a table attempts to
> open a cursor against that table... FOR UPDATE? (seriously, read that
> last sentence again, and tell me this is a feature).
>
> Code sample
> --- AS USER1, SESSION 1
> CREATE TABLE T1 AS SELECT DUMMY FROM DUAL
> /
> GRANT SELECT ON T1 TO USER2
> /
>
>
> -- AS USER2, SESSION 2
> BEGIN
> FOR J IN (SELECT * FROM T1 FOR UPDATE) LOOP
> DBMS_LOCK.SLEEP(180); -- LUNCHBREAK
> END LOOP;
> END;
> /
>
> -- AS USER1, SESSION 1, DURING USER2'S LUNCHBREAK
> UPDATE T1 SET DUMMY = 'Y' -- <This update is blocked 180 seconds
> /
> COMMIT
> /

Reality is pretty straight forward. Tell your management they can either change the code or be patient. I'd suggest you find a new job that gives you longer lunchbreaks too.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace 'x' with 'u' to respond)


----== Posted via Newsfeeds.Com - Unlimited-Uncensored-Secure Usenet News==----
http://www.newsfeeds.com The #1 Newsgroup Service in the World! >100,000 Newsgroups
---= East/West-Coast Server Farms - Total Privacy via Encryption =---