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 dj...@apache.org on 2006/08/04 19:57:54 UTC
svn commit: r428801 - in /db/derby/code/trunk/java:
engine/org/apache/derby/iapi/sql/dictionary/
engine/org/apache/derby/impl/sql/execute/
testing/org/apache/derbyTesting/functionTests/master/
testing/org/apache/derbyTesting/functionTests/tests/lang/
Author: djd
Date: Fri Aug 4 10:57:54 2006
New Revision: 428801
URL: http://svn.apache.org/viewvc?rev=428801&view=rev
Log:
DERBY-1612 (partial) If revoke statement finds a constraint dependent on the table/column on which privilege
is being revoked, the constraint will be dropped automatically. This functionality is similar to what is
supported for triggers and views. And just like triggers and views, more work is required so that constraint will get
dropped only if it depends on the particular privilege TYPE or particular column that is being revoked.
Patch contributed by Mamta Satoor.
Modified:
db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/dictionary/ConstraintDescriptor.java
db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/AlterTableConstantAction.java
db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/DropConstraintConstantAction.java
db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/DropIndexConstantAction.java
db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/DropTableConstantAction.java
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/grantRevokeDDL.out
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/grantRevokeDDL.sql
Modified: db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/dictionary/ConstraintDescriptor.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/dictionary/ConstraintDescriptor.java?rev=428801&r1=428800&r2=428801&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/dictionary/ConstraintDescriptor.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/dictionary/ConstraintDescriptor.java Fri Aug 4 10:57:54 2006
@@ -35,6 +35,8 @@
import org.apache.derby.iapi.sql.depend.DependencyManager;
import org.apache.derby.iapi.sql.conn.LanguageConnectionContext;
+import org.apache.derby.impl.sql.execute.DropConstraintConstantAction;
+
/**
* This class is used to get information from a ConstraintDescriptor.
* A ConstraintDescriptor can represent a constraint on a table or on a
@@ -580,7 +582,13 @@
{
if (action == DependencyManager.REVOKE_PRIVILEGE)
{
- //for now, ignore revoke privilege action
+ DropConstraintConstantAction.dropConstraintAndIndex(
+ getDataDictionary().getDependencyManager(),
+ table,
+ getDataDictionary(),
+ this,
+ lcc.getTransactionExecute(),
+ lcc, true);
return;
}
Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/AlterTableConstantAction.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/AlterTableConstantAction.java?rev=428801&r1=428800&r2=428801&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/AlterTableConstantAction.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/AlterTableConstantAction.java Fri Aug 4 10:57:54 2006
@@ -821,7 +821,7 @@
dm.invalidateFor(cd, DependencyManager.DROP_CONSTRAINT,
lcc);
DropConstraintConstantAction.dropConstraintAndIndex(dm, td, dd,
- cd, tc, activation, true);
+ cd, tc, lcc, true);
activation.addWarning(StandardException.newWarning(SQLState.LANG_CONSTRAINT_DROPPED,
cd.getConstraintName(), td.getName()));
}
@@ -830,7 +830,7 @@
{
ConstraintDescriptor cd = toBeRemoved[i];
DropConstraintConstantAction.dropConstraintAndIndex(dm, td, dd, cd,
- tc, activation, false);
+ tc, lcc, false);
activation.addWarning(StandardException.newWarning(SQLState.LANG_CONSTRAINT_DROPPED,
cd.getConstraintName(), td.getName()));
@@ -845,7 +845,7 @@
lcc);
DropConstraintConstantAction.dropConstraintAndIndex(
- dm, fkcd.getTableDescriptor(), dd, fkcd, tc, activation, true);
+ dm, fkcd.getTableDescriptor(), dd, fkcd, tc, lcc, true);
activation.addWarning(StandardException.newWarning(SQLState.LANG_CONSTRAINT_DROPPED,
fkcd.getConstraintName(), fkcd.getTableDescriptor().getName()));
}
@@ -1590,7 +1590,7 @@
*/
ConglomerateDescriptor cd = td.getConglomerateDescriptor
(indexConglomerateNumbers[i]);
- DropIndexConstantAction.dropIndex(dm, dd, tc, cd, td, activation);
+ DropIndexConstantAction.dropIndex(dm, dd, tc, cd, td, activation.getLanguageConnectionContext());
compressIRGs[i] = null; // mark it
continue;
Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/DropConstraintConstantAction.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/DropConstraintConstantAction.java?rev=428801&r1=428800&r2=428801&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/DropConstraintConstantAction.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/DropConstraintConstantAction.java Fri Aug 4 10:57:54 2006
@@ -65,7 +65,7 @@
* @author Jerry Brenner
*/
-class DropConstraintConstantAction extends ConstraintConstantAction
+public class DropConstraintConstantAction extends ConstraintConstantAction
{
private boolean cascade; // default false
@@ -227,7 +227,7 @@
** be repeatedly changing the reference count of the referenced
** key and generating unnecessary I/O.
*/
- dropConstraintAndIndex(dm, td, dd, conDesc, tc, activation, !cascadeOnRefKey);
+ dropConstraintAndIndex(dm, td, dd, conDesc, tc, lcc, !cascadeOnRefKey);
if (cascadeOnRefKey)
{
@@ -244,7 +244,7 @@
fkcd = (ForeignKeyConstraintDescriptor) cdl.elementAt(index);
dm.invalidateFor(fkcd, DependencyManager.DROP_CONSTRAINT, lcc);
dropConstraintAndIndex(dm, fkcd.getTableDescriptor(), dd, fkcd,
- tc, activation, true);
+ tc, lcc, true);
}
/*
@@ -263,12 +263,12 @@
* from the list on the table descriptor. Does NOT
* do an dm.invalidateFor()
*/
- protected static void dropConstraintAndIndex(DependencyManager dm,
+ public static void dropConstraintAndIndex(DependencyManager dm,
TableDescriptor td,
DataDictionary dd,
ConstraintDescriptor conDesc,
TransactionController tc,
- Activation activation,
+ LanguageConnectionContext lcc,
boolean clearDependencies)
throws StandardException
{
@@ -280,7 +280,7 @@
if (clearDependencies)
{
- dm.clearDependencies(activation.getLanguageConnectionContext(), conDesc);
+ dm.clearDependencies(lcc, conDesc);
}
/* Drop the constraint.
@@ -313,7 +313,8 @@
if (conglomDescs[i].isConstraint())
{
DropIndexConstantAction.dropIndex(dm, dd, tc,
- conglomDescs[i], td, activation);
+ conglomDescs[i], td,
+ lcc);
break;
}
}
Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/DropIndexConstantAction.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/DropIndexConstantAction.java?rev=428801&r1=428800&r2=428801&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/DropIndexConstantAction.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/DropIndexConstantAction.java Fri Aug 4 10:57:54 2006
@@ -178,7 +178,7 @@
* since the conglomerate descriptor may be looked up as part of
* decoding tuples in SYSDEPENDS.
*/
- dropIndex(dm, dd, tc, cd, td, activation);
+ dropIndex(dm, dd, tc, cd, td, activation.getLanguageConnectionContext());
}
public static void dropIndex(DependencyManager dm,
@@ -186,10 +186,9 @@
TransactionController tc,
ConglomerateDescriptor cd,
TableDescriptor td,
- Activation act)
+ LanguageConnectionContext lcc)
throws StandardException
{
- LanguageConnectionContext lcc = act.getLanguageConnectionContext();
if (SanityManager.DEBUG)
{
SanityManager.ASSERT(tc != null, "tc is null");
Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/DropTableConstantAction.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/DropTableConstantAction.java?rev=428801&r1=428800&r2=428801&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/DropTableConstantAction.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/DropTableConstantAction.java Fri Aug 4 10:57:54 2006
@@ -313,7 +313,7 @@
dm.invalidateFor(cd, DependencyManager.DROP_CONSTRAINT, lcc);
DropConstraintConstantAction.dropConstraintAndIndex(dm, td, dd, cd,
- tc, activation, true);
+ tc, lcc, true);
}
/*
@@ -345,7 +345,7 @@
** doesn't clear dependencies, we'll do that ourselves.
*/
DropConstraintConstantAction.dropConstraintAndIndex(dm, td, dd, cd,
- tc, activation, false);
+ tc, lcc, false);
/*
** If we are going to cascade, get all the
@@ -370,7 +370,7 @@
dm.invalidateFor(fkcd, DependencyManager.DROP_CONSTRAINT, lcc);
DropConstraintConstantAction.dropConstraintAndIndex(
dm, fkcd.getTableDescriptor(), dd, fkcd,
- tc, activation, true);
+ tc, lcc, true);
activation.addWarning(
StandardException.newWarning(SQLState.LANG_CONSTRAINT_DROPPED,
fkcd.getConstraintName(),
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/grantRevokeDDL.out
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/grantRevokeDDL.out?rev=428801&r1=428800&r2=428801&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/grantRevokeDDL.out (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/grantRevokeDDL.out Fri Aug 4 10:57:54 2006
@@ -1442,6 +1442,388 @@
0 rows inserted/updated/deleted
ij(MAMTA1)> drop table t11ViewTest;
0 rows inserted/updated/deleted
+ij(MAMTA1)> -- Constraint test
+-- test1
+-- Give a constraint privilege at table level to a user. Let user define a foreign key constraint based on that privilege.
+-- Later revoke that references privilege and make sure that foreign key constraint gets dropped
+set connection mamta1;
+ij(MAMTA1)> drop table t11ConstraintTest;
+ERROR: Failed with SQLSTATE 42Y55
+ij(MAMTA1)> create table t11ConstraintTest (c111 int not null primary key);
+0 rows inserted/updated/deleted
+ij(MAMTA1)> insert into t11ConstraintTest values(1);
+1 row inserted/updated/deleted
+ij(MAMTA1)> insert into t11ConstraintTest values(2);
+1 row inserted/updated/deleted
+ij(MAMTA1)> grant references on t11ConstraintTest to mamta2;
+0 rows inserted/updated/deleted
+ij(MAMTA1)> set connection mamta2;
+ij(MAMTA2)> drop table t21ConstraintTest;
+ERROR: Failed with SQLSTATE 42Y55
+ij(MAMTA2)> create table t21ConstraintTest (c211 int references mamta1.t11ConstraintTest, c212 int);
+0 rows inserted/updated/deleted
+ij(MAMTA2)> insert into t21ConstraintTest values(1,1);
+1 row inserted/updated/deleted
+ij(MAMTA2)> -- should fail because the foreign key constraint will fail
+insert into t21ConstraintTest values(3,1);
+ERROR: Failed with SQLSTATE 23503
+ij(MAMTA2)> set connection mamta1;
+ij(MAMTA1)> revoke references on t11ConstraintTest from mamta2;
+0 rows inserted/updated/deleted
+ij(MAMTA1)> set connection mamta2;
+ij(MAMTA2)> -- will pass because the foreign key constraint got dropped because of revoke statement
+insert into t21ConstraintTest values(3,1);
+1 row inserted/updated/deleted
+ij(MAMTA2)> -- cleanup
+set connection mamta2;
+ij(MAMTA2)> drop table t21ConstraintTest;
+0 rows inserted/updated/deleted
+ij(MAMTA2)> set connection mamta1;
+ij(MAMTA1)> drop table t11ConstraintTest;
+0 rows inserted/updated/deleted
+ij(MAMTA1)> -- Constraint test
+-- test2
+-- Have user mamta1 give a references privilege to mamta3.
+-- Have user mamta2 give a references privilege to mamta3.
+-- Have mamta3 create a table with 2 foreign key constraints relying on both these granted privileges.
+-- Revoke one of those privileges and make sure that the foreign key constraint defined based on that privilege gets dropped.
+-- Now revoke the 2nd references privilege and make sure that remaining foreign key constraint gets dropped
+set connection mamta1;
+ij(MAMTA1)> drop table t11ConstraintTest;
+ERROR: Failed with SQLSTATE 42Y55
+ij(MAMTA1)> create table t11ConstraintTest (c111 int not null primary key);
+0 rows inserted/updated/deleted
+ij(MAMTA1)> insert into t11ConstraintTest values(1);
+1 row inserted/updated/deleted
+ij(MAMTA1)> insert into t11ConstraintTest values(2);
+1 row inserted/updated/deleted
+ij(MAMTA1)> grant references on t11ConstraintTest to mamta3;
+0 rows inserted/updated/deleted
+ij(MAMTA1)> set connection mamta2;
+ij(MAMTA2)> drop table t21ConstraintTest;
+ERROR: Failed with SQLSTATE 42Y55
+ij(MAMTA2)> create table t21ConstraintTest (c111 int not null primary key);
+0 rows inserted/updated/deleted
+ij(MAMTA2)> insert into t21ConstraintTest values(1);
+1 row inserted/updated/deleted
+ij(MAMTA2)> insert into t21ConstraintTest values(2);
+1 row inserted/updated/deleted
+ij(MAMTA2)> grant references on t21ConstraintTest to mamta3;
+0 rows inserted/updated/deleted
+ij(MAMTA2)> set connection mamta3;
+ij(MAMTA3)> drop table t31ConstraintTest;
+ERROR: Failed with SQLSTATE 42Y55
+ij(MAMTA3)> create table t31ConstraintTest (c311 int references mamta1.t11ConstraintTest, c312 int references mamta2.t21ConstraintTest);
+0 rows inserted/updated/deleted
+ij(MAMTA3)> select * from t31ConstraintTest;
+C311 |C312
+-----------------------
+0 rows selected
+ij(MAMTA3)> insert into t31ConstraintTest values(1,1);
+1 row inserted/updated/deleted
+ij(MAMTA3)> -- following should fail because it violates the foreign key reference by column c312
+insert into t31ConstraintTest values(1,3);
+ERROR: Failed with SQLSTATE 23503
+ij(MAMTA3)> -- following should fail because it violates the foreign key reference by column c311
+insert into t31ConstraintTest values(3,1);
+ERROR: Failed with SQLSTATE 23503
+ij(MAMTA3)> -- following should fail because it violates the foreign key reference by column c311 and c312
+insert into t31ConstraintTest values(3,4);
+ERROR: Failed with SQLSTATE 23503
+ij(MAMTA3)> set connection mamta2;
+ij(MAMTA2)> -- the following revoke should drop the foreign key reference by column t31ConstraintTest.c312
+revoke references on t21ConstraintTest from mamta3;
+0 rows inserted/updated/deleted
+ij(MAMTA2)> set connection mamta3;
+ij(MAMTA3)> -- verify that foreign key reference by column t31ConstraintTest.c312 got dropped by inserting a row.
+-- following should pass
+insert into t31ConstraintTest values(1,3);
+1 row inserted/updated/deleted
+ij(MAMTA3)> -- following should still fail because foreign key reference by column c311 is still around
+insert into t31ConstraintTest values(3,1);
+ERROR: Failed with SQLSTATE 23503
+ij(MAMTA3)> set connection mamta1;
+ij(MAMTA1)> -- now drop the references privilege so that the only foreign key reference on table mamta3.t31ConstraintTest will get dropped
+revoke references on t11ConstraintTest from mamta3;
+0 rows inserted/updated/deleted
+ij(MAMTA1)> set connection mamta3;
+ij(MAMTA3)> -- verify that foreign key reference by column t31ConstraintTest.c311 got dropped by inserting a row.
+-- following should pass
+insert into t31ConstraintTest values(3,1);
+1 row inserted/updated/deleted
+ij(MAMTA3)> -- no more foreign key references left and hence following should pass
+insert into t31ConstraintTest values(3,3);
+1 row inserted/updated/deleted
+ij(MAMTA3)> -- cleanup
+drop table t31ConstraintTest;
+0 rows inserted/updated/deleted
+ij(MAMTA3)> set connection mamta2;
+ij(MAMTA2)> drop table t21ConstraintTest;
+0 rows inserted/updated/deleted
+ij(MAMTA2)> set connection mamta1;
+ij(MAMTA1)> drop table t11ConstraintTest;
+0 rows inserted/updated/deleted
+ij(MAMTA1)> -- Constraint test
+-- test3
+-- Have mamta1 grant REFERENCES privilege on one of it's tables to mamta2
+-- Have mamta2 create a table with primary which references mamta1's granted REFERENCES privilege
+-- Have mamta2 grant REFERENCES privilege on that table to user mamta3
+-- Have mamta3 create a table which references mamta2's granted REFERENCES privilege
+-- Now revoke of granted REFERENCES privilege by mamta1 should drop the foreign key reference
+-- by mamta2's table t21ConstraintTest. It should not impact the foreign key reference by
+-- mamta3's table t31ConstraintTest.
+-- a)mamta1.t11ConstraintTest (primary key)
+-- b)mamta2.t21ConstraintTest (primary key references t11ConstraintTest)
+-- c)mamta3.t31ConstraintTest (primary key references t21ConstraintTest)
+set connection mamta1;
+ij(MAMTA1)> drop table t11ConstraintTest;
+ERROR: Failed with SQLSTATE 42Y55
+ij(MAMTA1)> create table t11ConstraintTest (c111 int not null primary key);
+0 rows inserted/updated/deleted
+ij(MAMTA1)> insert into t11ConstraintTest values(1);
+1 row inserted/updated/deleted
+ij(MAMTA1)> insert into t11ConstraintTest values(2);
+1 row inserted/updated/deleted
+ij(MAMTA1)> grant references on t11ConstraintTest to mamta2;
+0 rows inserted/updated/deleted
+ij(MAMTA1)> set connection mamta2;
+ij(MAMTA2)> drop table t21ConstraintTest;
+ERROR: Failed with SQLSTATE 42Y55
+ij(MAMTA2)> create table t21ConstraintTest (c111 int not null primary key references mamta1.t11ConstraintTest);
+0 rows inserted/updated/deleted
+ij(MAMTA2)> insert into t21ConstraintTest values(1);
+1 row inserted/updated/deleted
+ij(MAMTA2)> insert into t21ConstraintTest values(2);
+1 row inserted/updated/deleted
+ij(MAMTA2)> -- following should fail because of foreign key constraint failure
+insert into t21ConstraintTest values(3);
+ERROR: Failed with SQLSTATE 23503
+ij(MAMTA2)> grant references on t21ConstraintTest to mamta3;
+0 rows inserted/updated/deleted
+ij(MAMTA2)> set connection mamta3;
+ij(MAMTA3)> drop table t31ConstraintTest;
+ERROR: Failed with SQLSTATE 42Y55
+ij(MAMTA3)> create table t31ConstraintTest (c311 int references mamta2.t21ConstraintTest);
+0 rows inserted/updated/deleted
+ij(MAMTA3)> select * from t31ConstraintTest;
+C311
+-----------
+0 rows selected
+ij(MAMTA3)> insert into t31ConstraintTest values (1);
+1 row inserted/updated/deleted
+ij(MAMTA3)> -- following should fail because of foreign key constraint failure
+insert into t31ConstraintTest values (4);
+ERROR: Failed with SQLSTATE 23503
+ij(MAMTA3)> set connection mamta1;
+ij(MAMTA1)> -- This revoke should drop foreign key constraint on mamta2.t21ConstraintTest
+-- This revoke should not impact the foeign key constraint on mamta3.t31ConstraintTest
+revoke references on t11ConstraintTest from mamta2;
+0 rows inserted/updated/deleted
+ij(MAMTA1)> set connection mamta2;
+ij(MAMTA2)> -- because the foreign key reference got revoked, no constraint violation check will be done
+insert into t21ConstraintTest values(3);
+1 row inserted/updated/deleted
+ij(MAMTA2)> set connection mamta3;
+ij(MAMTA3)> -- Make sure the foreign key constraint on t31ConstraintTest is still active
+insert into t31ConstraintTest values(3);
+1 row inserted/updated/deleted
+ij(MAMTA3)> -- because the foreign key constraint is still around, following should fail
+insert into t31ConstraintTest values(4);
+ERROR: Failed with SQLSTATE 23503
+ij(MAMTA3)> -- cleanup
+set connection mamta3;
+ij(MAMTA3)> drop table t31ConstraintTest;
+0 rows inserted/updated/deleted
+ij(MAMTA3)> set connection mamta2;
+ij(MAMTA2)> drop table t21ConstraintTest;
+0 rows inserted/updated/deleted
+ij(MAMTA2)> set connection mamta1;
+ij(MAMTA1)> drop table t11ConstraintTest;
+0 rows inserted/updated/deleted
+ij(MAMTA1)> -- Constraint test
+-- test4
+-- Grant a REFERENCES permission at public level, create constraint, grant same permission at user level
+-- and take away the public level permission. It ends up dropping the constraint. DERBY-1632
+set connection mamta1;
+ij(MAMTA1)> drop table t11ConstraintTest;
+ERROR: Failed with SQLSTATE 42Y55
+ij(MAMTA1)> create table t11ConstraintTest (c111 int not null primary key);
+0 rows inserted/updated/deleted
+ij(MAMTA1)> insert into t11ConstraintTest values(1);
+1 row inserted/updated/deleted
+ij(MAMTA1)> insert into t11ConstraintTest values(2);
+1 row inserted/updated/deleted
+ij(MAMTA1)> grant references on t11ConstraintTest to PUBLIC;
+0 rows inserted/updated/deleted
+ij(MAMTA1)> set connection mamta2;
+ij(MAMTA2)> drop table t21ConstraintTest;
+ERROR: Failed with SQLSTATE 42Y55
+ij(MAMTA2)> create table t21ConstraintTest (c111 int not null primary key, constraint fk foreign key(c111) references mamta1.t11ConstraintTest);
+0 rows inserted/updated/deleted
+ij(MAMTA2)> insert into t21ConstraintTest values(1);
+1 row inserted/updated/deleted
+ij(MAMTA2)> insert into t21ConstraintTest values(2);
+1 row inserted/updated/deleted
+ij(MAMTA2)> -- following should fail because of foreign key constraint failure
+insert into t21ConstraintTest values(3);
+ERROR: Failed with SQLSTATE 23503
+ij(MAMTA2)> set connection mamta1;
+ij(MAMTA1)> -- grant REFERENCES permission again but this time at user level
+grant references on t11ConstraintTest to mamta2;
+0 rows inserted/updated/deleted
+ij(MAMTA1)> -- Now, revoke REFERENCES permission which was granted at PUBLIC level, This drops the constraint.
+-- DERBY-1632. This should be fixed at some point so that constraint won't get dropped, instead
+-- it will start depending on same privilege available at user-level
+revoke references on t11ConstraintTest from PUBLIC;
+0 rows inserted/updated/deleted
+ij(MAMTA1)> set connection mamta2;
+ij(MAMTA2)> -- because the foreign key reference got revoked, no constraint violation check will be done
+insert into t21ConstraintTest values(3);
+1 row inserted/updated/deleted
+ij(MAMTA2)> -- cleanup
+set connection mamta2;
+ij(MAMTA2)> drop table t21ConstraintTest;
+0 rows inserted/updated/deleted
+ij(MAMTA2)> set connection mamta1;
+ij(MAMTA1)> drop table t11ConstraintTest;
+0 rows inserted/updated/deleted
+ij(MAMTA1)> -- Constraint test
+-- test5
+-- Grant refrences privilege and select privilege on a table. Have a constraint depend on the references
+-- privilege. Later, a revoke of select privilege will end up dropping the constraint which shouldn't
+-- happen. This will be addressed in a subsequent patch
+set connection mamta1;
+ij(MAMTA1)> drop table t11ConstraintTest;
+ERROR: Failed with SQLSTATE 42Y55
+ij(MAMTA1)> create table t11ConstraintTest (c111 int not null primary key);
+0 rows inserted/updated/deleted
+ij(MAMTA1)> insert into t11ConstraintTest values(1);
+1 row inserted/updated/deleted
+ij(MAMTA1)> insert into t11ConstraintTest values(2);
+1 row inserted/updated/deleted
+ij(MAMTA1)> grant references on t11ConstraintTest to PUBLIC;
+0 rows inserted/updated/deleted
+ij(MAMTA1)> grant select on t11ConstraintTest to PUBLIC;
+0 rows inserted/updated/deleted
+ij(MAMTA1)> set connection mamta2;
+ij(MAMTA2)> drop table t21ConstraintTest;
+ERROR: Failed with SQLSTATE 42Y55
+ij(MAMTA2)> create table t21ConstraintTest (c111 int not null primary key, constraint fk foreign key(c111) references mamta1.t11ConstraintTest);
+0 rows inserted/updated/deleted
+ij(MAMTA2)> insert into t21ConstraintTest values(1);
+1 row inserted/updated/deleted
+ij(MAMTA2)> insert into t21ConstraintTest values(2);
+1 row inserted/updated/deleted
+ij(MAMTA2)> -- following should fail because of foreign key constraint failure
+insert into t21ConstraintTest values(3);
+ERROR: Failed with SQLSTATE 23503
+ij(MAMTA2)> set connection mamta1;
+ij(MAMTA1)> -- revoke of select privilege is going to drop the constraint which is incorrect. Will be handled in a later patch
+revoke select on t11ConstraintTest from PUBLIC;
+0 rows inserted/updated/deleted
+ij(MAMTA1)> set connection mamta2;
+ij(MAMTA2)> -- following should have failed but it doesn't because foreign key constraint got dropped by revoke select privilege
+-- Will be fixed in a subsequent patch
+insert into t21ConstraintTest values(3);
+1 row inserted/updated/deleted
+ij(MAMTA2)> -- cleanup
+set connection mamta2;
+ij(MAMTA2)> drop table t21ConstraintTest;
+0 rows inserted/updated/deleted
+ij(MAMTA2)> set connection mamta1;
+ij(MAMTA1)> drop table t11ConstraintTest;
+0 rows inserted/updated/deleted
+ij(MAMTA1)> -- Constraint test
+-- test6
+-- Have a primary key and a unique key on a table and grant reference on both. Have another table rely on unique
+-- key references privilege to create a foreign key constraint. Later, the revoke of primary key reference will end up
+-- dropping the foreign key constraint. This will be fixed in a subsequent patch (same as test5)
+set connection mamta1;
+ij(MAMTA1)> drop table t11ConstraintTest;
+ERROR: Failed with SQLSTATE 42Y55
+ij(MAMTA1)> create table t11ConstraintTest (c111 int not null primary key, c112 int not null unique, c113 int);
+0 rows inserted/updated/deleted
+ij(MAMTA1)> insert into t11ConstraintTest values(1,1,1);
+1 row inserted/updated/deleted
+ij(MAMTA1)> insert into t11ConstraintTest values(2,2,1);
+1 row inserted/updated/deleted
+ij(MAMTA1)> grant references(c111, c112) on t11ConstraintTest to PUBLIC;
+0 rows inserted/updated/deleted
+ij(MAMTA1)> set connection mamta2;
+ij(MAMTA2)> drop table t21ConstraintTest;
+ERROR: Failed with SQLSTATE 42Y55
+ij(MAMTA2)> create table t21ConstraintTest (c111 int not null primary key, constraint fk foreign key(c111) references mamta1.t11ConstraintTest(c112));
+0 rows inserted/updated/deleted
+ij(MAMTA2)> insert into t21ConstraintTest values(1);
+1 row inserted/updated/deleted
+ij(MAMTA2)> insert into t21ConstraintTest values(2);
+1 row inserted/updated/deleted
+ij(MAMTA2)> -- following should fail because of foreign key constraint failure
+insert into t21ConstraintTest values(3);
+ERROR: Failed with SQLSTATE 23503
+ij(MAMTA2)> set connection mamta1;
+ij(MAMTA1)> -- revoke of references privilege on c111 which is not used by foreign key constraint on t21ConstraintTest ends up dropping that
+-- foreign key constraint. This Will be handled in a later patch
+revoke references(c111) on t11ConstraintTest from PUBLIC;
+0 rows inserted/updated/deleted
+ij(MAMTA1)> set connection mamta2;
+ij(MAMTA2)> -- following should have failed but it doesn't because foreign key constraint got dropped by revoke references privilege
+-- Will be fixed in a subsequent patch
+insert into t21ConstraintTest values(3);
+1 row inserted/updated/deleted
+ij(MAMTA2)> -- cleanup
+-- Miscellaneous test
+-- test1
+-- Have multiple objects depends on a privilege and make sure they all get dropped when that privilege is revoked.
+set connection mamta1;
+ij(MAMTA1)> drop table t11MiscTest;
+ERROR: Failed with SQLSTATE 42Y55
+ij(MAMTA1)> create table t11MiscTest (c111 int, c112 int, c113 int);
+0 rows inserted/updated/deleted
+ij(MAMTA1)> grant select, update, trigger on t11MiscTest to mamta2, mamta3;
+0 rows inserted/updated/deleted
+ij(MAMTA1)> drop table t12MiscTest;
+ERROR: Failed with SQLSTATE 42Y55
+ij(MAMTA1)> create table t12MiscTest (c121 int, c122 int);
+0 rows inserted/updated/deleted
+ij(MAMTA1)> grant select on t12MiscTest to mamta2;
+0 rows inserted/updated/deleted
+ij(MAMTA1)> set connection mamta2;
+ij(MAMTA2)> drop view v21MiscTest;
+ERROR: Failed with SQLSTATE X0X05
+ij(MAMTA2)> create view v21MiscTest as select * from mamta1.t11MiscTest, mamta1.t12MiscTest where c111=c121;
+0 rows inserted/updated/deleted
+ij(MAMTA2)> select * from v21MiscTest;
+C111 |C112 |C113 |C121 |C122
+-----------------------------------------------------------
+0 rows selected
+ij(MAMTA2)> set connection mamta3;
+ij(MAMTA3)> drop view v31MiscTest;
+ERROR: Failed with SQLSTATE X0X05
+ij(MAMTA3)> create view v31MiscTest as select c111 from mamta1.t11MiscTest;
+0 rows inserted/updated/deleted
+ij(MAMTA3)> select * from v31MiscTest;
+C111
+-----------
+0 rows selected
+ij(MAMTA3)> set connection mamta1;
+ij(MAMTA1)> -- this should drop both the dependent views
+revoke select, update on t11MiscTest from mamta2, mamta3;
+0 rows inserted/updated/deleted
+ij(MAMTA1)> set connection mamta2;
+ij(MAMTA2)> -- should fail because it got dropped as part of revoke statement
+select * from v21MiscTest;
+ERROR: Failed with SQLSTATE 42X05
+ij(MAMTA2)> set connection mamta3;
+ij(MAMTA3)> -- should fail because it got dropped as part of revoke statement
+select * from v31MiscTest;
+ERROR: Failed with SQLSTATE 42X05
+ij(MAMTA3)> -- cleanup
+set connection mamta1;
+ij(MAMTA1)> drop table t11MiscTest;
+0 rows inserted/updated/deleted
+ij(MAMTA1)> drop table t12MiscTest;
+0 rows inserted/updated/deleted
ij(MAMTA1)> -- create trigger privilege collection
-- TriggerTest
-- first grant one column level privilege at user level and another at public level and then define the trigger
@@ -1840,7 +2222,7 @@
ij(MAMTA3)> -- Test constraints
set connection mamta1;
ij(MAMTA1)> drop table t11ConstraintTest;
-ERROR: Failed with SQLSTATE 42Y55
+0 rows inserted/updated/deleted
ij(MAMTA1)> create table t11ConstraintTest (c111 int not null primary key);
0 rows inserted/updated/deleted
ij(MAMTA1)> insert into t11ConstraintTest values(1);
@@ -1851,7 +2233,7 @@
0 rows inserted/updated/deleted
ij(MAMTA1)> set connection mamta2;
ij(MAMTA2)> drop table t21ConstraintTest;
-ERROR: Failed with SQLSTATE 42Y55
+0 rows inserted/updated/deleted
ij(MAMTA2)> create table t21ConstraintTest (c111 int not null primary key);
0 rows inserted/updated/deleted
ij(MAMTA2)> insert into t21ConstraintTest values(1);
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/grantRevokeDDL.sql
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/grantRevokeDDL.sql?rev=428801&r1=428800&r2=428801&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/grantRevokeDDL.sql (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/grantRevokeDDL.sql Fri Aug 4 10:57:54 2006
@@ -943,6 +943,266 @@
drop table t12ViewTest;
drop table t11ViewTest;
+-- Constraint test
+-- test1
+-- Give a constraint privilege at table level to a user. Let user define a foreign key constraint based on that privilege.
+-- Later revoke that references privilege and make sure that foreign key constraint gets dropped
+set connection mamta1;
+drop table t11ConstraintTest;
+create table t11ConstraintTest (c111 int not null primary key);
+insert into t11ConstraintTest values(1);
+insert into t11ConstraintTest values(2);
+grant references on t11ConstraintTest to mamta2;
+set connection mamta2;
+drop table t21ConstraintTest;
+create table t21ConstraintTest (c211 int references mamta1.t11ConstraintTest, c212 int);
+insert into t21ConstraintTest values(1,1);
+-- should fail because the foreign key constraint will fail
+insert into t21ConstraintTest values(3,1);
+set connection mamta1;
+revoke references on t11ConstraintTest from mamta2;
+set connection mamta2;
+-- will pass because the foreign key constraint got dropped because of revoke statement
+insert into t21ConstraintTest values(3,1);
+-- cleanup
+set connection mamta2;
+drop table t21ConstraintTest;
+set connection mamta1;
+drop table t11ConstraintTest;
+
+-- Constraint test
+-- test2
+-- Have user mamta1 give a references privilege to mamta3.
+-- Have user mamta2 give a references privilege to mamta3.
+-- Have mamta3 create a table with 2 foreign key constraints relying on both these granted privileges.
+-- Revoke one of those privileges and make sure that the foreign key constraint defined based on that privilege gets dropped.
+-- Now revoke the 2nd references privilege and make sure that remaining foreign key constraint gets dropped
+set connection mamta1;
+drop table t11ConstraintTest;
+create table t11ConstraintTest (c111 int not null primary key);
+insert into t11ConstraintTest values(1);
+insert into t11ConstraintTest values(2);
+grant references on t11ConstraintTest to mamta3;
+set connection mamta2;
+drop table t21ConstraintTest;
+create table t21ConstraintTest (c111 int not null primary key);
+insert into t21ConstraintTest values(1);
+insert into t21ConstraintTest values(2);
+grant references on t21ConstraintTest to mamta3;
+set connection mamta3;
+drop table t31ConstraintTest;
+create table t31ConstraintTest (c311 int references mamta1.t11ConstraintTest, c312 int references mamta2.t21ConstraintTest);
+select * from t31ConstraintTest;
+insert into t31ConstraintTest values(1,1);
+-- following should fail because it violates the foreign key reference by column c312
+insert into t31ConstraintTest values(1,3);
+-- following should fail because it violates the foreign key reference by column c311
+insert into t31ConstraintTest values(3,1);
+-- following should fail because it violates the foreign key reference by column c311 and c312
+insert into t31ConstraintTest values(3,4);
+set connection mamta2;
+-- the following revoke should drop the foreign key reference by column t31ConstraintTest.c312
+revoke references on t21ConstraintTest from mamta3;
+set connection mamta3;
+-- verify that foreign key reference by column t31ConstraintTest.c312 got dropped by inserting a row.
+-- following should pass
+insert into t31ConstraintTest values(1,3);
+-- following should still fail because foreign key reference by column c311 is still around
+insert into t31ConstraintTest values(3,1);
+set connection mamta1;
+-- now drop the references privilege so that the only foreign key reference on table mamta3.t31ConstraintTest will get dropped
+revoke references on t11ConstraintTest from mamta3;
+set connection mamta3;
+-- verify that foreign key reference by column t31ConstraintTest.c311 got dropped by inserting a row.
+-- following should pass
+insert into t31ConstraintTest values(3,1);
+-- no more foreign key references left and hence following should pass
+insert into t31ConstraintTest values(3,3);
+-- cleanup
+drop table t31ConstraintTest;
+set connection mamta2;
+drop table t21ConstraintTest;
+set connection mamta1;
+drop table t11ConstraintTest;
+
+-- Constraint test
+-- test3
+-- Have mamta1 grant REFERENCES privilege on one of it's tables to mamta2
+-- Have mamta2 create a table with primary which references mamta1's granted REFERENCES privilege
+-- Have mamta2 grant REFERENCES privilege on that table to user mamta3
+-- Have mamta3 create a table which references mamta2's granted REFERENCES privilege
+-- Now revoke of granted REFERENCES privilege by mamta1 should drop the foreign key reference
+-- by mamta2's table t21ConstraintTest. It should not impact the foreign key reference by
+-- mamta3's table t31ConstraintTest.
+-- a)mamta1.t11ConstraintTest (primary key)
+-- b)mamta2.t21ConstraintTest (primary key references t11ConstraintTest)
+-- c)mamta3.t31ConstraintTest (primary key references t21ConstraintTest)
+set connection mamta1;
+drop table t11ConstraintTest;
+create table t11ConstraintTest (c111 int not null primary key);
+insert into t11ConstraintTest values(1);
+insert into t11ConstraintTest values(2);
+grant references on t11ConstraintTest to mamta2;
+set connection mamta2;
+drop table t21ConstraintTest;
+create table t21ConstraintTest (c111 int not null primary key references mamta1.t11ConstraintTest);
+insert into t21ConstraintTest values(1);
+insert into t21ConstraintTest values(2);
+-- following should fail because of foreign key constraint failure
+insert into t21ConstraintTest values(3);
+grant references on t21ConstraintTest to mamta3;
+set connection mamta3;
+drop table t31ConstraintTest;
+create table t31ConstraintTest (c311 int references mamta2.t21ConstraintTest);
+select * from t31ConstraintTest;
+insert into t31ConstraintTest values (1);
+-- following should fail because of foreign key constraint failure
+insert into t31ConstraintTest values (4);
+set connection mamta1;
+-- This revoke should drop foreign key constraint on mamta2.t21ConstraintTest
+-- This revoke should not impact the foeign key constraint on mamta3.t31ConstraintTest
+revoke references on t11ConstraintTest from mamta2;
+set connection mamta2;
+-- because the foreign key reference got revoked, no constraint violation check will be done
+insert into t21ConstraintTest values(3);
+set connection mamta3;
+-- Make sure the foreign key constraint on t31ConstraintTest is still active
+insert into t31ConstraintTest values(3);
+-- because the foreign key constraint is still around, following should fail
+insert into t31ConstraintTest values(4);
+-- cleanup
+set connection mamta3;
+drop table t31ConstraintTest;
+set connection mamta2;
+drop table t21ConstraintTest;
+set connection mamta1;
+drop table t11ConstraintTest;
+
+-- Constraint test
+-- test4
+-- Grant a REFERENCES permission at public level, create constraint, grant same permission at user level
+-- and take away the public level permission. It ends up dropping the constraint. DERBY-1632
+set connection mamta1;
+drop table t11ConstraintTest;
+create table t11ConstraintTest (c111 int not null primary key);
+insert into t11ConstraintTest values(1);
+insert into t11ConstraintTest values(2);
+grant references on t11ConstraintTest to PUBLIC;
+set connection mamta2;
+drop table t21ConstraintTest;
+create table t21ConstraintTest (c111 int not null primary key, constraint fk foreign key(c111) references mamta1.t11ConstraintTest);
+insert into t21ConstraintTest values(1);
+insert into t21ConstraintTest values(2);
+-- following should fail because of foreign key constraint failure
+insert into t21ConstraintTest values(3);
+set connection mamta1;
+-- grant REFERENCES permission again but this time at user level
+grant references on t11ConstraintTest to mamta2;
+-- Now, revoke REFERENCES permission which was granted at PUBLIC level, This drops the constraint.
+-- DERBY-1632. This should be fixed at some point so that constraint won't get dropped, instead
+-- it will start depending on same privilege available at user-level
+revoke references on t11ConstraintTest from PUBLIC;
+set connection mamta2;
+-- because the foreign key reference got revoked, no constraint violation check will be done
+insert into t21ConstraintTest values(3);
+-- cleanup
+set connection mamta2;
+drop table t21ConstraintTest;
+set connection mamta1;
+drop table t11ConstraintTest;
+
+-- Constraint test
+-- test5
+-- Grant refrences privilege and select privilege on a table. Have a constraint depend on the references
+-- privilege. Later, a revoke of select privilege will end up dropping the constraint which shouldn't
+-- happen. This will be addressed in a subsequent patch
+set connection mamta1;
+drop table t11ConstraintTest;
+create table t11ConstraintTest (c111 int not null primary key);
+insert into t11ConstraintTest values(1);
+insert into t11ConstraintTest values(2);
+grant references on t11ConstraintTest to PUBLIC;
+grant select on t11ConstraintTest to PUBLIC;
+set connection mamta2;
+drop table t21ConstraintTest;
+create table t21ConstraintTest (c111 int not null primary key, constraint fk foreign key(c111) references mamta1.t11ConstraintTest);
+insert into t21ConstraintTest values(1);
+insert into t21ConstraintTest values(2);
+-- following should fail because of foreign key constraint failure
+insert into t21ConstraintTest values(3);
+set connection mamta1;
+-- revoke of select privilege is going to drop the constraint which is incorrect. Will be handled in a later patch
+revoke select on t11ConstraintTest from PUBLIC;
+set connection mamta2;
+-- following should have failed but it doesn't because foreign key constraint got dropped by revoke select privilege
+-- Will be fixed in a subsequent patch
+insert into t21ConstraintTest values(3);
+-- cleanup
+set connection mamta2;
+drop table t21ConstraintTest;
+set connection mamta1;
+drop table t11ConstraintTest;
+
+-- Constraint test
+-- test6
+-- Have a primary key and a unique key on a table and grant reference on both. Have another table rely on unique
+-- key references privilege to create a foreign key constraint. Later, the revoke of primary key reference will end up
+-- dropping the foreign key constraint. This will be fixed in a subsequent patch (same as test5)
+set connection mamta1;
+drop table t11ConstraintTest;
+create table t11ConstraintTest (c111 int not null primary key, c112 int not null unique, c113 int);
+insert into t11ConstraintTest values(1,1,1);
+insert into t11ConstraintTest values(2,2,1);
+grant references(c111, c112) on t11ConstraintTest to PUBLIC;
+set connection mamta2;
+drop table t21ConstraintTest;
+create table t21ConstraintTest (c111 int not null primary key, constraint fk foreign key(c111) references mamta1.t11ConstraintTest(c112));
+insert into t21ConstraintTest values(1);
+insert into t21ConstraintTest values(2);
+-- following should fail because of foreign key constraint failure
+insert into t21ConstraintTest values(3);
+set connection mamta1;
+-- revoke of references privilege on c111 which is not used by foreign key constraint on t21ConstraintTest ends up dropping that
+-- foreign key constraint. This Will be handled in a later patch
+revoke references(c111) on t11ConstraintTest from PUBLIC;
+set connection mamta2;
+-- following should have failed but it doesn't because foreign key constraint got dropped by revoke references privilege
+-- Will be fixed in a subsequent patch
+insert into t21ConstraintTest values(3);
+-- cleanup
+
+-- Miscellaneous test
+-- test1
+-- Have multiple objects depends on a privilege and make sure they all get dropped when that privilege is revoked.
+set connection mamta1;
+drop table t11MiscTest;
+create table t11MiscTest (c111 int, c112 int, c113 int);
+grant select, update, trigger on t11MiscTest to mamta2, mamta3;
+drop table t12MiscTest;
+create table t12MiscTest (c121 int, c122 int);
+grant select on t12MiscTest to mamta2;
+set connection mamta2;
+drop view v21MiscTest;
+create view v21MiscTest as select * from mamta1.t11MiscTest, mamta1.t12MiscTest where c111=c121;
+select * from v21MiscTest;
+set connection mamta3;
+drop view v31MiscTest;
+create view v31MiscTest as select c111 from mamta1.t11MiscTest;
+select * from v31MiscTest;
+set connection mamta1;
+-- this should drop both the dependent views
+revoke select, update on t11MiscTest from mamta2, mamta3;
+set connection mamta2;
+-- should fail because it got dropped as part of revoke statement
+select * from v21MiscTest;
+set connection mamta3;
+-- should fail because it got dropped as part of revoke statement
+select * from v31MiscTest;
+-- cleanup
+set connection mamta1;
+drop table t11MiscTest;
+drop table t12MiscTest;
+
-- create trigger privilege collection
-- TriggerTest
-- first grant one column level privilege at user level and another at public level and then define the trigger