db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From ma...@apache.org
Subject svn commit: r958230 - in /db/derby/code/branches/10.5: ./ java/engine/org/apache/derby/iapi/sql/conn/ java/engine/org/apache/derby/iapi/sql/dictionary/ java/engine/org/apache/derby/impl/sql/catalog/ java/engine/org/apache/derby/impl/sql/compile/ java/t...
Date Sat, 26 Jun 2010 14:16:04 GMT
Author: mamta
Date: Sat Jun 26 14:16:04 2010
New Revision: 958230

URL: http://svn.apache.org/viewvc?rev=958230&view=rev
Log:
DERBY-4191 ( Lack of SELECT privilege does not prevent SELECT COUNT(*)) Merging the changes
into 10.5 codeline


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

Propchange: db/derby/code/branches/10.5/
------------------------------------------------------------------------------
--- svn:mergeinfo (original)
+++ svn:mergeinfo Sat Jun 26 14:16:04 2010
@@ -1,2 +1,2 @@
 /db/derby/code/branches/10.6:957000
-/db/derby/code/trunk:769596,769602,769606,769962,772090,772337,772449,772534,774281,777105,779681,782991,785131,785139,785163,785570,785662,788369,788670,788674,788968,789264,790218,792434,793089,793588,794106,794303,794955,795166,796020,796027,796316,796372,797147,798347,798742,800523,803548,803948,805696,808494,808850,809643,810860,812669,816531,816536,819006,822289,823659,824694,829022,832379,833430,835286,881074,881444,882732,884163,887246,892912,897161,901165,901648,901760,903108,908418,911315,915733,916075,916897,918359,921028,927430,928065,942286,942476,942480,942587,946794,948045,948069,951346,954748,955001,955634,956075,956445,956659
+/db/derby/code/trunk:769596,769602,769606,769962,772090,772337,772449,772534,774281,777105,779681,782991,785131,785139,785163,785570,785662,788369,788670,788674,788968,789264,790218,792434,793089,793588,794106,794303,794955,795166,796020,796027,796316,796372,797147,798347,798742,800523,803548,803948,805696,808494,808850,809643,810860,812669,816531,816536,819006,822289,823659,824694,829022,832379,833430,835286,881074,881444,882732,884163,887246,892912,897161,898635,901165,901648,901760,903108,908418,911315,915733,916075,916897,918359,921028,927430,928065,942286,942476,942480,942587,946794,948045,948069,951346,954748,955001,955634,956075,956445,956659

Modified: db/derby/code/branches/10.5/java/engine/org/apache/derby/iapi/sql/conn/Authorizer.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.5/java/engine/org/apache/derby/iapi/sql/conn/Authorizer.java?rev=958230&r1=958229&r2=958230&view=diff
==============================================================================
--- db/derby/code/branches/10.5/java/engine/org/apache/derby/iapi/sql/conn/Authorizer.java
(original)
+++ db/derby/code/branches/10.5/java/engine/org/apache/derby/iapi/sql/conn/Authorizer.java
Sat Jun 26 14:16:04 2010
@@ -57,7 +57,22 @@ public interface Authorizer
 	public static final int DELETE_PRIV = 4;
 	public static final int TRIGGER_PRIV = 5;
 	public static final int EXECUTE_PRIV = 6;
-	public static final int PRIV_TYPE_COUNT = 7;
+	/*
+	 * 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 = 7;
+	public static final int PRIV_TYPE_COUNT = 8;
 
 	/* Used to check who can create schemas or who can modify objects in schema */
 	public static final int CREATE_SCHEMA_PRIV = 16;

Modified: db/derby/code/branches/10.5/java/engine/org/apache/derby/iapi/sql/dictionary/StatementColumnPermission.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.5/java/engine/org/apache/derby/iapi/sql/dictionary/StatementColumnPermission.java?rev=958230&r1=958229&r2=958230&view=diff
==============================================================================
--- db/derby/code/branches/10.5/java/engine/org/apache/derby/iapi/sql/dictionary/StatementColumnPermission.java
(original)
+++ db/derby/code/branches/10.5/java/engine/org/apache/derby/iapi/sql/dictionary/StatementColumnPermission.java
Sat Jun 26 14:16:04 2010
@@ -122,6 +122,17 @@ public class StatementColumnPermission e
 												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 @@ public class StatementColumnPermission e
 
 				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 @@ public class StatementColumnPermission e
 
 
 	/**
-	 * 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 @@ public class StatementColumnPermission e
 
 		// 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/branches/10.5/java/engine/org/apache/derby/iapi/sql/dictionary/StatementTablePermission.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.5/java/engine/org/apache/derby/iapi/sql/dictionary/StatementTablePermission.java?rev=958230&r1=958229&r2=958230&view=diff
==============================================================================
--- db/derby/code/branches/10.5/java/engine/org/apache/derby/iapi/sql/dictionary/StatementTablePermission.java
(original)
+++ db/derby/code/branches/10.5/java/engine/org/apache/derby/iapi/sql/dictionary/StatementTablePermission.java
Sat Jun 26 14:16:04 2010
@@ -247,6 +247,7 @@ public class StatementTablePermission ex
 		switch( privType)
 		{
 		case Authorizer.SELECT_PRIV:
+		case Authorizer.MIN_SELECT_PRIV:
 			priv = perms.getSelectPriv();
 			break;
 		case Authorizer.UPDATE_PRIV:
@@ -292,6 +293,7 @@ public class StatementTablePermission ex
 		switch( privType)
 		{
 		case Authorizer.SELECT_PRIV:
+		case Authorizer.MIN_SELECT_PRIV:
 			return "SELECT";
 		case Authorizer.UPDATE_PRIV:
 			return "UPDATE";

Modified: db/derby/code/branches/10.5/java/engine/org/apache/derby/impl/sql/catalog/DataDictionaryImpl.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.5/java/engine/org/apache/derby/impl/sql/catalog/DataDictionaryImpl.java?rev=958230&r1=958229&r2=958230&view=diff
==============================================================================
--- db/derby/code/branches/10.5/java/engine/org/apache/derby/impl/sql/catalog/DataDictionaryImpl.java
(original)
+++ db/derby/code/branches/10.5/java/engine/org/apache/derby/impl/sql/catalog/DataDictionaryImpl.java
Sat Jun 26 14:16:04 2010
@@ -11862,6 +11862,8 @@ public final class	DataDictionaryImpl
     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/branches/10.5/java/engine/org/apache/derby/impl/sql/compile/CompilerContextImpl.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.5/java/engine/org/apache/derby/impl/sql/compile/CompilerContextImpl.java?rev=958230&r1=958229&r2=958230&view=diff
==============================================================================
--- db/derby/code/branches/10.5/java/engine/org/apache/derby/impl/sql/compile/CompilerContextImpl.java
(original)
+++ db/derby/code/branches/10.5/java/engine/org/apache/derby/impl/sql/compile/CompilerContextImpl.java
Sat Jun 26 14:16:04 2010
@@ -736,7 +736,7 @@ public class CompilerContextImpl extends
 	{
 		currPrivType = ((Integer) privTypeStack.pop()).intValue();
 	}
-	
+
 	/**
 	 * Add a column privilege to the list of used column privileges.
 	 *
@@ -776,6 +776,36 @@ public class CompilerContextImpl extends
 		}
 
 		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);
@@ -804,6 +834,20 @@ public class CompilerContextImpl extends
 			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/branches/10.5/java/engine/org/apache/derby/impl/sql/compile/CursorNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.5/java/engine/org/apache/derby/impl/sql/compile/CursorNode.java?rev=958230&r1=958229&r2=958230&view=diff
==============================================================================
--- db/derby/code/branches/10.5/java/engine/org/apache/derby/impl/sql/compile/CursorNode.java
(original)
+++ db/derby/code/branches/10.5/java/engine/org/apache/derby/impl/sql/compile/CursorNode.java
Sat Jun 26 14:16:04 2010
@@ -30,6 +30,7 @@ import org.apache.derby.iapi.services.co
 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;
@@ -263,6 +264,28 @@ public class CursorNode extends DMLState
 							+ 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/branches/10.5/java/engine/org/apache/derby/impl/sql/compile/SubqueryNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.5/java/engine/org/apache/derby/impl/sql/compile/SubqueryNode.java?rev=958230&r1=958229&r2=958230&view=diff
==============================================================================
--- db/derby/code/branches/10.5/java/engine/org/apache/derby/impl/sql/compile/SubqueryNode.java
(original)
+++ db/derby/code/branches/10.5/java/engine/org/apache/derby/impl/sql/compile/SubqueryNode.java
Sat Jun 26 14:16:04 2010
@@ -28,6 +28,7 @@ import org.apache.derby.iapi.sql.compile
 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;
@@ -466,6 +467,17 @@ public class SubqueryNode extends ValueN
 		 * 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);
@@ -533,6 +545,7 @@ public class SubqueryNode extends ValueN
 		/* Add this subquery to the subquery list */
 		subqueryList.addSubqueryNode(this);
 
+		cc.popCurrentPrivType();
 		return this;
 	}
 

Modified: db/derby/code/branches/10.5/java/testing/org/apache/derbyTesting/functionTests/tests/lang/GrantRevokeDDLTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.5/java/testing/org/apache/derbyTesting/functionTests/tests/lang/GrantRevokeDDLTest.java?rev=958230&r1=958229&r2=958230&view=diff
==============================================================================
--- db/derby/code/branches/10.5/java/testing/org/apache/derbyTesting/functionTests/tests/lang/GrantRevokeDDLTest.java
(original)
+++ db/derby/code/branches/10.5/java/testing/org/apache/derbyTesting/functionTests/tests/lang/GrantRevokeDDLTest.java
Sat Jun 26 14:16:04 2010
@@ -10045,6 +10045,173 @@ public final class GrantRevokeDDLTest ex
 
 
     /**
+     * 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/branches/10.5/java/testing/org/apache/derbyTesting/functionTests/tests/lang/RolesConferredPrivilegesTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.5/java/testing/org/apache/derbyTesting/functionTests/tests/lang/RolesConferredPrivilegesTest.java?rev=958230&r1=958229&r2=958230&view=diff
==============================================================================
--- db/derby/code/branches/10.5/java/testing/org/apache/derbyTesting/functionTests/tests/lang/RolesConferredPrivilegesTest.java
(original)
+++ db/derby/code/branches/10.5/java/testing/org/apache/derbyTesting/functionTests/tests/lang/RolesConferredPrivilegesTest.java
Sat Jun 26 14:16:04 2010
@@ -63,7 +63,7 @@ public class RolesConferredPrivilegesTes
     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 @@ public class RolesConferredPrivilegesTes
      *
      * @throws SQLException
      */
-    public void testConferredPrivileges() throws SQLException
+    public void atestConferredPrivileges() throws SQLException
     {
         Connection dboConn = getConnection();
         Statement s = dboConn.createStatement();
@@ -1086,6 +1086,104 @@ public class RolesConferredPrivilegesTes
         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 @@ public class RolesConferredPrivilegesTes
                                        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();
     }
 
 



Mime
View raw message