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