Re: Oracle security vulnerability, nuisance, or paranoia?

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
  /