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

["Rick Hillegas (JIRA)" <ji...@apache.org>]

     [ https://issues.apache.org/jira/browse/DERBY-6491?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Rick Hillegas updated DERBY-6491:
---------------------------------

    Attachment: derby-6491-aa-stopRequiringUsagePriv.diff

Attaching derby-6491-aa-stopRequiringUsagePriv.diff. This patch turns off the adding of USAGE privilege for types during table binding and during the binding of cursor nodes. I am running tests now.


Touches the following files:

----------------

M       java/engine/org/apache/derby/iapi/sql/compile/CompilerContext.java
M       java/engine/org/apache/derby/impl/sql/compile/CompilerContextImpl.java
M       java/engine/org/apache/derby/impl/sql/compile/QueryTreeNode.java

Give the compiler a way to turn off the addition of USAGE privileges for UDTs.

----------------

M       java/engine/org/apache/derby/impl/sql/compile/FromList.java

Turn off the adding of USAGE privs for types while binding tables.

----------------

M       java/engine/org/apache/derby/impl/sql/compile/CursorNode.java

Turn off the adding of USAGE privs for types while binding cursor nodes.

----------------

M       java/engine/org/apache/derby/impl/sql/GenericStatement.java

Turn off the adding of USAGE privs for types when we're done with the bind() phase.

----------------


M       java/testing/org/apache/derbyTesting/functionTests/tests/lang/GrantRevokeDDLTest.java

Correct canonized wrong results. Add a test to verify that the repro succeeds now.


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