在Oracle中将列设为READONLY的最简单方法是什么? [英] What is the easiest way to make a column READONLY in Oracle?

问题描述

我们有一些奇怪的，隐秘的数据损坏错误，每隔几周就会弹出一次，没人知道为什么.到目前为止，表上的主键似乎是自发更改的，因此指向该表的其他行现在被弄乱了.

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
)

我们当然需要能够创建新行，但是无论运行什么奇怪的查询，我都想防止任何更改INITIATIVEID.

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.

我能想到的一些想法:

• 我对Oracle的表权限不是很熟悉(我更了解 的人)，但是您不能授予或拒绝更新某人的权利 所有用户的特定列?这只会影响更新，还是 也要插入? DENY将更新此列的命令是什么?
• 创建某种在ROW UPDATE上运行的触发器.我们可以吗 检测INITIATIVEID是否被更改，如果是，则抛出一个 异常还是以某种方式炸毁?

• 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?

至少，我们是否可以捕获和/或记录此事件以查看它何时发生以及导致INITIATIVEID更改的查询是什么?

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?

谢谢！

推荐答案

如果子表中填充了引用INITIATIVEID列的数据，Oracle应该通过阻止您创建来自动使更改主键值变得困难通过更改父级的主键来孤立行.因此，例如，如果有一个子表的外键约束为TPM_INITIATIVES，并且该子表中有一行的INITIATIVEID为17，则您将无法更改INITIATIVEID TPM_INITIAITVES表中当前值为17的行中的行.如果在任何子表中没有任何引用TPM_INITIATIVES表中特定行的行，则可以更改该值，但是假设没有关系，更改主键值并不重要，因为根据定义，它不会引起数据完整性问题.当然，您可能具有将新行插入到TPM_INITIATIVES中并带有新INITIATIVEID的代码，将子表中引用旧行的所有行更改为引用新行，然后修改旧行.但这不会被任何提议的解决方案所困.

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.

话虽这么说，但Arnon创建视图的解决方案应该行得通.您将重命名该表，创建一个与现有表同名的视图，并(可能)在该视图上定义一个INSTEAD OF触发器，该触发器将永远不会更新INITIATIVEID列.不需要更改应用程序的其他位.

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;

然后，您可以查询TPM_INITIATIVES_HIST，以查看随着时间的推移对特定行所做的所有更改.因此，您可以查看主键值是否正在更改，或者有人只是在更改非键字段.理想情况下，您可以将其他列添加到历史记录表中，以帮助跟踪更改(例如，V$SESSION中可能有用的内容).

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

这篇关于在Oracle中将列设为READONLY的最简单方法是什么?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！