[
https://issues.apache.org/jira/browse/DERBY-6428?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Rick Hillegas updated DERBY-6428:
---------------------------------
Issue & fix info: Repro attached,Workaround attached (was: Repro
attached)
Bug behavior facts: (was: Deviation from standard)
Issue Type: Improvement (was: Bug)
Changing this issue from Bug to Improvement and turning off the Deviation from
Standard flag. This may be a violation of the Standard, but I am having a hard
time finding chapter and verse on the access rules for trigger executions. The
behavior looks inefficient at best to me. At worst it is a security issue
because it requires the granting of overbroad permissions.
There seem to be at least 2 problems with this behavior:
1) UpdateNode.getUpdateReadMap() adds all columns in the table to the select
list if a trigger is fired and the trigger does not have a column list in it. I
think this could be improved by only including those columns which are actually
mentioned by the trigger action.
2) I have not traced where the UPDATE privilege is added but it seems to me
that, at most, SELECT privilege needs to be added for the columns mentioned by
the trigger action. UPDATE privilege should only be needed for the columns on
the left side of SET clauses plus generated columns built out of them.
I am turning on the Workaround flag because you can avoid this behavior by
adding a column list to the trigger definition. For instance, the UPDATE works
with the following trigger definition:
create trigger t1_025_upd_before
no cascade before update of e_update_t1_ruth on t1_025
referencing old as old
for each row
call addHistoryRow_025( 'before', old.e_update_t1_ruth );
> Adding an update trigger to a table causes Derby to require overbroad update
> privileges
> ---------------------------------------------------------------------------------------
>
> Key: DERBY-6428
> URL: https://issues.apache.org/jira/browse/DERBY-6428
> Project: Derby
> Issue Type: Improvement
> Components: SQL
> Affects Versions: 10.11.0.0
> Reporter: Rick Hillegas
>
> If you put a before update row-level trigger on a table, then when you try to
> update the table, Derby requires UPDATE privilege on columns which you aren't
> updating. The following script shows this problem:
> {noformat}
> 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,
> e_update_t1_ruth int
> );
> create procedure addHistoryRow_025
> (
> actionString varchar( 20 ),
> actionValue int
> )
> language java parameter style java reads sql data
> external name
> 'org.apache.derbyTesting.functionTests.tests.lang.MergeStatementTest.addHistoryRow';
> -- if you comment out this trigger definition, then the final UPDATE works
> create trigger t1_025_upd_before
> no cascade before update on t1_025
> referencing old as old
> for each row
> call addHistoryRow_025( 'before', old.e_update_t1_ruth );
> grant update ( e_update_t1_ruth ) on t1_025 to ruth;
> grant execute on procedure addHistoryRow_025 to ruth;
> insert into t1_025 values ( 1, 1 );
> connect 'jdbc:derby:memory:db;user=ruth;password=ruthpassword' as ruth;
> -- fails, saying that ruth doesn't have UPDATE privilege on test_dbo.t1_025.a
> update test_dbo.t1_025 set e_update_t1_ruth = 17;
> set connection dbo;
> drop trigger t1_025_upd_before;
> set connection ruth;
> -- without the trigger, the statement succeeds
> update test_dbo.t1_025 set e_update_t1_ruth = 17;
> {noformat}
--
This message was sent by Atlassian JIRA
(v6.1.4#6159)
