Michel Cadot wrote:
> "Howard J. Rogers" <hjr_at_dizwell.com> a écrit dans le message de
> news:41759464$0$20127$afc38c87_at_news.optusnet.com.au...
>
>>Asif Merchant wrote:
>>
>>
>>>SQL> create table t2 ( c1 int,c2 varchar2(10), constraint pk_t1
>>>primary key(c1)
>>> 2 deferrable initially immediate )
>>> 3 /
>>>
>>>Table created.
>>>
>>>SQL> insert into t2 values (1, 'A');
>>>
>>>1 row created.
>>>
>>>SQL> commit;
>>>
>>>Commit complete.
>>>
>>>SQL> alter table t2 modify constraint pk_t1 disable validate;
>>>
>>>SQL> insert into t2 values (1,'B');
>>>insert into t2 values (1,'B')
>>>*
>>>ERROR at line 1:
>>>ORA-25128: No insert/update/delete on table with constraint
>>>(TESTUSER123.PK_T1) disabled and validated
>>>
>>>SQL> select * from t2;
>>>
>>> C1 C2
>>>---------- ----------
>>> 1 A
>>
>>
>>Yes, someone else has already suggested this today -and as I pointed out,
>>it's risky because it will affect execution plans, and be a pain to manage
>>long-term.
>>
>>This thread is certainly interesting, but it's getting a bit dangerous if
>>you ask me. What you have done here is not to make a table read-only, but
>>to employ a perfectly legitimate technique to achieve utterly unreliable
>>ends.
>>
>>A constraint can be re-enabled at any time, and that would not be easy to
>>spot being done. The data in the table is thus rendered immediately
>>suspect.
>>
>>In other words: consider why someone would want to make their table
>>read-only. Perhaps it is for audit reasons. Perhaps it is for historic
>>archiving reasons. Perhaps it is for legal reasons. Perhaps it is because
>>someone doesn't want/need to keep backing a table up night after night.
>>
>>All of those possible reasons would be seriously compromised by this DISABLE
>>VALIDATE technique, because there is no guarantee that the constraint stays
>>disabled.
>>
>>Compare that to doing the "official" thing, which is to move the table into
>>read-only tablespace (and potentially then to move the data files involved
>>onto some sort of read-only media). That tablespace cannot be made
>>read-write again except by issuing a command which is recorded in the alert
>>log -which means attempts to compromise the table's data integrity are
>>highly visible. (And if you've moved the data files onto read-only media,
>>even the attempt to make it read-write would fail). The only other way to
>>do it would be to move the table into a new read-write tablespace -and that
>>(assuming for a moment the table is quite large) would not again be a
>>trivial operation that could sneak under the radar.
>>
>>So this suggestion is certainly inventive. As is the one about disabling the
>>table lock. But they are all reversible with near-invisible commands (or
>>require extra measures already mentioned in this thread, such as before DDL
>>triggers, to make them 'stick'... and the dropping or invalidating of a
>>trigger is a trivial and near-invisible operation, too, to a determined
>>hacker).
>>
>>So, if security and assurance and auditability and simplicity are what the
>>original poster was after, then I still think that the move to a read-only
>>tablespace is the only practical and safe answer. The disabling of the
>>table lock was indeed neat, however! A shame it is so readily (and
>>sneakily) reversible.
>>
>>Regards
>>HJR
>>
>
>
> As using read only tablespace is an excluded option for the OP, we can go
> deeply in the other solutions.
> We can avoid the re-enabling of the constraint or locks with a
> "before alter" trigger (neglecting all your warnings on these solutions).
>
> Another solution may be to create a read only snapshot with no refresh
> and use it instead of the table and protect the snapshot with a "before alter"
> trigger.
Or a read-only view and not give access to the original table at all.
--
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace 'x' with 'u' to respond)
Received on Wed Oct 20 2004 - 01:35:19 CDT
