db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From fuzzylo...@apache.org
Subject svn commit: r521432 - in /db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests: master/ suites/ tests/lang/
Date Thu, 22 Mar 2007 20:09:45 GMT
Author: fuzzylogic
Date: Thu Mar 22 13:09:44 2007
New Revision: 521432

URL: http://svn.apache.org/viewvc?view=rev&rev=521432
Log:
DERBY-2410: convert grantRevoke.java to JUnit

Added:
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/GrantRevokeTest.java   (with props)
Removed:
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/grantRevoke.out
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/grantRevoke.java
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/grantRevoke_app.properties
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/grantRevoke_derby.properties
Modified:
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/suites/DerbyNetClientRemote.exclude
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/suites/derbylang.runall
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/suites/derbynetclientmats.runall
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/_Suite.java

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/suites/DerbyNetClientRemote.exclude
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/suites/DerbyNetClientRemote.exclude?view=diff&rev=521432&r1=521431&r2=521432
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/suites/DerbyNetClientRemote.exclude (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/suites/DerbyNetClientRemote.exclude Thu Mar 22 13:09:44 2007
@@ -32,8 +32,6 @@
 # jdbcapi/checkDataSource.java - test leaves things in a bad state, causing the subsequent
 #       tests to fail when run on same database. See DERBY-1760.
 # jdbcapi/checkDataSource30.java - ditto to checkDataSource.
-# lang/grantRevoke.java - this test expects certain properties in the remote server's 
-#       derby.properties file.
 # stress/stress.multi - attempts to connect to localhost.
 # 
 store/bootLock.java
@@ -58,5 +56,4 @@
 junitTests/derbyNet/CompatibilityTest.java
 jdbcapi/checkDataSource.java
 jdbcapi/checkDataSource30.java
-lang/grantRevoke.java
 stress/stress.multi

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/suites/derbylang.runall
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/suites/derbylang.runall?view=diff&rev=521432&r1=521431&r2=521432
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/suites/derbylang.runall (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/suites/derbylang.runall Thu Mar 22 13:09:44 2007
@@ -36,7 +36,6 @@
 lang/forupdate.sql
 lang/grantRevokeDDL.sql
 lang/grantRevokeDDL2.sql
-lang/grantRevoke.java
 lang/holdCursorIJ.sql
 lang/holdCursorJavaReflection.java
 lang/inbetween.sql

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/suites/derbynetclientmats.runall
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/suites/derbynetclientmats.runall?view=diff&rev=521432&r1=521431&r2=521432
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/suites/derbynetclientmats.runall (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/suites/derbynetclientmats.runall Thu Mar 22 13:09:44 2007
@@ -3,4 +3,3 @@
 jdbcapi/xaStateTran.sql
 jdbcapi/XATest.java
 jdbcapi/checkDataSource.java
-lang/grantRevoke.java

Added: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/GrantRevokeTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/GrantRevokeTest.java?view=auto&rev=521432
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/GrantRevokeTest.java (added)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/GrantRevokeTest.java Thu Mar 22 13:09:44 2007
@@ -0,0 +1,1535 @@
+/*
+
+Derby - Class org.apache.derbyTesting.functionTests.tests.lang.GrantRevokeTest
+
+Licensed to the Apache Software Foundation (ASF) under one or more
+contributor license agreements.  See the NOTICE file distributed with
+this work for additional information regarding copyright ownership.
+The ASF licenses this file to You under the Apache License, Version 2.0
+(the "License"); you may not use this file except in compliance with
+the License.  You may obtain a copy of the License at
+
+http://www.apache.org/licenses/LICENSE-2.0
+
+Unless required by applicable law or agreed to in writing, software
+distributed under the License is distributed on an "AS IS" BASIS,
+WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+See the License for the specific language governing permissions and
+limitations under the License.
+
+*/
+
+package org.apache.derbyTesting.functionTests.tests.lang;
+
+import java.io.UnsupportedEncodingException;
+import java.sql.Connection;
+import java.sql.DatabaseMetaData;
+import java.sql.PreparedStatement;
+import java.sql.ResultSet;
+import java.sql.SQLException;
+import java.sql.Statement;
+import java.sql.Types;
+import java.util.ArrayList;
+
+import org.apache.derbyTesting.junit.BaseJDBCTestCase;
+import org.apache.derbyTesting.junit.CleanDatabaseTestSetup;
+import org.apache.derbyTesting.junit.DatabasePropertyTestSetup;
+import org.apache.derbyTesting.junit.JDBC;
+import org.apache.derbyTesting.junit.TestConfiguration;
+import junit.framework.Test;
+import junit.framework.TestSuite;
+
+/**
+ * Test SQL GRANT and REVOKE statements
+ */
+public class GrantRevokeTest extends BaseJDBCTestCase {
+
+	public GrantRevokeTest(String name) {
+		super(name);
+	}
+	
+	/**
+	 * The set of users available for grant/revoke testing
+	 */
+	public final static String[] users = new String[] { "TEST_DBO","U1","U2","U3","U4"};
+	
+	/**
+	 * Most tests run in embedded only, since they are only checking DDL
+	 * statements. Metadata methods test also runs in client/server mode.
+	 * @return
+	 */
+	public static Test suite() {
+		TestSuite suite = new TestSuite();
+		
+		// following is useful for debugging the lock timeout seen in rollback tests,
+		// can connect via network server and look at the lock table.
+		//suite.addTest(TestConfiguration.clientServerDecorator(basesuite()));
+		
+		suite.addTest(basesuite());
+		suite.addTest(TestConfiguration.clientServerDecorator(new GrantRevokeTest("testGrantDatabaseMetaDataMethods")));
+
+		return suite;
+	}
+	
+	/**
+	 * One set of grant/revoke tests for either client/server or embedded.
+	 */
+	public static Test basesuite() {
+		Test basesuite = new TestSuite(GrantRevokeTest.class);
+		Test clean = new CleanDatabaseTestSetup(basesuite) {
+	    	protected void decorateSQL(Statement s) throws SQLException {
+	    		s.execute("create schema s1");
+	    		s.execute("create schema s2");
+	    		s.execute("create table s1.t1(c1 int, c2 int, c3 int)");
+	    		s.execute("create table s2.t1(c1 int, c2 int, c3 int)");
+	    		s.execute("create table s2.t2(c1 int, c2 int, c3 int)");
+	    		s.execute("create table s2.t3(c1 int, c2 int, c3 int)");
+	    	    s.execute("create table s2.noPerms(c1 int, c2 int, c3 int)");
+	    	    s.execute("create function s1.f1() returns int" +
+	    	        "  language java parameter style java" +
+	    	        "  external name 'org.apache.derbyTesting.functionTests.tests.lang.GrantRevokeTest.s1F1'" +
+	    	        "  no sql called on null input");
+	    	    /*
+	    	     * RESOLVE Derby does not implement SPECIFIC names
+	    	       
+	    	       s.execute("create function s2.f1() returns int" +
+                             "  specific s2.s2sp1" +
+	    	                 "  language java parameter style java" +
+	    	                 "  external name 'org.apache.derbyTesting.functionTests.tests.lang.grantRevoke.s2F1a'" +
+	    	                 "  no sql called on null input");
+	    	     * RESOLVE Derby doesn't seem to support function overloading. It doesn't allow us to create two
+	    	     * functions with the same name but different signatures. (Though the StaticMethodCallNode.bindExpression
+	    	     * method does have code to handle overloaded methods). So we cannot throughly test
+	    	     * grant/revoke on overloaded procedures.
+	    	        	         
+	    	       s.execute("create function s2.f1( p1 char(8)) returns int" +
+	    	                 "  language java parameter style java" +
+	    	                 "  external name 'org.apache.derbyTesting.functionTests.tests.lang.grantRevoke.s2F1b'" +
+	    	                 "  no sql called on null input");
+	    	       s.execute("create function s2.f1( char(8), char(8)) returns int" +
+	    	                 "  language java parameter style java" +
+	    	                 "  external name 'org.apache.derbyTesting.functionTests.tests.lang.grantRevoke.s2F1c'" +
+	    	                 "  no sql called on null input");
+	    	       s.execute("create function s2.f1( int) returns int" +
+	    	                 "  language java parameter style java" +
+	    	                 "  external name 'org.apache.derbyTesting.functionTests.tests.lang.grantRevoke.s2F1d'" +
+	    	                 "  no sql called on null input");
+	    	      
+	    	       NOTE: This function definition does not match the s2F2() definition
+	    	             in this class, and thus is not used.
+	    	       s.execute("create function s2.f2( p1 char(8), p2 integer) returns int" +
+	    	                 "  language java parameter style java" +
+	    	                 "  external name 'org.apache.derbyTesting.functionTests.tests.lang.GrantRevokeTest.s2F2'" +
+	    	                 "  no sql called on null input");
+                 */
+	    	   
+	    	   // functions and procedures are supposed to have separate name spaces. Make sure that this does
+	    	   // not confuse grant/revoke.
+	    	   s.execute("create procedure s1.f1( )" +
+	    	        "  language java parameter style java" +
+	    	        "  external name 'org.apache.derbyTesting.functionTests.tests.lang.GrantRevokeTest.s1F1P'" +
+	    	        "  no sql called on null input");
+	    	   s.execute("create procedure s1.p1( )" +
+	    	        "  language java parameter style java" +
+	    	        "  external name 'org.apache.derbyTesting.functionTests.tests.lang.GrantRevokeTest.s1P1'" +
+	    	        "  no sql called on null input");
+	    	}
+	    };
+		Test test = DatabasePropertyTestSetup.builtinAuthentication(
+				clean, users, "grantrevoke");
+        test = TestConfiguration.sqlAuthorizationDecorator(test);
+	    
+	    return test;
+	}
+	
+	public void testSimpleGrant() throws Exception {
+		grant("select", "s1", "t1", users[1]);
+		assertSelectPrivilege(true, users[1], "s1", "t1", null);
+		assertSelectPrivilege(false, users[2], "s1", "t1", null);
+		assertSelectPrivilege(false, users[2], "s1", "t1", null);
+		revoke("select", "s1", "t1", users[1]);
+	}
+	
+	public void testAllPrivileges() throws Exception {
+		grant("all privileges", "s2", "t1", new String[] {users[2], users[3]});
+		assertAllPrivileges(false, users[1], "S2", "T1", null);
+		assertAllPrivileges(true, users[2], "S2", "T1", null);
+		assertAllPrivileges(true, users[3], "S2", "T1", null);
+		assertSelectPrivilege(false, users[1], "s1", "t1", null);
+		assertSelectPrivilege(false, users[1], "s2", "t2", null);
+		revoke("all privileges", "s2", "t1", new String[] {users[2], users[3]});
+	}
+	
+	public void testColumnPrivileges() throws Exception {
+		grant("select(c1),update(c3,c2),references(c3,c1,c2)", "s1", "t1", users[4]);
+		assertSelectPrivilege(true, users[4], "s1", "t1", new String[] {"c1"});
+		assertSelectPrivilege(false, users[4], "s1", "t1", new String[] {"c2"});
+		assertSelectPrivilege(false, users[4], "s1", "t1", new String[] {"c3"});
+		assertSelectPrivilege(false, users[4], "s1", "t1", null);
+		assertUpdatePrivilege(false, users[4], "S1", "T1", new String[] {"C1"});
+		assertUpdatePrivilege(false, users[4], "S1", "T1", new String[] {"C2", "C3"});
+		assertReferencesPrivilege(true, users[4], "s1", "t1", new String[] {"c1","c2","c3"});
+		revoke("select(c1),update(c3,c2),references(c3,c1,c2)", "s1", "t1", users[4]);
+	}
+	
+	public void testFunctionWithSameProcedureName() throws Exception {
+		grant("execute", "function s1", "f1", users[1]);
+		assertFunctionPrivilege( true, users[1], "S1", "F1", false);
+		assertProcedurePrivilege( false, users[1], "S1", "F1");
+		assertFunctionPrivilege( false, users[2], "S1", "F1", false);
+		revoke("execute", "function s1", "f1", users[1]);
+	}
+	
+	public void testGrantOnProcedure() throws Exception {
+		grant("execute", "procedure s1", "p1", users[1]);
+		assertProcedurePrivilege( true, users[1], "S1", "P1");
+		assertFunctionPrivilege( false, users[1], "S1", "P1", true);
+		assertProcedurePrivilege( false, users[2], "S1", "P1");
+		assertFunctionPrivilege( false, users[2], "S1", "P1", true);
+		revoke("execute", "procedure s1", "p1", users[1]);
+	}
+	
+    public void testPublicTablePrivileges() throws Exception {
+    	grant("select, references(c1)", "s2", "t2", "public");
+    	assertSelectPrivilege(true, users[4], "S2", "T2", null);
+        assertSelectPrivilege(true, users[1], "S2", "T2", null);
+        assertSelectPrivilege(false, users[4], "S2", "NOPERMS", null);
+        assertUpdatePrivilege(false, users[4], "S2", "T2", null);
+        assertReferencesPrivilege(true, users[4], "S2", "T2",
+                                 new String[] {"C1"});
+        assertReferencesPrivilege(false, users[4], "S2", "T2", null);
+    	revoke("select, references(c1)", "s2", "t2", "public");
+    }
+    
+    public void testPublicRoutinePrivileges() throws Exception {
+        grant("execute", "procedure s1", "p1", "public");
+        grant("execute", "procedure s1", "p1", users[1]);        
+		assertProcedurePrivilege(true, users[1], "S1", "P1");
+		assertProcedurePrivilege(true, users[4], "S1", "P1");
+        revoke("execute", "procedure s1", "p1", "public");
+        // user1 should still have execute privilege
+        assertProcedurePrivilege(true, users[1], "S1", "P1");
+        assertProcedurePrivilege(false, users[4], "S1", "P1");
+        revoke("execute", "procedure s1", "p1", users[1]);
+        assertProcedurePrivilege(false, users[1], "S1", "P1");
+    }
+
+    /**
+     * Test grant statements before, during, and after a rolled-back
+     * transaction
+     */
+    public void testGrantRollbackAndCommit() throws SQLException {
+    
+    	// NOTE: grantrevoke.java originally used S2.F2 for the function
+    	// below, but the signature on the function didn't match the
+    	// declaration, so was not used properly. Have substituted
+    	// function S1.F1 here to get the testcase to pass.
+    	
+    	// NOTE 2: executing the grant statements on the owner connection
+    	//         leads to a lock timeout when asserting any privilege?
+    	
+    	Connection oc = openUserConnection(users[0]);
+    	oc.setAutoCommit(false);
+    	
+    	// set up some privileges and check them
+    	grant(oc, "select", "s2", "t2", "public");
+    	oc.commit();
+
+        assertSelectPrivilege(true, users[3], "S2", "T2", null);
+        assertUpdatePrivilege(false, users[3], "S2", "T2", null);
+        assertSelectPrivilege(false, users[1], "S2", "T3", new String[] {"C2"});
+        assertDeletePrivilege(false, users[1], "S2", "T3");
+        assertTriggerPrivilege(false, users[2], "S2", "T2");
+        assertFunctionPrivilege(false, users[1], "S1", "F1", false);
+    	
+    	// alter some privileges, assert that they were granted.
+        grant(oc, "select(c2),delete", "s2", "t3", users[1]);
+        grant(oc, "trigger", "s2", "t2", "public");
+    	grant(oc, "execute", "function s1", "f1", users[1]);
+        // the following asserts fail due to lock timeout
+    	//assertSelectPrivilege(true, users[1], "s2", "t3", new String[] {"C2"});
+        //assertDeletePrivilege(true, users[1], "s2", "t3");
+        //assertTriggerPrivilege(true, users[2], "S2", "T2");
+        //assertFunctionPrivilege(true, users[1], "S1", "F1", false);
+        
+        // roll it back and assert the privileges were not granted.
+        oc.rollback();
+        assertSelectPrivilege(false, users[1], "S2", "T3", new String[] {"C2"});
+        assertDeletePrivilege(false, users[1], "S2", "T3");
+        assertTriggerPrivilege(false, users[2], "S2", "T2");
+        assertFunctionPrivilege(false, users[1], "S1", "F1", false);
+        
+        // do it again... 
+        grant(oc, "select(c2),delete", "s2", "t3", users[1]);
+        grant(oc, "trigger", "s2", "t2", "public");
+    	grant(oc, "execute", "function s1", "f1", users[1]);
+        // the following asserts fail due to lock timeout
+    	//assertSelectPrivilege(true, users[1], "S2", "T3", new String[] {"C2"});
+        //assertDeletePrivilege(true, users[1], "S2", "T3");
+        //assertTriggerPrivilege(true, users[2], "S2", "T2");
+        //assertFunctionPrivilege(true, users[1], "S1", "F1", false);
+        
+        // commit and ensure the permissions are correct
+        oc.commit();
+        assertSelectPrivilege(true, users[1], "S2", "T3", new String[] {"C2"});
+        assertDeletePrivilege(true, users[1], "S2", "T3");
+        assertTriggerPrivilege( true, users[2], "S2", "T2");
+        assertFunctionPrivilege( true, users[1], "S1", "F1", false);
+       
+    	// remove any permissions we granted
+    	revoke(oc, "select", "s2", "t2", "public");
+    	revoke(oc, "select(c2),delete", "s2", "t3", users[1]);
+        revoke(oc, "trigger", "s2", "t2", "public");
+    	revoke(oc, "execute", "function s1", "f1", users[1]);
+    	oc.commit();
+    	oc.setAutoCommit(false);
+        assertSelectPrivilege(false, users[3], "S2", "T2", null);
+        assertUpdatePrivilege(false, users[3], "S2", "T2", null);
+        assertSelectPrivilege(false, users[1], "S2", "T3", new String[] {"C2"});
+        assertDeletePrivilege(false, users[1], "S2", "T3");
+        assertTriggerPrivilege(false, users[2], "S2", "T2");
+        assertFunctionPrivilege(false, users[1], "S1", "F1", false);
+    	
+    	oc.close();
+    	
+    }
+	/**
+	 * Test Grant/Revoke related DatabaseMetaData methods.
+	 */
+    public void testGrantDatabaseMetaDataMethods() throws Exception{
+    	DatabaseMetaData dm = getConnection().getMetaData();
+    	assertFalse("GrantRevoke: DatabaseMetaData.supportsCatalogsInPrivilegeDefinitionSupport", dm.supportsCatalogsInPrivilegeDefinitions());
+    	assertTrue("GrantRevoke: DatabaseMetaData.supportsSchemasInPrivilegeDefinitions", dm.supportsSchemasInPrivilegeDefinitions());
+    }
+    
+    /* Revoke test methods begin here */
+    
+    /**
+     * Test revoke statements when user already has no permissions.
+     */
+    public void testRevokeWithNoPermissions() throws Exception {
+        // assert users don't already have these privileges.
+    	assertSelectPrivilege(false, users[1], "S1", "T1", null);
+        assertSelectPrivilege(false, users[2], "S1", "T1", new String[] {"C2"});
+        assertUpdatePrivilege(false, users[2], "S1", "T1", new String[] {"C1", "C3"});
+        assertProcedurePrivilege(false, users[1], "S1", "P1");
+        
+        // no unexpected exception should be thrown revoking these privileges.
+        revoke("all privileges", "s1", "t1", users[1]);
+    	assertSelectPrivilege(false, users[1], "S1", "T1", null);
+        assertSelectPrivilege(false, users[1], "S1", "T1", new String[] {"C2"});
+        revoke("execute", "procedure s1", "p1", users[1]);
+        assertProcedurePrivilege(false, users[1], "S1", "P1");
+        revoke("select(c2), update(c1,c3)", "s1", "t1", users[2]);
+        assertSelectPrivilege(false, users[2], "S1", "T1", new String[] {"C2"});
+        assertUpdatePrivilege(false, users[2], "S1", "T1", new String[] {"C1", "C3"});  
+    }
+    
+    public void testRevokeSingleTableSingleUser() throws Exception {
+    	grant("all privileges", "s2", "t1", users[1]);
+        grant("update(c3)", "s2", "t1", users[1]);
+        assertSelectPrivilege(true, users[1], "S2", "T1", null);
+        assertUpdatePrivilege(true, users[1], "S2", "T1", new String[] {"C3"});  
+        
+        revoke("update", "S2", "t1", users[1]);
+        assertSelectPrivilege( true, users[1], "S2", "T1", null);
+        assertUpdatePrivilege( false, users[1], "S2", "T1", null);
+        assertUpdatePrivilege( false, users[1], "S2", "T1", new String[] {"C3"});
+        assertInsertPrivilege( true, users[1], "S2", "T1", null);
+        assertDeletePrivilege( true, users[1], "S2", "T1");
+        assertReferencesPrivilege( true, users[1], "S2", "T1", null);
+        assertTriggerPrivilege( true, users[1], "S2", "T1");
+        
+        revoke("all privileges", "s2", "t1", users[1]);
+        assertAllPrivileges(false, users[1], "S2", "T1", null);
+    }
+    
+    public void testRevokeMultiplePermissionsMultipleUsers() throws SQLException {
+    	grant("select", "s1", "t1", new String[] {users[1], users[2], users[3]});
+    	grant("update(c1,c2,c3)", "s1", "t1", users[1]);
+    	grant("update(c3)", "s1", "t1", users[2]);
+    	grant("trigger", "s1", "t1", users[1]);
+        assertSelectPrivilege(true, users[1], "S1", "T1", null);
+        assertSelectPrivilege(true, users[2], "S1", "T1", null);
+        assertSelectPrivilege(true, users[3], "S1", "T1", null);
+        // DatabaseMetaData.getTablePrivileges() returns false for the following
+        // due to column privileges for table being used, so assert
+        // with null for columns is disabled
+        //assertUpdatePrivilege(true, users[1], "S1", "T1", null);  
+        assertUpdatePrivilege(true, users[1], "S1", "T1", new String[] {"C1", "C2", "C3" }); 
+        assertUpdatePrivilege(false, users[2], "S1", "T1", new String[] {"C1", "C2"});
+        assertUpdatePrivilege(true, users[2], "S1", "T1", new String[] {"C3"});
+        assertTriggerPrivilege(true, users[1], "S1", "T1");
+        assertTriggerPrivilege(false, users[2], "S1", "T1");
+        
+        revoke("select, update(c2,c3)", "s1", "t1", new String[] {users[1], users[2], users[3]});
+        assertSelectPrivilege(false, users[1], "S1", "T1", null);
+        assertSelectPrivilege(false, users[2], "S1", "T1", null);
+        assertSelectPrivilege(false, users[3], "S1", "T1", null);
+        assertUpdatePrivilege(true, users[1], "S1", "T1", new String[] {"C1"});  
+        assertUpdatePrivilege(false, users[1], "S1", "T1", new String[] {"C2", "C3"});
+        assertUpdatePrivilege(false, users[2], "S1", "T1", null);
+        assertTriggerPrivilege(true, users[1], "S1", "T1");
+        assertTriggerPrivilege(false, users[2], "S1", "T1");
+       
+        revoke("update", "s1", "t1", users[1]);
+        assertUpdatePrivilege(false, users[1], "S1", "T1", new String[] {"C1"});  
+        assertUpdatePrivilege(false, users[1], "S1", "T1", null);
+     
+        revoke("all privileges", "s1", "t1", users[1]);
+        assertAllPrivileges(false, users[1], "S1", "T1", null);
+    }
+    
+    public void testRevokeExecutePrivileges() throws Exception {
+    	grant("execute", "function s1", "f1", new String[] {users[1], users[2]});
+        grant("execute", "procedure s1", "f1", users[1]);
+        assertFunctionPrivilege(true, users[1], "S1", "F1", false);
+        assertFunctionPrivilege(true, users[2], "S1", "F1", false);
+        assertProcedurePrivilege(true, users[1], "S1", "F1");
+        
+        revoke("execute", "function s1", "f1", users[1]);
+        assertFunctionPrivilege(false, users[1], "S1", "F1", false);
+        assertFunctionPrivilege(true, users[2], "S1", "F1", false);
+        assertProcedurePrivilege(true, users[1], "S1", "F1");
+        
+        grant("execute", "function s1", "f1", users[1]);
+        revoke("execute", "procedure s1", "f1", users[1]);
+        assertFunctionPrivilege(true, users[1], "S1", "F1", false);
+        assertFunctionPrivilege(true, users[2], "S1", "F1", false);
+        assertProcedurePrivilege(false, users[1], "S1", "F1");
+        
+    	revoke("execute", "function s1", "f1", new String[] {users[1], users[2]});
+        assertFunctionPrivilege(false, users[1], "S1", "F1", false);
+        assertFunctionPrivilege(false, users[2], "S1", "F1", false);
+        assertProcedurePrivilege(false, users[1], "S1", "F1");
+    }
+    
+    public void testRevokeWithPublicPrivilege() throws Exception {
+        grant("select, delete", "s2", "t1", "public");
+        grant("select, delete", "s2", "t1", new String[] {users[1], users[2]});
+        grant("update(c1,c3)", "s2", "t1", "public");
+        grant("update(c1,c3)", "s2", "t1", new String[] {users[1], users[2]});
+        assertSelectPrivilege( true, users[1], "S2", "T1", null);
+        assertSelectPrivilege( true, users[2], "S2", "T1", null);
+        assertSelectPrivilege( true, users[4], "S2", "T1", null);
+        assertDeletePrivilege( true, users[1], "S2", "T1");
+        assertDeletePrivilege( true, users[2], "S2", "T1");
+        assertDeletePrivilege( true, users[4], "S2", "T1");
+        assertUpdatePrivilege( true, users[1], "S2", "T1", new String[] {"C1", "C3"});
+        assertUpdatePrivilege( true, users[2], "S2", "T1", new String[] {"C1", "C3"});
+        assertUpdatePrivilege( true, users[4], "S2", "T1", new String[] {"C1", "C3"});
+
+        // revoke from user, should still be able to access via public privilege
+        revoke("select, update(c1,c3), delete", "S2", "T1", users[1]);
+        assertSelectPrivilege( true, users[1], "S2", "T1", null);
+        assertSelectPrivilege( true, users[2], "S2", "T1", null);
+        assertSelectPrivilege( true, users[4], "S2", "T1", null);
+        assertDeletePrivilege( true, users[1], "S2", "T1");
+        assertDeletePrivilege( true, users[2], "S2", "T1");
+        assertDeletePrivilege( true, users[4], "S2", "T1");
+        assertUpdatePrivilege( true, users[1], "S2", "T1", new String[] {"C1", "C3"});
+        assertUpdatePrivilege( true, users[2], "S2", "T1", new String[] {"C1", "C3"});
+        assertUpdatePrivilege( true, users[4], "S2", "T1", new String[] {"C1", "C3"});
+       
+        // now, revoke public permissions
+        revoke("select, update(c1,c3), delete", "S2", "t1", "public");
+        assertSelectPrivilege(false, users[1], "S2", "T1", null);
+        assertSelectPrivilege(true, users[2], "S2", "T1", null);
+        assertSelectPrivilege(false, users[4], "S2", "T1", null);
+        assertDeletePrivilege(false, users[1], "S2", "T1");
+        assertDeletePrivilege(true, users[2], "S2", "T1");
+        assertDeletePrivilege(false, users[4], "S2", "T1");
+        assertUpdatePrivilege(false, users[1], "S2", "T1", new String[] {"C1", "C3"});
+        assertUpdatePrivilege(true, users[2], "S2", "T1", new String[] {"C1", "C3"});
+        assertUpdatePrivilege(false, users[4], "S2", "T1", new String[] {"C1", "C3"});
+
+        // clean up
+        revoke("all privileges", "S2", "t1", users[2]);
+        assertAllPrivileges(false, users[2], "S2", "T1", null);
+     }
+    
+    public void testRevokeExecuteWithPublicPrivilege() throws Exception {
+        grant("execute", "function s1", "f1", "public");
+        grant("execute", "function s1", "f1", new String[] {users[1], users[2]});
+        assertFunctionPrivilege(true, users[1], "S1", "F1", false);
+        assertFunctionPrivilege(true, users[2], "S1", "F1", false);
+        assertFunctionPrivilege(true, users[4], "S1", "F1", false);
+        
+        //revoke from user, should still be able to execute through public privilege
+        revoke("execute", "function s1", "f1", users[1]);
+        assertFunctionPrivilege(true, users[1], "S1", "F1", false);
+        assertFunctionPrivilege(true, users[2], "S1", "F1", false);
+        assertFunctionPrivilege(true, users[4], "S1", "F1", false);
+   
+        revoke("execute", "function s1", "f1", "public");
+        assertFunctionPrivilege(false, users[1], "S1", "F1", false);
+        assertFunctionPrivilege(true, users[2], "S1", "F1", false);
+        assertFunctionPrivilege(false, users[4], "S1", "F1", false);
+        
+        // clean up
+        revoke("execute", "function s1", "t1", users[2]);
+        assertFunctionPrivilege(false, users[2], "s1", "F1", false);
+    }
+    
+    public void testRevokeRollbackAndCommit() throws Exception {
+        
+    	// open a connection as database owner.
+    	Connection oc = openUserConnection(users[0]);
+    	oc.setAutoCommit(false);
+   	
+    	//set up some permissions
+        grant(oc, "select(c1,c2), update(c1), insert, delete", "s2", "t3", users[1]);
+        grant(oc, "select, references", "s2", "t3", users[2]);
+        grant(oc, "select", "s2", "t3", users[3]);
+        grant(oc, "execute", "procedure s1", "p1", users[1]);
+        oc.commit();
+        assertSelectPrivilege(true, users[1], "S2", "T3", new String[] { "C1", "C2"});
+        assertUpdatePrivilege(true, users[1], "S2", "T3", new String[] { "C1"});
+        assertInsertPrivilege(true, users[1], "S2", "T3", null);
+        assertDeletePrivilege(true, users[1], "S2", "T3");
+        assertSelectPrivilege(true, users[2], "S2", "T3", null);
+        assertReferencesPrivilege(true, users[2], "S2", "T3", null);
+        assertSelectPrivilege(true, users[3], "S2", "T3", null);
+        assertProcedurePrivilege(true, users[1], "S1", "P1");
+    	
+    	// revoke the privileges and verify they were revoked.
+        revoke(oc, "select(c2), update(c1), delete", "s2", "t3", users[1]);
+        revoke(oc, "select, references", "s2", "t3", users[2]);
+        revoke(oc, "select", "s2", "t3", users[3]);
+        revoke(oc, "execute", "procedure s1", "p1", users[1]);
+        // these asserts fail before rollback due to lock timeout
+        //assertSelectPrivilege(true, users[1], "S2", "T3", new String[] {"C1"});
+        //assertSelectPrivilege(false, users[1], "S2", "T3", new String[] {"C2", "C3"});
+        //assertUpdatePrivilege(false, users[1], "S2", "T3", new String[] {"C1"});
+        //assertInsertPrivilege(false, users[1], "S2", "T3", null);
+        //assertDeletePrivilege(false, users[1], "S2", "T3");
+        //assertSelectPrivilege(false, users[2], "S2", "T3", null);
+        //assertReferencesPrivilege(false, users[2], "S2", "T3", null);
+        //assertSelectPrivilege(false, users[3], "S2", "T3", null);
+        //assertProcedurePrivilege(false, users[1], "S1", "P1");
+
+        // rollback and verify that we have them again.
+        oc.rollback();
+        assertSelectPrivilege(true, users[1], "S2", "T3", new String[] {"C1", "C2"});
+        assertUpdatePrivilege(true, users[1], "S2", "T3", new String[] {"C1"});
+        assertInsertPrivilege(true, users[1], "S2", "T3", null);
+        assertDeletePrivilege(true, users[1], "S2", "T3");
+        assertSelectPrivilege(true, users[2], "S2", "T3", null);
+        assertReferencesPrivilege(true, users[2], "S2", "T3", null);
+        assertSelectPrivilege(true, users[3], "S2", "T3", null);
+        assertProcedurePrivilege(true, users[1], "S1", "P1");
+        
+    	// revoke again, verify they were revoked.
+        revoke(oc, "select(c2), update(c1), delete", "s2", "t3", users[1]);
+        revoke(oc, "select, references", "s2", "t3", users[2]);
+        revoke(oc, "select", "s2", "t3", users[3]);
+        revoke(oc, "execute", "procedure s1", "p1", users[1]);
+        // these asserts fail before commit due to lock timeout
+        //assertSelectPrivilege(false, users[1], "S2", "T3", new String[] {"C1", "C2"});
+        //assertUpdatePrivilege(false, users[1], "S2", "T3", new String[] {"C1"});
+        //assertInsertPrivilege(false, users[1], "S2", "T3", null);
+        //assertDeletePrivilege(false, users[1], "S2", "T3");
+        //assertSelectPrivilege(false, users[2], "S2", "T3", null);
+        //assertReferencesPrivilege(false, users[2], "S2", "T3", null);
+        //assertSelectPrivilege(false, users[3], "S2", "T3", null);
+        //assertProcedurePrivilege(false, users[1], "S1", "P1");
+        
+        //commit and verify again
+        oc.commit();
+        oc.setAutoCommit(true);
+        assertSelectPrivilege(false, users[1], "S2", "T3", new String[] {"C1", "C2"});
+        assertUpdatePrivilege(false, users[1], "S2", "T3", new String[] {"C1"});
+        assertInsertPrivilege(true, users[1], "S2", "T3", null);
+        assertDeletePrivilege(false, users[1], "S2", "T3");
+        assertSelectPrivilege(false, users[2], "S2", "T3", null);
+        assertReferencesPrivilege(false, users[2], "S2", "T3", null);
+        assertSelectPrivilege(false, users[3], "S2", "T3", null);
+        assertProcedurePrivilege(false, users[1], "S1", "P1");
+        
+    }
+    
+    /*
+     * TODO - write tests for abandoned views / triggers / constraints
+     * 
+    public void testAbandonedView() {
+    	//TODO
+    }
+    
+    public void testAbandonedTrigger() {
+        //TODO	
+    }
+
+    public void testAbandonedConstraint() {
+    	//TODO
+    }
+    */
+    
+    /*
+     * TODO - write more extensive tests for DatabaseMetaData.getColumnPrivileges()
+     *        and DatabaseMetaData.getTablePrivileges(). Specifically,
+     *        would be nice to have some test cases which verify grantor
+     *         and is_grantable are valid.
+     */
+    
+    /* Begin standard error cases */
+    
+    public void testInvalidGrantAction() throws Exception {
+    	try {
+            grant("xx", "s1", "t1", users[1]);
+    	} catch (SQLException e) {
+        	assertSQLState("42X01", e);
+        }
+    }
+
+    public void testInvalidReservedWordAction() throws Exception {
+    	try {
+            grant("between", "s1", "t1", users[1]);
+    	} catch (SQLException e) {
+            	assertSQLState("42X01", e);
+    	}
+
+    	try {
+            runSQLCommands("grant select on schema t1 to " + users[1]);
+    	} catch (SQLException e) {
+            	assertSQLState("42X01", e);
+    	}
+    	
+    	try {
+            runSQLCommands("grant select on decimal t1 to " + users[1]);
+    	} catch (SQLException e) {
+            	assertSQLState("42X01", e);
+    	} 
+    }
+
+    public void testGrantOnNonexistantColumn() throws Exception {
+    	try {
+            grant("select(nosuchCol)", "s1", "t1", users[1]);
+    	} catch (SQLException e) {
+        	assertSQLState("42X14", e);
+        }
+    }
+    
+    public void testGrantOnNonexistantSchema() throws Exception {
+    	try {
+            grant("select", "nosuch", "t1", users[1]);
+    	} catch (SQLException e) {
+        	assertSQLState("42Y07", e);
+        }
+    }
+    
+    public void testGrantOnNonexistantTable() throws Exception {
+    	try {
+            grant("select(nosuchCol)", "s1", "nosuch", users[1]);
+    	} catch (SQLException e) {
+        	assertSQLState("42X05", e);
+        }
+    }
+
+    public void testGrantOnFunctionWithBadSchema() throws Exception {
+    	try {
+            grant("execute", "function nosuch", "f0", users[1]);
+    	} catch (SQLException e) {
+        	assertSQLState("42Y07", e);
+        }
+    }
+    
+    public void testGrantOnNonexistantFunction() throws Exception {
+    	try {
+            grant("execute", "function s1", "nosuch", users[1]);
+    	} catch (SQLException e) {
+        	assertSQLState("42Y03", e);
+        }
+    }
+    
+    public void testGrantOnNonexistantFunctionForProcedure() throws Exception {
+    	try {
+            grant("execute", "function s1", "p1", users[1]);
+    	} catch (SQLException e) {
+        	assertSQLState("42Y03", e);
+        }
+    }
+    
+    public void testGrantOnProcedureWithBadSchema() throws Exception {
+    	try {
+            grant("execute", "procedure nosuch", "f0", users[1]);
+    	} catch (SQLException e) {
+        	assertSQLState("42Y07", e);
+        }
+    }
+    
+    public void testGrantOnNonexistantProcedure() throws Exception {
+    	try {
+            grant("execute", "procedure s1", "nosuch", users[1]);
+    	} catch (SQLException e) {
+        	assertSQLState("42Y03", e);
+        }
+    }
+    
+    public void testGrantOnNonexistantProcedureForFunction() throws Exception {
+    	try {
+            grant("execute", "procedure s1", "f2", users[1]);
+    	} catch (SQLException e) {
+        	assertSQLState("42Y03", e);
+        }
+    }
+    
+    public void testGrantExecuteOnTable() throws Exception {
+    	try {
+            runSQLCommands("grant execute on table s1.t1 to " + users[1]);
+    	} catch (SQLException e) {
+        	assertSQLState("42X01", e);
+        }
+    }
+    
+    public void testGrantSelectOnRoutine() throws Exception {
+    	try {
+            runSQLCommands("grant select on function s1.f1 to " + users[1]);
+    	} catch (SQLException e) {
+        	assertSQLState("42X01", e);
+        }
+    	
+    	try {
+            runSQLCommands("grant select on procedure s1.p1 to " + users[1]);
+    	} catch (SQLException e) {
+        	assertSQLState("42X01", e);
+        }
+    }
+    
+    public void testGrantExecuteWithRestrict() throws Exception {
+    	// restrict invalid in grant statement
+    	try {
+            runSQLCommands("grant execute on function s1.f1 to " + users[1] + " restrict");
+    	} catch (SQLException e) {
+        	assertSQLState("42X01", e);
+        }
+    }
+    
+    public void testGrantRevokeWithoutRestrict() throws Exception {
+    	// restrict invalid in grant statement
+    	try {
+            runSQLCommands("revoke execute on function s1.f1 from " + users[0]);
+    	} catch (SQLException e) {
+        	assertSQLState("42X01", e);
+        }
+    }
+    
+    public void testGrantRevokeSelectWithRestrict() throws Exception {
+    	// restrict invalid in grant statement
+    	try {
+            runSQLCommands("revoke select on s1.t1 from " + users[0] + " restrict");
+    	} catch (SQLException e) {
+        	assertSQLState("42X01", e);
+        }
+    }
+    
+    public void testGrantDeleteWithColumnList() throws Exception {
+    	try {
+    		grant("delete(c1)", "s1", "t1", users[1]);
+    	} catch (SQLException e) {
+    		assertSQLState("42X01", e);
+    	}
+    }
+    
+    public void testGrantTriggerWithColumnList() throws Exception {
+    	try {
+    		grant("trigger(c1)", "s1", "t1", users[1]);
+    	} catch (SQLException e) {
+    		assertSQLState("42X01", e);
+    	}
+    }
+    
+    /* End standard error cases */
+    
+    /* Begin testcases from grantRevokeDDL */
+
+    public void testOtherUserCannotRevokeOwnerPrivileges() throws SQLException {
+    	grant("select", "s1", "t1", "public");
+    	grant("insert", "s1", "t1", users[1]);
+    	grant("update", "s1", "t1", users[1]);
+    	grant("delete", "s1", "t1", users[1]);
+    	grant("update(c1)", "s1", "t1", users[2]);
+        try {
+        	revoke(users[2], "select", "s1", "t1", "public");
+        } catch (SQLException e) {
+        	assertSQLState("2850C", e);
+        }
+        try {
+        	revoke(users[2], "select", "s1", "t1", users[0]);
+        } catch (SQLException e) {
+        	assertSQLState("2850F", e);
+        }
+        try {
+        	revoke(users[2], "insert", "s1", "t1", users[1]);
+        } catch (SQLException e) {
+        	assertSQLState("2850C", e);
+        }
+        try {
+        	revoke(users[2], "update(c1)", "s1", "t1", users[2]);
+        } catch (SQLException e) {
+        	assertSQLState("2850C", e);
+        }
+    }
+    
+    /* End testcases from grantRevokeDDL */
+    
+    /* Begin utility methods specific to grant / revoke */
+    
+    /**
+     * Grant a single permission to a single user.
+     * Utility method that takes a single string for user instead
+     * of an array of Strings.
+     * 
+     * @param perm Permission to grant
+     * @param schema Schema on which to grant permission
+     * @param table Table on which to grant permission
+     * @param user User to grant permission to
+     * @throws Exception throws all exceptions
+     */
+	void grant(String perm, String schema, String table, String user) throws SQLException {
+		grant(perm, schema, table, new String[] {user});
+	}
+	
+    /**
+     * Grant a single permission from a specific user to a single user.
+     * Utility method that takes a single string for user instead
+     * of an array of Strings.
+     * 
+     * @param grantor Grantor of permission
+     * @param perm Permission to grant
+     * @param schema Schema on which to grant permission
+     * @param table Table on which to grant permission
+     * @param user User to grant permission to
+     * @throws Exception throws all exceptions
+     */
+	void grant(String grantor, String perm, String schema, String table, String user) throws SQLException {
+		Connection c = openUserConnection(grantor);
+		grant(c, perm, schema, table, user);
+		c.close();
+	}
+	
+	/**
+	 * Grant a SQL permission to a set of users.
+	 * 
+	 * @param perm The permission to grant
+	 * @param schema the schema on which to grant the permission
+	 * @param table the table on which to grant the permission
+	 * @param users an Array of users to grant the permission
+	 * @throws Exception throws all exceptions
+	 */
+	void grant(String perm, String schema, String table, String[] users) throws SQLException {
+		StringBuffer command = new StringBuffer("grant " + perm + " on " + schema + "." + table + " to " + users[0]);
+		for (int i = 1; i < users.length; i++ ) {
+		    command.append("," + users[i]);
+		}
+        
+		// default connection is for database owner.
+		Statement s = getConnection().createStatement();
+		s.executeUpdate(command.toString());
+		s.close();
+	}
+	
+    /**
+     * Grant a single permission to a single user for a given connection.
+     * Callers of this method should ensure that they close the Connection
+     * that is passed in. Used primarily in rollback tests where we want to ensure
+     * the grant/revoke statements are being called by the database owner.
+     * 
+     * @param c the Connection used to execute the grant statement
+     * @param perm Permission to grant
+     * @param schema Schema on which to grant permission
+     * @param table Table on which to grant permission
+     * @param user User to grant permission to
+     * @throws Exception throws all exceptions
+     */
+	void grant(Connection c, String perm, String schema, String table, String user) throws SQLException {
+		Statement s = c.createStatement();
+		s.executeUpdate("grant " + perm + " on " + schema + "." + table + " to " + user);
+		s.close();
+	}
+	
+    /**
+     * Revoke a single permission from a single user.
+     * Utility method that takes a single string for user instead
+     * of an array of Strings.
+     * 
+     * @param perm Permission to revoke
+     * @param schema Schema on which to revoke permission
+     * @param table Table on which to revoke permission
+     * @param user User to revoke permissions
+     * @throws Exception throws all exceptions
+     */
+	void revoke(String perm, String schema, String table, String user) throws SQLException {
+		revoke(perm, schema, table, new String[] {user});
+	}
+	
+
+    /**
+     * Revoke a single permission from a specific user to a single user.
+     * Utility method that takes a single string for user instead
+     * of an array of Strings.
+     * 
+     * @param revoker Grantor of permission
+     * @param perm Permission to revoke
+     * @param schema Schema on which to revoke permission
+     * @param table Table on which to revoke permission
+     * @param user User to revoke permission to
+     * @throws Exception throws all exceptions
+     */
+	void revoke(String revoker, String perm, String schema, String table, String user) throws SQLException {
+		Connection c = openUserConnection(revoker);
+		revoke(c, perm, schema, table, user);
+		c.close();
+	}
+	
+	/**
+	 * Revoke a SQL permission from a set of users.
+	 * 
+	 * @param perm The permission to revoke
+	 * @param schema the schema on which to revoke the permission
+	 * @param table the table on which to revoke the permission
+	 * @param users an array of users to revoke the permission
+	 * @throws Exception throws all exceptions
+	 */
+	void revoke(String perm, String schema, String table, String[] users) throws SQLException {
+		StringBuffer command = new StringBuffer("revoke " + perm + " on " + schema + "." + table + " from " + users[0]);
+		for (int i = 1; i < users.length; i++ ) {
+		    command.append("," + users[i]);
+		}
+		//add restrict to revoke execute... 
+		if (perm.equalsIgnoreCase("execute"))
+			command.append(" restrict");
+		try {
+			runSQLCommands(command.toString());
+		} catch (UnsupportedEncodingException e) {
+			fail(e.getMessage());
+		}
+	}
+
+    /**
+     * Revoke a single permission to a single user for a given connection.
+     * Callers of this method should ensure that they close the Connection
+     * that is passed in. Used primarily in rollback tests where we want to ensure
+     * the grant/revoke statements are being called by the database owner.
+     * 
+     * @param c the connection to execute the revoke statement
+     * @param perm Permission to revoke
+     * @param schema Schema on which to revoke permission
+     * @param table Table on which to revoke permission
+     * @param user User to revoke permission
+     * @throws Exception throws all exceptions
+     */
+	void revoke(Connection c, String perm, String schema, String table, String user) throws SQLException {
+		Statement s = c.createStatement();
+		s.execute("revoke " + perm + " on " + schema + "." + table + " from " + user + (perm.equalsIgnoreCase("execute") ? " restrict" : ""));
+		s.close();
+	}
+	
+    /* End utility methods specific to grant / revoke */
+	
+	/**
+	 * Utility function to test grant/revoke
+	 * @return 1
+	 */
+    public static int s1F1()
+    {
+        return 1;
+    }
+    
+    /**
+     * Another utility function to test grant/revoke - placeholder for
+     * future if SPECIFIC names are implemented.
+     * @return 1
+     */
+    public static int s2F1a()
+    {
+        return 1;
+    }
+    
+    /**
+     * Another utility function to test grant/revoke
+     * @return 1
+     */
+    public static int s2F2()
+    {
+        return 1;
+    }
+    
+    /**
+     * Utility method to test procedure with identical name to a
+     * function
+     */
+    public static void s1F1P( )
+    {
+    }
+    
+    /**
+     * A utility method to test procedures with test grant/revoke
+     */
+    public static void s1P1( )
+    {
+    }
+    
+    /* 
+     * public methods for asserting privileges begin here
+     * May move to BaseJDBCTestCase if appropriate.
+     */
+    
+
+    /**
+     * Assert all privileges for a given user / schema / table / column set
+     * 
+     * @param hasPrivilege whether we expect the given user to have the privilege
+     * @param user the user to check
+     * @param schema the schema to check
+     * @param table the table to check
+     * @param columns the set of columns to check for the user
+     */
+    public void assertAllPrivileges(boolean hasPrivilege, String user, String schema, String table, String[] columns) throws SQLException {
+    	assertSelectPrivilege(hasPrivilege, user, schema, table, columns);
+    	assertDeletePrivilege(hasPrivilege, user, schema, table);
+    	assertInsertPrivilege(hasPrivilege, user, schema, table, columns);
+    	assertUpdatePrivilege(hasPrivilege, user, schema, table, columns);
+    	assertReferencesPrivilege(hasPrivilege, user, schema, table, columns);
+       	assertTriggerPrivilege(hasPrivilege, user, schema, table);
+    }
+    
+    
+    /**
+     * Assert that a user has select privilege on a given table / column
+     * @param hasPrivilege whether or not the user has the privilege
+     * @param user the user to check
+     * @param schema the schema to check
+     * @param table the table to check
+     * @param columns the set of columns to check
+     * @throws SQLException throws all exceptions
+     */
+    public void assertSelectPrivilege(boolean hasPrivilege, String user, String schema, String table, String[] columns) throws SQLException{
+    	Connection c = openUserConnection(user);
+    	
+    	Statement s = c.createStatement();
+    	try {
+    	    boolean b = s.execute("select " + columnListAsString(columns) + " from " + schema + "." + table);
+    	    if (hasPrivilege)
+    	    {
+    	        assertEquals(true, b);
+    	    }
+    	} catch (SQLException e) {
+    		if (!hasPrivilege) {
+    			assertSQLState("28508", e);
+    		} else {
+    			e.printStackTrace();
+    			fail("Unexpected lack of select privilege.");
+    		}
+    	}
+        s.close();
+    	c.close();
+    	
+    	assertPrivilegeMetadata(hasPrivilege, "SELECT", user, schema, table, columns);
+    }
+    
+    /**
+     * Assert that a user has delete privilege on a given table / column
+     * @param hasPrivilege whether or not the user has the privilege
+     * @param user the user to check
+     * @param schema the schema to check
+     * @param table the table to check
+     * @throws SQLException throws all exceptions
+     */
+    public void assertDeletePrivilege(boolean hasPrivilege, String user, String schema, String table) throws SQLException {
+    	Connection c = openUserConnection(user);
+    	
+    	Statement s = c.createStatement();
+    	try {
+    	    boolean b = s.execute("delete from " + schema + "." + table);
+    	    if (hasPrivilege)
+    	    {
+    	        // b is false if no rows updated.
+    	    }
+    	} catch (SQLException e) {
+    		if (!hasPrivilege) {
+    			assertSQLState("28506", e);
+    		} else {
+    			fail("Unexpected lack of delete privilege.");
+    			e.printStackTrace();
+    		}
+    	}
+    	s.close();
+    	c.close();
+    	
+    	assertPrivilegeMetadata(hasPrivilege, "DELETE", user, schema, table, null);
+    }
+    
+    /**
+     * Assert that a user has insert privilege on a given table / column
+     * @param hasPrivilege whether or not the user has the privilege
+     * @param user the user to check
+     * @param schema the schema to check
+     * @param table the table to check
+     * @param columns the set of columns to check
+     * @throws SQLException throws all exceptions
+     */
+    public void assertInsertPrivilege(boolean hasPrivilege, String user, String schema, String table, String[] columns) throws SQLException {
+   	
+    	// NOTE - getColumns returns empty result set if schema / table names not capitalized.
+        // TODO - should implement asserting insert privilege on a subset of columns at some point
+    	
+    	Connection c = openUserConnection(users[0]);
+
+    	Statement s = c.createStatement();
+    	try {
+    		StringBuffer command = new StringBuffer("insert into " + schema + "." + table + " values (");
+    		ResultSet rs = c.getMetaData().getColumns( null, schema, table, null);
+            boolean first = true;
+            while(rs.next())
+            {
+                if(first)
+                    first = false;
+                else
+                    command.append(",");
+                appendColumnValue(command, rs.getInt(5));
+            }
+            rs.close();
+            command.append(")");
+    	    int i = s.executeUpdate(command.toString());
+    	    if (hasPrivilege)
+    	    {
+    	        assertEquals(1, i);
+    	    }
+    	} catch (SQLException e) {
+    		if (!hasPrivilege) {
+    			assertSQLState("28508", e);
+    		} else {
+    			fail("Unexpected lack of insert privilege on " + schema + "." + table + " by " + user);
+    		}
+    	}
+    	s.close();
+    	c.close();
+    	
+    	assertPrivilegeMetadata(hasPrivilege, "INSERT", user, schema, table, columns);
+    }
+    
+    /**
+     * Assert that a user has update privilege on a given table / column
+     * 
+     * @param hasPrivilege whether or not the user has the privilege
+     * @param user the user to check
+     * @param schema the schema to check
+     * @param table the table to check
+     * @param columns the set of columns to check
+     * @throws SQLException throws all exceptions
+     */
+    public void assertUpdatePrivilege(boolean hasPrivilege, String user, String schema, String table, String[] columns) throws SQLException {
+ 
+    	String[] checkColumns = (columns == null) ? getAllColumns(schema, table) : columns;
+    	Connection c = openUserConnection(user);
+    	
+    	Statement s = c.createStatement();
+    	int columnCount = 0;
+    	boolean checkCount;
+    	for (int i = 0; i < checkColumns.length; i++) {
+    		checkCount = false;
+  
+    		try {
+    			// if possible, get count of rows to verify update rows
+    			try {
+    				ResultSet countRS = s.executeQuery("select count(" + checkColumns[i] +") from " + schema + "." + table);
+    			    if (!countRS.next()) {
+    				    fail("Could not get count on " + checkColumns[i] + " to verify update");
+    			    }
+    			    columnCount = countRS.getInt(1);
+    			    checkCount = true;
+    			} catch (SQLException e) {
+                    // may not have select privilege on the column, in
+    				// which case, we simply don't verify the count.
+    			}
+    			
+    			StringBuffer command = new StringBuffer("update " + schema + "." + table + " set " + checkColumns[i] + "=");
+        		ResultSet rs = c.getMetaData().getColumns( (String) null, schema, table, checkColumns[i]);
+                if (!rs.next())
+                {
+                	fail("Could not get column metadata for " + checkColumns[i]);
+                }
+                appendColumnValue(command, rs.getInt( 5));
+                rs.close();
+        	    int actualCount = s.executeUpdate(command.toString());
+        	    if (hasPrivilege && checkCount)
+        	    {
+        	    	// update count should equal select count
+        	        assertEquals(columnCount, actualCount);
+        	    }
+    		} catch (SQLException e) {
+        		if (!hasPrivilege) {
+        			assertSQLState("28508", e);
+        		} else {
+        			e.printStackTrace();
+        			fail("Unexpected lack of privilege to update on " + schema + "." + table + " by " + user);
+        		}
+        	}
+        }
+
+        s.close();
+    	c.close();
+    	
+    	assertPrivilegeMetadata(hasPrivilege, "UPDATE", user, schema, table, columns);
+    }
+    
+    /**
+     * Assert that a user has references privilege on a given table / column
+     * 
+     * @param hasPrivilege whether or not the user has the privilege
+     * @param user the user to check
+     * @param schema the schema to check
+     * @param table the table to check
+     * @param columns the set of columns to check
+     * @throws SQLException throws all exceptions
+     */
+    public void assertReferencesPrivilege(boolean hasPrivilege, String user, String schema, String table, String[] columns) throws SQLException {
+
+    	assertPrivilegeMetadata(hasPrivilege, "REFERENCES", user, schema, table, columns);
+    	
+    	/* no way to empirically test any arbitrary column can be
+    	 * referenced, as the column that is to be referenced must be
+    	 * a primary key or a unique constraint. Leaving this here, as it
+    	 * might form the useful basis of another assert method for cases
+    	 * where we know this to be certain.
+    	
+    	Connection c = openUserConnection(user);
+    	Statement s = c.createStatement();
+        for (int i = 0; i < columns.length; i++) {
+            // if it works, need to assert this as false.
+        	boolean b = true;
+        	try {
+        	    if (columns == null) {
+            	    b = s.execute("create table referencestest (c1 " + getColumnDataType(schema, table, columns[i]) + " references " + schema + "." + table + ")" );
+        	    } else {
+        	        b = s.execute("create table referencestest (c1 " + getColumnDataType(schema, table, columns[i]) + " references " + schema + "." + table + "(" + column + "))" );
+        	    }
+        	} catch (SQLException e) {
+        		if (!hasPrivilege) {
+        			assertSQLState("28508", e);
+        		} else {
+        			e.printStackTrace();
+        			fail("Unexpected lack of references privilege");
+        		}
+        	}
+        	// no rows updated, so false.
+        	assertFalse(b);
+        	s.execute("drop table referencestest");   	
+        }
+        s.close();
+        c.close();
+        */
+    }
+    
+    /**
+     * Assert that a user has trigger execute privilege on a given table / column
+     * @param hasPrivilege whether or not the user has the privilege
+     * @param user the user to check
+     * @param schema the schema to check
+     * @param table the table to check
+     * @throws SQLException throws all exceptions
+     */
+    public void assertTriggerPrivilege(boolean hasPrivilege, String user, String schema, String table) throws SQLException {
+    	
+    	Connection c = openUserConnection(user);
+    	c.setAutoCommit(false);
+    	
+    	Statement s = c.createStatement();
+    	try {
+    	    int i = s.executeUpdate("create trigger \"" + table + "Trig\" after insert on " +
+    	    		              schema + "." + table + " for each row values 1");
+    	    if (hasPrivilege)
+    	    {
+    	        assertEquals(0, i); 
+    	    }
+    	} catch (SQLException e) {
+    		if (!hasPrivilege) {
+    			assertSQLState("28506", e);
+    		} else {
+    			e.printStackTrace();
+    			fail("Unexpected lack of trigger privilege on " + schema + "." + table + " by " + user);
+    		}
+    	}
+    	
+    	c.rollback();
+        s.close();
+    	c.close();
+
+    	assertPrivilegeMetadata(hasPrivilege, "TRIGGER", user, schema, table, null);
+ 
+    }
+    
+    /**
+     * Assert that a user has function execute privilege on a given table / column
+     * 
+     * @param hasPrivilege whether or not the user has the privilege
+     * @param user the user to check
+     * @param schema the schema to check
+     * @param function the function to check
+     * @param forProcedure true if checking for lack of function execute privilege against procedure of same name.
+     * @throws SQLException throws all exceptions
+     */
+    public void assertFunctionPrivilege(boolean hasPrivilege, String user, String schema, String function, boolean forProcedure) throws SQLException {
+        Connection c = openUserConnection(user);
+        
+        String functioncall = "values " + schema + "." + function + "()";
+ 
+	    PreparedStatement ps = null;
+	    ResultSet rs = null;
+		try {
+		    ps = c.prepareStatement(functioncall);
+		    rs = ps.executeQuery();
+		} catch (SQLException e) {
+			if (!hasPrivilege){
+				if (forProcedure) 
+					assertSQLState("42Y03", e);
+				else 
+   				    assertSQLState("2850A", e);
+			} else {
+				e.printStackTrace();
+				fail("Unexpected lack of function execute privilege");
+			}
+		}
+		if (ps != null)
+			ps.close();
+		if (rs != null)
+			rs.close();
+    }
+    
+    /**
+     * Assert that a user has procedure execute privilege on a given table / column
+     * 
+     * @param hasPrivilege whether or not the user has the privilege
+     * @param user the user to check
+     * @param schema the schema to check
+     * @param table the table to check
+     * @throws SQLException throws all exceptions
+     */
+    public void assertProcedurePrivilege(boolean hasPrivilege, String user, String schema, String procedure) throws SQLException {
+        Connection c = openUserConnection(user);
+        
+        String procedurecall = "call " + schema + "." + procedure + "()";
+        
+		PreparedStatement ps = c.prepareStatement(procedurecall);
+		ResultSet rs = null;
+		try {
+			ps.execute();
+			rs = ps.getResultSet();
+		} catch (SQLException e) {
+			if (!hasPrivilege)
+				assertSQLState("2850A", e);
+			else {
+				e.printStackTrace();
+				fail("Unexpected lack of procedure execute privilege.");
+			}
+		}
+		ps.close();
+		if (rs != null)
+		{
+				rs.close();
+		}
+    }
+    
+    /**
+     * Assert that a specific privilege exists by checking the
+     * database metadata available to a user
+     * 
+     * @param hasPrivilege true if we expect the caller to have the privilege
+     * @param type type of privilege, e.g. SELECT, INSERT, DELETE, etc.
+     * @param user the user to check
+     * @param schema the schema to check
+     * @param table the table to check
+     * @param columns the set of columns to check, or all columns if null
+     * @throws SQLException
+     */
+    public void assertPrivilegeMetadata(boolean hasPrivilege, String type, String user, String schema, String table, String[] columns) throws SQLException {
+        
+    	Connection c = openUserConnection(user);
+    	DatabaseMetaData dm = c.getMetaData();
+    	ResultSet rs = dm.getTablePrivileges(null, schema.toUpperCase(), table.toUpperCase());
+     	boolean found = false;
+    	
+    	// check getTablePrivileges
+    	if (columns == null) {
+        	while (rs.next())
+        	{
+          	    if (rs.getString(6).equals(type)) {
+        	    	String privUser = rs.getString(5);
+        	    	if (privUser.equals(user) || privUser.equals("PUBLIC")) {
+        	    		found = true;
+        	    	}
+        	    }
+        	}
+        	assertEquals(hasPrivilege, found);
+        	rs.close();
+    	}
+
+    	// check getColumnPrivileges()
+    	ResultSet cp = null;
+    	if (columns == null) {
+    		/*
+    		 * Derby does not record table level privileges in SYSCOLPERMS,
+    		 * so the following does not work. If it is ever changed so that
+    		 * getColumnPrivileges returns proper results for table level privileges,
+    		 * this can be reenabled.
+    		 * 
+        	ResultSet cols = dm.getColumns(null, schema.toUpperCase(), table.toUpperCase(), null);
+			int foundCount = 0;
+			int colCount = 0;
+        	while (cols.next())
+        	{
+				colCount++;
+    			String col = cols.getString(4);
+    			//System.out.println("getting column privs for " + col);
+    			cp = dm.getColumnPrivileges(null, schema.toUpperCase(), table.toUpperCase(), col);
+
+    			while (cp.next()) {
+
+					//System.out.println(schema + "." + table + ": "
+					//		+ cp.getString(4) + ", " + cp.getString(5) + ", "
+					//		+ cp.getString(6) + ", " + cp.getString(7));
+					if (cp.getString(7).equals(type)) {
+						String privUser = cp.getString(6);
+						if (privUser.equals(user) || privUser.equals("PUBLIC")) {
+							foundCount++;
+						}
+					}
+				}
+        	}
+			if (hasPrivilege) {
+				assertEquals(colCount, foundCount);
+			} else {
+			    assertFalse(colCount == foundCount);
+			}
+			*/
+    	} else {
+    		// or, check the given columns
+    		for (int i = 0; i < columns.length; i++) {
+    			cp = dm.getColumnPrivileges(null, schema.toUpperCase(), table.toUpperCase(), columns[i].toUpperCase());
+    			found = false;
+    			while (cp.next()) {
+					if (cp.getString(7).equals(type)) {
+						String privUser = cp.getString(6);
+						if (privUser.equals(user) || privUser.equals("PUBLIC")) {
+							found = true;
+						}
+					}
+    			}
+    			if (hasPrivilege)
+    				assertTrue(found);
+    		}        	
+    	}
+		if (cp != null)
+			cp.close();
+    	
+    	c.close();
+    }
+    
+    /* End assert methods */
+    
+    /* Begin helper methods */
+    
+    /**
+     * Append a particular SQL datatype value to the given StringBuffer
+     * 
+     * @param sb the StringBuffer to append the value
+     * @param type the java.sql.Types value to append
+     */
+    static void appendColumnValue(StringBuffer sb, int type)
+    {
+        switch(type)
+        {
+        case Types.BIGINT:
+        case Types.DECIMAL:
+        case Types.DOUBLE:
+        case Types.FLOAT:
+        case Types.INTEGER:
+        case Types.NUMERIC:
+        case Types.REAL:
+        case Types.SMALLINT:
+        case Types.TINYINT:
+            sb.append("0");
+            break;
+
+        case Types.CHAR:
+        case Types.VARCHAR:
+            sb.append("' '");
+            break;
+
+        case Types.DATE:
+            sb.append("CURRENT_DATE");
+            break;
+
+        case Types.TIME:
+            sb.append("CURRENT_TIME");
+            break;
+
+        case Types.TIMESTAMP:
+            sb.append("CURRENT_TIMESTAMP");
+            break;
+
+        default:
+            sb.append("null");
+            break;
+        }
+    }
+    
+    /**
+     * Return the given String array as a comma separated String
+     * 
+     * @param columns an array of columns to format
+     * @return a comma separated String of the column names
+     */
+    static String columnListAsString(String[] columns) {
+    	if (columns == null) {
+    		return "*";
+    	}
+    	
+    	StringBuffer sb = new StringBuffer(columns[0]);
+		for (int i = 1; i < columns.length; i++ ) {
+		    sb.append("," + columns[i]);
+		}
+		return sb.toString();
+    }
+    
+    /**
+     * Get all the columns in a given schema / table
+     * 
+     * @return an array of Strings with the column names
+     * @throws SQLException
+     */    
+    String[] getAllColumns(String schema, String table) throws SQLException
+    {
+    	DatabaseMetaData dbmd = getConnection().getMetaData();
+        ArrayList columnList = new ArrayList();
+        ResultSet rs = dbmd.getColumns( (String) null, schema, table, (String) null);
+        while(rs.next())
+        {
+            columnList.add(rs.getString(4));
+        }
+          
+        return (String[]) columnList.toArray(new String[0]);
+    }
+    
+    /**
+     * Given a schema, table, and column as Strings, return the datatype of
+     * the column as a String.
+     * 
+     * @param schema the schema for the table in which the column resides
+     * @param table the table containing the column to check
+     * @param column the column to get the data type as a String
+     * @return the Type of the column as a String
+     * @throws SQLException
+     */
+    String getColumnDataType(String schema, String table, String column) throws SQLException {
+    	DatabaseMetaData dm = getConnection().getMetaData();
+    	ResultSet rs = dm.getColumns(null, schema, table, column);
+    	
+    	int type = 0;
+    	while (rs.next()) {
+    		type = rs.getInt(5);
+    	}
+    	rs.close();
+    	return JDBC.sqlNameFromJdbc(type);
+    }
+}

Propchange: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/GrantRevokeTest.java
------------------------------------------------------------------------------
    svn:eol-style = native

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/_Suite.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/_Suite.java?view=diff&rev=521432&r1=521431&r2=521432
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/_Suite.java (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/_Suite.java Thu Mar 22 13:09:44 2007
@@ -62,6 +62,7 @@
 
         suite.addTest(CreateTableFromQueryTest.suite());
         suite.addTest(DatabaseClassLoadingTest.suite());
+        suite.addTest(GrantRevokeTest.suite());
         suite.addTest(GroupByExpressionTest.suite());
 		suite.addTest(LangScripts.suite());
         suite.addTest(MathTrigFunctionsTest.suite());



Mime
View raw message