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