Re: Make table read-only

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 Tue Oct 19 2004 - 17:25:43 CDT