Mailing-List: contact derby-commits-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Derby Development" <derby-dev@db.apache.org>
Content-Type: text/plain; charset="utf-8"
MIME-Version: 1.0
Content-Transfer-Encoding: 7bit
Subject: svn commit: r898635 - in /db/derby/code/trunk/java:
 engine/org/apache/derby/iapi/sql/conn/
 engine/org/apache/derby/iapi/sql/dictionary/
 engine/org/apache/derby/impl/sql/catalog/
 engine/org/apache/derby/impl/sql/compile/ testing/org/apache/derbyTesting/...
Date: Wed, 13 Jan 2010 03:24:54 -0000
To: derby-commits@db.apache.org
From: mamta@apache.org
Message-Id: <20100113032454.E9F44238897D@eris.apache.org>

Author: mamta
Date: Wed Jan 13 03:24:52 2010
New Revision: 898635

URL: http://svn.apache.org/viewvc?rev=898635&view=rev
Log:
DERBY-4191

Require minimum select privilege from the tables in the SELECT sql if no column is selected from the table by the user eg
select count(*) from root.t; 
select 1 from root.t; 
For the query above, Derby was letting the user execute the select even if the user had no select privilege available on root.t With this fix, Derby will check if there is atleast one column on which the user has select privilege available to it or if the user select privilege at the table level. If yes, only then the user will be able to select from another user's table.

select myTable.a from myTable, admin.privateTable
for the query above, since no column is selected specifically from admin.privateTable, Derby will now see if there is table level select privilege or atleast one column level select privilege available on admin.privatTable

One other problem scenario was
update ruth.t_ruth set a = ( select max(c) from ruth.v_ruth ); 
For the query above, prior to fix for DERBY-4191, we were not looking for select privilege for the subquery. That has also been fixed with fix for DERBY-4191

All the existing tests passed with no regression. Added few tests for the fixes involved in this jira.

Modified:
    db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/conn/Authorizer.java
    db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/dictionary/StatementColumnPermission.java
    db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/dictionary/StatementTablePermission.java
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/catalog/DataDictionaryImpl.java
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CompilerContextImpl.java
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CursorNode.java
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/SubqueryNode.java
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/GrantRevokeDDLTest.java
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/RolesConferredPrivilegesTest.java

Modified: db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/conn/Authorizer.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/conn/Authorizer.java?rev=898635&r1=898634&r2=898635&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/conn/Authorizer.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/conn/Authorizer.java Wed Jan 13 03:24:52 2010
@@ -58,8 +58,23 @@
 	public static final int TRIGGER_PRIV = 5;
 	public static final int EXECUTE_PRIV = 6;
 	public static final int USAGE_PRIV = 7;
-    public static final int PRIV_TYPE_COUNT = 8;
-
+    /* 
+     * DERBY-4191
+     * Used to check if user has a table level select privilege/any column 
+     * level select privilege to fulfill the requirements for following kind 
+     * of queries
+     * select count(*) from t1
+     * select count(1) from t1
+     * select 1 from t1
+     * select t1.c1 from t1, t2
+     * DERBY-4191 was added for Derby bug where for first 3 queries above,
+     * we were not requiring any select privilege on t1. And for the 4th
+     * query, we were not requiring any select privilege on t2 since no
+     * column was selected from t2
+     */
+	public static final int MIN_SELECT_PRIV = 8;
+    public static final int PRIV_TYPE_COUNT = 9;
+    
 	/* Used to check who can create schemas or who can modify objects in schema */
 	public static final int CREATE_SCHEMA_PRIV = 16;
 	public static final int MODIFY_SCHEMA_PRIV = 17;

Modified: db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/dictionary/StatementColumnPermission.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/dictionary/StatementColumnPermission.java?rev=898635&r1=898634&r2=898635&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/dictionary/StatementColumnPermission.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/dictionary/StatementColumnPermission.java Wed Jan 13 03:24:52 2010
@@ -122,6 +122,17 @@
 												true /* grantable permissions */,
 												authorizationId,
 												permittedColumns);
+		
+		//DERBY-4191
+		//If we are looking for select privilege on ANY column,
+		//then we can quit as soon as we find some column with select
+		//privilege. This is needed for queries like
+		//select count(*) from t1
+		//select count(1) from t1
+		//select 1 from t1
+		//select t1.c1 from t1, t2
+		if (privType == Authorizer.MIN_SELECT_PRIV && permittedColumns != null)
+			return;
 
 		FormatableBitSet unresolvedColumns = (FormatableBitSet)columns.clone();
 
@@ -181,18 +192,59 @@
 
 				while (unresolvedColumns.anySetBit() >= 0 &&
 					   (r = rci.next()) != null ) {
+					//The user does not have needed privilege directly 
+					//granted to it, so let's see if he has that privilege
+					//available to him/her through his roles.
+					permittedColumns = tryRole(lcc, dd,	forGrant, r);
+					//DERBY-4191
+					//If we are looking for select privilege on ANY column,
+					//then we can quit as soon as we find some column with select
+					//privilege through this role. This is needed for queries like
+					//select count(*) from t1
+					//select count(1) from t1
+					//select 1 from t1
+					//select t1.c1 from t1, t2
+					if (privType == Authorizer.MIN_SELECT_PRIV && permittedColumns != null) {
+						DependencyManager dm = dd.getDependencyManager();
+						RoleGrantDescriptor rgd =
+							dd.getRoleDefinitionDescriptor(role);
+						ContextManager cm = lcc.getContextManager();
+
+						dm.addDependency(ps, rgd, cm);
+						dm.addDependency(activation, rgd, cm);
+						return;
+					}
 
-					unresolvedColumns = tryRole(lcc, dd, forGrant,
-												r, unresolvedColumns);
+					//Use the privileges obtained through the role to satisfy
+					//the column level privileges we need. If all the remaining
+					//column level privileges are satisfied through this role,
+					//we will quit out of this while loop
+					for(int i = unresolvedColumns.anySetBit();
+						i >= 0;
+						i = unresolvedColumns.anySetBit(i)) {
+
+						if(permittedColumns != null && permittedColumns.get(i)) {
+							unresolvedColumns.clear(i);
+						}
+					}
 				}
 			}
 		}
+		TableDescriptor td = getTableDescriptor(dd);
+		//if we are still here, then that means that we didn't find any select
+		//privilege on the table or any column in the table
+		if (privType == Authorizer.MIN_SELECT_PRIV)
+			throw StandardException.newException( forGrant ? SQLState.AUTH_NO_TABLE_PERMISSION_FOR_GRANT
+					  : SQLState.AUTH_NO_TABLE_PERMISSION,
+					  authorizationId,
+					  getPrivName(),
+					  td.getSchemaName(),
+					  td.getName());
 
 		int remains = unresolvedColumns.anySetBit();
 
 		if (remains >= 0) {
 			// No permission on this column.
-			TableDescriptor td = getTableDescriptor(dd);
 			ColumnDescriptor cd = td.getColumnDescriptor(remains + 1);
 
 			if(cd == null) {
@@ -378,23 +430,20 @@
 
 
 	/**
-	 * Given the set of yet unresolved column permissions, try to use
-	 * the supplied role r to resolve them. After this is done, return
-	 * the set of columns still unresolved. If the role is used for
-	 * anything, record a dependency.
+	 * Try to use the supplied role r to see what column privileges are we 
+	 * entitled to. 
 	 *
 	 * @param lcc language connection context
 	 * @param dd  data dictionary
 	 * @param forGrant true of a GRANTable permission is sought
 	 * @param r the role to inspect to see if it can supply the required
 	 *          privileges
-	 * @param unresolvedColumns the set of columns yet unaccounted for
+	 * return the set of columns on which we have privileges through this role
 	 */
 	private FormatableBitSet tryRole(LanguageConnectionContext lcc,
 									 DataDictionary dd,
 									 boolean forGrant,
-									 String r,
-									 FormatableBitSet unresolvedColumns)
+									 String r)
 			throws StandardException {
 
 		FormatableBitSet permittedColumns = null;
@@ -407,17 +456,7 @@
 
 		// if grantable is given, applicable in both cases, so use union
 		permittedColumns = addPermittedColumns(dd, true, r, permittedColumns);
-
-		for(int i = unresolvedColumns.anySetBit();
-			i >= 0;
-			i = unresolvedColumns.anySetBit(i)) {
-
-			if(permittedColumns != null && permittedColumns.get(i)) {
-				unresolvedColumns.clear(i);
-			}
-		}
-
-		return unresolvedColumns;
+		return permittedColumns;
 	}
 
 

Modified: db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/dictionary/StatementTablePermission.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/dictionary/StatementTablePermission.java?rev=898635&r1=898634&r2=898635&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/dictionary/StatementTablePermission.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/dictionary/StatementTablePermission.java Wed Jan 13 03:24:52 2010
@@ -247,6 +247,7 @@
 		switch( privType)
 		{
 		case Authorizer.SELECT_PRIV:
+		case Authorizer.MIN_SELECT_PRIV:
 			priv = perms.getSelectPriv();
 			break;
 		case Authorizer.UPDATE_PRIV:
@@ -292,6 +293,7 @@
 		switch( privType)
 		{
 		case Authorizer.SELECT_PRIV:
+		case Authorizer.MIN_SELECT_PRIV:
 			return "SELECT";
 		case Authorizer.UPDATE_PRIV:
 			return "UPDATE";

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/catalog/DataDictionaryImpl.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/catalog/DataDictionaryImpl.java?rev=898635&r1=898634&r2=898635&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/catalog/DataDictionaryImpl.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/catalog/DataDictionaryImpl.java Wed Jan 13 03:24:52 2010
@@ -12146,6 +12146,8 @@
     static {
         colPrivTypeMap = new String[ Authorizer.PRIV_TYPE_COUNT];
         colPrivTypeMapForGrant = new String[ Authorizer.PRIV_TYPE_COUNT];
+        colPrivTypeMap[ Authorizer.MIN_SELECT_PRIV] = "s";
+        colPrivTypeMapForGrant[ Authorizer.MIN_SELECT_PRIV] = "S";
         colPrivTypeMap[ Authorizer.SELECT_PRIV] = "s";
         colPrivTypeMapForGrant[ Authorizer.SELECT_PRIV] = "S";
         colPrivTypeMap[ Authorizer.UPDATE_PRIV] = "u";

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CompilerContextImpl.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CompilerContextImpl.java?rev=898635&r1=898634&r2=898635&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CompilerContextImpl.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CompilerContextImpl.java Wed Jan 13 03:24:52 2010
@@ -741,7 +741,7 @@
 	{
 		currPrivType = ((Integer) privTypeStack.pop()).intValue();
 	}
-	
+
 	/**
 	 * Add a column privilege to the list of used column privileges.
 	 *
@@ -781,6 +781,36 @@
 		}
 
 		UUID tableUUID = td.getUUID();
+
+		//DERBY-4191
+		if( currPrivType == Authorizer.MIN_SELECT_PRIV){
+			//If we are here for MIN_SELECT_PRIV requirement, then first
+			//check if there is already a SELECT privilege requirement on any 
+			//of the columns in the table. If yes, then we do not need to add 
+			//MIN_SELECT_PRIV requirement for the table because that 
+			//requirement is already getting satisfied with the already
+			//existing SELECT privilege requirement
+			StatementTablePermission key = new StatementTablePermission( 
+					tableUUID, Authorizer.SELECT_PRIV);
+			StatementColumnPermission tableColumnPrivileges
+			  = (StatementColumnPermission) requiredColumnPrivileges.get( key);
+			if( tableColumnPrivileges != null)
+				return;
+		}
+		if( currPrivType == Authorizer.SELECT_PRIV){
+			//If we are here for SELECT_PRIV requirement, then first check
+			//if there is already any MIN_SELECT_PRIV privilege required
+			//on this table. If yes, then that requirement will be fulfilled
+			//by the SELECT_PRIV requirement we are adding now. Because of
+			//that, remove the MIN_SELECT_PRIV privilege requirement
+			StatementTablePermission key = new StatementTablePermission( 
+					tableUUID, Authorizer.MIN_SELECT_PRIV);
+			StatementColumnPermission tableColumnPrivileges
+			  = (StatementColumnPermission) requiredColumnPrivileges.get( key);
+			if( tableColumnPrivileges != null)
+				requiredColumnPrivileges.remove(key);
+		}
+		
 		StatementTablePermission key = new StatementTablePermission( tableUUID, currPrivType);
 		StatementColumnPermission tableColumnPrivileges
 		  = (StatementColumnPermission) requiredColumnPrivileges.get( key);
@@ -809,6 +839,20 @@
 			return; // no priv needed, it is per session anyway
 		}
 
+		if( currPrivType == Authorizer.SELECT_PRIV){
+			//DERBY-4191
+			//Check if there is any MIN_SELECT_PRIV select privilege required
+			//on this table. If yes, then that requirement will be fulfilled
+			//by the SELECT_PRIV requirement we are adding now. Because of
+			//that, remove the MIN_SELECT_PRIV privilege requirement
+			StatementTablePermission key = new StatementTablePermission( 
+					table.getUUID(), Authorizer.MIN_SELECT_PRIV);
+			StatementColumnPermission tableColumnPrivileges
+			  = (StatementColumnPermission) requiredColumnPrivileges.get( key);
+			if( tableColumnPrivileges != null)
+				requiredColumnPrivileges.remove(key);
+		}
+
 		StatementTablePermission key = new StatementTablePermission( table.getUUID(), currPrivType);
 		requiredTablePrivileges.put(key, key);
 	}

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CursorNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CursorNode.java?rev=898635&r1=898634&r2=898635&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CursorNode.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CursorNode.java Wed Jan 13 03:24:52 2010
@@ -31,6 +31,7 @@
 import org.apache.derby.iapi.services.sanity.SanityManager;
 import org.apache.derby.iapi.sql.ResultColumnDescriptor;
 import org.apache.derby.iapi.sql.compile.C_NodeTypes;
+import org.apache.derby.iapi.sql.conn.Authorizer;
 import org.apache.derby.iapi.sql.dictionary.DataDictionary;
 import org.apache.derby.iapi.sql.dictionary.TableDescriptor;
 import org.apache.derby.impl.sql.CursorInfo;
@@ -267,6 +268,28 @@
 							+ fromList.size()
 							+ " on return from RS.bindExpressions()");
 			}
+			
+			//DERBY-4191 Make sure that we have minimum select privilege on 
+			//each of the tables in the query.
+			getCompilerContext().pushCurrentPrivType(Authorizer.MIN_SELECT_PRIV);
+			FromList resultSetFromList = resultSet.getFromList();
+			for (int index = 0; index < resultSetFromList.size(); index++) {
+				FromTable fromTable = (FromTable) resultSetFromList.elementAt(index);
+				if (fromTable.isPrivilegeCollectionRequired() && fromTable instanceof FromBaseTable)
+					//We ask for MIN_SELECT_PRIV requirement of the first
+					//column in the table. The first column is just a 
+					//place holder. What we really do at execution time when 
+					//we see we are looking for MIN_SELECT_PRIV privilege is
+					//as follows
+					//1)we will look for SELECT privilege at table level
+					//2)If not found, we will look for SELECT privilege on 
+					//ANY column, not necessarily the first column. But since
+					//the constructor for column privilege requires us to pass
+					//a column descriptor, we just choose the first column for
+					//MIN_SELECT_PRIV requirement.
+					getCompilerContext().addRequiredColumnPriv(fromTable.getTableDescriptor().getColumnDescriptor(1));
+			}
+			getCompilerContext().popCurrentPrivType();
 		}
 		finally
 		{

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/SubqueryNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/SubqueryNode.java?rev=898635&r1=898634&r2=898635&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/SubqueryNode.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/SubqueryNode.java Wed Jan 13 03:24:52 2010
@@ -28,6 +28,7 @@
 import org.apache.derby.iapi.sql.compile.Visitable;
 import org.apache.derby.iapi.sql.compile.Visitor;
 import org.apache.derby.iapi.sql.compile.C_NodeTypes;
+import org.apache.derby.iapi.sql.conn.Authorizer;
 
 import org.apache.derby.iapi.sql.dictionary.DataDictionary;
 import org.apache.derby.iapi.reference.SQLState;
@@ -497,6 +498,17 @@
 		 * any *'s have been replaced, so that they don't get expanded.
 		 */
 		CompilerContext cc = getCompilerContext();
+		/* DERBY-4191
+		 * We should make sure that we require select privileges
+		 * on the tables in the underlying subquery and not the
+		 * parent sql's privilege. eg
+		 * update t1 set c1=(select c2 from t2) 
+		 * For the query above, when working with the subquery, we should
+		 * require select privilege on t2.c2 rather than update privilege.
+		 * Prior to fix for DERBY-4191, we were collecting update privilege
+		 * requirement for t2.c2 rather than select privilege 
+		 */
+		cc.pushCurrentPrivType(Authorizer.SELECT_PRIV);
 
 		resultSet = resultSet.bindNonVTITables(getDataDictionary(), fromList);
 		resultSet = resultSet.bindVTITables(fromList);
@@ -574,6 +586,7 @@
 		/* Add this subquery to the subquery list */
 		subqueryList.addSubqueryNode(this);
 
+		cc.popCurrentPrivType();
 		return this;
 	}
 

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/GrantRevokeDDLTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/GrantRevokeDDLTest.java?rev=898635&r1=898634&r2=898635&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/GrantRevokeDDLTest.java (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/GrantRevokeDDLTest.java Wed Jan 13 03:24:52 2010
@@ -10065,6 +10065,173 @@
 
 
     /**
+     * DERBY-4191
+     * Make sure that we check for atleast table level select privilege or
+     * any column level select privilege for following kind of queries
+     * select count(*) from t1
+     * select count(1) from t1
+     * select 1 from t1
+     * select t1.c1 from t1, t2
+     */
+    public void testMinimumSelectPrivilegeRequirement() throws SQLException {
+        Connection user1 = openUserConnection("user1");
+        Statement user1St = user1.createStatement();
+
+        Connection user2 = openUserConnection("user2");
+        Statement user2St = user2.createStatement();
+
+        ResultSet rs = null;
+
+        //user1 creates table t4191 and t4191_table2
+        user1St.executeUpdate("create table t4191(x int, y int)");
+        user1St.executeUpdate("create table t4191_table2(z int)");
+        user1St.executeUpdate("create table t4191_table3(c31 int, c32 int)");
+        user1St.executeUpdate("create view view_t4191_table3(v31, v32) " +
+        		"as select c31, c32 from t4191_table3");
+
+        user1St.execute("grant update on t4191_table3 to public");
+        user1St.execute("grant insert on t4191_table3 to public");
+        user1St.execute("grant delete on t4191_table3 to public");
+        //none of following DMLs will work because there is no select
+        //privilege available on the view to user2.
+        assertStatementError("42502", user2St, "update user1.t4191_table3 "+
+        		"set c31 = ( select max(v31) from user1.view_t4191_table3 )");
+        assertStatementError("42502", user2St, "update user1.t4191_table3 "+
+        		"set c31 = ( select count(*) from user1.view_t4191_table3 )");
+        assertStatementError("42502", user2St, "update user1.t4191_table3 "+
+        		"set c31 = ( select 1 from user1.view_t4191_table3 )");
+        //Following should succeed
+        user2St.execute("delete from user1.t4191_table3");
+
+        //Grant select privilege on view so the above DMLs will start working
+        user1St.execute("grant select on view_t4191_table3 to public");
+        user2St.execute("update user1.t4191_table3 "+
+        		"set c31 = ( select max(v31) from user1.view_t4191_table3 )");
+        user2St.execute("update user1.t4191_table3 "+
+        		"set c31 = ( select count(*) from user1.view_t4191_table3 )");
+        user2St.execute("update user1.t4191_table3 "+
+        		"set c31 = ( select 1 from user1.view_t4191_table3 )");
+
+        //none of following selects will work because there is no select
+        //privilege available to user2 yet.
+        assertStatementError("42500", user2St, "select count(*) from user1.t4191");
+        assertStatementError("42500", user2St, "select count(1) from user1.t4191");
+        assertStatementError("42502", user2St, "select count(y) from user1.t4191");
+        assertStatementError("42500", user2St, "select 1 from user1.t4191");
+        //update below should fail because user2 does not have update 
+        //privileges on user1.t4191
+        assertStatementError("42502", user2St, "update user1.t4191 set x=0");
+        //update with subquery should fail too
+        assertStatementError("42502", user2St, "update user1.t4191 set x=" +
+		" ( select max(x) + 2 from user1.t4191 )");
+        assertStatementError("42502", user2St, "update user1.t4191 set x=" +
+		" ( select z from user1.t4191_table2 )");
+
+        //grant select on user1.t4191(x) to user2 and now the above select 
+        //statements will work
+        user1St.execute("grant select(x) on t4191 to user2");
+        String[][] expRS = new String [][]
+                              {
+                                  {"0"}
+                              };
+        rs = user2St.executeQuery("select count(*) from user1.t4191");
+        JDBC.assertFullResultSet(rs, expRS, true);
+        rs = user2St.executeQuery("select count(1) from user1.t4191");
+        JDBC.assertFullResultSet(rs, expRS, true);
+        rs = user2St.executeQuery("select 1 from user1.t4191");
+        JDBC.assertEmpty(rs);
+
+        //user2 does not have select privilege on 2nd column from user1.t4191
+        assertStatementError("42502", user2St, "select count(y) from user1.t4191");
+        //user2 does not have any select privilege on user1.table t4191_table2
+        assertStatementError("42500", user2St, "select x from user1.t4191_table2, user1.t4191");
+        
+        //grant select privilege on a column in user1.table t4191_table2 to user2
+        user1St.execute("grant select(z) on t4191_table2 to user2");
+        //now the following should run fine without any privilege issues
+        rs = user2St.executeQuery("select x from user1.t4191_table2, user1.t4191");
+        JDBC.assertEmpty(rs);
+        
+        //revoke some column level privileges from user2
+        user1St.execute("revoke select(x) on t4191 from user2");
+        user1St.execute("revoke select(z) on t4191_table2 from user2");
+        //update below should fail because user2 does not have update 
+        //privileges on user1.t4191
+        assertStatementError("42502", user2St, "update user1.t4191 set x=0");
+        //update with subquery should fail too
+        assertStatementError("42502", user2St, "update user1.t4191 set x=" +
+		" ( select max(x) + 2 from user1.t4191 )");
+        assertStatementError("42502", user2St, "update user1.t4191 set x=" +
+		" ( select z from user1.t4191_table2 )");
+        //grant update on user1.t4191 to user2
+        user1St.execute("grant update on t4191 to user2");
+        //following update will now work because it has the required update
+        //privilege
+        assertUpdateCount(user2St, 0, "update user1.t4191 set x=0");
+        //folowing will still fail because there is no select privilege on 
+        //user1.t4191(x)
+        assertStatementError("42502", user2St, "update user1.t4191 set x=" +
+		" ( select max(x) + 2 from user1.t4191 )");
+        //following update will fail because there is no select privilege
+        //on user1.t4191_table2
+        assertStatementError("42502", user2St, "update user1.t4191 set x=" +
+		" ( select z from user1.t4191_table2 )");
+        user1St.execute("grant select(y) on t4191 to user2");
+        //folowing will still fail because there is no select privilege on 
+        //user1.t4191(x)
+        assertStatementError("42502", user2St, "update user1.t4191 set x=" +
+		" ( select max(x) + 2 from user1.t4191 )");
+        user1St.execute("grant select(x) on t4191 to user2");
+        //following will now work because we have all the required privileges
+        assertUpdateCount(user2St, 0, "update user1.t4191 set x=" +
+		" ( select max(x) + 2 from user1.t4191 )");
+        //folowing will still fail because there is no select privilege on 
+        //user1.t4191(x)
+        assertStatementError("42502", user2St, "update user1.t4191 set x=" +
+		" ( select z from user1.t4191_table2 )");
+        user1St.execute("grant select on t4191_table2 to user2");
+        //following will now pass
+        assertUpdateCount(user2St, 0, "update user1.t4191 set x=" +
+		" ( select z from user1.t4191_table2 )");
+
+        //take away select privilege from one column and grant privilege on
+        //another column in user1.t4191 to user2
+        user1St.execute("revoke select(x) on t4191 from user2");
+        //the following update will work because we still have update
+        //privilege granted to user2
+        assertUpdateCount(user2St, 0, "update user1.t4191 set x=0");
+        //but following update won't work because there are no select
+        //privileges available to user2 on user1.t4191(x)
+        assertStatementError("42502", user2St, "update user1.t4191 set x=" +
+		" ( select max(x) + 2 from user1.t4191 )");
+        user1St.execute("grant select(y) on t4191 to user2");
+        //following update still won't work because the select is granted on
+        //user1.t4191(y) and not user1.t4191(x)
+        assertStatementError("42502", user2St, "update user1.t4191 set x=" +
+		" ( select max(x) + 2 from user1.t4191 )");
+        //following queries will still work because there is still a 
+        //select privilege on user1.t4191 available to user2
+        rs = user2St.executeQuery("select count(*) from user1.t4191");
+        JDBC.assertFullResultSet(rs, expRS, true);
+        rs = user2St.executeQuery("select count(1) from user1.t4191");
+        JDBC.assertFullResultSet(rs, expRS, true);
+        rs = user2St.executeQuery("select 1 from user1.t4191");
+        JDBC.assertEmpty(rs);
+        rs = user2St.executeQuery("select count(y) from user1.t4191");
+        JDBC.assertFullResultSet(rs, expRS, true);
+        //grant select privilege on user1.t4191(x) back to user2 so following
+        //update can succeed
+        user1St.execute("grant select(x) on t4191 to user2");
+        assertUpdateCount(user2St, 0, "update user1.t4191 set x=" +
+		" ( select max(x) + 2 from user1.t4191 )");
+
+        user1St.execute("drop table t4191");
+        user1.close();
+        user2.close();
+}
+
+
+    /**
      * DERBY-3266
      */
     public void testGlobalTempTables() throws SQLException {

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/RolesConferredPrivilegesTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/RolesConferredPrivilegesTest.java?rev=898635&r1=898634&r2=898635&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/RolesConferredPrivilegesTest.java (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/RolesConferredPrivilegesTest.java Wed Jan 13 03:24:52 2010
@@ -63,7 +63,7 @@
     private final static String TRIGGERDROPPED       = "01502";
     private final static String UNRELIABLE           = "42Y39";
 
-    private final static String[] users = {"test_dbo", "DonaldDuck"};
+    private final static String[] users = {"test_dbo", "DonaldDuck", "MickeyMouse"};
 
     /**
      * Create a new instance of RolesConferredPrivilegesTest.
@@ -255,7 +255,7 @@
      *
      * @throws SQLException
      */
-    public void testConferredPrivileges() throws SQLException
+    public void atestConferredPrivileges() throws SQLException
     {
         Connection dboConn = getConnection();
         Statement s = dboConn.createStatement();
@@ -1086,6 +1086,104 @@
         dboConn.close();
     }
 
+    /**
+     * DERBY-4191
+     * There are times when no column is selected from a table in the from
+     * list. At such a time, we should make sure that we make sure there
+     * is atleast some kind of select privilege available on that table for
+     * the query to succeed. eg of such queries
+     * select count(*) from t1
+     * select count(1) from t1
+     * select 1 from t1
+     * select t1.c1 from t1, t2
+     * 
+     * In addition, the subquery inside of a NON-select query should require
+     * select privilege on the tables involved in the subquery eg
+     * update dbo.t set a = ( select max(a1) + 2 from dbo.t1 )
+     * update dbo.t set a = ( select max(b1) + 2 from dbo.t2 )
+     * For both the queries above, in addition to update privilege requirement
+     * on dbo.t(a), we need to require select privileges on columns/tables
+     * within the select list. So for first query, the user should have select
+     * privilege on dbo.t1 or dbo.t1(a1). Similarly, for 2nd query, the user
+     * should have select privilege on dbo.t2 or dbo.t2(b1) 
+     * @throws SQLException
+     */
+    public void testMinimumSelectPrivilege() throws SQLException {
+        Connection dboConn = getConnection();
+        Statement stmtDBO = dboConn.createStatement();
+
+        Connection cDD = openUserConnection("DonaldDuck");
+        Statement stmtDD = cDD.createStatement();
+
+        Connection cMM = openUserConnection("MickeyMouse");
+        Statement stmtMM = cMM.createStatement();
+
+        stmtDBO.executeUpdate("create role role1");
+        stmtDBO.executeUpdate("grant role1 to MickeyMouse");
+
+        stmtDD.executeUpdate("create table DDtable1(c11 int, c12 int)");
+        stmtDD.executeUpdate("insert into DDtable1 values(1, 2)");
+        stmtDD.executeUpdate("create table DDtable2(c21 int, c22 int)");
+        stmtDD.executeUpdate("insert into DDtable2 values(3, 4)");
+        
+        stmtMM.executeUpdate("set role role1");
+        try {
+        	stmtMM.executeQuery("select c11 from DonaldDuck.DDtable1");
+        	fail("select should have failed");
+        } catch (SQLException e) {
+            assertSQLState("42502", e);
+        }
+        try {
+        	stmtMM.executeUpdate("update DonaldDuck.DDtable1 set c11 = " +
+        			" (select c21 from DonaldDuck.DDtable2)");
+        	fail("select should have failed");
+        } catch (SQLException e) {
+            assertSQLState("42502", e);
+        }
+
+        stmtDD.executeUpdate("grant select(c12) on DDtable1 to role1");
+        stmtDD.executeUpdate("grant update on DDtable1 to role1");
+    	stmtMM.executeQuery("select c12 from DonaldDuck.DDtable1");
+        try {
+        	stmtMM.executeQuery("select c11 from DonaldDuck.DDtable1");
+        	fail("select should have failed");
+        } catch (SQLException e) {
+            assertSQLState("42502", e);
+        }
+        try {
+        	stmtMM.executeUpdate("update DonaldDuck.DDtable1 set c11 = " +
+        			" (select c21 from DonaldDuck.DDtable2)");
+        	fail("select should have failed");
+        } catch (SQLException e) {
+            assertSQLState("42502", e);
+        }
+
+        stmtDD.executeUpdate("grant select(c11) on DDtable1 to role1");
+    	stmtMM.executeQuery("select c12 from DonaldDuck.DDtable1");
+    	stmtMM.executeQuery("select c11 from DonaldDuck.DDtable1");
+        try {
+        	stmtMM.executeQuery("select c11 from DonaldDuck.DDtable1, " +
+        			"DonaldDuck.DDtable2");
+        	fail("select should have failed");
+        } catch (SQLException e) {
+            assertSQLState("42500", e);
+        }
+        try {
+        	stmtMM.executeQuery("update DonaldDuck.DDtable1 set c11 = " +
+        			" (select c21 from DonaldDuck.DDtable2)");
+        	fail("select should have failed");
+        } catch (SQLException e) {
+            assertSQLState("42502", e);
+        }
+
+        stmtDD.executeUpdate("grant select(c21) on DDtable2 to role1");
+    	stmtMM.executeQuery("select c12 from DonaldDuck.DDtable1");
+    	stmtMM.executeQuery("select c11 from DonaldDuck.DDtable1");
+    	stmtMM.executeQuery("select c11 from DonaldDuck.DDtable1, " +
+    			"DonaldDuck.DDtable2");
+    	stmtMM.executeUpdate("update DonaldDuck.DDtable1 set c11 = " +
+    			" (select c21 from DonaldDuck.DDtable2)");
+    }
 
     /**
      * Test that a prepared statement can no longer execute after its required
@@ -2040,8 +2138,108 @@
                                        String schema,
                                        String table,
                                        String[] columns) throws SQLException {
-        assertSelectPrivilege
-            (hasPrivilege, c, schema, table, columns, NOCOLUMNPERMISSION);
+      assertSelectPrivilege(hasPrivilege, c, schema, 
+        		table, columns, NOCOLUMNPERMISSION);
+      assertSelectConstantPrivilege(hasPrivilege, c, schema, 
+        		table, NOTABLEPERMISSION);
+      assertSelectCountPrivilege(hasPrivilege, c, schema, 
+        		table, columns, NOTABLEPERMISSION);
+    }
+
+    /**
+     * Assert that a user has select privilege at the table(s) level  or 
+     * atleast on one column from each of the tables involved in the 
+     * query when running a select query which selects count(*) or
+     * count(constant) from the tables.
+     *
+     * @param hasPrivilege whether or not the user has the privilege
+     * @param c connection to use
+     * @param schema the schema to check
+     * @param table the table to check
+     * @param columns used for error handling if ran into exception
+     * @param sqlState expected state if hasPrivilege == NOPRIV
+     * @throws SQLException throws all exceptions
+     */
+    private void assertSelectCountPrivilege(int hasPrivilege,
+                                       Connection c,
+                                       String schema,
+                                       String table,
+                                       String[] columns,
+                                       String sqlState) throws SQLException {
+        Statement s = c.createStatement();
+
+        try {
+            s.execute("select count(*) from " + schema + "." + table);
+
+            if (hasPrivilege == NOPRIV) {
+                fail("expected no SELECT privilege on table " +
+                     formatArgs(c, schema, table, columns));
+            }
+        } catch (SQLException e) {
+            if (hasPrivilege == NOPRIV) {
+                assertSQLState(sqlState, e);
+            } else {
+                fail("Unexpected lack of select privilege. " +
+                     formatArgs(c, schema, table, columns), e);
+            }
+        }
+
+        try {
+            s.execute("select count('a') from " + schema + "." + table);
+
+            if (hasPrivilege == NOPRIV) {
+                fail("expected no SELECT privilege on table " +
+                     formatArgs(c, schema, table, columns));
+            }
+        } catch (SQLException e) {
+            if (hasPrivilege == NOPRIV) {
+                assertSQLState(sqlState, e);
+            } else {
+                fail("Unexpected lack of select privilege. " +
+                     formatArgs(c, schema, table, columns), e);
+            }
+        }
+
+        s.close();
+    }
+
+    /**
+     * Assert that a user has select privilege at the table(s) level  or 
+     * atleast on one column from each of the tables involved in the 
+     * query when running a select query which only selects constants from 
+     * the tables.
+     *
+     * @param hasPrivilege whether or not the user has the privilege
+     * @param c connection to use
+     * @param schema the schema to check
+     * @param table the table to check
+     * @param sqlState expected state if hasPrivilege == NOPRIV
+     * @throws SQLException throws all exceptions
+     */
+    private void assertSelectConstantPrivilege(int hasPrivilege,
+                                       Connection c,
+                                       String schema,
+                                       String table,
+                                       String sqlState) throws SQLException {
+        Statement s = c.createStatement();
+
+        try {
+            s.execute("select 1 from " + schema + "." + table);
+
+            if (hasPrivilege == NOPRIV) {
+                fail("expected no SELECT privilege on table " +
+                     formatArgs(c, schema, table));
+            }
+        } catch (SQLException e) {
+            if (hasPrivilege == NOPRIV) {
+                assertSQLState(sqlState, e);
+            } else {
+                fail("Unexpected lack of select privilege. " +
+                     formatArgs(c, schema, table), e);
+            }
+        }
+
+        s.close();
     }