[
https://issues.apache.org/jira/browse/DERBY-6429?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13846484#comment-13846484
]
Rick Hillegas commented on DERBY-6429:
--------------------------------------
In offline conversations with Dag, Knut, Dyre, and Kim, I have come to the
conclusion that the authorization context stack (described in the 2011 SQL
Standard, part 2, section 4.28.3 (Execution of SQL-invoked routines)) refers to
the pushing of a new authorization context AFTER you enter a routine and start
executing nested SQL statements inside it. I cannot find any language in the
Standard suggesting that the user who issues an INSERT/UPDATE/MERGE statement
must have privilege to run routines invoked by CHECK constraints or generated
columns. Those routines execute under the aegis of the table owner. So I agree
with Dag that we should strike the privilege restrictions mentioned in C2 and
G2 above.
I also think we should strike claim T2 above. The SQL Standard explicitly
states that triggered actions execute under the aegis of the trigger owner.
Here is the relevant language from the 2011 SQL Standard, part 2, section
4.39.1 (General description of triggers):
"A triggered action is always executed under the authorization of the owner of
the schema that includes the trigger."
Much of this is asserted by the Developer's Guide section titled "Privileges on
views, triggers, and constraints":
http://db.apache.org/derby/docs/10.10/devguide/cdevcsecureprivileges.html. That
section should be expanded to include generated columns as expressions which
execute under the aegis of the table owner.
> 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)
