What is the easiest way to make a column READONLY in Oracle?

Question

We have one of those weird cryptic data corruption bugs that pops up every few weeks and no one knows why. So far, it appears that the primary key on a table is spontaneously changing, so other rows that point to it are now messed up.

Though I'm still looking for the root cause of this (it's impossible to repro), I would like some sort of temporary hack to prevent a column value from ever changing. Here's the table schema:

CREATE TABLE TPM_INITIATIVES  ( 
    INITIATIVEID    NUMBER NOT NULL,
    NAME            VARCHAR2(100) NOT NULL,
    ACTIVE          CHAR(1) NULL,
    SORTORDER       NUMBER NULL,
    SHORTNAME       VARCHAR2(100) NULL,
    PROJECTTYPEID   NUMBER NOT NULL,
    CONSTRAINT TPM_INITIATIVES_PK PRIMARY KEY(INITIATIVEID)
    NOT DEFERRABLE
     VALIDATE
)

We of course need to be able to create new rows, but I want to prevent ANYTHING from changing INITIATIVEID ever, no matter what weird queries are being run.

Some ideas I can think of:

• I'm not really familiar with table permissions on Oracle (I'm more of a Postgres guy), but can't you GRANT or DENY update rights on a certain column to all users? Would this just affect updates, or INSERTS as well? What would be the command the DENY updates to this column?
• Create some sort of trigger that runs on ROW UPDATE. Can we detect if the INITIATIVEID is being changed, and if so, throw an exception or blow up in some way?

At the very least, can we trap and/or log this event to see when it happens and what the query is that causes INITIATIVEID to change?

Thanks!

Answer 1

If there are child tables populated with data that references the INITIATIVEID column, Oracle should automatically make it difficult to change the primary key value by preventing you from creating orphan rows by changing the parent's primary key. So, for example, if there is a child table that has a foreign key constraint to TPM_INITIATIVES and there is a row in this child table with an INITIATIVEID of 17, you won't be able to change the INITIATIVEID of the row in the TPM_INITIAITVES table whose current value is 17. If there is no row in any child table that refers to the particular row in the TPM_INITIATIVES table, you could change the value but, presumably, if there are no relationships, changing the primary key value is unimportant since it can't, by definition, cause a data integrity problem. Of course, you could have code that inserts a new row into TPM_INITIATIVES with a new INITIATIVEID, change all the rows in the child table that refer to the old row to refer to the new row, then modify the old row. But this won't be trapped by any of the proposed solutions.

If your application has defined child tables but not declared the appropriate foreign key constraints, that would be the best way to resolve the problem.

That being said, Arnon's solution of creating a view should work. You'd rename the table, create a view with the same name as the existing table, and (potentially) define an INSTEAD OF trigger on the view that would simply never update the INITIATIVEID column. That shouldn't require changes to other bits of the application.

You could also define a trigger on the table

CREATE TRIGGER trigger_name 
  BEFORE UPDATE ON TPM_INITIATIVES  
  FOR EACH ROW
DECLARE
BEGIN
  IF( :new.initiativeID != :old.initiativeID )
  THEN
    RAISE_APPLICATION_ERROR( -20001, 'Sorry Charlie.  You can''t update the initiativeID column' );
  END IF;
END;

Someone could, of course, disable the trigger and issue an update. But I'm assuming you're not trying to stop an attacker, just a buggy piece of code.

Based on the description of what symptoms you are seeing, however, it would seem to make more sense to log the history of changes to columns in this table so that you can actually determine what is going on rather than guessing and trying to plug holes one-by-one. So, for example, you could do something like this

CREATE TABLE TPM_INITIATIVES_HIST (
   INITIATIVEID    NUMBER NOT NULL,
   NAME            VARCHAR2(100) NOT NULL,
   ACTIVE          CHAR(1) NULL,
   SORTORDER       NUMBER NULL,
   SHORTNAME       VARCHAR2(100) NULL,
   PROJECTTYPEID   NUMBER NOT NULL,
   OPERATIONTYPE   VARCHAR2(1) NOT NULL,
   CHANGEUSERNAME  VARCHAR2(30),
   CHANGEDATE      DATE,
   COMMENT         VARCHAR2(4000)
);

CREATE TRIGGER trigger_name 
  BEFORE INSERT or UPDATE or DELETE ON TPM_INITIATIVES  
  FOR EACH ROW
DECLARE
  l_comment VARCHAR2(4000);
BEGIN
  IF( inserting )
  THEN
    INSERT INTO tpm_initiatives_hist( INITIATIVEID, NAME, ACTIVE, SORTORDER, SHORTNAME, PROJECTTYPEID, 
                                      OPERATIONTYPE, CHANGEUSERNAME, CHANGEDATE )
      VALUES( :new.initiativeID, :new.name, :new.active, :new.sortOrder, :new.shortName, :new.projectTypeID, 
              'I', USER, SYSDATE );
  ELSIF( inserting )
  THEN
    IF( :new.initiativeID != :old.initiativeID )
    THEN
      l_comment := 'Initiative ID changed from ' || :old.initiativeID || ' to ' || :new.initiativeID;
    END IF;
    INSERT INTO tpm_initiatives_hist( INITIATIVEID, NAME, ACTIVE, SORTORDER, SHORTNAME, PROJECTTYPEID, 
                                      OPERATIONTYPE, CHANGEUSERNAME, CHANGEDATE, COMMENT )
      VALUES( :new.initiativeID, :new.name, :new.active, :new.sortOrder, :new.shortName, :new.projectTypeID, 
              'U', USER, SYSDATE, l_comment );
  ELSIF( deleting )
  THEN
    INSERT INTO tpm_initiatives_hist( INITIATIVEID, NAME, ACTIVE, SORTORDER, SHORTNAME, PROJECTTYPEID, 
                                      OPERATIONTYPE, CHANGEUSERNAME, CHANGEDATE )
      VALUES( :old.initiativeID, :old.name, :old.active, :old.sortOrder, :old.shortName, :old.projectTypeID, 
              'D', USER, SYSDATE );
  END IF;
END;

Then you can query TPM_INITIATIVES_HIST to see all the changes that had been made to a particular row over time. So you can see if the primary key values are changing or if someone is just changing the non-key fields. Ideally, you may have additional columns that you can add to the history table to help tracking the changes (i.e. perhaps there is something from V$SESSION that might be useful).

Answer 2

Hide the table behind a view and make the update trigger update everything but the column you want to protect

Answer 3

The second option might be better. If you have a logging table/file, you could try writing a message with as much diagnostic information as you can, every time there's an attempt to change the value.