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 Wed, 11 Dec 2013 14:19:08 GMT

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

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

Thanks again for the feedback, Dag. Let me continue the discussion of privileges related to
foreign keys.

-----------------------------

>> "have" meaning have a privilege granted (to user of PUBLIC), or
>> granted to the current role, right?

Part 2, section 11.8 (referential constraint definition), access rule 1 is all I have to go
on:

"The applicable privileges for the owner of T shall include REFERENCES for each referenced
column."

Note that the Standard is talking about the privileges of the owner of T, not the privileges
of the person issuing the DDL. So consider the following statements issued by the DBO:

create table alice.primaryTable
(
    a int primary key
);

grant references ( a ) on table alice.primary to ref_role;

grant ref_role to fred;

Now suppose that the DBO issues the following statement:

create table fred.foreignTable
(
    b int references alice.primaryTable( a )
);

I don't think that FRED has a meaningful default role here which can be used to supplement
the privileges needed for this statement. I think that the CREATE TABLE statement should fail.
The following GRANT is needed to make it succeed:

grant references ( a ) on table alice.primary to fred;

-----------------------------

>> Hmm.. this seems wrong to me. I'd expect this dependency to be
>> tracked, and Derby to barf (with RESTRICT semantics by default) when
>> that privilege was revoked (or cascades as the case might be)...

Yes, I agree with you. The following statement will fail...

revoke references ( a ) on table alice.primary from fred restrict;

...if the foreign key was created. The following statement will drop the foreign key...

revoke references ( a ) on table alice.primary from fred cascade;

That, at least, is my reading of the extensive general rules in part 2, section 12.7 (revoke
statement).

Note that all of this is orthogonal to the privileges required for an UPDATE of fred.foreignTable.
If there is a foreign key, it imposes no privilege-checking burden on an UPDATE statement.
The UPDATE statement may need to be re-compiled if a foreign key is added or dropped, but
the foreign key does not add any privilege checks to the UPDATE statement.

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)

Mime
View raw message