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.

Thanks! But this is not well maintainable. What if customer later decides he wants a new role that will include column2, for example? And then another one? Will I have to update the code every time?
–
JurajJan 26 '10 at 15:50

@Juraj, you missed Quassnoi's point. His view selects * (all columns) from the table. The trigger only updates the columns that have actually changed (albeit one column at a time, which is very inefficient, but the only practical way to solve your problem). You only need to update the code if the structure of the underlying table changes, not if you just change some role grants.
–
Jeffrey KempJan 27 '10 at 0:20

Of course, if user A doesn't have update privilege on a column, but tries to change it anyway, they'll still get ORA-01031 - which is a good thing.
–
Jeffrey KempJan 27 '10 at 0:21

Another approach would be to construct the UPDATE statement using dynamic SQL - this would have the advantage of only requiring a single UPDATE statement, but might increase the usage of the shared pool depending on how many different combinations of columns are updated.
–
Jeffrey KempJan 27 '10 at 0:23

Now I see the point. Constructing the surrogate SQL statement with only changed columns and passing it into EXECUTE IMMEDIATE seems doable. Server has enough unused SGA memory and CPU power left so it shouldn't be a problem.
–
JurajJan 27 '10 at 11:24