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

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

尽管我仍在寻找导致此问题的根本原因(无法进行复制)，但我还是希望通过某种临时手段来防止列值不断变化。这是表模式:

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的表权限不是很熟悉(我更了解
的人)，但您不能授予或拒绝更新某人的权利
所有用户的特定列？这只会影响更新，还是
也要插入？ DENY更新到此列的命令是什么？

创建某种在ROW UPDATE上运行的触发器。我们可以吗
检测INITIATIVEID是否被更改，如果是，则抛出一个
异常(exception)或以某种方式炸毁？

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

谢谢!

最佳答案

如果有子表填充有引用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中可能有一些有用的内容)。

关于sql - 在Oracle中将列设为READONLY的最简单方法是什么？，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/8330884/