Re: Make table read-only

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

-- 
Regards
Michel Cadot