Re: Make table read-only

From: Howard J. Rogers <hjr_at_dizwell.com>
Date: Wed, 20 Oct 2004 15:40:36 +1000
Message-Id: <4175fa4f$0$20124$afc38c87@news.optusnet.com.au>

"Michel Cadot" <micadot{at}altern{dot}org> 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.

I'm not suggesting that the other options are not interesting and intriguing. And perhaps they will indeed help the original poster (who has not said that rebuilding to another tablespace is a complete and utter no-no, merely that he was hoping for a quick and inexpensive option that would achieve the same thing. So actually, we don't know whether it is a truly excluded option, or whether he's just looking for a quick fix. That's how I remember his post, anyway).

But that's the point. These other options don't achieve the same thing as making a tablespace read-only, and they therefore might not actually *be* solutions for him.

It all depends on what the reason for making something read-only is, doesn't it? (Which I don't think we know in this particular case). If its for audit or history or legal reasons, and the table must be read-only long term and without any possibility of sneaking in modifications when no-one's looking, then these 'alternatives' just don't pass muster, as I say. The only one that counts in that league is "proper" read-only tablespace. But if the requirement is for something to flip a table into a locked state temporarily, over lunch, whilst the DBA is downing a few glasses, then the alternatives that have been suggested in this entertaining thread are perfectly viable.

> We can avoid the re-enabling of the constraint or locks with a > "before alter" trigger (neglecting all your warnings on these solutions).

But 'drop trigger X' is NOT an audited statement, is it? Of course, you *could* audit that statement, like any other. But then who polices the AUD$ table?

However, alter tablespace read write *is* audited, in the alert log, which can be written to an operating system directory to which the DBA has no access, however powerful he is on the database itself.

That's my point. Anything that uses triggers to achieve "read only" status for a table is fooling management if they need to place reliance on that status. Certainly it works for an hour. Or three. But not forever. So if "forever" is what is needed, by the original poster or anyone else, then triggers can't count. Neither can disabling the table locks, or disable novalidating the constraints.

Again: it comes down to the real business requirement here. Imagine a table which MUST remain read only for seven years. Even a one minute interlude in read-write mode would be disastrous from the legal point of view. So, even if you could guarantee to audit the disabling of the trigger, or the enabling of the constraint, that wouldn't help: finding out about it after the event is too late; the damage has been done, the table has had a read-write interlude. God knows what happened to its data in that one minute. There is only one option that *guarantees* no interludes. Triggers and code and alter table statements don't past that most stringent of tests.

But, I accept that we still don't know regarding the original poster whether that most stringent of tests (or something like it) is what he is actually after.

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

Same deal. It doesn't past legal and auditing stringency standards as a technique.

The other danger with this thread is in its potential for myth-making. If the question is "Can I make a table read-only" the answer is, and must be, No. Because otherwise, a myth will arise that there is a fundamentally reliable way of doing it. But there isn't, except making the data files themselves read-only. And that *requires* the use of a read-only tablespace. Back to square one.

Precision of language, again, I guess. Technically, you cannot make a table read-only. You can only make it *appear* (or behave, if you prefer) read-only for a period of time. Which might be good enough sometimes. But it's not -fundamentally- the same thing.

Are there subtleties to the answer we can provide for those with less stringent requirements? Yes... but we shouldn't say that those make a table read-only. I don't know what we call it, actually. Maybe we should say they are methods of temporarily suspending DML on a table?

Regards
HJR Received on Wed Oct 20 2004 - 00:40:36 CDT