db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Rick Hillegas (JIRA)" <j...@apache.org>
Subject [jira] [Created] (DERBY-6491) SELECT statements incorrectly require USAGE privilege on column types
Date Fri, 28 Feb 2014 15:24:19 GMT
Rick Hillegas created DERBY-6491:
------------------------------------

             Summary: 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


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