db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "ASF subversion and git services (JIRA)" <j...@apache.org>
Subject [jira] [Commented] (DERBY-6491) SELECT statements incorrectly require USAGE privilege on column types
Date Fri, 28 Feb 2014 18:48:20 GMT

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

Mime
View raw message