db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From fuzzylo...@apache.org
Subject svn commit: r532547 [2/3] - in /db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests: master/ master/j9_foundation/ master/jdk16/ suites/ tests/lang/
Date Thu, 26 Apr 2007 00:10:17 GMT
Added: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/GrantRevokeDDLTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/GrantRevokeDDLTest.java?view=auto&rev=532547
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/GrantRevokeDDLTest.java (added)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/GrantRevokeDDLTest.java Wed Apr 25 17:10:15 2007
@@ -0,0 +1,9791 @@
+/*
+
+Derby - Class org.apache.derbyTesting.functionTests.tests.lang.GrantRevokeDDLTest
+
+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.sql.CallableStatement;
+import java.sql.Connection;
+import java.sql.PreparedStatement;
+import java.sql.ResultSet;
+import java.sql.SQLException;
+import java.sql.SQLWarning;
+import java.sql.Statement;
+
+import junit.framework.Test;
+import junit.framework.TestSuite;
+
+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.SupportFilesSetup;
+import org.apache.derbyTesting.junit.TestConfiguration;
+
+
+public final class GrantRevokeDDLTest extends BaseJDBCTestCase {
+
+	private static String[] users = { "TEST_DBO", "george", "sam", 
+			"monica", "swiper", "sam", "satheesh", "bar",
+			"mamta4", "mamta3", "mamta2", "mamta1", "sammy",
+			"user5", "user4", "user3", "user2", "user1"
+	};
+	
+    /**
+     * Public constructor required for running test as standalone JUnit.
+     */
+    public GrantRevokeDDLTest(String name)
+    {
+        super(name);
+    }
+
+    public static Test suite()
+    {
+        TestSuite suite = new TestSuite(GrantRevokeDDLTest.class, "GrantRevokeDDL Test");
+	    Test test = new SupportFilesSetup(suite);
+	    test = new CleanDatabaseTestSetup(test);
+	    test = DatabasePropertyTestSetup.builtinAuthentication(
+				test, users, "grantrevokeddl");
+        test = TestConfiguration.sqlAuthorizationDecorator(test);
+        
+        return test;
+    }
+    
+    public void testGrantRevokeDDL() throws Exception
+    {
+        ResultSet rs = null;
+        SQLWarning sqlWarn = null;
+
+        CallableStatement cSt;
+        Statement st = createStatement();
+
+        String [][] expRS;
+        String [] expColNames;
+        
+        Connection satConnection = openUserConnection("satheesh");
+        Statement st_satConnection = satConnection.createStatement();
+        
+        // Test table privileges
+        
+        st = createStatement();
+        
+        st.executeUpdate("create schema authorization satheesh");
+        
+        st_satConnection.executeUpdate(
+            "create table satheesh.tsat(i int not null primary "
+            + "key, j int)");
+        
+        st_satConnection.executeUpdate(
+            " create index tsat_ind on satheesh.tsat(j)");
+        
+        st_satConnection.executeUpdate(
+            " create table satheesh.table1 (a int, b int, c char(10))");
+        
+        st_satConnection.executeUpdate(
+            " grant select on satheesh.tsat to public");
+        
+        st_satConnection.executeUpdate(
+            " grant insert on satheesh.tsat to foo");
+        
+        st_satConnection.executeUpdate(
+            " grant delete on satheesh.tsat to foo");
+        
+        st_satConnection.executeUpdate(
+            " grant update on satheesh.tsat to foo");
+        
+        st_satConnection.executeUpdate(
+            " grant update(i) on satheesh.tsat to bar");
+        
+        rs = st_satConnection.executeQuery(
+            " select GRANTEE, GRANTOR, SELECTPRIV, DELETEPRIV, INSERTPRIV, UPDATEPRIV, REFERENCESPRIV, TRIGGERPRIV from sys.systableperms");
+        
+        expColNames = new String [] {"GRANTEE", "GRANTOR", "SELECTPRIV", "DELETEPRIV", "INSERTPRIV", "UPDATEPRIV", "REFERENCESPRIV", "TRIGGERPRIV"};
+        JDBC.assertColumnNames(rs, expColNames);
+        
+        expRS = new String [][]
+        {
+            {"PUBLIC", "SATHEESH", "y", "N", "N", "N", "N", "N"},
+            {"FOO", "SATHEESH", "N", "y", "y", "y", "N", "N"}
+        };
+        
+        JDBC.assertFullResultSet(rs, expRS, true);
+        
+        Connection barConnection = openUserConnection("bar");
+        Statement st_barConnection = barConnection.createStatement();
+  
+        // Following revokes should fail. Only owner can revoke 
+        // permissions
+        
+        assertStatementError("2850C", st_barConnection,
+            "revoke select on satheesh.tsat from public");
+        
+        assertStatementError("2850C", st_barConnection,
+            " revoke insert on satheesh.tsat from foo");
+        
+        assertStatementError("2850C", st_barConnection,
+            " revoke update(i) on satheesh.tsat from foo");
+        
+        assertStatementError("2850C", st_barConnection,
+            " revoke update on satheesh.tsat from foo");
+        
+        assertStatementError("2850C", st_barConnection,
+            " revoke delete on satheesh.tsat from foo");
+        
+        // set connection satConnection
+        
+        // Revoke table permissions not granted already. This 
+        // should raise warnings.
+        
+        st_satConnection.executeUpdate(
+            "revoke trigger on satheesh.tsat from foo");
+        
+        if (usingEmbedded())
+        {
+            if ((sqlWarn == null) && (st_satConnection != null))
+                sqlWarn = st_satConnection.getWarnings();
+            if (sqlWarn == null)
+                sqlWarn = satConnection.getWarnings();
+            assertNotNull("Expected warning but found none", sqlWarn);
+            assertSQLState("01006", sqlWarn);
+            sqlWarn = null;
+        }
+        
+        st_satConnection.executeUpdate(
+            " revoke references on satheesh.tsat from foo");
+        
+        if (usingEmbedded())
+        {
+            if ((sqlWarn == null) && (st_satConnection != null))
+                sqlWarn = st_satConnection.getWarnings();
+            if (sqlWarn == null)
+                sqlWarn = satConnection.getWarnings();
+            assertNotNull("Expected warning but found none", sqlWarn);
+            assertSQLState("01006", sqlWarn);
+            sqlWarn = null;
+        }
+        
+        // This should raise warnings for bar
+        
+        st_satConnection.executeUpdate(
+            "revoke insert on satheesh.tsat from foo, bar");
+        
+        if (usingEmbedded())
+        {
+            if ((sqlWarn == null) && (st_satConnection != null))
+                sqlWarn = st_satConnection.getWarnings();
+            if (sqlWarn == null)
+                sqlWarn = satConnection.getWarnings();
+            assertNotNull("Expected warning but found none", sqlWarn);
+            assertSQLState("01006", sqlWarn);
+            sqlWarn = null;
+        }
+        
+        // This should raise warnings for both foo and bar
+        
+        st_satConnection.executeUpdate(
+            "revoke insert on satheesh.tsat from foo, bar");
+        
+        if (usingEmbedded())
+        {
+            if ((sqlWarn == null) && (st_satConnection != null))
+                sqlWarn = st_satConnection.getWarnings();
+            if (sqlWarn == null)
+                sqlWarn = satConnection.getWarnings();
+            assertNotNull("Expected warning but found none", sqlWarn);
+            assertSQLState("01006", sqlWarn);
+            sqlWarn = null;
+        }
+        
+        if (usingEmbedded())
+        {
+            if ((sqlWarn == null) && (st_satConnection != null))
+                sqlWarn = st_satConnection.getWarnings();
+            if (sqlWarn == null)
+                sqlWarn = satConnection.getWarnings();
+            assertNotNull("Expected warning but found none", sqlWarn);
+            assertSQLState("01006", sqlWarn);
+            sqlWarn = null;
+        }
+        
+        st_satConnection.executeUpdate(
+            " grant insert on satheesh.tsat to foo");
+        
+        // Following revokes should revoke permissions
+        
+        st_satConnection.executeUpdate(
+            "revoke update on satheesh.tsat from foo");
+        
+        st_satConnection.executeUpdate(
+            " revoke delete on satheesh.tsat from foo");
+        
+        // Check success by looking at systableperms directly for now
+        
+        rs = st_satConnection.executeQuery(
+            "select GRANTEE, GRANTOR, SELECTPRIV, DELETEPRIV, INSERTPRIV, UPDATEPRIV, REFERENCESPRIV, TRIGGERPRIV from sys.systableperms");
+        
+        expColNames = new String [] {"GRANTEE", "GRANTOR", "SELECTPRIV", "DELETEPRIV", "INSERTPRIV", "UPDATEPRIV", "REFERENCESPRIV", "TRIGGERPRIV"};
+        JDBC.assertColumnNames(rs, expColNames);
+        
+        expRS = new String [][]
+        {
+            { "PUBLIC", "SATHEESH", "y", "N", "N", "N", "N", "N"},
+            { "FOO", "SATHEESH", "N", "N", "y", "N", "N", "N"}
+        };
+        
+        JDBC.assertFullResultSet(rs, expRS, true);
+        
+        st_satConnection.executeUpdate(
+            " revoke insert on satheesh.tsat from foo");
+        
+        st_satConnection.executeUpdate(
+            " revoke select on satheesh.tsat from public");
+        
+        // Check success by looking at systableperms directly for now
+        
+        rs = st_satConnection.executeQuery(
+            "select GRANTEE, GRANTOR, SELECTPRIV, DELETEPRIV, INSERTPRIV, UPDATEPRIV, REFERENCESPRIV, TRIGGERPRIV from sys.systableperms");
+        
+        expColNames = new String [] {"GRANTEE", "GRANTOR", "SELECTPRIV", "DELETEPRIV", "INSERTPRIV", "UPDATEPRIV", "REFERENCESPRIV", "TRIGGERPRIV"};
+        JDBC.assertColumnNames(rs, expColNames);
+        
+        JDBC.assertEmpty(rs);
+        
+        // Test routine permissions
+        
+        st_satConnection.executeUpdate(
+            "CREATE FUNCTION F_ABS(P1 INT) RETURNS INT NO "
+            + "SQL RETURNS NULL ON NULL INPUT EXTERNAL NAME "
+            + "'java.lang.Math.abs' LANGUAGE JAVA PARAMETER STYLE JAVA");
+        
+        // Revoke routine permission not granted already. This 
+        // should raise a warning.
+        
+        st_satConnection.executeUpdate(
+            "revoke execute on function F_ABS(int) from bar RESTRICT");
+        
+        if (usingEmbedded())
+        {
+            if ((sqlWarn == null) && (st_satConnection != null))
+                sqlWarn = st_satConnection.getWarnings();
+            if (sqlWarn == null)
+                sqlWarn = satConnection.getWarnings();
+            assertNotNull("Expected warning but found none", sqlWarn);
+            assertSQLState("01006", sqlWarn);
+            sqlWarn = null;
+        }
+        
+        st_satConnection.executeUpdate(
+            " grant execute on function F_ABS to foo");
+        
+        st_satConnection.executeUpdate(
+            " grant execute on function F_ABS(int) to bar");
+        
+        st_satConnection.executeUpdate(
+            " revoke execute on function F_ABS(int) from bar RESTRICT");
+        
+        st_satConnection.executeUpdate(
+            " drop function f_abs");
+        
+        // Tests with views
+        
+        st_satConnection.executeUpdate(
+            "create view v1 as select * from tsat");
+        
+        st_satConnection.executeUpdate(
+            " grant select on v1 to bar");
+        
+        assertStatementError("2850F", st_satConnection,
+            " grant insert on v1 to foo");
+        
+        assertStatementError("2850F", st_satConnection,
+            " grant update on v1 to public");
+        
+        // Tests for synonym. Not supported currently.
+        
+        st_satConnection.executeUpdate(
+            "create synonym mySym for satheesh.tsat");
+        
+        // Expected to fail
+        
+        assertStatementError("42X05", st_satConnection,
+            "grant select on mySym to bar");
+        
+        assertStatementError("42X05", st_satConnection,
+            " grant insert on mySym to foo");
+        
+        st_satConnection.executeUpdate(
+            " CREATE FUNCTION F_ABS(P1 INT) RETURNS INT NO "
+            + "SQL RETURNS NULL ON NULL INPUT EXTERNAL NAME "
+            + "'java.lang.Math.abs' LANGUAGE JAVA PARAMETER STYLE JAVA");
+        
+        rs = st_satConnection.executeQuery(
+            " values f_abs(-5)");
+        
+        expColNames = new String [] {"1"};
+        JDBC.assertColumnNames(rs, expColNames);
+        
+        expRS = new String [][]
+        {
+            {"5"}
+        };
+        
+        JDBC.assertFullResultSet(rs, expRS, true);
+        
+        // Test for AUTHORIZATION option for create schema 
+        // GrantRevoke TODO: Need to enforce who can create which 
+        // schema. More negative test cases need to be added once 
+        // enforcing is done.
+        
+        getConnection().createStatement().executeUpdate(
+            "CREATE SCHEMA MYDODO AUTHORIZATION DODO");
+        
+        getConnection().createStatement().executeUpdate(
+            " CREATE SCHEMA AUTHORIZATION DERBY");
+        
+        rs = st_satConnection.executeQuery(
+            " select SCHEMANAME, AUTHORIZATIONID from sys.sysschemas where schemaname not "
+            + "like 'SYS%'");
+        
+        expColNames = new String [] {"SCHEMANAME", "AUTHORIZATIONID"};
+        JDBC.assertColumnNames(rs, expColNames);
+        
+        expRS = new String [][]
+        {
+            {"NULLID", "TEST_DBO"},
+            {"SQLJ", "TEST_DBO"},
+            {"APP", "APP"},
+            {"SATHEESH", "SATHEESH"},
+            {"MYDODO", "DODO"},
+            {"DERBY", "DERBY"}
+        };
+        
+        JDBC.assertFullResultSet(rs, expRS, true);
+        
+        // Now connect as different user and try to do DDLs in 
+        // schema owned by satheesh
+        
+        Connection swiperConnection = openUserConnection("swiper");
+        Statement st_swiperConnection = swiperConnection.createStatement();
+        
+        st_swiperConnection.executeUpdate(
+            " create table swiperTab (i int, j int)");
+        
+        st_swiperConnection.executeUpdate(
+            " insert into swiperTab values (1,1)");
+        
+        st_swiperConnection.executeUpdate(
+            " set schema satheesh");
+        
+        // All these DDLs should fail.
+        
+        assertStatementError("2850D", st_swiperConnection,
+            "create table NotMyTable (i int, j int)");
+        
+        assertStatementError("2850D", st_swiperConnection,
+            " drop table tsat");
+        
+        assertStatementError("2850D", st_swiperConnection,
+            " drop index tsat_ind");
+        
+        assertStatementError("2850D", st_swiperConnection,
+            " create view myview as select * from satheesh.tsat");
+        
+        assertStatementError("2850D", st_swiperConnection,
+            " CREATE FUNCTION FuncNotMySchema(P1 INT) RETURNS INT "
+            + "NO SQL RETURNS NULL ON NULL INPUT EXTERNAL NAME "
+            + "'java.lang.Math.abs' LANGUAGE JAVA PARAMETER STYLE JAVA");
+        
+        assertStatementError("2850D", st_swiperConnection,
+            " alter table tsat add column k int");
+        
+        st_swiperConnection.executeUpdate(
+            " create table swiper.mytab ( i int, j int)");
+        
+        st_swiperConnection.executeUpdate(
+            " set schema swiper");
+        
+        // Some simple DML tests. Should all fail.
+        
+        assertStatementError("28508", st_swiperConnection,
+            "select * from satheesh.tsat");
+        
+        assertStatementError("28506", st_swiperConnection,
+            " insert into satheesh.tsat values (1, 2)");
+        
+        assertStatementError("28508", st_swiperConnection,
+            " update satheesh.tsat set i=j");
+        
+        assertStatementError("28508", st_swiperConnection,
+            " create table my_tsat (i int not null, c char(10), "
+            + "constraint fk foreign key(i) references satheesh.tsat)");
+        
+        // set connection satConnection
+        //ij(SWIPERCONNECTION)> -- Now grant some permissions to 
+        // swiper
+        
+        
+        st_satConnection.executeUpdate(
+            " grant select(i), update(j) on tsat to swiper");
+        
+        st_satConnection.executeUpdate(
+            " grant all privileges on table1 to swiper");
+        
+        st_satConnection.executeUpdate(
+            " grant references on tsat to swiper");
+        
+        // set connection swiperConnection
+        
+        // Now some of these should pass
+        
+        assertStatementError("28508", st_swiperConnection,
+            "select * from satheesh.tsat");
+        
+        rs = st_swiperConnection.executeQuery(
+            " select i from satheesh.tsat");
+        
+        expColNames = new String [] {"I"};
+        JDBC.assertColumnNames(rs, expColNames);
+        
+        JDBC.assertEmpty(rs);
+        
+        assertStatementError("28508", st_swiperConnection,
+            " select i from satheesh.tsat where j=2");
+        
+        rs = st_swiperConnection.executeQuery(
+            " select i from satheesh.tsat where 2 > (select "
+            + "count(i) from satheesh.tsat)");
+        
+        expColNames = new String [] {"I"};
+        JDBC.assertColumnNames(rs, expColNames);
+        
+        JDBC.assertEmpty(rs);
+        
+        assertStatementError("28508", st_swiperConnection,
+            " select i from satheesh.tsat where 2 > (select "
+            + "count(j) from satheesh.tsat)");
+        
+        rs = st_swiperConnection.executeQuery(
+            " select i from satheesh.tsat where 2 > (select "
+            + "count(*) from satheesh.tsat)");
+        
+        expColNames = new String [] {"I"};
+        JDBC.assertColumnNames(rs, expColNames);
+        
+        JDBC.assertEmpty(rs);
+        
+        assertUpdateCount(st_swiperConnection, 0,
+            " update satheesh.tsat set j=j+1");
+        
+        assertUpdateCount(st_swiperConnection, 0,
+            " update satheesh.tsat set j=2 where i=2");
+        
+        assertStatementError("28508", st_swiperConnection,
+            " update satheesh.tsat set j=2 where j=1");
+        
+        rs = st_swiperConnection.executeQuery(
+            " select * from satheesh.table1");
+        
+        expColNames = new String [] {"A", "B", "C"};
+        JDBC.assertColumnNames(rs, expColNames);
+        
+        JDBC.assertEmpty(rs);
+        
+        rs = st_swiperConnection.executeQuery(
+            " select c from satheesh.table1 t1, satheesh.tsat t2 "
+            + "where t1.a = t2.i");
+        
+        expColNames = new String [] {"C"};
+        JDBC.assertColumnNames(rs, expColNames);
+        
+        JDBC.assertEmpty(rs);
+        
+        assertStatementError("28508", st_swiperConnection,
+            " select b from satheesh.table1 t1, satheesh.tsat t2 "
+            + "where t1.a = t2.j");
+        
+        rs = st_swiperConnection.executeQuery(
+            " select * from satheesh.table1, (select i from "
+            + "satheesh.tsat) table2");
+        
+        expColNames = new String [] {"A", "B", "C", "I"};
+        JDBC.assertColumnNames(rs, expColNames);
+                
+        JDBC.assertEmpty(rs);
+        
+        assertStatementError("28508", st_swiperConnection,
+            " select * from satheesh.table1, (select j from "
+            + "satheesh.tsat) table2");
+        
+        // GrantRevoke TODO: This one should pass, but currently 
+        // fails. Bind update expression in two steps.
+        
+        assertStatementError("28508", st_swiperConnection,
+            "update satheesh.tsat set j=i");
+        
+        st_swiperConnection.executeUpdate(
+            " create table my_tsat (i int not null, c char(10), "
+            + "constraint fk foreign key(i) references satheesh.tsat)");
+        
+        // set connection swiperConnection
+        // Some TRIGGER privilege checks. See GrantRevoke.java for 
+        // more tests
+        
+        
+        // Should fail
+        
+        assertStatementError("28506", st_swiperConnection,
+            "create trigger trig_sat1 after update on "
+            + "satheesh.tsat for each statement values 1");
+        
+        assertStatementError("28506", st_swiperConnection,
+            " create trigger trig_sat2 no cascade before delete "
+            + "on satheesh.tsat for each statement values 1");
+        
+        // set connection satConnection
+        //ij(SWIPERCONNECTION)> -- Grant trigger privilege
+        
+        
+        st_satConnection.executeUpdate(
+            " grant trigger on tsat to swiper");
+        
+        // set connection swiperConnection
+        //ij(SATCONNECTION)> -- Try now
+        
+        
+        st_swiperConnection.executeUpdate(
+            " create trigger trig_sat1 after update on "
+            + "satheesh.tsat for each statement values 1");
+        
+        st_swiperConnection.executeUpdate(
+            " create trigger trig_sat2 no cascade before delete "
+            + "on satheesh.tsat for each statement values 1");
+        
+        st_swiperConnection.executeUpdate(
+            " drop trigger trig_sat1");
+        
+        st_swiperConnection.executeUpdate(
+            " drop trigger trig_sat2");
+        
+        // set connection satConnection
+        //ij(SWIPERCONNECTION)> -- Now revoke and try again
+        
+        
+        st_satConnection.executeUpdate(
+            " revoke trigger on tsat from swiper");
+        
+        // set connection swiperConnection
+        
+        assertStatementError("28506", st_swiperConnection,
+            " create trigger trig_sat1 after update on "
+            + "satheesh.tsat for each statement values 1");
+        
+        assertStatementError("28506", st_swiperConnection,
+            " create trigger trig_sat2 no cascade before delete "
+            + "on satheesh.tsat for each statement values 1");
+        
+        // set connection satConnection
+        //ij(SWIPERCONNECTION)> -- Now grant access to public and 
+        // try again
+        
+        
+        st_satConnection.executeUpdate(
+            " grant trigger on tsat to public");
+        
+        // set connection swiperConnection
+        
+        st_swiperConnection.executeUpdate(
+            " create trigger trig_sat1 after update on "
+            + "satheesh.tsat for each statement values 1");
+        
+        st_swiperConnection.executeUpdate(
+            " create trigger trig_sat2 no cascade before delete "
+            + "on satheesh.tsat for each statement values 1");
+        
+        st_swiperConnection.executeUpdate(
+            " drop trigger trig_sat1");
+        
+        st_swiperConnection.executeUpdate(
+            " drop trigger trig_sat2");
+
+        // set connection satConnection
+        
+        // clean up
+
+        st_satConnection.executeUpdate(
+            " drop view v1");
+        
+        st_satConnection.executeUpdate(
+            " drop table tsat");
+         
+        st_satConnection.executeUpdate(
+            " drop table table1");
+        
+        //ij(SWIPERCONNECTION)> -- Some simple routine tests. See 
+        // GrantRevoke.java for more tests
+        
+        
+        rs = st_satConnection.executeQuery(
+            " values f_abs(-5)");
+        
+        expColNames = new String [] {"1"};
+        JDBC.assertColumnNames(rs, expColNames);
+        
+        expRS = new String [][]
+        {
+            {"5"}
+        };
+        
+        JDBC.assertFullResultSet(rs, expRS, true);
+        
+        rs = st_satConnection.executeQuery(
+            " select f_abs(-4) from sys.systables where "
+            + "tablename like 'SYSTAB%'");
+        
+        expColNames = new String [] {"1"};
+        JDBC.assertColumnNames(rs, expColNames);
+        
+        expRS = new String [][]
+        {
+            {"4"},
+            {"4"}
+        };
+        
+        JDBC.assertFullResultSet(rs, expRS, true);
+        
+        // set connection swiperConnection
+        //ij(SATCONNECTION)> -- Same tests should fail
+        
+        
+        st_swiperConnection.executeUpdate(
+            " set schema satheesh");
+        
+        assertStatementError("2850A", st_swiperConnection,
+            " values f_abs(-5)");
+        
+        assertStatementError("2850A", st_swiperConnection,
+            " select f_abs(-4) from sys.systables where "
+            + "tablename like 'SYSTAB%'");
+        
+        // set connection satConnection
+        // Now grant execute permission 
+        // and try again
+        
+        
+        st_satConnection.executeUpdate(
+            " grant execute on function f_abs to swiper");
+        
+        // set connection swiperConnection
+        
+        // Should pass now
+        
+        rs = st_swiperConnection.executeQuery(
+            "values f_abs(-5)");
+        
+        expColNames = new String [] {"1"};
+        JDBC.assertColumnNames(rs, expColNames);
+        
+        expRS = new String [][]
+        {
+            {"5"}
+        };
+        
+        JDBC.assertFullResultSet(rs, expRS, true);
+        
+        rs = st_swiperConnection.executeQuery(
+            " select f_abs(-4) from sys.systables where "
+            + "tablename like 'SYSTAB%'");
+        
+        expColNames = new String [] {"1"};
+        JDBC.assertColumnNames(rs, expColNames);
+        
+        expRS = new String [][]
+        {
+            {"4"},
+            {"4"}
+        };
+        
+        JDBC.assertFullResultSet(rs, expRS, true);
+        
+        // set connection satConnection
+        //ij(SWIPERCONNECTION)> -- Now revoke permission and try
+        
+        
+        st_satConnection.executeUpdate(
+            " revoke execute on function f_abs from swiper RESTRICT");
+        
+        // set connection swiperConnection
+        
+        assertStatementError("2850A", st_swiperConnection,
+            " values f_abs(-5)");
+        
+        assertStatementError("2850A", st_swiperConnection,
+            " select f_abs(-4) from sys.systables where "
+            + "tablename like 'SYSTAB%'");
+        
+        // set connection satConnection
+        //ij(SWIPERCONNECTION)> -- Now try public permission
+        
+        
+        st_satConnection.executeUpdate(
+            " grant execute on function f_abs to public");
+        
+        // set connection swiperConnection
+        
+        // Should pass again
+        
+        rs = st_swiperConnection.executeQuery(
+            "values f_abs(-5)");
+        
+        expColNames = new String [] {"1"};
+        JDBC.assertColumnNames(rs, expColNames);
+        
+        expRS = new String [][]
+        {
+            {"5"}
+        };
+        
+        JDBC.assertFullResultSet(rs, expRS, true);
+        
+        rs = st_swiperConnection.executeQuery(
+            " select f_abs(-4) from sys.systables where "
+            + "tablename like 'SYSTAB%'");
+        
+        expColNames = new String [] {"1"};
+        JDBC.assertColumnNames(rs, expColNames);
+        
+        expRS = new String [][]
+        {
+            {"4"},
+            {"4"}
+        };
+        
+        JDBC.assertFullResultSet(rs, expRS, true);
+        
+        // some more cleanup
+
+        st_satConnection.executeUpdate("drop synonym satheesh.mySym");
+        st_satConnection.executeUpdate("drop function satheesh.f_abs");
+        
+        // set connection swiperConnection
+        // Test schema creation authorization checks
+        
+        
+        // Negative tests. Should all fail
+        
+        assertStatementError("2850E", st_swiperConnection,
+            "create schema myFriend");
+        
+        assertStatementError("2850E", st_swiperConnection,
+            " create schema mySchema authorization me");
+        
+        assertStatementError("2850E", st_swiperConnection,
+            " create schema myschema authorization swiper");
+        
+        Connection CONNECTION0 = openUserConnection("sam");
+        Statement st_CONNECTION0 = CONNECTION0.createStatement();
+      
+        assertStatementError("2850E", st_CONNECTION0,
+            " create schema sam authorization swiper");
+        
+        // Should pass
+        
+        st_CONNECTION0.executeUpdate(
+            "create schema authorization sam");
+        
+        Connection CONNECTION1 = openUserConnection("george");
+        Statement st_CONNECTION1 = CONNECTION1.createStatement();
+      
+        st_CONNECTION1.executeUpdate(
+            " create schema george");
+        
+        // set connection satConnection
+        //ij(CONNECTION1)> -- Now try as DBA (satheesh)
+        
+        
+        st.executeUpdate(
+            " create schema myFriend");
+        
+        st.executeUpdate(
+            " create schema mySchema authorization me");
+        
+        st.executeUpdate(
+            " create schema authorization testSchema");
+        
+        rs = st.executeQuery(
+            " select SCHEMANAME, AUTHORIZATIONID from sys.sysschemas");
+        
+        expColNames = new String [] {"SCHEMANAME", "AUTHORIZATIONID"};
+        JDBC.assertColumnNames(rs, expColNames);
+        
+        expRS = new String [][]
+        {
+            {"SYSIBM", "TEST_DBO"},
+            {"SYS", "TEST_DBO"},
+            {"SYSCAT", "TEST_DBO"},
+            {"SYSFUN", "TEST_DBO"},
+            {"SYSPROC", "TEST_DBO"},
+            {"SYSSTAT", "TEST_DBO"},
+            {"NULLID", "TEST_DBO"},
+            {"SQLJ", "TEST_DBO"},
+            {"SYSCS_DIAG", "TEST_DBO"},
+            {"SYSCS_UTIL", "TEST_DBO"},
+            {"APP", "APP"},
+            {"SATHEESH", "SATHEESH"},
+            {"MYDODO", "DODO"},
+            {"DERBY", "DERBY"},
+            {"SWIPER", "SWIPER"},
+            {"SAM", "SAM"},
+            {"GEORGE", "GEORGE"},
+            {"MYFRIEND", "TEST_DBO"},
+            {"MYSCHEMA", "ME"},
+            {"TESTSCHEMA", "TESTSCHEMA"}
+        };
+        
+        JDBC.assertFullResultSet(rs, expRS, true);
+        
+        // don't need satheesh schema anymore.
+        st.executeUpdate("drop schema satheesh restrict");
+ 
+        // set connection swiperConnection
+        // Test implicit creation of 
+        // schemas.. Should fail
+        
+        
+        assertStatementError("2850E", st_swiperConnection,
+            " create table mywork.t1(i int)");
+        
+        assertStatementError("2850E", st_swiperConnection,
+            " create view mywork.v1 as select * from swiper.swiperTab");
+        
+        // Implicit schema creation should only work if creating 
+        // own schema
+        
+        Connection monicaConnection = openUserConnection("monica");
+        Statement st_monicaConnection = monicaConnection.createStatement();
+        
+        assertStatementError("2850E", st_monicaConnection,
+            " create table mywork.t1 ( i int)");
+        
+        st_monicaConnection.executeUpdate(
+            " create table monica.shouldPass(c char(10))");
+        
+        // set connection swiperConnection
+        //ij(MONICACONNECTION)> -- Check if DBA can ignore all 
+        // privilege checks
+        
+        
+        st_swiperConnection.executeUpdate(
+            " set schema swiper");
+        
+        st_swiperConnection.executeUpdate(
+            " revoke select on swiperTab from satheesh");
+        
+        if (usingEmbedded())
+        {
+            if ((sqlWarn == null) && (st_swiperConnection != null))
+                sqlWarn = st_swiperConnection.getWarnings();
+            if (sqlWarn == null)
+                sqlWarn = swiperConnection.getWarnings();
+            assertNotNull("Expected warning but found none", sqlWarn);
+            assertSQLState("01006", sqlWarn);
+            sqlWarn = null;
+        }
+        
+        st_swiperConnection.executeUpdate(
+            " revoke insert on swiperTab from satheesh");
+        
+        if (usingEmbedded())
+        {
+            if ((sqlWarn == null) && (st_swiperConnection != null))
+                sqlWarn = st_swiperConnection.getWarnings();
+            if (sqlWarn == null)
+                sqlWarn = swiperConnection.getWarnings();
+            assertNotNull("Expected warning but found none", sqlWarn);
+            assertSQLState("01006", sqlWarn);
+            sqlWarn = null;
+        }
+        
+        // Should still work, as DBA
+        
+        rs = st.executeQuery(
+            "select * from swiper.swiperTab");
+        
+        expColNames = new String [] {"I", "J"};
+        JDBC.assertColumnNames(rs, expColNames);
+        
+        expRS = new String [][]
+        {
+            {"1", "1"}
+        };
+        
+        JDBC.assertFullResultSet(rs, expRS, true);
+        
+        st.executeUpdate(
+            " insert into swiper.swiperTab values (2,2)");
+        
+        rs = st.executeQuery(
+            " select * from swiper.swiperTab");
+        
+        expColNames = new String [] {"I", "J"};
+        JDBC.assertColumnNames(rs, expColNames);
+        
+        expRS = new String [][]
+        {
+            {"1", "1"},
+            {"2", "2"}
+        };
+        
+        JDBC.assertFullResultSet(rs, expRS, true);
+        
+        st.executeUpdate(
+            " grant select on swiper.swiperTab to sam");
+        
+        st.executeUpdate(
+            " revoke insert on swiper.swiperTab from satheesh");
+        
+        if (usingEmbedded())
+        {
+            if ((sqlWarn == null) && (st != null))
+                sqlWarn = st.getWarnings();
+            if (sqlWarn == null)
+                sqlWarn = getConnection().getWarnings();
+            assertNotNull("Expected warning but found none", sqlWarn);
+            assertSQLState("01006", sqlWarn);
+            sqlWarn = null;
+        }
+        
+        // Test system routines. Some don't need explicit grant 
+        // and others do allowing for only DBA use by default
+        
+        
+        // Try granting or revoking from system tables. Should fail
+        
+        assertStatementError("2850F", st,
+            "grant select on sys.systables to sam");
+        
+        assertStatementError("2850F", st,
+            " grant delete on sys.syscolumns to sam");
+        
+        assertStatementError("2850F", st,
+            " grant update(alias) on sys.sysaliases to swiper");
+        
+        assertStatementError("2850F", st,
+            " revoke all privileges on sys.systableperms from public");
+        
+        assertStatementError("2850F", st,
+            " revoke trigger on sys.sysroutineperms from sam");
+        
+        // Try granting or revoking from system routines that is 
+        // expected fail
+        
+        assertStatementError("2850F", st,
+            "grant execute on procedure sysibm.sqlprocedures to sam");
+        
+        assertStatementError("2850F", st,
+            " revoke execute on procedure sysibm.sqlcamessage "
+            + "from public restrict");
+        
+        // Try positive tests
+        
+        Connection samConnection = openUserConnection("sam");
+        Statement st_samConnection = samConnection.createStatement();
+      
+        st_samConnection.executeUpdate(
+            " create table samTable(i int)");
+        
+        st_samConnection.executeUpdate(
+            " insert into samTable values 1,2,3,4,5,6,7");
+        
+        // Following should pass... PUBLIC should have access to these
+        
+        cSt = samConnection.prepareCall(
+            "call SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1)");
+        assertUpdateCount(cSt, 0);
+        
+        cSt = samConnection.prepareCall(
+            " call SYSCS_UTIL.SYSCS_SET_STATISTICS_TIMING(1)");
+        assertUpdateCount(cSt, 0);
+        
+        rs = st_samConnection.executeQuery(
+            " values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS()");
+        
+        expColNames = new String [] {"1"};
+        JDBC.assertColumnNames(rs, expColNames);
+        
+        JDBC.assertDrainResults(rs, 1);
+        
+        cSt = samConnection.prepareCall(
+            " call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('SAM', 'SAMTABLE', 1)");
+        assertUpdateCount(cSt, 0);
+        
+        cSt = samConnection.prepareCall(
+            " call "
+            + "SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE('SAM', "
+            + "'SAMTABLE', 1, 1, 1)");
+        assertUpdateCount(cSt, 0);
+        
+        // Try compressing tables not owned... INPLACE_COMPRESS 
+        // currently passes, pending DERBY-1062
+        
+        cSt = samConnection.prepareCall(
+            "call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('SWIPER', 'MYTAB', 1)");
+        assertStatementError("38000", cSt);
+        
+        cSt = samConnection.prepareCall(
+            " call "
+            + "SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE('SWIPER', "
+            + "'MYTAB', 1, 1, 1)");
+        assertUpdateCount(cSt, 0);
+        
+        // Try other system routines. All should fail
+        
+        cSt = samConnection.prepareCall(
+            "call SYSCS_UTIL.SYSCS_EXPORT_TABLE('SAM', "
+            + "'SAMTABLE' , 'extinout/table.dat', null, null, null)");
+        assertStatementError("2850A", cSt);
+        
+        cSt = samConnection.prepareCall(
+            " call "
+            + "SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storag"
+            + "e.pageSize', '4096')");
+        assertStatementError("2850A", cSt);
+        
+        assertStatementError("2850A", st_samConnection,
+            " values "
+            + "SYSCS_UTIL.SYSCS_GET_DATABASE_PROPERTY('derby.storag"
+            + "e.pageSize')");
+        
+        // set connection satConnection
+        // Try after DBA grants permissions
+        
+        
+        st.executeUpdate(
+            " grant execute on procedure "
+            + "SYSCS_UTIL.SYSCS_EXPORT_TABLE to public");
+        
+        st.executeUpdate(
+            " grant execute on procedure "
+            + "SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY to sam");
+        
+        st.executeUpdate(
+            " grant execute on function "
+            + "SYSCS_UTIL.SYSCS_GET_DATABASE_PROPERTY to sam");
+        
+        // Now these should pass
+        
+        cSt = samConnection.prepareCall(
+            "call SYSCS_UTIL.SYSCS_EXPORT_TABLE('SAM', "
+            + "'SAMTABLE' , 'extinout/table.dat', null, null, null)");
+        
+        cSt = samConnection.prepareCall(
+            " call "
+            + "SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storag"
+            + "e.pageSize', '4096')");
+        assertUpdateCount(cSt, 0);
+        
+        rs = st_samConnection.executeQuery(
+            " values "
+            + "SYSCS_UTIL.SYSCS_GET_DATABASE_PROPERTY('derby.storag"
+            + "e.pageSize')");
+        
+        expColNames = new String [] {"1"};
+        JDBC.assertColumnNames(rs, expColNames);
+        
+        expRS = new String [][]
+        {
+            {"4096"}
+        };
+        
+        JDBC.assertFullResultSet(rs, expRS, true);
+    
+        // revoke the previously granted permissions, these
+        // are tested again in testGrantRevokeDDL2
+        st.executeUpdate(
+                " revoke execute on procedure "
+                + "SYSCS_UTIL.SYSCS_EXPORT_TABLE from public restrict");
+            
+        st.executeUpdate(
+                " revoke execute on procedure "
+                + "SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY from sam restrict");
+            
+        st.executeUpdate(
+                " revoke execute on function "
+                + "SYSCS_UTIL.SYSCS_GET_DATABASE_PROPERTY from sam restrict");
+            
+        // grant one permission on table to user1 and another 
+        // permission to user3, then grant another permission on 
+        // that same table to user1 and user2(this is the first 
+        // permission to user2 on the table) and user3 (this user 
+        // already has the permission being granted). Notice that 
+        // the first 2 grant statements created a row in 
+        // SYSTABLEPERMS for user1 and user3. Third grant is going 
+        // to update the pre-existing row for user1. The third 
+        // grant is going to insert a new row for user2 in 
+        // SYSTABLEPERMS and the third grant is going to be a no-op 
+        // for user3. So, basically, this is to test that one 
+        // single grant statment can update and insert and no-op 
+        // rows into SYSTABLEPERMS for different users.
+        
+        Connection mamta1 = openUserConnection("mamta1");
+        Statement st_mamta1 = mamta1.createStatement();
+
+        st_mamta1.executeUpdate(
+            " create table t11 (c111 int not null primary key)");
+        
+        st_mamta1.executeUpdate(
+            " insert into t11 values(1)");
+        
+        st_mamta1.executeUpdate(
+            " grant select on t11 to mamta2");
+        
+        st_mamta1.executeUpdate(
+            " grant insert on t11 to mamta3");
+        
+        st_mamta1.executeUpdate(
+            " grant insert on t11 to mamta2, mamta3, mamta4");
+        
+        Connection mamta2 = openUserConnection("mamta2");
+        Statement st_mamta2 = mamta2.createStatement();
+        
+        rs = st_mamta2.executeQuery(
+            " select * from mamta1.t11");
+        
+        expColNames = new String [] {"C111"};
+        JDBC.assertColumnNames(rs, expColNames);
+        
+        expRS = new String [][]
+        {
+            {"1"}
+        };
+        
+        JDBC.assertFullResultSet(rs, expRS, true);
+        
+        st_mamta2.executeUpdate(
+            " insert into mamta1.t11 values(2)");
+        
+        rs = st_mamta2.executeQuery(
+            " select * from mamta1.t11");
+        
+        expColNames = new String [] {"C111"};
+        JDBC.assertColumnNames(rs, expColNames);
+        
+        expRS = new String [][]
+        {
+            {"1"},
+            {"2"}
+        };
+        
+        JDBC.assertFullResultSet(rs, expRS, true);
+        
+        Connection mamta3 = openUserConnection("mamta3");
+        Statement st_mamta3 = mamta3.createStatement();
+ 
+        // following select will fail because no permissions
+        
+        assertStatementError("28508", st_mamta3,
+            "select * from mamta1.t11");
+        
+        st_mamta3.executeUpdate(
+            " insert into mamta1.t11 values(3)");
+        
+        Connection mamta4 = openUserConnection("mamta4");
+        Statement st_mamta4 = mamta4.createStatement();
+        
+        // following select will fail because no permissions
+        
+        assertStatementError("28508", st_mamta4,
+            "select * from mamta1.t11");
+        
+        st_mamta4.executeUpdate(
+            " insert into mamta1.t11 values(4)");
+        
+        // set connection mamta1
+        
+        st_mamta1.executeUpdate(
+            " revoke all privileges on t11 from PUBLIC");
+        
+        if (usingEmbedded())
+        {
+            if ((sqlWarn == null) && (st_mamta1 != null))
+                sqlWarn = st_mamta1.getWarnings();
+            if (sqlWarn == null)
+                sqlWarn = mamta1.getWarnings();
+            assertNotNull("Expected warning but found none", sqlWarn);
+            assertSQLState("01006", sqlWarn);
+            sqlWarn = null;
+        }
+        
+        rs = st_mamta1.executeQuery(
+            " select * from mamta1.t11");
+        
+        expColNames = new String [] {"C111"};
+        JDBC.assertColumnNames(rs, expColNames);
+        
+        expRS = new String [][]
+        {
+            {"1"},
+            {"2"},
+            {"3"},
+            {"4"}
+        };
+        
+        JDBC.assertFullResultSet(rs, expRS, true);
+        
+        st_mamta1.executeUpdate(
+            " drop table t11");
+        
+        // set connection mamta1
+        // now test the column level permissions
+        
+        
+        st_mamta1.executeUpdate(
+            " create table t11 (c111 int not null primary key, "
+            + "c112 int, c113 int, c114 int)");
+        
+        st_mamta1.executeUpdate(
+            " insert into t11 values(1,1,1,1)");
+        
+        st_mamta1.executeUpdate(
+            " grant select(c111) on t11 to mamta2");
+        
+        st_mamta1.executeUpdate(
+            " grant select(c112) on t11 to mamta2, mamta3");
+        
+        st_mamta1.executeUpdate(
+            " grant update(c112) on t11 to mamta2, mamta3, mamta4");
+        
+        st_mamta1.executeUpdate(
+            " grant update on t11 to mamta2");
+        
+        // set connection mamta2
+        
+        assertUpdateCount(st_mamta2, 1,
+            " update mamta1.t11 set c113 = 2 where c111=1");
+        
+        rs = st_mamta2.executeQuery(
+            " select c111,c112 from mamta1.t11");
+        
+        expColNames = new String [] {"C111", "C112"};
+        JDBC.assertColumnNames(rs, expColNames);
+        
+        expRS = new String [][]
+        {
+            {"1", "1"}
+        };
+        
+        JDBC.assertFullResultSet(rs, expRS, true);
+        
+        // following will fail because no select permissions on 
+        // all the columns
+        
+        assertStatementError("28508", st_mamta2,
+            "select * from mamta1.t11");
+        
+        // set connection mamta3
+        
+        // following will fail because no update permission on 
+        // column c113
+        
+        assertStatementError("28508", st_mamta3,
+            "update mamta1.t11 set c113=3");
+        
+        rs = st_mamta3.executeQuery(
+            " select c112 from mamta1.t11");
+        
+        expColNames = new String [] {"C112"};
+        JDBC.assertColumnNames(rs, expColNames);
+        
+        expRS = new String [][]
+        {
+            {"1"}
+        };
+        
+        JDBC.assertFullResultSet(rs, expRS, true);
+        
+        // set connection mamta4
+        
+        // following will fail because no select permission on 
+        // column c112
+        
+        assertStatementError("28508", st_mamta4,
+            "select c112 from mamta1.t11");
+        
+        // set connection mamta1
+        
+        rs = st_mamta1.executeQuery(
+            " select * from mamta1.t11");
+        
+        expColNames = new String [] {"C111", "C112", "C113", "C114"};
+        JDBC.assertColumnNames(rs, expColNames);
+        
+        expRS = new String [][]
+        {
+            {"1", "1", "2", "1"}
+        };
+        
+        JDBC.assertFullResultSet(rs, expRS, true);
+        
+        st_mamta1.executeUpdate(
+            " revoke select on t11 from mamta2, mamta3, mamta4");
+        
+        if (usingEmbedded())
+        {
+            if ((sqlWarn == null) && (st_mamta1 != null))
+                sqlWarn = st_mamta1.getWarnings();
+            if (sqlWarn == null)
+                sqlWarn = mamta1.getWarnings();
+            assertNotNull("Expected warning but found none", sqlWarn);
+            assertSQLState("01006", sqlWarn);
+            sqlWarn = null;
+        }
+        
+        st_mamta1.executeUpdate(
+            " revoke update(c111, c112) on t11 from mamta2, "
+            + "mamta3, mamta4");
+        
+        st_mamta1.executeUpdate(
+            " drop table t11");
+        
+        // set connection mamta1
+        // Testing views to make sure we collect their depedencies 
+        // on privileges in SYSDEPENDS table
+        
+        
+        st_mamta1.executeUpdate(
+            " create table t11 (c111 int not null primary key)");
+        
+        st_mamta1.executeUpdate(
+            " insert into t11 values(1)");
+        
+        st_mamta1.executeUpdate(
+            " insert into t11 values(2)");
+        
+        rs = st_mamta1.executeQuery(
+            " select * from t11");
+        
+        expColNames = new String [] {"C111"};
+        JDBC.assertColumnNames(rs, expColNames);
+        
+        expRS = new String [][]
+        {
+            {"1"},
+            {"2"}
+        };
+        
+        JDBC.assertFullResultSet(rs, expRS, true);
+        
+        st_mamta1.executeUpdate(
+            " create table t12 (c121 int, c122 char)");
+        
+        st_mamta1.executeUpdate(
+            " insert into t12 values (1,'1')");
+        
+        rs = st_mamta1.executeQuery(
+            " select * from t12");
+        
+        expColNames = new String [] {"C121", "C122"};
+        JDBC.assertColumnNames(rs, expColNames);
+        
+        expRS = new String [][]
+        {
+            {"1", "1"}
+        };
+        
+        JDBC.assertFullResultSet(rs, expRS, true);
+        
+        st_mamta1.executeUpdate(
+            " create table t13 (c131 int, c132 char)");
+        
+        st_mamta1.executeUpdate(
+            " insert into t13 values (1,'1')");
+        
+        rs = st_mamta1.executeQuery(
+            " select * from t13");
+        
+        expColNames = new String [] {"C131", "C132"};
+        JDBC.assertColumnNames(rs, expColNames);
+        
+        expRS = new String [][]
+        {
+            {"1", "1"}
+        };
+        
+        JDBC.assertFullResultSet(rs, expRS, true);
+        
+        st_mamta1.executeUpdate(
+            " grant select on t12 to mamta2");
+        
+        st_mamta1.executeUpdate(
+            " grant select on t11 to public");
+        
+        // set connection mamta2
+        
+        // both of following will pass because mamt2 has has 
+        // required privileges because of PUBLIC select access of 
+        // mamta1.t11.
+        
+        st_mamta2.executeUpdate(
+            "create view v21 as select t1.c111, t2.c122 from "
+            + "mamta1.t11 as t1, mamta1.t12 as t2");
+        
+        st_mamta2.executeUpdate(
+            " create view v22 as select * from mamta1.t11");
+        
+        st_mamta2.executeUpdate(
+            " create view v23 as select * from mamta1.t12");
+        
+        // set connection mamta1
+        
+        // When the create view v23 from mamta2's session is 
+        // executed in mamta1, there will be only    one row in 
+        // sysdepends for view v23. That row will be for view's 
+        // dependency on t12.    There will be no row for privilege 
+        // dependency because table t12 is owned by the same    
+        // user who is creating the view v23 and hence there is no 
+        // privilege required.
+        
+        st_mamta1.executeUpdate(
+            "create view v23 as select * from mamta1.t12");
+        
+        // set connection satConnection
+        //ij(MAMTA1)> -- satConnection is dba and hence doesn't 
+        // need explicit privileges to access ojects in any schema 
+        // within the database
+        
+        
+        // since test_dbo is dba, following will not fail 
+        // even if test_dbo has no explicit privilege to 
+        // mamta2.v22
+        
+        st.executeUpdate(
+            "create view v11 as select * from mamta2.v22");
+        
+        // set connection mamta3
+        
+        st_mamta3.executeUpdate(
+            " create table t31(c311 int)");
+        
+        // since mamta3 is not dba, following will fail because no 
+        // access to mamta2.v22
+        
+        assertStatementError("28508", st_mamta3,
+            "create view v31 as select * from mamta2.v22");
+        
+        // mamta3 has access to mamta1.t11 since there is PUBLIC 
+        // select access on that table but there is no access to 
+        // mamta2.v22
+        
+        assertStatementError("28508", st_mamta3,
+            "create view v32 as select v22.c111 as a, t11.c111 "
+            + "as b from mamta2.v22 v22, mamta1.t11 t11");
+        
+        // Try to create a view with no privilege to more than one 
+        // object.
+        
+        assertStatementError("28508", st_mamta3,
+            "create view v33 as select v22.c111 as a, t11.c111 "
+            + "as b from mamta2.v22 v22, mamta1.t11 t11, mamta2.v21");
+        
+        // set connection mamta2
+        //ij(MAMTA3)> -- connect as mamta2 and give select 
+        // privilege on v22 to mamta3
+        
+        
+        // should fail
+        
+        assertStatementError("2850G", st_mamta2,
+            "grant select on v22 to mamta3");
+        
+        // set connection mamta3
+        
+        // should fail
+        
+        assertStatementError("28508", st_mamta3,
+            "create view v31 as select * from mamta2.v22");
+        
+        // following will fail because mamta3 has no access to v22
+        
+        assertStatementError("28508", st_mamta3,
+            "create view v32 as select v22.c111 as a, t11.c111 "
+            + "as b from mamta2.v22 v22, mamta1.t11 t11");
+        
+        // following will still fail because mamta3 doesn't have 
+        // access to mamta1.t12.c121
+        
+        assertStatementError("28508", st_mamta3,
+            "create view v33 as select v22.c111 as a, t12.c121 "
+            + "as b from mamta2.v22 v22, mamta1.t12 t12");
+        
+        // set connection mamta2
+        //ij(MAMTA3)> -- connect as mamta2 and give select 
+        // privilege on v23 to mamta3
+        
+        
+        assertStatementError("2850G", st_mamta2,
+            " grant select on v23 to mamta3");
+        
+        // set connection mamta3
+        
+        // should fail
+        
+        assertStatementError("28508", st_mamta3,
+            "create view v34 as select * from mamta2.v23");
+        
+        // should fail
+        
+        assertStatementError("42X05", st_mamta3,
+            "create view v35 as select * from v34");
+       
+        // set connection mamta1
+        //ij(MAMTA3)> -- Write some views based on a routine
+        
+        
+        assertStatementError("42Y55", st_mamta1,
+            " drop function f_abs1");
+        
+        st_mamta1.executeUpdate(
+            " CREATE FUNCTION F_ABS1(P1 INT) RETURNS INT NO "
+            + "SQL RETURNS NULL ON NULL INPUT EXTERNAL NAME "
+            + "'java.lang.Math.abs' LANGUAGE JAVA PARAMETER STYLE JAVA");
+        
+        rs = st_mamta1.executeQuery(
+            " values f_abs1(-5)");
+        
+        expColNames = new String [] {"1"};
+        JDBC.assertColumnNames(rs, expColNames);
+        
+        expRS = new String [][]
+        {
+            {"5"}
+        };
+        
+        JDBC.assertFullResultSet(rs, expRS, true);
+        
+        assertStatementError("X0X05", st_mamta1,
+            " drop view v11");
+        
+        st_mamta1.executeUpdate(
+            " create view v11(c111) as values mamta1.f_abs1(-5)");
+        
+        st_mamta1.executeUpdate(
+            " grant select on v11 to mamta2");
+        
+        rs = st_mamta1.executeQuery(
+            " select * from v11");
+        
+        expColNames = new String [] {"C111"};
+        JDBC.assertColumnNames(rs, expColNames);
+        
+        expRS = new String [][]
+        {
+            {"5"}
+        };
+        
+        JDBC.assertFullResultSet(rs, expRS, true);
+        
+        // set connection mamta2
+        
+        assertStatementError("X0X05", st_mamta2,
+            " drop view v24");
+        
+        st_mamta2.executeUpdate(
+            " create view v24 as select * from mamta1.v11");
+        
+        rs = st_mamta2.executeQuery(
+            " select * from v24");
+        
+        expColNames = new String [] {"C111"};
+        JDBC.assertColumnNames(rs, expColNames);
+        
+        expRS = new String [][]
+        {
+            {"5"}
+        };
+        
+        JDBC.assertFullResultSet(rs, expRS, true);
+        
+        assertStatementError("X0X05", st_mamta2,
+            " drop view v25");
+        
+        // following will fail because no execute permissions on 
+        // mamta1.f_abs1
+        
+        assertStatementError("2850A", st_mamta2,
+            "create view v25(c251) as (values mamta1.f_abs1(-1))");
+        
+        // set connection mamta1
+        
+        st_mamta1.executeUpdate(
+            " grant execute on function f_abs1 to mamta2");
+        
+        // set connection mamta2
+        
+        // this view creation will pass now because have execute 
+        // privileges on the function
+        
+        st_mamta2.executeUpdate(
+            "create view v25(c251) as (values mamta1.f_abs1(-1))");
+        
+        rs = st_mamta2.executeQuery(
+            " select * from v25");
+        
+        expColNames = new String [] {"C251"};
+        JDBC.assertColumnNames(rs, expColNames);
+        
+        expRS = new String [][]
+        {
+            {"1"}
+        };
+        
+        JDBC.assertFullResultSet(rs, expRS, true);
+        
+        // set connection mamta1
+        
+        // try revoke execute privilege. Since there are dependent 
+        // objects, the revoke shold fail
+        
+        assertStatementError("X0Y23", st_mamta1,
+            "revoke execute on function f_abs1 from mamta2 restrict");
+        
+        // set connection mamta2
+        //ij(MAMTA1)> -- drop the dependent objects on the execute 
+        // privilege and then try to revoke the execute privilege
+        
+        
+        st_mamta2.executeUpdate(
+            " drop view v25");
+        
+        // set connection mamta1
+        
+        // revoke execute privilege should pass this time because 
+        // no dependents on that permission.
+        
+        st_mamta1.executeUpdate(
+            "revoke execute on function f_abs1 from mamta2 restrict");
+        
+        // set connection mamta2
+        
+        // following select should still pass because v24 is not 
+        // directly dependent on the execute permission.   It gets 
+        // to the routine via view v11 which will be run with 
+        // definer's privileges and definer of   view v11 is also 
+        // the owner of the routine
+        
+        rs = st_mamta2.executeQuery(
+            "select * from v24");
+        
+        expColNames = new String [] {"C111"};
+        JDBC.assertColumnNames(rs, expColNames);
+        
+        expRS = new String [][]
+        {
+            {"5"}
+        };
+        
+        JDBC.assertFullResultSet(rs, expRS, true);
+        
+        // cleanup
+        
+        st_mamta2.executeUpdate(
+            "drop view v24");
+        
+        // set connection mamta1
+        
+        st_mamta1.executeUpdate(
+            " drop view v11");
+        
+        st_mamta1.executeUpdate(
+            " drop function f_abs1");
+        
+        // set connection mamta1
+        // try column level privileges and views In this test, 
+        // user has permission on one column but not on the other
+        
+        
+        st_mamta1.executeUpdate(
+            " create table t14(c141 int, c142 int)");
+        
+        st_mamta1.executeUpdate(
+            " insert into t14 values (1,1), (2,2)");
+        
+        st_mamta1.executeUpdate(
+            " grant select(c141) on t14 to mamta2");
+        
+        // set connection mamta2
+        
+        // following will fail because no access on column 
+        // mamta1.t14.c142
+        
+        assertStatementError("28508", st_mamta2,
+            "create view v26 as (select * from mamta1.t14 where c142=1)");
+        
+        // following will fail for the same reason
+        
+        assertStatementError("28508", st_mamta2,
+            "create view v26 as (select c141 from mamta1.t14 "
+            + "where c142=1)");
+        
+        // following will pass because view is based on column 
+        // that it can access
+        
+        st_mamta2.executeUpdate(
+            "create view v27 as (select c141 from mamta1.t14)");
+        
+        rs = st_mamta2.executeQuery(
+            " select * from v27");
+        
+        expColNames = new String [] {"C141"};
+        JDBC.assertColumnNames(rs, expColNames);
+        
+        expRS = new String [][]
+        {
+            {"1"},
+            {"2"}
+        };
+        
+        JDBC.assertFullResultSet(rs, expRS, true);
+        
+        // set connection mamta1
+        
+        // give access to all the columns in t14 to mamta2
+        
+        st_mamta1.executeUpdate(
+            "grant select on t14 to mamta2");
+        
+        // set connection mamta2
+        
+        // now following will pass
+        
+        st_mamta2.executeUpdate(
+            "create view v26 as (select c141 from mamta1.t14 "
+            + "where c142=1)");
+        
+        rs = st_mamta2.executeQuery(
+            " select * from v26");
+        
+        expColNames = new String [] {"C141"};
+        JDBC.assertColumnNames(rs, expColNames);
+        
+        expRS = new String [][]
+        {
+            {"1"}
+        };
+        
+        JDBC.assertFullResultSet(rs, expRS, true);
+        
+        // set connection mamta1
+        //ij(MAMTA2)> -- in this column level privilege test, 
+        // there is a user level permission on one column   and a 
+        // PUBLIC level on the other column.
+        
+        
+        st_mamta1.executeUpdate(
+            " create table t15(c151 int, c152 int)");
+        
+        st_mamta1.executeUpdate(
+            " insert into t15 values(1,1),(2,2)");
+        
+        st_mamta1.executeUpdate(
+            " grant select(c151) on t15 to mamta2");
+        
+        st_mamta1.executeUpdate(
+            " grant select(c152) on t15 to public");
+        
+        // set connection mamta2
+        
+        st_mamta2.executeUpdate(
+            " create view v28 as (select c152 from mamta1.t15 "
+            + "where c151=1)");
+        
+        // set connection mamta1
+        //ij(MAMTA2)> -- write some view based tests and revoke 
+        // privileges to see if the right thing happens View tests 
+        // test1  A simple test where a user creates a view based 
+        // on objects in other schemas and revoke privilege on one 
+        // of those  objects will drop the view
+        
+        
+        assertStatementError("42Y55", st_mamta1,
+            " drop table t11ViewTest");
+        
+        st_mamta1.executeUpdate(
+            " create table t11ViewTest (c111 int not null primary key)");
+        
+        st_mamta1.executeUpdate(
+            " insert into t11ViewTest values(1)");
+        
+        st_mamta1.executeUpdate(
+            " insert into t11ViewTest values(2)");
+        
+        assertStatementError("42Y55", st_mamta1,
+            " drop table t12ViewTest");
+        
+        st_mamta1.executeUpdate(
+            " create table t12ViewTest (c121 int, c122 char)");
+        
+        st_mamta1.executeUpdate(
+            " insert into t12ViewTest values (1,'1')");
+        
+        // user mamta2 is going to create a view based on 
+        // following grants
+        
+        st_mamta1.executeUpdate(
+            "grant select on t12ViewTest to mamta2");
+        
+        st_mamta1.executeUpdate(
+            " grant select on t11ViewTest to public");
+        
+        // set connection mamta2
+        
+        assertStatementError("X0X05", st_mamta2,
+            " drop view v21ViewTest");
+        
+        // will succeed because all the required privileges are in 
+        // place
+        
+        st_mamta2.executeUpdate(
+            "create view v21ViewTest as select t1.c111, t2.c122 "
+            + "from mamta1.t11ViewTest as t1, mamta1.t12ViewTest as t2");
+        
+        rs = st_mamta2.executeQuery(
+            " select * from v21ViewTest");
+        
+        expColNames = new String [] {"C111", "C122"};
+        JDBC.assertColumnNames(rs, expColNames);
+        
+        expRS = new String [][]
+        {
+            {"1", "1"},
+            {"2", "1"}
+        };
+        
+        JDBC.assertFullResultSet(rs, expRS, true);
+        
+        // set connection mamta1
+        
+        // this revoke should drop the dependent view in schema mamta2
+        
+        st_mamta1.executeUpdate(
+            "revoke select on t11ViewTest from public");
+        
+        // set connection mamta2
+        if (usingEmbedded())
+        {
+            if ((sqlWarn == null) && (st_mamta1 != null))
+                sqlWarn = st_mamta1.getWarnings();
+            if (sqlWarn == null)
+                sqlWarn = mamta1.getWarnings();
+            assertNotNull("Expected warning but found none", sqlWarn);
+            assertSQLState("01501", sqlWarn);
+            sqlWarn = null;
+        }
+        
+        
+        // the view shouldn't exist anymore because one of the 
+        // privileges required by it was revoked
+        
+        assertStatementError("42X05", st_mamta2,
+            "select * from v21ViewTest");
+        
+        // set connection mamta1
+        
+        // this revoke should not impact any objects because none 
+        // depend on it
+        
+        st_mamta1.executeUpdate(
+            "revoke select on t12ViewTest from mamta2");
+        
+        // set connection mamta2
+        
+        assertStatementError("42X05", st_mamta2,
+            " select * from v21ViewTest");
+        
+        // set connection mamta1
+        //ij(MAMTA2)> -- cleanup
+        
+        
+        st_mamta1.executeUpdate(
+            " drop table t11ViewTest");
+        
+        st_mamta1.executeUpdate(
+            " drop table t12ViewTest");
+        
+        // set connection mamta1
+        // View tests test2  Let the dba create a view in schema 
+        // mamta2 (owned by user mamta2). The view's definition 
+        // accesses    objects from schema mamta1. The owner of 
+        // schema mamta2 does not have access to objects in schema 
+        // mamta1    but the create view by dba does not fail 
+        // because dba has access to all the objects.  mamta2 will 
+        // have access to the view created by the dba because 
+        // mamta2 is owner of the schema "mamta2" and    it has 
+        // access to all the objects created in it's schema, 
+        // whether they were created by mamta2 or the dba.  user 
+        // mamta2 is owner of the schema mamta2 because user mamta2 
+        // was the first one to create an object in    schema 
+        // mamta2 earlier in this test.  Any other user (except the 
+        // dba) will need to get explicit select privileges on the 
+        // view in order to access it
+        
+        
+        // Note that mamta1 is creating couple tables but has not 
+        // granted permissions on those tables to anyone
+        
+        assertStatementError("42Y55", st_mamta1,
+            "drop table t11ViewTest");
+        
+        st_mamta1.executeUpdate(
+            " create table t11ViewTest (c111 int not null primary key)");
+        
+        st_mamta1.executeUpdate(
+            " insert into t11ViewTest values(1)");
+        
+        st_mamta1.executeUpdate(
+            " insert into t11ViewTest values(2)");
+        
+        assertStatementError("42Y55", st_mamta1,
+            " drop table t12ViewTest");
+        
+        st_mamta1.executeUpdate(
+            " create table t12ViewTest (c121 int, c122 char)");
+        
+        st_mamta1.executeUpdate(
+            " insert into t12ViewTest values (1,'1')");
+        
+        // set connection satConnection
+        //ij(MAMTA1)> -- connect as dba
+        
+        
+        // dba is creating a view in schema owned by another user. 
+        // dba can create objects anywhere and access objects from 
+        // anywhere
+        
+        st.executeUpdate(
+            "create view mamta2.v21ViewTest as select t1.c111, "
+            + "t2.c122 from mamta1.t11ViewTest as t1, "
+            + "mamta1.t12ViewTest as t2");
+        
+        // dba can do select from that view
+        
+        rs = st.executeQuery(
+            "select * from mamta2.v21ViewTest");
+        
+        expColNames = new String [] {"C111", "C122"};
+        JDBC.assertColumnNames(rs, expColNames);
+        
+        expRS = new String [][]
+        {
+            {"1", "1"},
+            {"2", "1"}
+        };
+        
+        JDBC.assertFullResultSet(rs, expRS, true);
+        
+        // set connection mamta2
+        
+        // the schema owner can do a select from an object that is 
+        // part of it's schema even though it was created by the dba
+        
+        rs = st_mamta2.executeQuery(
+            "select * from v21ViewTest");
+        
+        expColNames = new String [] {"C111", "C122"};
+        JDBC.assertColumnNames(rs, expColNames);
+        
+        expRS = new String [][]
+        {
+            {"1", "1"},
+            {"2", "1"}
+        };
+        
+        JDBC.assertFullResultSet(rs, expRS, true);
+        
+        // set connection mamta3
+        
+        // mamta3 has not been granted select privileges on 
+        // mamta2.v21ViewTest
+        
+        assertStatementError("28508", st_mamta3,
+            "select * from mamta2.v21ViewTest");
+        
+        // set connection mamta2
+        
+        // give select privileges on the view to mamta3, should fail
+        
+        assertStatementError("2850G", st_mamta2,
+            "grant select on v21ViewTest to mamta3");
+        
+        // set connection mamta3
+        
+        // select from mamta2.v21ViewTest will fail for mamta3 
+        // because mamta3 has no select privilege on mamta2.v21ViewTest
+        
+        assertStatementError("28508", st_mamta3,
+            "select * from mamta2.v21ViewTest");
+        
+        // set connection satConnection
+        
+        // have the dba take away select privilege on 
+        // mamta2.v21ViewTest from mamta3
+        
+        st.executeUpdate(
+            "revoke select on mamta2.v21ViewTest from mamta3");
+        
+        // set connection mamta3
+        if (usingEmbedded())
+        {
+            if ((sqlWarn == null) && (st != null))
+                sqlWarn = st.getWarnings();
+            if (sqlWarn == null)
+                sqlWarn = getConnection().getWarnings();
+            assertNotNull("Expected warning but found none", sqlWarn);
+            assertSQLState("01006", sqlWarn);
+            sqlWarn = null;
+        }
+        
+        
+        // select from mamta2.v21ViewTest will fail this time for 
+        // mamta3 because dba took away the select privilege on 
+        // mamta2.v21ViewTest
+        
+        assertStatementError("28508", st_mamta3,
+            "select * from mamta2.v21ViewTest");
+        
+        // set connection mamta2
+        //ij(MAMTA3)> -- cleanup
+        
+        
+        st_mamta2.executeUpdate(
+            " drop view v21ViewTest");
+        
+        // set connection mamta1
+        
+        st_mamta1.executeUpdate(
+            " drop table t12ViewTest");
+        
+        st_mamta1.executeUpdate(
+            " drop table t11ViewTest");
+        
+        // set connection mamta1
+        // View tests test3  Create a view that relies on table 
+        // level and column permissions and see that view gets 
+        // dropped correctly when any of the    required privilege 
+        // is revoked
+        
+        
+        assertStatementError("42Y55", st_mamta1,
+            " drop table t11ViewTest");
+        
+        st_mamta1.executeUpdate(
+            " create table t11ViewTest (c111 int not null primary key)");
+        
+        st_mamta1.executeUpdate(
+            " insert into t11ViewTest values(1)");
+        
+        assertStatementError("42Y55", st_mamta1,
+            " drop table t12ViewTest");
+        
+        st_mamta1.executeUpdate(
+            " create table t12ViewTest (c121 int, c122 char)");
+        
+        st_mamta1.executeUpdate(
+            " insert into t12ViewTest values (1,'1')");
+        
+        st_mamta1.executeUpdate(
+            " grant select (c111) on t11ViewTest to mamta3");
+        
+        st_mamta1.executeUpdate(
+            " grant select (c121, c122) on t12ViewTest to public");
+        
+        // set connection mamta2
+        
+        assertStatementError("42Y55", st_mamta2,
+            " drop table t21ViewTest");
+        
+        st_mamta2.executeUpdate(
+            " create table t21ViewTest (c211 int)");
+        
+        st_mamta2.executeUpdate(
+            " insert into t21ViewTest values(1)");
+        
+        st_mamta2.executeUpdate(
+            " grant select on t21ViewTest to mamta3");
+        
+        // set connection mamta3
+        
+        assertStatementError("X0X05", st_mamta3,
+            " drop view v31ViewTest");
+        
+        st_mamta3.executeUpdate(
+            " create view v31ViewTest as select t2.c122, t1.*, "
+            + "t3.* from mamta1.t11ViewTest as t1, "
+            + "mamta1.t12ViewTest as t2,mamta2.t21ViewTest as t3 "
+            + "where t1.c111 = t3.c211");
+        
+        rs = st_mamta3.executeQuery(
+            " select * from v31ViewTest");
+        
+        expColNames = new String [] {"C122", "C111", "C211"};
+        JDBC.assertColumnNames(rs, expColNames);
+        
+        expRS = new String [][]
+        {
+            {"1", "1", "1"}
+        };
+        
+        JDBC.assertFullResultSet(rs, expRS, true);
+        
+        // set connection mamta1
+        
+        // revoke a column level privilege. It should drop the view
+        
+        st_mamta1.executeUpdate(
+            "revoke select(c122) on t12ViewTest from public");
+        
+        // set connection mamta3
+        if (usingEmbedded())
+        {
+            if ((sqlWarn == null) && (st_mamta1 != null))
+                sqlWarn = st_mamta1.getWarnings();
+            if (sqlWarn == null)
+                sqlWarn = mamta1.getWarnings();
+            assertNotNull("Expected warning but found none", sqlWarn);
+            assertSQLState("01501", sqlWarn);
+            sqlWarn = null;
+        }
+        
+        
+        // the view got dropped because of revoke issued earlier
+        
+        assertStatementError("42X05", st_mamta3,
+            "select * from v31ViewTest");
+        
+        // set connection mamta2
+        //ij(MAMTA3)> -- cleanup
+        
+        
+        st_mamta2.executeUpdate(
+            " drop table t21ViewTest");
+        
+        // set connection mamta1
+        
+        st_mamta1.executeUpdate(
+            " drop table t12ViewTest");
+        
+        st_mamta1.executeUpdate(
+            " drop table t11ViewTest");
+        
+        // set connection mamta1
+        // View tests test4  Create a view that relies on a 
+        // user-level table privilege and a user-level column 
+        // privilege.   There also exists a PUBLIC-level column 
+        // privilege but objects at the creation time always first  
+        //  look for the required privilege at the user 
+        // level(DERBY-1632). This behavior can be confirmed by the 
+        //   following test case where when PUBLIC-level column 
+        // privilege is revoked, it does not impact the   view in 
+        // anyway because the view is relying on user-level column 
+        // privilege. Confirm that object   is relying on 
+        // user-level privilege by revoking the user-level 
+        // privilege and that should drop the object
+        
+        
+        assertStatementError("42Y55", st_mamta1,
+            " drop table t11ViewTest");
+        
+        st_mamta1.executeUpdate(
+            " create table t11ViewTest (c111 int not null primary key)");
+        
+        st_mamta1.executeUpdate(
+            " insert into t11ViewTest values(1)");
+        
+        assertStatementError("42Y55", st_mamta1,
+            " drop table t12ViewTest");
+        
+        st_mamta1.executeUpdate(
+            " create table t12ViewTest (c121 int, c122 char)");
+        
+        st_mamta1.executeUpdate(
+            " insert into t12ViewTest values (1,'1')");
+        
+        st_mamta1.executeUpdate(
+            " grant select (c111) on t11ViewTest to mamta3, public");
+        
+        st_mamta1.executeUpdate(
+            " grant select (c121, c122) on t12ViewTest to public");
+        
+        // set connection mamta2
+        
+        assertStatementError("42Y55", st_mamta2,
+            " drop table t21ViewTest");
+        
+        st_mamta2.executeUpdate(
+            " create table t21ViewTest (c211 int)");
+        
+        st_mamta2.executeUpdate(
+            " insert into t21ViewTest values(1)");
+        
+        st_mamta2.executeUpdate(
+            " grant select on t21ViewTest to mamta3, mamta5");
+        
+        // set connection mamta3
+        
+        assertStatementError("X0X05", st_mamta3,
+            " drop view v31ViewTest");
+        
+        st_mamta3.executeUpdate(
+            " create view v31ViewTest as select t2.c122, t1.*, "
+            + "t3.* from mamta1.t11ViewTest as t1, "
+            + "mamta1.t12ViewTest as t2,mamta2.t21ViewTest as t3 "
+            + "where t1.c111 = t3.c211");
+        
+        rs = st_mamta3.executeQuery(
+            " select * from v31ViewTest");
+        
+        expColNames = new String [] {"C122", "C111", "C211"};
+        JDBC.assertColumnNames(rs, expColNames);
+        
+        expRS = new String [][]
+        {
+            {"1", "1", "1"}
+        };
+        
+        JDBC.assertFullResultSet(rs, expRS, true);
+        
+        // set connection mamta1
+        
+        // revoke public level privilege. Should not impact the 
+        // view because user objects always rely on user level 
+        // privilege.   If no user level privilege is found at 
+        // create object time, then PUBLIC level privilege (if 
+        // there) is used.   If there is no privilege granted at 
+        // user level or public level at create object time, the 
+        // create sql will fail   DERBY-1632
+        
+        st_mamta1.executeUpdate(
+            "revoke select(c111) on t11ViewTest from public");
+        
+        // set connection mamta3
+        
+        // still exists because privileges required by it are not 
+        // revoked
+        
+        rs = st_mamta3.executeQuery(
+            "select * from v31ViewTest");
+        
+        expColNames = new String [] {"C122", "C111", "C211"};
+        JDBC.assertColumnNames(rs, expColNames);
+        
+        expRS = new String [][]
+        {
+            {"1", "1", "1"}
+        };
+        
+        JDBC.assertFullResultSet(rs, expRS, true);
+        
+        // set connection mamta1
+        
+        // this revoke should drop the view mamta3.v31ViewTest
+        
+        st_mamta1.executeUpdate(
+            "revoke select(c111) on t11ViewTest from mamta3");
+        
+        // set connection mamta3
+        if (usingEmbedded())
+        {
+            if ((sqlWarn == null) && (st_mamta1 != null))
+                sqlWarn = st_mamta1.getWarnings();
+            if (sqlWarn == null)
+                sqlWarn = mamta1.getWarnings();
+            assertNotNull("Expected warning but found none", sqlWarn);
+            assertSQLState("01501", sqlWarn);
+            sqlWarn = null;
+        }
+        
+        
+        // View shouldn't exist anymore
+        
+        assertStatementError("42X05", st_mamta3,
+            "select * from v31ViewTest");
+        
+        // set connection mamta2
+        //ij(MAMTA3)> -- cleanup
+        
+        
+        st_mamta2.executeUpdate(
+            " drop table t21ViewTest");
+        
+        // set connection mamta1
+        
+        st_mamta1.executeUpdate(
+            " drop table t12ViewTest");
+        
+        st_mamta1.executeUpdate(
+            " drop table t11ViewTest");
+        
+        // set connection mamta1
+        // View tests test5 Create a view that relies on a SELECT 
+        // privilege on only one column of a table. revoke SELECT 
+        // privilege on  another column in that table and it ends 
+        // up dropping the view. This is happening because the 
+        // revoke privilege  work is not completely finished and 
+        // any dependent object on that permission type for table's 
+        // columns  get dropped when a revoke privilege is issued 
+        // against any column of that table
+        
+        
+        assertStatementError("42Y55", st_mamta1,
+            " drop table t11ViewTest");
+        
+        st_mamta1.executeUpdate(
+            " create table t11ViewTest (c111 int not null "
+            + "primary key, c112 int)");
+        
+        st_mamta1.executeUpdate(
+            " insert into t11ViewTest values(1,1)");
+        
+        st_mamta1.executeUpdate(
+            " grant select (c111, c112) on t11ViewTest to mamta2");
+        
+        // set connection mamta2
+        
+        assertStatementError("X0X05", st_mamta2,
+            " drop view v21ViewTest");
+        
+        st_mamta2.executeUpdate(
+            " create view v21ViewTest as select c111 from "
+            + "mamta1.t11ViewTest");
+        
+        // set connection mamta1
+        //ij(MAMTA2)> -- notice that the view above needs SELECT 
+        // privilege on column c111 of mamta1.t11ViewTest and does 
+        // not care about column c112
+        
+        
+        // the revoke below ends up dropping the view 
+        // mamta2.v21ViewTest eventhough the view does not depend 
+        // on column c112 This will be fixed in a subsequent patch 
+        // for revoke privilege
+        
+        st_mamta1.executeUpdate(
+            "revoke select (c111) on t11ViewTest from mamta2");
+        
+        // set connection mamta2
+        if (usingEmbedded())
+        {
+            if ((sqlWarn == null) && (st_mamta1 != null))
+                sqlWarn = st_mamta1.getWarnings();
+            if (sqlWarn == null)
+                sqlWarn = mamta1.getWarnings();
+            assertNotNull("Expected warning but found none", sqlWarn);
+            assertSQLState("01501", sqlWarn);
+            sqlWarn = null;
+        }
+        
+        
+        assertStatementError("42X05", st_mamta2,
+            " select * from v21ViewTest");
+        
+        // set connection mamta1
+        //ij(MAMTA2)> -- cleanup
+        
+        
+        st_mamta1.executeUpdate(
+            " drop table t11ViewTest");
+        
+        // set connection mamta1
+        // View tests test6  Create a view that requires a 
+        // privilege. grant select on the view to another user.    
+        // Let that user create a trigger based on the granted 
+        // view.    Now if the privilege is revoked from the view 
+        // owner, the view gets dropped, as    expected. But I had 
+        // also expected the trigger to fail the next time it gets 
+        // fired    because view used by it doesn't exist anymore. 
+        // But because of a bug in Derby,    DERBY-1613(A trigger 
+        // does not get invalidated when the view used by it is 
+        // dropped),    during some runs of this test, the trigger 
+        // continues to fire successfully and    during other runs 
+        // of this test, it gives the error that the view does    
+        // not exist anymore. Seems like this is timing related 
+        // issue. So, may see    diffs in this particular test 
+        // until DERBY-1613 is resolved. After the    resolution of 
+        // DERBY-1613, the insert trigger will always fail after 
+        // the view    gets dropped because of the revoke privilege.
+        
+        
+        assertStatementError("42Y55", st_mamta1,
+            " drop table t11TriggerTest");
+        
+        st_mamta1.executeUpdate(
+            " create table t11TriggerTest (c111 int not null "
+            + "primary key, c112 int)");
+        
+        st_mamta1.executeUpdate(
+            " insert into t11TriggerTest values(1,1)");
+        
+        st_mamta1.executeUpdate(
+            " insert into t11TriggerTest values(2,2)");
+        
+        st_mamta1.executeUpdate(
+            " grant select on t11TriggerTest to mamta2");
+        
+        // set connection mamta2
+        
+        st_mamta2.executeUpdate(
+            " create view v21ViewTest as select * from "
+            + "mamta1.t11TriggerTest");
+        
+        // should fail
+        
+        assertStatementError("2850G", st_mamta2,
+            "grant select on v21ViewTest to mamta3");
+        
+        rs = st_mamta2.executeQuery(
+            " select * from v21ViewTest");
+        
+        expColNames = new String [] {"C111", "C112"};
+        JDBC.assertColumnNames(rs, expColNames);
+        
+        expRS = new String [][]
+        {
+            {"1", "1"},
+            {"2", "2"}
+        };
+        
+        JDBC.assertFullResultSet(rs, expRS, true);
+        
+        // set connection mamta3
+        
+        assertStatementError("42Y55", st_mamta3,
+            " drop table t31TriggerTest");
+        
+        st_mamta3.executeUpdate(
+            " create table t31TriggerTest (c311 int)");
+        
+        assertStatementError("42Y55", st_mamta3,
+            " drop table t32TriggerTest");
+        
+        st_mamta3.executeUpdate(
+            " create table t32TriggerTest (c321 int)");
+        
+        // following should fail because not all the privileges 
+        // are in place
+        
+        assertStatementError("28508", st_mamta3,
+            "create trigger tr31t31TriggerTest after insert on "
+            + "t31TriggerTest for each statement insert into "
+            + "t32TriggerTest values (select c111 from "
+            + "mamta2.v21ViewTest where c112=1)");
+        
+        st_mamta3.executeUpdate(
+            " insert into t31TriggerTest values(1)");
+        
+        rs = st_mamta3.executeQuery(
+            " select * from t31TriggerTest");
+        
+        expColNames = new String [] {"C311"};
+        JDBC.assertColumnNames(rs, expColNames);
+        
+        expRS = new String [][]
+        {
+            {"1"}
+        };
+        
+        JDBC.assertFullResultSet(rs, expRS, true);
+        
+        rs = st_mamta3.executeQuery(
+            " select * from t32TriggerTest");
+        
+        expColNames = new String [] {"C321"};
+        JDBC.assertColumnNames(rs, expColNames);
+        
+        JDBC.assertEmpty(rs);
+        
+        // set connection mamta1
+        
+        // This will drop the dependent view
+        
+        st_mamta1.executeUpdate(
+            "revoke select on t11TriggerTest from mamta2");
+        
+        // set connection mamta2
+        if (usingEmbedded())
+        {
+            if ((sqlWarn == null) && (st_mamta1 != null))
+                sqlWarn = st_mamta1.getWarnings();
+            if (sqlWarn == null)
+                sqlWarn = mamta1.getWarnings();
+            assertNotNull("Expected warning but found none", sqlWarn);
+            assertSQLState("01501", sqlWarn);
+            sqlWarn = null;
+        }
+        
+        
+        assertStatementError("42X05", st_mamta2,
+            " select * from v21ViewTest");
+        
+        // set connection mamta3
+        
+        // During some runs of this test, the trigger continues to 
+        // fire even though the view used by it  has been dropped. 
+        // (DERBY-1613) During other runs of this test, the trigger 
+        // gives error as expected about the missing view.  After 
+        // DERBY-1613 is fixed, we should consistently get error 
+        // from insert below because the  insert trigger can't find 
+        // the view it uses.
+        
+        st_mamta3.executeUpdate(
+            "insert into t31TriggerTest values(1)");
+        
+        rs = st_mamta3.executeQuery(
+            " select * from t31TriggerTest");
+        
+        expColNames = new String [] {"C311"};
+        JDBC.assertColumnNames(rs, expColNames);
+        
+        expRS = new String [][]
+        {
+            {"1"},
+            {"1"}
+        };
+        
+        JDBC.assertFullResultSet(rs, expRS, true);
+        
+        rs = st_mamta3.executeQuery(
+            " select * from t32TriggerTest");
+        

[... 7220 lines stripped ...]


Mime
View raw message