db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Dag H. Wanvik (JIRA)" <j...@apache.org>
Subject [jira] [Commented] (DERBY-6429) Privilege checks for UPDATE statements are wrong.
Date Tue, 10 Dec 2013 21:16:07 GMT

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

Dag H. Wanvik commented on DERBY-6429:
--------------------------------------

> -----------------------------------------
> 
> 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. 

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

> 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. 

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)...

> 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.

Makes sense to me.

> 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 thes functions invoked by the generation
> clause if the generation clause is run as a consequence of UPDATing a
> column.

Hmm.. this doesn't seem right to me. I would have thought the
definer/owner of the table containing the generation clause would
carry that burden alone. In section 10.4 6 SR 6 a), the wording is 

"If RI is contained in an <SQL schema statement>, then an SQL-invoked
routine R is an executable routine if and only if R is a possibly
candidate routine and the applicable privileges for the <authorization
identifier> that owns the containing schema include EXECUTE on R."

A create table statement is a SQL schema statement, so I think the
above will apply?


> 
> 
> -----------------------------------------
> 
> 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.

Again, makes sense.

> 
> 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.

Same objection as above for generation clause.



> -----------------------------------------
> 
> 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.

Again, I'd expect a revoke to barf in this case unless we have drop
cascading...

> 
> 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.

Yes, that is my understanding, too.


> 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