Column Level and Legacy Application Values

Question

I received a request to implement privileges at the column level, for example:

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

But I found that client applications (in Delphi + ODAC) always emit SQL updates, for example:

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

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

Is there an even more elegant solution?

Edit: I forgot to mention that my Delphi sources have a significant number of hard-coded insert / update requests. ODAC cannot help in this case.

Answer 1

You can create a view and an INSTEAD OF UPDATE trigger in this 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 has not changed, you will have to write several UPDATE :

 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 effective.

In Oracle , UPDATE is executed even if the actual column value does not change. This means that the line is blocked, starts fire, etc.

Answer 2

I do not know about the components or the ODAC library, but can you set any property, for example: update only : changed fields or all fields ?

It seems like such a waste of time includes all columns, even if they are not changed. I think most client libraries offer this option.

Of course, if you set any SQL property for the sn TQuery-like component, you must create the sql statement yourself (also based on only the modified columns).