Re: Make table read-only

Didnt see it until I finished posting. I understand that it will affect lot of things and why would somebody wanna do this.

Anyways the question was can it be done or no. so yes it can be done.

Regds,
Asif.

"Howard J. Rogers" <hjr_at_dizwell.com> wrote in message 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
Received on Wed Oct 20 2004 - 10:11:30 CDT