[jira] [Commented] (DERBY-6491) SELECT statements incorrectly require USAGE privilege on column types

["ASF subversion and git services (JIRA)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/DERBY-6491?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13916170#comment-13916170 ] 

ASF subversion and git services commented on DERBY-6491:
--------------------------------------------------------

Commit 1573030 from [~rhillegas] in branch 'code/trunk'
[ https://svn.apache.org/r1573030 ]

DERBY-6491: Don't require USAGE priv on the datatypes of SELECTed columns; commit derby-6491-ab-stopRequiringUsagePriv.diff.

> 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.1.5#6160)