[jira] [Reopened] (DERBY-6434) Incorrect privileges may be required for INSERT and DELETE statements.

["Rick Hillegas (JIRA)" <ji...@apache.org>]

     [ https://issues.apache.org/jira/browse/DERBY-6434?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Rick Hillegas reopened DERBY-6434:
----------------------------------


The work on DERBY-6491 has uncovered another case where INSERTs are requiring overbroad privileges. USAGE privilege is incorrectly required on the user-defined type of a target table column. 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 type SourceValueType_045 external name 'java.util.HashMap' language java;
create type TargetValueType_045 external name 'java.util.HashMap' language java;

create function sourceValueExtractor_045( hashMap SourceValueType_045, hashKey varchar( 32672 ) ) returns int
language java parameter style java deterministic no sql
external name 'org.apache.derbyTesting.functionTests.tests.lang.UDTTest.getIntValue';

create function sourceValueMaker_045( hashKey varchar( 32672 ), hashValue int ) returns SourceValueType_045
language java parameter style java deterministic no sql
external name 'org.apache.derbyTesting.functionTests.tests.lang.UDTTest.makeHashMap';

create function targetValueMaker_045( hashKey varchar( 32672 ), hashValue int ) returns TargetValueType_045
language java parameter style java deterministic no sql
external name 'org.apache.derbyTesting.functionTests.tests.lang.UDTTest.makeHashMap';

create table targetTable_045( a TargetValueType_045 );
create table sourceTable_045( b SourceValueType_045 );

grant insert on targetTable_045 to ruth;
grant execute on function sourceValueExtractor_045 to ruth;
grant execute on function sourceValueMaker_045 to ruth;
grant execute on function targetValueMaker_045 to ruth;
grant select on sourceTable_045 to ruth;


connect 'jdbc:derby:memory:db;user=ruth;password=ruthpassword' as ruth;

-- by themselves, the select and function calls don't require any type privileges
select test_dbo.targetValueMaker_045( 'bar', test_dbo.sourceValueExtractor_045( b, 'foo' ) )
from test_dbo.sourceTable_045;

-- but this insert incorrectly fails because ruth doesn't have USAGE privilege on TargetValueType_045;
insert into test_dbo.targetTable_045
  select test_dbo.targetValueMaker_045( 'bar', test_dbo.sourceValueExtractor_045( b, 'foo' ) )
  from test_dbo.sourceTable_045;


set connection dbo;

grant usage on type TargetValueType_045 to ruth;

set connection ruth;

-- now the insert works
insert into test_dbo.targetTable_045
  select test_dbo.targetValueMaker_045( 'bar', test_dbo.sourceValueExtractor_045( b, 'foo' ) )
  from test_dbo.sourceTable_045;
{noformat}


> Incorrect privileges may be required for INSERT and DELETE statements.
> ----------------------------------------------------------------------
>
>                 Key: DERBY-6434
>                 URL: https://issues.apache.org/jira/browse/DERBY-6434
>             Project: Derby
>          Issue Type: Bug
>          Components: SQL
>    Affects Versions: 10.11.0.0
>            Reporter: Rick Hillegas
>            Assignee: Rick Hillegas
>              Labels: derby_backport_reject_10_10
>             Fix For: 10.11.0.0
>
>         Attachments: derby-6434-01-aa-correctInsertPrivs.diff, derby-6434-01-ac-correctInsertPrivs.diff, derby-6434-02-ac-correctDeletePrivs.diff, derby-6434-03-ac-testCaseForDerby-6432.diff, derby-6434-04-aa-dontNeedPrivOnGeneratedColumnTypeForInsert.diff, releaseNote.html
>
>
> This issue is a place to address problems with INSERT and DELETE statements similar to the problems affecting UPDATE statements recorded on DERBY-6429. In particular, DERBY-6432 and DERBY-6433 list some of the problems with INSERT statements.



--
This message was sent by Atlassian JIRA
(v6.2#6252)