在Oracle中将列设为READONLY的最简单方法是什么？

Question

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

尽管我仍在寻找造成这种情况的根本原因（无法进行复制），但我还是希望通过某种临时手段来防止列值不断变化。这是表模式：

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无论运行什么奇怪的查询，我都想防止任何更改。

我能想到的一些想法：

• 我对Oracle的表权限不是很熟悉（我更像是Postgres的人），但是您不能授予或授予所有用户特定列的更新权限吗？这只会影响更新，也可能影响插入吗？DENY更新到此列的命令是什么？
• 创建某种在ROW UPDATE上运行的触发器。我们可以检测出是否INITIATIVEID正在更改，如果是，则抛出异常或以某种方式炸毁？

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

谢谢！

Answer 1

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

如果您的应用程序已定义子表但未声明适当的外键约束，则这将是解决问题的最佳方法。

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

您还可以在表上定义触发器

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;

当然，有人可以禁用触发器并发布更新。但是我假设您不是要阻止攻击者，而只是要阻止一段错误的代码。

但是，基于对所见症状的描述，将此表中列的更改历史记录在日志中似乎更有意义，这样您才可以实际确定正在发生的事情，而不是猜测和尝试堵塞漏洞。一一
因此，例如，您可以执行以下操作

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内容可能有用）。