[jira] [Created] (DERBY-6436) Overbroad privileges required when selecting from a view.

[Rick Hillegas (JIRA)]

Rick Hillegas created DERBY-6436:
------------------------------------

             Summary: Overbroad privileges required when selecting from a view.
                 Key: DERBY-6436
                 URL: https://issues.apache.org/jira/browse/DERBY-6436
             Project: Derby
          Issue Type: Bug
          Components: SQL
            Reporter: Rick Hillegas


If you have SELECT permission on only one column of a view and you try to 
SELECT it, you get an error complaining that you don't have SELECT permission 
on the other columns of the view.

I believe this requirement stems from the following code block in 
FromBaseTable.bindNonVTITables():

{noformat}
                                //Views execute with definer's privileges and 
if any one of 
                                //those privileges' are revoked from the 
definer, the view gets
                                //dropped. So, a view can exist in Derby only 
if it's owner has
                                //all the privileges needed to create one. In 
order to do a 
                                //select from a view, a user only needs select 
privilege on the
                                //view and doesn't need any privilege for 
objects accessed by
                                //the view. Hence, when collecting privilege 
requirement for a
                                //sql accessing a view, we only need to look 
for select privilege
                                //on the actual view and that is what the 
following code is
                                //checking.
                for (ResultColumn rc : resultColumns) {
                    if (rc.isPrivilegeCollectionRequired()) {
                                                
compilerContext.addRequiredColumnPriv( rc.getTableColumnDescriptor());
                    }
                                }

{noformat}


The following script shows this behavior:

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

-- bounce database to turn on authentication and authorization
connect 'jdbc:derby:memory:db;shutdown=true';
connect 'jdbc:derby:memory:db;user=test_dbo;password=test_dbopassword' as dbo;

create table t1( a int, b int );

create view v1( c, d ) as select a, b from t1;

grant select ( c ) on v1 to ruth;

connect 'jdbc:derby:memory:db;user=ruth;password=ruthpassword' as ruth;

-- correctly fails because ruth doesn't have select permission on v1.d
select * from test_dbo.v1;

-- incorrectly fails because ruth doesn't have select permission on v1.d
select c from test_dbo.v1;




--
This message was sent by Atlassian JIRA
(v6.1.4#6159)