Uploaded image for project: 'Derby'
  1. Derby
  2. DERBY-6436

Overbroad privileges required when selecting from a view.



    • Type: Bug
    • Status: Open
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: None
    • Fix Version/s: None
    • Component/s: SQL
    • Labels:
    • Urgency:
    • Issue & fix info:
      Repro attached
    • Bug behavior facts:
      Deviation from standard, Security


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

      				//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
                      for (ResultColumn rc : resultColumns) {
                          if (rc.isPrivilegeCollectionRequired()) {
      						compilerContext.addRequiredColumnPriv( rc.getTableColumnDescriptor());

      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;


          Issue Links



              • Assignee:
                rhillegas Richard N. Hillegas
              • Votes:
                0 Vote for this issue
                2 Start watching this issue


                • Created: