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] [Updated] (DERBY-6428) Adding an update trigger to a table causes Derby to require overbroad update privileges
Date Mon, 09 Dec 2013 15:05:08 GMT

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

Mime
View raw message