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] [Commented] (DERBY-6429) Privilege checks for UPDATE statements are wrong.
Date Mon, 23 Dec 2013 16:53:55 GMT

    [ https://issues.apache.org/jira/browse/DERBY-6429?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13855751#comment-13855751
] 

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

Thanks for continuing to test-drive this patch, Knut. I am able to reproduce your results.
Looking at the stack traces for both experiments (with and without rows in the table in the
subquery), here is my theory about what is happening:

1) The subquery result is being materialized as a OnceResultSet which runs before the UPDATE
runs.

2) But all of the permissions attached to the statement are checked just before the UpdateResultSet
executes.

3) If the OnceResultSet contains more than one row, then it raises the "Scalar subquery is
only allowed to return a single row" error before the permissions associated with the UpdateResultSet
are checked.

Fixing this may required some thought. The permissions are attached to the CompilerContext
during the bind() phase. They are not attached to individual query blocks. I think that the
decision to materialize the subquery is made during the pre-processing phase of optimization.
By that time, we have lost all memory of which query block the permissions came from.

I would tend to regard this as another bug with privilege management, which deserves its own
JIRA. What are your thoughts?

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