Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Oracle security vulnerability, nuisance, or paranoia?

Re: Oracle security vulnerability, nuisance, or paranoia?

From: Thomas Kyte <tkyte_at_oracle.com>
Date: 11 Jan 2005 05:05:32 -0800
Message-ID: <115448732.00010290.008@drn.newsguy.com>


In article <1105418043.640928.56820_at_c13g2000cwb.googlegroups.com>, casey.kirkpatrick_at_gmail.com says...
>
>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.
>

and what if there were a unique constraint on the table, and the user INSERTS and record with unique_column=1234.

User does not commit.

Others attempting to insert unique_column=1234 will block as well. This is normal lockin' and blockin'. It is what databases do.

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

Other databases call is repeatable read. Heck, in OTHER databases (they who shall remain nameless), a simple query takes shared read locks when the person on the other end of the query wants a correct answer (they have to, if they do not, you will get an inconsistent answer -- a result set that never actually existed -- from these other databases).

Meaning -- all queries block modifications in these other databases.

select for update allows you to get a repeatable read. It is vital for data integrity checks. Consider a foreign key -- you have NO write access at all to the parent table -- NONE (no access). Your modification to the child table locks entries in the parent. Or vice versa, you have access to the parent table (DEPT), you have NO access to the EMP table (with an unindexed foreign key). You delete a row frmo DEPT -- you will lock the EMP table either momentarily (9i and above) or until you commit (8i and before). You lock a table to which you have no access.

All in the name of data integrity.

Suppose this table you have SELECT and INSERT on also has a "business rule" that the average SAL of existing records in that department cannot be more than 10% away from the SAL of the newly inserted record. You need to lock the existing records from modification during the processing of this rule.

> 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).

it is, locking takings place all over the place. did you know they could "lock table"

http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96540/statements_914a.htm#2064407

<quote>
Prerequisites

The table or view must be in your own schema or you must have the LOCK ANY TABLE

system privilege, or you must have any object privilege on the table or view.
                  ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
</quote>

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 table t ( x int );  

Table created.  

ops$tkyte_at_ORA9IR2> insert into t values ( 1 );  

1 row created.  

ops$tkyte_at_ORA9IR2>
ops$tkyte_at_ORA9IR2> create or replace view v   2 as
  3 select x from t order by x;  

View created.  

ops$tkyte_at_ORA9IR2>
ops$tkyte_at_ORA9IR2> grant insert, select on v to a;  

Grant succeeded.  

ops$tkyte_at_ORA9IR2>
ops$tkyte_at_ORA9IR2> create or replace trigger v_trigger instead of insert on v   2 begin
  3 insert into t (x) values (:new.x);   4 end;
  5 /  

Trigger created.  

ops$tkyte_at_ORA9IR2>
ops$tkyte_at_ORA9IR2> @connect a/a
ops$tkyte_at_ORA9IR2> set termout off
a_at_ORA9IR2> set termout on

a_at_ORA9IR2>
a_at_ORA9IR2> select * from ops$tkyte.v for update; select * from ops$tkyte.v for update
                        *

ERROR at line 1:
ORA-02014: cannot select FOR UPDATE from view with DISTINCT, GROUP BY, etc.    

a_at_ORA9IR2>
a_at_ORA9IR2> insert into ops$tkyte.v values ( 1 );  

1 row created.  

a_at_ORA9IR2> insert into ops$tkyte.v select 1 from dual;  

1 row created.

Note: yes, this will impact the performance of INSERTS. yes, this will impact queries as well -- the view is "mergable" (predicates can be pushed into it) but there will be an order by.

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

-- 
Thomas Kyte
Oracle Public Sector
http://asktom.oracle.com/
opinions are my own and may not reflect those of Oracle Corporation
Received on Tue Jan 11 2005 - 07:05:32 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US