列级权限与遗留应用程序

发布于 2024-08-18 20:27:33 字数 473 浏览 8 评论 0原文

我收到一个实现列级权限的请求，例如：

GRANT UPDATE("column1") ON "TABLE" TO ROLE;

但我发现客户端应用程序（在 Delphi+ODAC 中）总是发出 SQL 更新，例如：

update TABLE set column1=:column1,column2=:column2,column3=:column3,...etc
where id_c=:id_c;

是什么导致 Oracle 总是抛出ORA-01031：权限不足，即使仅更改了 column1。显而易见的解决方案是更改客户端应用程序，使其仅针对已更改的列发出 SQL 更新，但这看起来需要大量编码。

还有更优雅的解决方案吗？

编辑：我忘了提及，在我的 Delphi 源代码中有相当多的硬编码插入/更新查询。 ODAC 在这种情况下无法提供帮助。

原文

I got a request to implement Column-level privileges, for example:

GRANT UPDATE("column1") ON "TABLE" TO ROLE;

But I found that client applications ( in Delphi+ODAC ) always emits SQL updates like:

update TABLE set column1=:column1,column2=:column2,column3=:column3,...etc
where id_c=:id_c;

what causes Oracle to always throw ORA-01031: insufficient privileges, even if only column1 was changed. The obvious solution is to change the client application so that it emits SQL updates only with changed columns, but it looks like quite a lot of coding.

Is there any more elegant solution possible?

Edit: I forgot to mention that there is considerable number of hardcoded insert/update queries in my Delphi sources. ODAC cannot help in this case.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

○闲身 2024-08-25 20:27:33

您可以创建一个视图并在该视图上创建一个 INSTEAD OF UPDATE 触发器：

CREATE VIEW myview ON mytable
AS
SELECT  *
FROM    table

CREATE TRIGGER trg_myview_iu
INSTEAD OF UPDATE
ON myview
FOR EACH ROW
BEGIN
        UPDATE  mytable
        SET     column1 = :NEW.column1
        WHERE   id_c = :NEW.id_c;
END;

如果您只想在其值未更改时处理列，那么您必须编写多个 UPDATE< /code> 语句：

CREATE TRIGGER trg_myview_iu
INSTEAD OF UPDATE
ON myview
FOR EACH ROW
BEGIN
        IF :NEW.column1 <> :OLD.column1 THEN -- add `NULL` processing options if necessary
                UPDATE  mytable
                SET     column1 = :NEW.column1
                WHERE   id_c = :NEW.id_c;
        END IF;
        IF :NEW.column2 <> :OLD.column2 THEN
                UPDATE  mytable
                SET     column2 = :NEW.column2
                WHERE   id_c = :NEW.id_c;
        END IF;
        …
END;

但这远非高效。

在 Oracle 中，即使列的实际值没有更改，UPDATE 也会执行。这意味着该行被锁定、触发等。

You can create a view and an INSTEAD OF UPDATE trigger on that view:

CREATE VIEW myview ON mytable
AS
SELECT  *
FROM    table

CREATE TRIGGER trg_myview_iu
INSTEAD OF UPDATE
ON myview
FOR EACH ROW
BEGIN
        UPDATE  mytable
        SET     column1 = :NEW.column1
        WHERE   id_c = :NEW.id_c;
END;

If you want to process a column only if its value had not changed, then you'll have to write several UPDATE statements:

CREATE TRIGGER trg_myview_iu
INSTEAD OF UPDATE
ON myview
FOR EACH ROW
BEGIN
        IF :NEW.column1 <> :OLD.column1 THEN -- add `NULL` processing options if necessary
                UPDATE  mytable
                SET     column1 = :NEW.column1
                WHERE   id_c = :NEW.id_c;
        END IF;
        IF :NEW.column2 <> :OLD.column2 THEN
                UPDATE  mytable
                SET     column2 = :NEW.column2
                WHERE   id_c = :NEW.id_c;
        END IF;
        …
END;

This is far from being efficient, though.

In Oracle, the UPDATE executes even if the actual value of the column does not change. This means that the row gets locked, triggers fire etc.

回复收藏 0 原文