Re: Make table read-only

"Howard J. Rogers" <hjr_at_dizwell.com> a écrit dans le message de news:4175fa4f$0$20124$afc38c87_at_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
>
>

Just one precision on what i thought about triggers. I didn't say to use them for auditing purpose but to avoid the statement, that is use raise_application_error to abort the statement. But i agree with you: all that (and the read only view from Daniel) don't make a real read-only table but something like a workaround.

Btw, "audit trigger" audits drop trigger (as well as create trigger, alter trigger, alter table enable/disable all triggers).

-- 
Regards
Michel Cadot