I was in Slovenia earlier this week presenting one of the “Celebrity Seminars” for Oracle University. The audience was very good, and had some interesting questions and observations. One of them introduced me to the following oddity that he had discovered with updatable join views.
Given suitable constraints, I can write a query that joins several tables, then turn that query into an inline view and update from one table to another, for example (tested on 10.2.0.3 and 22.214.171.124):
update ( select chi.small_vc_c, gpa.small_vc_gp from test_user.child chi, test_user.parent par, test_user.grandparent gpa where chi.small_num_c between 200 and 215 and par.id_gp = chi.id_gp and par.id = chi.id_p and gpa.id = par.id_gp ) gen set gen.small_vc_c = gen.small_vc_gp ;
For this update to the child table to be legal, I have to be able to guarantee that any row from the table that appears in the inline view cannot logically appear there more than once.
This restriction is, in some ways, obvious: if a single row from the child table could appear twice in the join then the two appearances could correspond to two different rows from the grandparent table, which would leave you trying to decide which grandparent value to use in the update.
Consider for example this data:
create table tabX (x1 number, x2 number); create table tabY (y1 number, y2 number); insert into tabx values(1,1); insert into tabx values(1,99); insert into taby values(1,-1); insert into taby values(1,-99); commit; select y2, x2 from tabX, tabY where tabx.x1 = taby.y1 ; Y2 X2 ---------- ---------- -1 99 -1 1 -99 99 -99 1
Both rows in the tabY table appear twice in the join result (as do both rows from tabX). If I try the following update, which value of x2 should end up overwriting the value for y2 in the underlying table – the 99 that happens to appear first in the join, or the 99 that happens to appear last (bearing in mind, of course, that the order of the output is not guaranteed) ?
update ( select y2, x2 from tabX, tabY where tabx.x1 = taby.y1 ) set y2 = x2 ; ERROR at line 10: ORA-01779: cannot modify a column which maps to a non key-preserved table
The technical term used by Oracle is “key-preservation”. Going back to the original example: if you identify a row in the child table using a unique key then the same key could act as the key to the join view.
For this to work the join from child to parent has to be on a unique key in the parent table, and the join from the parent to grandparent has to be on a unique key of the grandparent.
The updatable join view concept is not commonly known, but there are circumstances where it is the most efficient option for updating from one table to another.
Finally we come to the oddity. You’ll notice that I had included the name of the table owner in my original query – this is because I want to be able to run the update from another schema called u1. So what privileges should test_user grant to u1 ? The obvious requirement would be as follows:
grant select, update (small_vc_c) on child to u1; grant select on parent to u1; grant select on grandparent to u1;
This means I can update the relevant column from the child table, and see the columns from the parent and grandparent tables that I need to do the update.
But when I try the update, I get the following error:
test_user.grandparent gpa * ERROR at line 8: ORA-01031: insufficient privileges
Until I grant further (unexpected) privileges to u1, I can’t execute the update. This is what it takes:
grant update (id, small_vc_gp) on grandparent to u1; grant update (id_gp, id) on parent to u1; grant update (id_gp, id_p, id) on child to u1;
I have to grant update column privileges of the relevant unique keys on all three tables and on the column from the grandparent table that I want to copy from. This means that u1 has to have a level of update privilege that I don’t want it to have.
Of course, I can use non-deferrable referential integrity constraints to ensure that the key columns on the parent and grandparent tables can’t be changed – but I can’t stop u1 from changing the small_vc_gp column on the grandparent table, or the id column of the child table. I can’t think of a good reason why these extra privileges are needed.
There’s an important moral to this story. You should not build a system that allows end-users to connect to the database as the data owner.
But it’s still quite common for development work to take place in an environment that is a little lax about this rule, with developers connecting as the data owner to write code. This could leave you with code that seemed to work in the development environment but suddenly needs a change which grants excess privileges to the end-user before it runs in the production environment.
Remember, it’s important to use a development environment that is a proper match for the production environment.