Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: <derby-dev@db.apache.org>
Date: Tue, 10 Dec 2013 14:23:08 +0000 (UTC)
From: "Rick Hillegas (JIRA)" <jira@apache.org>
To: derby-dev@db.apache.org
Message-ID: <JIRA.12683643.1386611369873.9818.1386685388490@arcas>
In-Reply-To: <JIRA.12683643.1386611369873@arcas>
References: <JIRA.12683643.1386611369873@arcas>
Subject: [jira] [Commented] (DERBY-6429) Privilege checks for UPDATE
 statements are wrong.
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit


    [ 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)