sql – 在Oracle中使列READONLY的最简单方法是什么？

发布时间：2020-12-12 06:30:10 所属栏目：MsSql教程来源：网络整理

导读：我们有一个奇怪的神秘数据损坏错误,每隔几周弹出一次,没有人知道原因.到目前为止,似乎表上的主键是自发更改的,因此指向它的其他行现在搞砸了. 虽然我仍在寻找这个的根本原因(这是不可能的重复),但我想要某种临时黑客来防止列值不断变化.这是表模式： CREATE T

我们有一个奇怪的神秘数据损坏错误,每隔几周弹出一次,没有人知道原因.到目前为止,似乎表上的主键是自发更改的,因此指向它的其他行现在搞砸了.

虽然我仍在寻找这个的根本原因(这是不可能的重复),但我想要某种临时黑客来防止列值不断变化.这是表模式：

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
)

我们当然需要能够创建新行,但我想阻止ANYTHING永远改变INITIATIVEID,无论运行什么奇怪的查询.

我能想到的一些想法：

>我不太熟悉Oracle的表权限(我更多
一个Postgres家伙),但你不能授予或拒绝更新权利
所有用户都在某栏？这只会影响更新,或者
INSERTS也是？ DENY更新到此列的命令是什么？
>创建一些在ROW UPDATE上运行的触发器.我们可以吗
检测是否正在更改INITIATIVEID,如果是,则抛出一个
异常或以某种方式爆炸？

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

谢谢！

解决方法

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

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

话虽这么说,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,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,CHANGEDATE,COMMENT )
      VALUES( :new.initiativeID,'U',SYSDATE,l_comment );
  ELSIF( deleting )
  THEN
    INSERT INTO tpm_initiatives_hist( INITIATIVEID,CHANGEDATE )
      VALUES( :old.initiativeID,:old.name,:old.active,:old.sortOrder,:old.shortName,:old.projectTypeID,'D',SYSDATE );
  END IF;
END;

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

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!