[
https://issues.apache.org/jira/browse/DERBY-6491?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13919727#comment-13919727
]
Rick Hillegas commented on DERBY-6491:
--------------------------------------
Hi Mamta,
Thanks for asking those questions. USAGE privilege is still checked during the
execution of CREATE TABLE. I think this is verified by a test case in UDTTest.
In any event, the following script shows that this privilege check is still
performed:
{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 PrivateType external name 'java.util.HashMap' language java;
connect 'jdbc:derby:memory:db;user=ruth;password=ruthpassword' as ruth;
-- fails because ruth does not have USAGE privilege on PrivateType
create table t( a test_dbo.PrivateType );
{noformat}
Your other question was about why privilege collection needs to be turned off
during optimization. There are two reasons for this:
1) Re-binding can occur during the pre-processing phase. I think this is a
defect but it's one we have to workaround for the moment.
2) I think that I did a bad job when I implemented USAGE collection for
user-defined types. I made the compiler consider adding USAGE privilege every
time a datatype was bound. That results in a lot of over-eager collection of
USAGE privileges. Having read the SQL Standard more closely, I now think that
we could probably limit USAGE collection to two situations:
a) explicit declaration of a UDT type in a CREATE/ALTER statement
b) explict CAST to a UDT type
Cleaning up item 2) may be straightforward. I suspect that item 1) is trickier.
Thanks,
-Rick
> SELECT statements incorrectly require USAGE privilege on column types
> ---------------------------------------------------------------------
>
> Key: DERBY-6491
> URL: https://issues.apache.org/jira/browse/DERBY-6491
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.11.0.0
> Reporter: Rick Hillegas
> Attachments: derby-6491-aa-stopRequiringUsagePriv.diff,
> derby-6491-ab-stopRequiringUsagePriv.diff
>
>
> A Derby SELECT requires that the user enjoy USAGE privilege on the types of
> all columns in the tables being read. This even includes USAGE privilege on
> the types of columns which are not being read. The latter privilege certainly
> seems overbroad. But I don't think that USAGE privilege should even be
> required on the types of columns being SELECTed. I can't find any language in
> the SQL Standard requiring this.
> This interpretation is in line with the general principle that the user who
> creates a table must enjoy privilege to use and execute functions and types
> mentioned in the table definition, but other users who access that table only
> need the correct INSERT/UPDATE/DELETE/SELECT privileges and otherwise operate
> under the aegis of the table owner.
> This interpretation of the Standard agrees with the behavior of DB2, as
> described here:
> http://pic.dhe.ibm.com/infocenter/iseries/v7r1m0/index.jsp?topic=%2Fdb2%2Frbafzgntudtp.htm
> I think that Derby also incorrectly requires USAGE privilege on the types of
> arguments/return values of routines when invoking those routines. But that is
> a separate issue which I do not want to address with this JIRA.
> The following script shows the overbroad USAGE requirements of SELECT
> statements:
> {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 SourceUnreferencedType_045 external name 'java.util.HashMap'
> language java;
> create type SourceValueType_045 external name 'java.util.HashMap' language
> java;
> create table sourceTable_045
> (
> sourceUnreferencedColumn SourceUnreferencedType_045,
> sourceValueColumn SourceValueType_045
> );
> grant select( sourceValueColumn ) on sourceTable_045 to ruth;
> connect 'jdbc:derby:memory:db;user=ruth;password=ruthpassword' as ruth;
> -- incorrectly fails because ruth does not have USAGE privilege on
> SourceUnreferencedType_045
> select sourceValueColumn from test_dbo.sourceTable_045;
> set connection dbo;
> grant usage on type SourceUnreferencedType_045 to ruth;
> set connection ruth;
> -- incorrectly fails because ruth does not have USAGE privilege on
> SourceValueType_045
> select sourceValueColumn from test_dbo.sourceTable_045;
> set connection dbo;
> grant usage on type SourceValueType_045 to ruth;
> set connection ruth;
> -- succeeds now that ruth has USAGE privilege on both types
> select sourceValueColumn from test_dbo.sourceTable_045;
> {noformat}
--
This message was sent by Atlassian JIRA
(v6.2#6252)
