db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From myrn...@apache.org
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 GMT
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)> 



Mime
View raw message