Return-Path: Delivered-To: apmail-db-derby-commits-archive@www.apache.org Received: (qmail 14185 invoked from network); 12 Dec 2006 22:46:34 -0000 Received: from hermes.apache.org (HELO mail.apache.org) (140.211.11.2) by minotaur.apache.org with SMTP; 12 Dec 2006 22:46:34 -0000 Received: (qmail 27476 invoked by uid 500); 12 Dec 2006 22:46:42 -0000 Delivered-To: apmail-db-derby-commits-archive@db.apache.org Received: (qmail 27405 invoked by uid 500); 12 Dec 2006 22:46:42 -0000 Mailing-List: contact derby-commits-help@db.apache.org; run by ezmlm Precedence: bulk list-help: list-unsubscribe: List-Post: Reply-To: "Derby Development" List-Id: Delivered-To: mailing list derby-commits@db.apache.org Received: (qmail 27282 invoked by uid 99); 12 Dec 2006 22:46:41 -0000 Received: from herse.apache.org (HELO herse.apache.org) (140.211.11.133) by apache.org (qpsmtpd/0.29) with ESMTP; Tue, 12 Dec 2006 14:46:41 -0800 X-ASF-Spam-Status: No, hits=-9.4 required=10.0 tests=ALL_TRUSTED,NO_REAL_NAME X-Spam-Check-By: apache.org Received: from [140.211.11.3] (HELO eris.apache.org) (140.211.11.3) by apache.org (qpsmtpd/0.29) with ESMTP; Tue, 12 Dec 2006 14:46:33 -0800 Received: by eris.apache.org (Postfix, from userid 65534) id 682721A981D; Tue, 12 Dec 2006 14:45:48 -0800 (PST) Content-Type: text/plain; charset="utf-8" MIME-Version: 1.0 Content-Transfer-Encoding: 7bit Subject: svn commit: r486380 - /db/derby/code/branches/10.2/java/testing/org/apache/derbyTesting/functionTests/master/j9_foundation/grantRevokeDDL.out Date: Tue, 12 Dec 2006 22:45:48 -0000 To: derby-commits@db.apache.org From: myrnavl@apache.org X-Mailer: svnmailer-1.1.0 Message-Id: <20061212224548.682721A981D@eris.apache.org> X-Virus-Checked: Checked by ClamAV on apache.org Author: myrnavl Date: Tue Dec 12 14:45:47 2006 New Revision: 486380 URL: http://svn.apache.org/viewvc?view=rev&rev=486380 Log: DERBY-1716 - 1 more master/canon to be updated. Modified: db/derby/code/branches/10.2/java/testing/org/apache/derbyTesting/functionTests/master/j9_foundation/grantRevokeDDL.out Modified: db/derby/code/branches/10.2/java/testing/org/apache/derbyTesting/functionTests/master/j9_foundation/grantRevokeDDL.out URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.2/java/testing/org/apache/derbyTesting/functionTests/master/j9_foundation/grantRevokeDDL.out?view=diff&rev=486380&r1=486379&r2=486380 ============================================================================== --- db/derby/code/branches/10.2/java/testing/org/apache/derbyTesting/functionTests/master/j9_foundation/grantRevokeDDL.out (original) +++ db/derby/code/branches/10.2/java/testing/org/apache/derbyTesting/functionTests/master/j9_foundation/grantRevokeDDL.out Tue Dec 12 14:45:47 2006 @@ -2081,9 +2081,9 @@ -- with definer privileges, routines always execute with session user's privilege, even when -- called by an object which runs with definer's privilege insert into t31TriggerTest values(1); -ERROR: Failed with SQLSTATE 40XT0 +ERROR: Failed with SQLSTATE 08003 ij(MAMTA3)> select * from t31TriggerTest; -ERROR: Failed with SQLSTATE 40XT0 +ERROR: Failed with SQLSTATE 08003 ij(MAMTA3)> set connection mamta1; ij(MAMTA1)> select * from t12RoutineTest; C121 @@ -2102,8 +2102,9 @@ insert into mamta3.t31TriggerTest values(1); ERROR: Failed with SQLSTATE 28506 ij(MAMTA2)> set connection mamta3; +ERROR: Failed with SQLSTATE 08003 ij(MAMTA3)> grant insert on t31TriggerTest to mamta2; -ERROR: Failed with SQLSTATE 40XT0 +ERROR: Failed with SQLSTATE 08003 ij(MAMTA3)> set connection mamta2; ij(MAMTA2)> -- should still fail because trigger on mamta3.t31TriggerTest accesses a routine which -- accesses a table on which mamta2 doesn't have SELECT privilege on. mamta3 doesn't @@ -2120,8 +2121,9 @@ insert into mamta3.t31TriggerTest values(1); ERROR: Failed with SQLSTATE 28506 ij(MAMTA2)> set connection mamta3; +ERROR: Failed with SQLSTATE 08003 ij(MAMTA3)> select * from t31TriggerTest; -ERROR: Failed with SQLSTATE 40XT0 +ERROR: Failed with SQLSTATE 08003 ij(MAMTA3)> set connection mamta1; ij(MAMTA1)> select * from t12RoutineTest; C121 @@ -2154,18 +2156,20 @@ ----------- 0 rows selected ij(MAMTA1)> set connection mamta3; +ERROR: Failed with SQLSTATE 08003 ij(MAMTA3)> -- drop the trigger manually drop trigger tr31t31; -ERROR: Failed with SQLSTATE 40XT0 +ERROR: Failed with SQLSTATE 08003 ij(MAMTA3)> set connection mamta1; ij(MAMTA1)> -- Now, we should be able to revoke execute permission on routine because there are no dependent objects on that permission revoke execute on function selectFromSpecificSchema from mamta3 restrict; 0 rows inserted/updated/deleted WARNING 01006: Privilege not revoked from user MAMTA3. ij(MAMTA1)> set connection mamta3; +ERROR: Failed with SQLSTATE 08003 ij(MAMTA3)> -- cleanup drop table t31TriggerTest; -ERROR: Failed with SQLSTATE 40XT0 +ERROR: Failed with SQLSTATE 08003 ij(MAMTA3)> set connection mamta1; ij(MAMTA1)> -- cleanup drop table t12RoutineTest; @@ -2200,15 +2204,16 @@ ij(MAMTA1)> grant execute on function selectFromSpecificSchema to mamta3; 0 rows inserted/updated/deleted ij(MAMTA1)> set connection mamta3; +ERROR: Failed with SQLSTATE 08003 ij(MAMTA3)> drop view v21ViewTest; -ERROR: Failed with SQLSTATE 40XT0 +ERROR: Failed with SQLSTATE 08003 ij(MAMTA3)> -- following will succeed because mamta3 has EXECUTE privileges on the function create view v21ViewTest(c211) as values mamta1.selectFromSpecificSchema(1); -ERROR: Failed with SQLSTATE 40XT0 +ERROR: Failed with SQLSTATE 08003 ij(MAMTA3)> select * from v21ViewTest; -ERROR: Failed with SQLSTATE 40XT0 +ERROR: Failed with SQLSTATE 08003 ij(MAMTA3)> grant select on v21ViewTest to mamta2; -ERROR: Failed with SQLSTATE 40XT0 +ERROR: Failed with SQLSTATE 08003 ij(MAMTA3)> set connection mamta2; ij(MAMTA2)> -- Although mamta2 has SELECT privileges on mamta3.v21ViewTest, mamta2 doesn't have -- SELECT privileges on table mamta1.t12RoutineTest accessed by the routine @@ -2244,12 +2249,13 @@ ij(MAMTA2)> grant select on v21ViewTest to mamta4; ERROR: Failed with SQLSTATE 2850G ij(MAMTA2)> set connection mamta3; +ERROR: Failed with SQLSTATE 08003 ij(MAMTA3)> drop table t31TriggerTest; -ERROR: Failed with SQLSTATE 40XT0 +ERROR: Failed with SQLSTATE 08003 ij(MAMTA3)> create table t31TriggerTest (c311 int); -ERROR: Failed with SQLSTATE 40XT0 +ERROR: Failed with SQLSTATE 08003 ij(MAMTA3)> grant insert on t31TriggerTest to mamta4; -ERROR: Failed with SQLSTATE 40XT0 +ERROR: Failed with SQLSTATE 08003 ij(MAMTA3)> set connection mamta4; ij(MAMTA4)> drop table t41TriggerTest; ERROR: Failed with SQLSTATE 42Y07 @@ -2285,25 +2291,27 @@ 2 2 rows selected ij(MAMTA2)> set connection mamta3; +ERROR: Failed with SQLSTATE 08003 ij(MAMTA3)> select * from t31TriggerTest; -ERROR: Failed with SQLSTATE 40XT0 +ERROR: Failed with SQLSTATE 08003 ij(MAMTA3)> -- will fail because no permissions on mamta4.t41TriggerTest insert into mamta4.t41TriggerTest values(1); -ERROR: Failed with SQLSTATE 40XT0 +ERROR: Failed with SQLSTATE 08003 ij(MAMTA3)> -- will fail because no permissions on mamta2.v21ViewTest select * from mamta2.v21ViewTest; -ERROR: Failed with SQLSTATE 40XT0 +ERROR: Failed with SQLSTATE 08003 ij(MAMTA3)> -- will fail because no permissions on mamta1.t11TriggerTest select * from mamta1.t11TriggerTest; -ERROR: Failed with SQLSTATE 40XT0 +ERROR: Failed with SQLSTATE 08003 ij(MAMTA3)> set connection mamta4; ij(MAMTA4)> grant insert on t41TriggerTest to mamta3; 0 rows inserted/updated/deleted ij(MAMTA4)> set connection mamta3; +ERROR: Failed with SQLSTATE 08003 ij(MAMTA3)> -- although mamta3 doesn't have access to the objects referenced by the insert trigger -- following insert will still pass because triggers run with definer's privileges. insert into mamta4.t41TriggerTest values(1); -ERROR: Failed with SQLSTATE 40XT0 +ERROR: Failed with SQLSTATE 08003 ij(MAMTA3)> -- Test constraints set connection mamta1; ij(MAMTA1)> drop table t11ConstraintTest; @@ -2328,10 +2336,11 @@ ij(MAMTA2)> grant references on t21ConstraintTest to mamta3; 0 rows inserted/updated/deleted ij(MAMTA2)> set connection mamta3; +ERROR: Failed with SQLSTATE 08003 ij(MAMTA3)> create table t31ConstraintTest (c311 int references mamta1.t11ConstraintTest, c312 int references mamta2.t21ConstraintTest); -ERROR: Failed with SQLSTATE 40XT0 +ERROR: Failed with SQLSTATE 08003 ij(MAMTA3)> drop table t31ConstraintTest; -ERROR: Failed with SQLSTATE 40XT0 +ERROR: Failed with SQLSTATE 08003 ij(MAMTA3)> -- multi-key foreign key constraint and the REFERENCES privilege granted at user level. This should cause only -- one row in SYSDEPENDS for REFERENCES privilege. set connection mamta1; @@ -2342,12 +2351,13 @@ ij(MAMTA1)> grant references on t11ConstraintTest to mamta3; 0 rows inserted/updated/deleted ij(MAMTA1)> set connection mamta3; +ERROR: Failed with SQLSTATE 08003 ij(MAMTA3)> drop table t31ConstraintTest; -ERROR: Failed with SQLSTATE 40XT0 +ERROR: Failed with SQLSTATE 08003 ij(MAMTA3)> create table t31ConstraintTest (c311 int, c312 int, foreign key(c311, c312) references mamta1.t11ConstraintTest); -ERROR: Failed with SQLSTATE 40XT0 +ERROR: Failed with SQLSTATE 08003 ij(MAMTA3)> drop table t31ConstraintTest; -ERROR: Failed with SQLSTATE 40XT0 +ERROR: Failed with SQLSTATE 08003 ij(MAMTA3)> -- Same test as above with multi-key foreign key constraint but one column REFERENCES privilege granted at user level -- and other column REFERENCES privilege granted at PUBLIC level. This should cause two rows in SYSDEPENDS for REFERENCES privilege. set connection mamta1; @@ -2361,27 +2371,28 @@ 0 rows inserted/updated/deleted ij(MAMTA1)> --connect 'jdbc:derby:c:/dellater/dbmaintest2;create=true' user 'mamta3' as mamta3; set connection mamta3; +ERROR: Failed with SQLSTATE 08003 ij(MAMTA3)> drop table t31ConstraintTest; -ERROR: Failed with SQLSTATE 40XT0 +ERROR: Failed with SQLSTATE 08003 ij(MAMTA3)> create table t31ConstraintTest (c311 int, c312 int, foreign key(c311, c312) references mamta1.t11ConstraintTest); -ERROR: Failed with SQLSTATE 40XT0 +ERROR: Failed with SQLSTATE 08003 ij(MAMTA3)> drop table t31ConstraintTest; -ERROR: Failed with SQLSTATE 40XT0 +ERROR: Failed with SQLSTATE 08003 ij(MAMTA3)> -- Same test as above with multi-key foreign key constraint, one column REFERENCES privilege granted at user level -- and other column REFERENCES privilege granted at PUBLIC level. This should cause two rows in SYSDEPENDS for REFERENCES privilege. -- But foreign key reference is added using alter table rather than at create table time create table t31constrainttest(c311 int, c312 int); -ERROR: Failed with SQLSTATE 40XT0 +ERROR: Failed with SQLSTATE 08003 ij(MAMTA3)> alter table t31constrainttest add foreign key (c311, c312) references mamta1.t11constrainttest; -ERROR: Failed with SQLSTATE 40XT0 +ERROR: Failed with SQLSTATE 08003 ij(MAMTA3)> drop table t31ConstraintTest; -ERROR: Failed with SQLSTATE 40XT0 +ERROR: Failed with SQLSTATE 08003 ij(MAMTA3)> -- create the table again, but this time one foreign key constraint on one table with single column primary key and -- another foreign key constraint on another table with multi-column primary key create table t31constrainttest(c311 int, c312 int, c313 int references mamta2.t21ConstraintTest); -ERROR: Failed with SQLSTATE 40XT0 +ERROR: Failed with SQLSTATE 08003 ij(MAMTA3)> alter table t31constrainttest add foreign key (c311, c312) references mamta1.t11constrainttest; -ERROR: Failed with SQLSTATE 40XT0 +ERROR: Failed with SQLSTATE 08003 ij(MAMTA3)> -- revoke of TRIGGERS and other privileges should drop dependent triggers set connection mamta1; ij(MAMTA1)> drop table t11TriggerRevokeTest; @@ -3060,12 +3071,13 @@ ij(MAMTA1)> grant references on d1589t11ConstraintTest to mamta3; 0 rows inserted/updated/deleted ij(MAMTA1)> set connection mamta3; +ERROR: Failed with SQLSTATE 08003 ij(MAMTA3)> drop table d1589t31ConstraintTest; -ERROR: Failed with SQLSTATE 40XT0 +ERROR: Failed with SQLSTATE 08003 ij(MAMTA3)> create table d1589t31ConstraintTest (c311 int, c312 int, foreign key(c311, c312) references mamta1.d1589t11ConstraintTest); -ERROR: Failed with SQLSTATE 40XT0 +ERROR: Failed with SQLSTATE 08003 ij(MAMTA3)> drop table d1589t31ConstraintTest; -ERROR: Failed with SQLSTATE 40XT0 +ERROR: Failed with SQLSTATE 08003 ij(MAMTA3)> set connection mamta1; ij(MAMTA1)> drop table d1589t11ConstraintTest; 0 rows inserted/updated/deleted @@ -3076,8 +3088,9 @@ ij(MAMTA1)> grant references(c112) on d1589t11ConstraintTest to PUBLIC; 0 rows inserted/updated/deleted ij(MAMTA1)> set connection mamta3; +ERROR: Failed with SQLSTATE 08003 ij(MAMTA3)> create table d1589t31ConstraintTest (c311 int, c312 int, foreign key(c311, c312) references mamta1.d1589t11ConstraintTest); -ERROR: Failed with SQLSTATE 40XT0 +ERROR: Failed with SQLSTATE 08003 ij(MAMTA3)> -- DERBY-1847 SELECT statement asserts with XJ001 when attempted to select a newly added column -- Grant access on 2 columns and then add another column to the table. The select on the new column -- by another user should complain about no permissions granted on that new column. @@ -3089,34 +3102,118 @@ ij(MAMTA2)> alter table t1Derby1847 add c3 int; 0 rows inserted/updated/deleted ij(MAMTA2)> set connection mamta3; +ERROR: Failed with SQLSTATE 08003 ij(MAMTA3)> -- should fail because mamta3 doesn't have any permission on this column in table mamta2.t1Derby1847 select c3 from mamta2.t1Derby1847; -ERROR: Failed with SQLSTATE 40XT0 +ERROR: Failed with SQLSTATE 08003 ij(MAMTA3)> set connection mamta2; ij(MAMTA2)> grant select on t1Derby1847 to mamta3; 0 rows inserted/updated/deleted ij(MAMTA2)> set connection mamta3; +ERROR: Failed with SQLSTATE 08003 ij(MAMTA3)> -- should work now because mamta3 got select permission on new column in table mamta2.t1Derby1847 through table level select permission select c3 from mamta2.t1Derby1847; -ERROR: Failed with SQLSTATE 40XT0 +ERROR: Failed with SQLSTATE 08003 ij(MAMTA3)> set connection mamta2; ij(MAMTA2)> revoke select on t1Derby1847 from mamta3; 0 rows inserted/updated/deleted ij(MAMTA2)> set connection mamta3; +ERROR: Failed with SQLSTATE 08003 ij(MAMTA3)> -- should fail because mamta3 lost it's select permission on new column in table mamta2.t1Derby1847 select c3 from mamta2.t1Derby1847; -ERROR: Failed with SQLSTATE 40XT0 +ERROR: Failed with SQLSTATE 08003 ij(MAMTA3)> set connection mamta2; ij(MAMTA2)> grant select(c3) on t1Derby1847 to mamta3; 0 rows inserted/updated/deleted ij(MAMTA2)> set connection mamta3; +ERROR: Failed with SQLSTATE 08003 ij(MAMTA3)> -- should work now because mamta3 got select permission on new column in table mamta2.t1Derby1847 through column level select permission select c3 from mamta2.t1Derby1847; -ERROR: Failed with SQLSTATE 40XT0 +ERROR: Failed with SQLSTATE 08003 ij(MAMTA3)> set connection mamta2; ij(MAMTA2)> drop table t1Derby1847; 0 rows inserted/updated/deleted ij(MAMTA2)> set connection mamta3; +ERROR: Failed with SQLSTATE 08003 ij(MAMTA3)> select c3 from mamta2.t1Derby1847; -ERROR: Failed with SQLSTATE 40XT0 -ij(MAMTA3)> +ERROR: Failed with SQLSTATE 08003 +ij(MAMTA3)> -- DERBY-1716 +-- Revoking select privilege from a user times out when that user still have +-- a cursor open before the patch. +set connection user1; +ij(USER1)> drop table t1; +ERROR: Failed with SQLSTATE 42Y55 +ij(USER1)> create table t1 (c varchar(1)); +0 rows inserted/updated/deleted +ij(USER1)> insert into t1 values 'a', 'b', 'c'; +3 rows inserted/updated/deleted +ij(USER1)> grant select on t1 to user2; +0 rows inserted/updated/deleted +ij(USER1)> set connection user2; +ij(USER2)> autocommit off; +ij(USER2)> GET CURSOR crs1 AS 'select * from user1.t1'; +ij(USER2)> next crs1; +C +---- +a +ij(USER2)> set connection user1; +ij(USER1)> -- should succeed without blocking +revoke select on t1 from user2; +0 rows inserted/updated/deleted +ij(USER1)> set connection user2; +ij(USER2)> -- still ok to fetch. +next crs1; +C +---- +b +ij(USER2)> next crs1; +C +---- +c +ij(USER2)> close crs1; +ij(USER2)> commit; +ij(USER2)> -- should fail since select privilege got revoked +GET CURSOR crs1 AS 'select * from user1.t1'; +ERROR: Failed with SQLSTATE 28508 +ij(USER2)> next crs1; +IJ ERROR: Unable to establish cursor +ij(USER2)> close crs1; +IJ ERROR: Unable to establish cursor +ij(USER2)> autocommit on; +ij(USER2)> -- repeat the scenario +set connection user1; +ij(USER1)> grant select on t1 to user2; +0 rows inserted/updated/deleted +ij(USER1)> set connection user2; +ij(USER2)> autocommit off; +ij(USER2)> GET CURSOR crs1 AS 'select * from user1.t1'; +ij(USER2)> next crs1; +C +---- +a +ij(USER2)> set connection user1; +ij(USER1)> -- should succeed without blocking +revoke select on t1 from user2; +0 rows inserted/updated/deleted +ij(USER1)> set connection user2; +ij(USER2)> -- still ok to fetch. +next crs1; +C +---- +b +ij(USER2)> next crs1; +C +---- +c +ij(USER2)> close crs1; +ij(USER2)> commit; +ij(USER2)> -- should fail since select privilege got revoked +GET CURSOR crs1 AS 'select * from user1.t1'; +ERROR: Failed with SQLSTATE 28508 +ij(USER2)> next crs1; +IJ ERROR: Unable to establish cursor +ij(USER2)> close crs1; +IJ ERROR: Unable to establish cursor +ij(USER2)> autocommit on; +ij(USER2)> set connection user1; +ij(USER1)>