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

我们有一种奇怪的神秘数据损坏错误，每隔几周就会出现一次，但没有人知道为什么。到目前为止，表上的主键似乎正在自发更改，因此指向它的其他行现在变得混乱。

尽管我仍在寻找其根本原因（不可能重现），但我希望采取某种临时措施来防​​止列值发生变化。这是表架构：

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 人员），但是你不能授予或拒绝更新权限 某些列对所有用户？这是否只会影响更新，或者 也插入？拒绝更新此列的命令是什么？
• 创建某种在 ROW UPDATE 上运行的触发器。我们可以吗 检测是否INITIATIVEID正在被改变，如果是这样，抛出一个 异常或以某种方式爆炸？

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

Thanks!

如果有子表填充了引用的数据INITIATIVEID列，Oracle 应该通过阻止您通过更改父项的主键来创建孤立行，从而自动使更改主键值变得困难。因此，例如，如果有一个子表具有外键约束TPM_INITIATIVES这个子表中有一行INITIATIVEID17，您将无法更改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这可能有用）。