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

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

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)