You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-commits@db.apache.org by fu...@apache.org on 2007/03/22 21:09:45 UTC
svn commit: r521432 - in
/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests:
master/ suites/ tests/lang/
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());