Howard J. Rogers wrote:
> 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
Reversible yes. But on the positive side by probably less than 1000
people on the planet.
Same holds true for one of my favorite tricks:
INSERT INTO product_user_profile
VALUES
('SQL*Plus', 'PUBLIC', 'SELECT', NULL, NULL, 'DISABLED', NULL, NULL);
Do this at your own risk.
And if you lose your job don't come whining to me. ;-)
--
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace 'x' with 'u' to respond)
Received on Wed Oct 20 2004 - 01:31:56 CDT
