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 Tue, 10 Dec 2013 17:46:07 GMT

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

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

Here are my thoughts about how foreign keys, generated columns, check constraints, and triggers
affect the permissions needed to execute an UPDATE statement. Does anyone have a different
opinion?

Thanks,
-Rick

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

Foreign Keys

Permissions for foreign keys are checked when the foreign key is declared. The owner of the
foreign table must have REFERENCES privilege on the referenced columns in the primary key
table. See part 2, section 11.8 (referential constraint definition), access rule 1. No further
permissions checking is required at execution time. I do not see any language indicating that
foreign keys become invalid or un-runnable if the owner of the foreign table later loses REFERENCES
privilege on the referenced columns. I do not see any indication that the user issuing the
UPDATE statement is required to have SELECT privilege on the referenced columns.


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

Generated Columns

There are two kinds of permissions related to generation clauses:

G1) SELECT permission on the columns mentioned in the generation clause. I do not see any
language in the Standard suggesting that you need explicit SELECT permission on all of the
columns mentioned by the generation clause. That permission seems to be implied by the INSERT
privilege you enjoy  on the table and the UPDATE privilege you may enjoy on a column mentioned
by the generation clause.

G2) EXECUTE permission on functions invoked by the generation clause. This is addressed by
part 2, section 4.28.3 (Execution of SQL-invoked routines). The user executing the UPDATE
statement must enjoy EXECUTE permission on the functions invoked by the generation clause
if the generation clause is run as a consequence of UPDATing a column.


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

Check Constraints

Similarly, there are two kinds of permissions related to CHECK constraints:

C1) SELECT permission on the columns mentioned in the CHECK constraint. I do not see any language
in the Standard suggesting that you need explicit SELECT permission on all of the columns
mentioned by the CHECK constraint. That permission seems to be implied by the INSERT privilege
you enjoy  on the table and the UPDATE privilege you may enjoy on a column mentioned by the
CHECK constraint.

C2) EXECUTE permission on functions invoked by the CHECK constraint. This is addressed by
part 2, section 4.28.3 (Execution of SQL-invoked routines). The user executing the UPDATE
statement must enjoy EXECUTE permission on the functions invoked by the CHECK constraint.


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

Triggers

There are two kinds of permissions related to triggers:

T1) The permission to SELECT columns from the source table in order to construct transition
variables and transition tables. These permissions are described by part 2 of the Standard,
section 11.49 (trigger definition), access rule 3. In order to declare a trigger, the trigger
owner must have SELECT privilege on the whole source table if the trigger has transition variables
or tables. It is my understanding that the SELECTion of source columns at runtime happens
under the original aegis of the trigger owner. Moreover, I don't see any language suggesting
that the trigger becomes un-runnable if the trigger owner later loses TRIGGER and/or SELECT
privilege on the source table. 

T2) The permission to execute the trigger action. I can't find any language specifying special
treatment of permissions for the trigger action. I believe that the trigger action runs with
the privileges of the user who invoked the original, triggering INSERT/UPDATE/DELETE.


> 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