Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: <derby-dev@db.apache.org>
Date: Wed, 11 Dec 2013 13:23:07 +0000 (UTC)
From: "Rick Hillegas (JIRA)" <jira@apache.org>
To: derby-dev@db.apache.org
Message-ID: <JIRA.12683643.1386611369873.17281.1386768187473@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=13845382#comment-13845382 ] 

Rick Hillegas commented on DERBY-6429:
--------------------------------------

Thanks for the feedback, Dag and Mamta. Dag, I will respond to your comments later.

Hi Mamta,

I'm sorry about that confusing wording. I was trying to say the following:

Given the following definition...

create table fred.t
(
    a int,
    b int,
    c generated always as ( a + b )
);

...RUTH can issue the following UDPATE...

update fred.t set a = 1;

...provided that she enjoys the following GRANT...

grant update ( a ) on fred.t to ruth;

...and she does not need the following additional GRANTs...

grant select ( b ) on fred.t to ruth;
grant update ( c ) on fred.t to ruth;

Hope that's less confusing.

Thanks,
-Rick


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