sql - 在 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正在被改变，如果是这样，抛出一个
异常(exception)或以某种方式爆炸？

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

谢谢!

Answer 1

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

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

话虽如此，Arnon 创建 View 的解决方案应该有效。您将重命名表，创建一个与现有表同名的 View ，并(可能)在 View 上定义一个 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 的某些内容可能有用)。