Re: Oracle security vulnerability, nuisance, or paranoia?

"Dave" <x_at_x.com> wrote in message
news:49EEd.8048$GG1.960_at_text.news.blueyonder.co.uk...
>
> <casey.kirkpatrick_at_gmail.com> wrote in message
> news:1105398303.897432.205180_at_c13g2000cwb.googlegroups.com...
>> We have an application with a staging table with a simple rule:
>> external systems may only insert onto and select from the table, but
>> may not update or delete records. We recently noticed that one of our
>> internal processes, which processes and updates values in the staging
>> table, was encountering some lock contention issues. We ultimately
>> determined that the contention problem was with some external systems
>> which had coded a cursor with a FOR UPDATE clause (however, it was only
>> being used to view the data). The FOR UPDATE clause was a historic
>> remnant from a time when the systems *could* update the table. Now,
>> the cursor is simply being used to view the table data, but it still
>> has the *FOR UPDATE* code. The problem: our table (and our ability to
>> process its records) is at the mercy of these external systems, which
>> can freely lock and unlock the rows w/out UPDATE access.
>>
>> My question: isn't this a bit of a security flaw that a user who does
>> *not* have UPDATE access to a table, and should *ONLY* be able to
>> SELECT from the table, can still open a *FOR UPDATE* cursor against
>> that table, and thus obtain exclusive locks on the table's rows?
>>
>> Should this be reported to Metalink, or am I being overly paranoid
>> about this being an DOS vulnerability?
>>
>> Oh, by the way, I am seeing this in Oracle 8.1.7 - does anyone know if
>> this exists for 9i and beyond?
>>
> back up a sec, readers to not block supposed( in your case) writers in
> oracle
>
> even if they have got it locked with for update, it wont stop others from
> reading it.
>
> e.g.
>
> session 1
>
> SQL*Plus: Release 10.1.0.2.0 - Production on Mon Jan 10 23:18:50 2005
>
> Copyright (c) 1982, 2004, Oracle. All rights reserved.
>
>
> Connected to:
> Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
> With the Partitioning, OLAP and Data Mining options
>
> SQL> create table t1 (dummy number);
>
> Table created.
>
> SQL> insert into t1 values (1)
> SQL> /
>
> 1 row created.
>
> SQL> commit;
>
> Commit complete.
>
> SQL> select * from t1 for update;
>
> DUMMY
> ----------
> 1
>
> SQL>
>
> now a new session (with this one still open)
>
> Connected to:
> Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
> With the Partitioning, OLAP and Data Mining options
>
> SQL> select * from t1;
>
> DUMMY
> ----------
> 1
>
> SQL>
>
> You have different issues me thinks
>
>
>
>

just realised what I did - many of them doing for updates will cause a problem - only one would be ok
sorry about that Received on Mon Jan 10 2005 - 18:22:46 CST