Re: Read Only Tables

  • From: Mark Brinsmead <mark.brinsmead@xxxxxxx>
  • To: adar76@xxxxxxxxxxxx
  • Date: Wed, 18 Jan 2006 18:05:24 -0700

Yeah, a privileged user can drop the constraint, but not accidentally. A "privileged" user can drop the table, too.

The idea here (I would think) is to lock a table for a while for maintenance or other purposes. During this period, you would want to be sure that nobody "forgets" about your change freeze and updates the table when they shouldn't.

The technique of using a disabled constraint to effectively make a table read-only is not entirely new. I definitely recall reading of this technique in a book published at least a few years ago. The idea of using a CHECK constraint created specifically for this purpose, however, may well be a novel refinement. It's certainly clever -- the sort of thing that is completely obvious once you see it, but chances are you might not think of yourself. Hmmm. I wonder if anybody has filed a patent on this idea. I have little doubt that the U.S. patent office (and others) would actually award one... ;-)

Certainly, this technique has limits.  But I'm sure it has its uses, too...

-- Mark.

Yechiel Adar wrote:

Hello Jared

Privileged person can probably also delete the check constraint.

Adar Yechiel
Rechovot, Israel

Jared Still wrote:

On 1/17/06, *Alex Gorbachev* <gorbyx@xxxxxxxxx <mailto:gorbyx@xxxxxxxxx>> wrote:

    BOf course, some privileged
    users can still modify the table.

There you have stated the reason.

Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist



Other related posts: