[DERBY-6429] Privilege checks for UPDATE statements are wrong.

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 10.11.1.1
• Fix Version/s: 10.11.1.1
• Component/s: SQL
• Labels: derby_backport_reject_10_10
• Urgency: Normal
• Issue & fix info: Release Note Needed, Repro attached
• Bug behavior facts: Deviation from standard, Security
• Assignee: Richard N. Hillegas
• Reporter: Richard N. Hillegas
• Created: 09/Dec/13 17:49
• Updated: 21/Jan/15 00:23
• Resolved: 16/Jan/14 15:50

Description

UPDATE statements confuse SELECT and UPDATE privileges. Consider the following SET clause:

SET updateColumn = selectColumn

According to part 2 of the 2011 edition of the SQL Standard, that SET clause requires the following privileges:

1) UPDATE privilege on updateColumn. Privileges for the left side of a SET clause are described by section 14.14 (update statement: searched), access rule 1b.

2) SELECT privilege on selectColumn. Privileges for the right side of a SET clause are described by section 14.15 (set clause list) and the various productions underneath value expression. In this case, we have a column reference, whose privileges are governed by section 6.7 (column reference), access rule 2.

However, Derby requires the following:

1') UPDATE privilege on both updateColumn and selectColumn

When we address this bug, we should make corresponding changes to the MERGE statement.

The following script shows the current behavior:

connect 'jdbc:derby:memory:db;user=test_dbo;create=true';

call syscs_util.syscs_create_user( 'TEST_DBO', 'test_dbopassword' );
call syscs_util.syscs_create_user( 'RUTH', 'ruthpassword' );

connect 'jdbc:derby:memory:db;shutdown=true';

connect 'jdbc:derby:memory:db;user=test_dbo;password=test_dbopassword' as dbo;

create table t1_025
(
a int primary key,
updateColumn int,
selectColumn int,
privateColumn int
);

grant update ( updateColumn ) on t1_025 to ruth;
grant select ( selectColumn ) on t1_025 to ruth;

insert into t1_025 values ( 1, 100, 1000, 10000 );

connect 'jdbc:derby:memory:db;user=ruth;password=ruthpassword' as ruth;

– correctly succeeds because ruth has UPDATE privilege on updateColumn
update test_dbo.t1_025 set updateColumn = 17;

– the error message incorrectly states that the missing privilege
– is UPDATE privilege on privateColumn
update test_dbo.t1_025 set updateColumn = privateColumn;

– incorrectly fails.
– ruth does have UPDATE privilege on updateColumn
– and SELECT privilege on selectColumn, which should be good enough.
– however, the error message incorrectly states that the missing privilege
– is UPDATE privilege on selectColumn.
update test_dbo.t1_025 set updateColumn = selectColumn;

– incorrectly succeeds even though ruth does not have SELECT privilege on updateColumn
update test_dbo.t1_025 set updateColumn = 2 * updateColumn;

set connection dbo;

select * from t1_025 order by a;

Issue Links

• is related to: DERBY-6431 Update Developer's Guide topic to include generated columns
• is related to: DERBY-6436 Overbroad privileges required when selecting from a view.
• is related to: DERBY-6439 Privileges not checked before scalar subquery is materialized
• is related to: DERBY-6432 INSERT/UPDATE incorrectly require user to have privilege to execute CHECK constraints on the target table.
• is related to: DERBY-6433 INSERT/UPDATE incorrectly require user to have privilege to run generation expressions.
• is related to: DERBY-6434 Incorrect privileges may be required for INSERT and DELETE statements.
• is related to: DERBY-6446 Test ordering problem in GrantRevokeDDLTest
• relates to: DERBY-6414 Incorrect handling when using an UPDATE to SET an identity column to DEFAULT
• relates to: DERBY-3155 Support for SQL:2003 MERGE statement