Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: <derby-dev@db.apache.org>
Date: Mon, 16 Dec 2013 13:19:07 +0000 (UTC)
From: "Rick Hillegas (JIRA)" <jira@apache.org>
To: derby-dev@db.apache.org
Message-ID: <JIRA.12683643.1386611369873.42575.1387199947964@arcas>
In-Reply-To: <JIRA.12683643.1386611369873@arcas>
References: <JIRA.12683643.1386611369873@arcas>
Subject: [jira] [Comment Edited] (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=13846518#comment-13846518 ] 

Rick Hillegas edited comment on DERBY-6429 at 12/16/13 1:19 PM:
----------------------------------------------------------------

As a result of the discussion over the past couple days, I have come to the conclusion that constraints, generated columns, and triggers do not impose any privilege burdens on INSERT/UPDATE/DELETE/MERGE statements. The privilege burdens related to constraints, generated columns, and triggers are satisfied at DDL time.

As a result, I would revise the privilege requirements for UPDATE statements as follows. Let P be the union of the privileges granted to the current user and the privileges granted to the current role.

1) P must contain UPDATE privilege for all columns on the left side of SET operators.

2) P must contain all privileges needed to evaluate the right side of SET operators and run the WHERE clause. That includes:

a) SELECT privilege on all columns mentioned on the right side of SET operators and by the WHERE clause

b) EXECUTE privilege on all functions invoked on the right side of SET operators and by the WHERE clause

c) USAGE privilege on all types, sequences, and aggregates mentioned on the right side of SET operators and by the WHERE clause.

Other opinions?

Thanks,
-Rick


was (Author: rhillegas):
As a result of the discussion over the past couple days, I have come to the conclusion that constraints, generated columns, and triggers do not impose any privilege burdens on INSERT/UPDATE/DELETE/MERGE statements. The privilege burdens related to constraints, generated columns, and triggers are satisfied at DDL time.

As a result, I would revise the privilege requirements for UPDATE statements as follows. Let P be the union of the privileges granted to the current user and the privileges granted to the current role.

1) P must contain UPDATE privilege for all columns on the left side of SET operators.

2) P must contain SELECT privilege on all columns on the right side of SET operators.

3) P must contain all privileges needed to run the WHERE clause. That includes:

a) SELECT privilege on all columns mentioned by the WHERE clause

b) EXECUTE privilege on all functions invoked by the WHERE clause

c) USAGE privilege on all types, sequences, and aggregates mentioned by the WHERE clause.

Other opinions?

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)