[
https://issues.apache.org/jira/browse/DERBY-6429?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13844297#comment-13844297
]
Rick Hillegas commented on DERBY-6429:
--------------------------------------
Here is a more extensive script, which shows the incorrect permissions behavior
of UPDATEs interacting with generated columns, constraints, and triggers.
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 t2_6429_primary
(
key1 int,
key2 int,
primary key( key1, key2 )
);
create table t1_6429_plain
(
a int primary key,
updateColumn int,
selectColumn int,
privateColumn int
);
create table t1_6429_generated
(
a int primary key,
updateColumn int,
selectColumn int,
privateColumn int,
generatedColumn generated always as ( -updateColumn )
);
create table t1_6429_check
(
a int primary key,
updateColumn int,
selectColumn int,
privateColumn int,
checkColumn int,
check ( updateColumn > checkColumn )
);
create table t1_6429_foreign
(
a int primary key,
updateColumn int,
selectColumn int,
privateColumn int,
foreignColumn int,
foreign key ( updateColumn, foreignColumn ) references t2_6429_primary(
key1, key2 )
);
create table t1_6429_trigger
(
a int primary key,
updateColumn int,
selectColumn int,
privateColumn int,
triggerContentColumn int
);
create procedure addHistoryRow_6429
(
actionString varchar( 20 ),
actionValue int
)
language java parameter style java reads sql data
external name
'org.apache.derbyTesting.functionTests.tests.lang.MergeStatementTest.addHistoryRow';
create trigger t1_6429_upd_before
no cascade before update of updateColumn on t1_6429_trigger
referencing old as old
for each row
call addHistoryRow_6429( 'before', old.triggerContentColumn );
grant update ( updateColumn ) on t1_6429_plain to ruth;
grant select ( selectColumn ) on t1_6429_plain to ruth;
grant update ( updateColumn ) on t1_6429_generated to ruth;
grant select ( selectColumn ) on t1_6429_generated to ruth;
grant update ( updateColumn ) on t1_6429_check to ruth;
grant select ( selectColumn ) on t1_6429_check to ruth;
grant update ( updateColumn ) on t1_6429_foreign to ruth;
grant select ( selectColumn ) on t1_6429_foreign to ruth;
grant update ( updateColumn ) on t1_6429_trigger to ruth;
grant select ( selectColumn ) on t1_6429_trigger to ruth;
grant select ( triggerContentColumn ) on t1_6429_trigger to ruth;
grant execute on procedure addHistoryRow_6429 to ruth;
insert into t2_6429_primary values ( 100, 1 ), ( 17, 1 ), ( 34, 1 );
insert into t1_6429_plain( a, updateColumn, selectColumn, privateColumn )
values ( 1, 100, 1000, 10000 );
insert into t1_6429_generated( a, updateColumn, selectColumn, privateColumn )
values ( 1, 100, 1000, 10000 );
insert into t1_6429_check( a, updateColumn, selectColumn, privateColumn,
checkColumn ) values ( 1, 100, 1000, 10000, -1000 );
insert into t1_6429_foreign( a, updateColumn, selectColumn, privateColumn,
foreignColumn ) values ( 1, 100, 1000, 10000, 1 );
insert into t1_6429_trigger( a, updateColumn, selectColumn, privateColumn,
triggerContentColumn ) values ( 1, 100, 1000, 10000, 1 );
connect 'jdbc:derby:memory:db;user=ruth;password=ruthpassword' as ruth;
--
-- plain
--
-- correct. succeeds.
update test_dbo.t1_6429_plain set updateColumn = 17;
-- incorrect.
-- the error message incorrectly states that the missing privilege
-- is UPDATE privilege on privateColumn
update test_dbo.t1_6429_plain set updateColumn = privateColumn;
-- incorrect.
-- 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_6429_plain set updateColumn = selectColumn;
-- incorrect. should not succeed. ruth does not have SELECT privilege on
updateColumn
update test_dbo.t1_6429_plain set updateColumn = 2 * updateColumn;
--
-- generated
--
-- incorrect.
-- sometimes fails because Derby wants UPDATE priv for generatedColumn.
-- sometimes fails because Derby wants SELECT permission on updateColumn.
update test_dbo.t1_6429_generated set updateColumn = 17;
-- incorrect.
-- sometimes fails because Derby wants UPDATE priv for generatedColumn.
-- sometimes fails because Derby wants SELECT permission on updateColumn.
update test_dbo.t1_6429_generated set updateColumn = privateColumn;
-- incorrect.
-- fails because ruth does not have SELECT permission on updateColumn
update test_dbo.t1_6429_generated set updateColumn = selectColumn;
-- incorrect. should fail because ruth does not have SELECT privilege on
updateColumn
update test_dbo.t1_6429_generated set updateColumn = 2 * updateColumn;
--
-- check
--
-- incorrect.
-- fails because ruth does not have UPDATE permission on checkColumn
update test_dbo.t1_6429_check set updateColumn = 17;
-- incorrect.
-- fails because ruth does not have UPDATE permission on privateColumn
update test_dbo.t1_6429_check set updateColumn = privateColumn;
-- incorrect.
-- fails because ruth does not have UPDATE permission on selectColumn
update test_dbo.t1_6429_check set updateColumn = selectColumn;
--
-- incorrect.
-- fails because ruth does not have UPDATE permission on checkColumn
update test_dbo.t1_6429_check set updateColumn = 2 * updateColumn;
--
-- foreign
--
-- incorrect.
-- fails because ruth does not have UPDATE permission on foreignColumn
update test_dbo.t1_6429_foreign set updateColumn = 17;
-- incorrect.
-- fails because ruth does not have UPDATE permission on privateColumn
update test_dbo.t1_6429_foreign set updateColumn = privateColumn;
-- incorrect.
-- fails because ruth does not have UPDATE permission on selectColumn
update test_dbo.t1_6429_foreign set updateColumn = selectColumn;
-- incorrect.
-- fails because ruth does not have UPDATE permission on foreignColumn
update test_dbo.t1_6429_foreign set updateColumn = 2 * updateColumn;
--
-- trigger
--
-- incorrect.
-- fails because ruth does not have UPDATE permission on triggerContentColumn
update test_dbo.t1_6429_trigger set updateColumn = 17;
-- incorrect.
-- fails because ruth does not have UPDATE permission on privateColumn
update test_dbo.t1_6429_trigger set updateColumn = privateColumn;
-- incorrect.
-- fails because ruth does not have UPDATE permission on selectColumn
update test_dbo.t1_6429_trigger set updateColumn = selectColumn;
-- incorrect.
-- fails because ruth does not have UPDATE permission on triggerContentColumn
update test_dbo.t1_6429_trigger set updateColumn = 2 * updateColumn;
set connection dbo;
select * from t1_6429_plain order by a;
select * from t1_6429_generated order by a;
select * from t1_6429_check order by a;
select * from t1_6429_foreign order by a;
select * from t1_6429_trigger order by a;
> Privilege checks for UPDATE statements are wrong.
> -------------------------------------------------
>
> Key: DERBY-6429
> URL: https://issues.apache.org/jira/browse/DERBY-6429
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.11.0.0
> Reporter: Rick Hillegas
>
> 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;
--
This message was sent by Atlassian JIRA
(v6.1.4#6159)
