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/04/26 02:10:17 UTC
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/
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 ...]