db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Rick Hillegas (JIRA)" <j...@apache.org>
Subject [jira] [Updated] (DERBY-6429) Privilege checks for UPDATE statements are wrong.
Date Mon, 23 Dec 2013 15:01:03 GMT

     [ https://issues.apache.org/jira/browse/DERBY-6429?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]

Rick Hillegas updated DERBY-6429:
---------------------------------

    Attachment: derby-6429-01-ae-privilegeFilters.diff

Attaching derby-6429-01-ae-privilegeFilters.diff. This patch fixes the bug Knut tripped across.

The problem was that a ColumnReference was being cloned and the tags from the original ColumnReference
weren't propagated to the clone. I have added a copyTagsFrom() call to ColumnReference.copyFields().
That fixes this bug. I added a regression test case for this problem to GrantRevokeDDLTest.

Let me know if you can think of other instances of this bug. The only other parallel to this
bug which I found by code inspection was the cloning of ResultColumns. I added a copyTagsFrom()
call to ResultColumn.cloneMe().

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
>            Assignee: Rick Hillegas
>         Attachments: derby-6429-01-ab-privilegeFilters.diff, derby-6429-01-ac-privilegeFilters.diff,
derby-6429-01-ad-privilegeFilters.diff, derby-6429-01-ae-privilegeFilters.diff
>
>
> 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.5#6160)

Mime
View raw message