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/05 02:22:40 UTC
svn commit: r428930 - in /db/derby/code/trunk/java:
engine/org/apache/derby/iapi/sql/depend/
engine/org/apache/derby/iapi/sql/dictionary/
engine/org/apache/derby/impl/sql/depend/
engine/org/apache/derby/impl/sql/execute/ testing/org/apache/derbyTesting...
Author: djd
Date: Fri Aug 4 17:22:39 2006
New Revision: 428930
URL: http://svn.apache.org/viewvc?rev=428930&view=rev
Log:
DERBY-1643 In Derby, the execute permission on a routine can't be revoked as long as there are dependent objects
relying on that permissions. This patch implements that behavior by adding a new revoke invalidation action
REVOKE_EXECUTE_PRIVILEGE, just for routine revoke. This invalidation gets sent when a revoke execute is issued.
When dependents, a ViewDescriptor and/or a TriggerDescriptor, get this invalidation action, they will
raise error that revoke execute can't be performed because there is a dependent object on that permission.
Patch submitted by Mamta Satoor
Modified:
db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/depend/DependencyManager.java
db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/dictionary/ConstraintDescriptor.java
db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/dictionary/TriggerDescriptor.java
db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/dictionary/ViewDescriptor.java
db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/depend/BasicDependencyManager.java
db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/RoutinePrivilegeInfo.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/depend/DependencyManager.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/depend/DependencyManager.java?rev=428930&r1=428929&r2=428930&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/depend/DependencyManager.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/depend/DependencyManager.java Fri Aug 4 17:22:39 2006
@@ -304,7 +304,15 @@
public static final int TRUNCATE_TABLE = 42;
public static final int DROP_SYNONYM = 43;
+ //A generic revoke action for TRIGGER, REFERENCES, SELECT, INSERT,
+ // UPDATE and DELETE privileges. For all these privilege types,
+ // a revoke statement causes the dependents to drop
public static final int REVOKE_PRIVILEGE = 44;
+ //A special action for revoke execute...restrict privilege. This is
+ // because when revoke execute ... restrict is issued, the dependents
+ // need to throw an exception. As long as there are dependent objects
+ // on the execute privilege, the execute privilege can't be revoked.
+ public static final int REVOKE_EXECUTE_PRIVILEGE = 45;
/**
* Extensions to this interface may use action codes > MAX_ACTION_CODE without fear of
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=428930&r1=428929&r2=428930&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 17:22:39 2006
@@ -553,7 +553,17 @@
case DependencyManager.SET_CONSTRAINTS_DISABLE:
case DependencyManager.SET_TRIGGERS_ENABLE:
case DependencyManager.SET_TRIGGERS_DISABLE:
- //for now, ignore revoke privilege action
+ //Notice that REVOKE_EXECUTE_PRIVILEGE is not included here.
+ //In Derby, at this point, a constraint can't depend on a
+ //routine and hence a REVOKE_EXECUTE_PRIVILEGE invalidation
+ //action should never be received by a ConstraintDescriptor.
+ //Don't know how, but if it ever happened for some mysterious
+ //reason, it will be caught in the default: case.
+ //
+ //For all the other types of revoke privileges, for instance,
+ //SELECT, UPDATE, DELETE, INSERT, REFERENCES, TRIGGER, we don't
+ //do anything here and later in makeInvalid, we make the
+ //ConstraintDescriptor drop itself.
case DependencyManager.REVOKE_PRIVILEGE:
break;
@@ -580,8 +590,19 @@
public void makeInvalid(int action, LanguageConnectionContext lcc)
throws StandardException
{
+ //Notice that REVOKE_EXECUTE_PRIVILEGE is not included here.
+ //This is because, in Derby, at this point, a constraint can't
+ //depend on a routine and hence a REVOKE_EXECUTE_PRIVILEGE
+ //invalidation action should never be received by a
+ //ConstraintDescriptor. Don't know how, but if it ever happened
+ //for some mysterious reason, it will be caught in prepareToInvalidate
+ //method
if (action == DependencyManager.REVOKE_PRIVILEGE)
{
+ //At this point (Derby 10.2), only a foreign key constraint can
+ //depend on a privilege. None of the other constraint types
+ //can be dependent on a privilege becuse those constraint types
+ //can not reference a table/routine.
DropConstraintConstantAction.dropConstraintAndIndex(
getDataDictionary().getDependencyManager(),
table,
Modified: db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/dictionary/TriggerDescriptor.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/dictionary/TriggerDescriptor.java?rev=428930&r1=428929&r2=428930&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/dictionary/TriggerDescriptor.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/dictionary/TriggerDescriptor.java Fri Aug 4 17:22:39 2006
@@ -642,6 +642,15 @@
case DependencyManager.DROP_SYNONYM:
case DependencyManager.DROP_SPS:
case DependencyManager.RENAME:
+ //Derby supports only RESTRICT form of revoke execute and that
+ //means that if there are any dependent objects on execute
+ //permission on routine, revoke execute on that routine should
+ //fail
+ //For all the other types of revoke privileges, for instance,
+ //SELECT, UPDATE, DELETE, INSERT, REFERENCES, TRIGGER, we don't
+ //do anything here and later in makeInvalid, we make the
+ //TriggerDescriptor drop itself.
+ case DependencyManager.REVOKE_EXECUTE_PRIVILEGE:
DependencyManager dm = getDataDictionary().getDependencyManager();
throw StandardException.newException(SQLState.LANG_PROVIDER_HAS_DEPENDENT_OBJECT,
dm.getActionString(action),
@@ -677,6 +686,12 @@
// that we would have to check against. This is hard to maintain,
// so don't bother.
+ //Notice that REVOKE_EXECUTE_PRIVILEGE is not included here.
+ //This is because Derby supports only RESTRICT form of revoke
+ //execute and that means that if there are any dependent
+ //objects on execute permission on routine, revoke execute on
+ //that routine should fail. This behvaior for revoke execute
+ //gets implemented in prepareToInvalidate method
if (action == DependencyManager.REVOKE_PRIVILEGE)
{
DropTriggerConstantAction.dropTriggerDescriptor(
Modified: db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/dictionary/ViewDescriptor.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/dictionary/ViewDescriptor.java?rev=428930&r1=428929&r2=428930&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/dictionary/ViewDescriptor.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/dictionary/ViewDescriptor.java Fri Aug 4 17:22:39 2006
@@ -267,7 +267,16 @@
*/
case DependencyManager.SET_CONSTRAINTS_ENABLE:
case DependencyManager.SET_TRIGGERS_ENABLE:
- //ignore revoke privilege action for now
+ //Notice that REVOKE_EXECUTE_PRIVILEGE is not included here.
+ //This is because Derby supports only RESTRICT form of revoke
+ //execute and that means that if there are any dependent objects
+ //on execute permission on routine, revoke execute on that
+ //routine should fail
+ //
+ //For all the other types of revoke privileges, for instance,
+ //SELECT, UPDATE, DELETE, INSERT, REFERENCES, TRIGGER, we don't
+ //do anything here and later in makeInvalid, we make the
+ //ViewDescriptor drop itself.
case DependencyManager.REVOKE_PRIVILEGE:
break;
@@ -317,6 +326,12 @@
case DependencyManager.TRUNCATE_TABLE:
break;
+ //Notice that REVOKE_EXECUTE_PRIVILEGE is not included here.
+ //This is because Derby supports only RESTRICT form of revoke
+ //execute and that means that if there are any dependent
+ //objects on execute permission on routine, revoke execute on
+ //that routine should fail. This behvaior for revoke execute
+ //gets implemented in prepareToInvalidate method
case DependencyManager.REVOKE_PRIVILEGE:
dropViewWork(getDataDictionary(),
getDataDictionary().getDependencyManager(), lcc,
Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/depend/BasicDependencyManager.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/depend/BasicDependencyManager.java?rev=428930&r1=428929&r2=428930&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/depend/BasicDependencyManager.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/depend/BasicDependencyManager.java Fri Aug 4 17:22:39 2006
@@ -837,6 +837,9 @@
case REVOKE_PRIVILEGE:
return "REVOKE PRIVILEGE";
+
+ case REVOKE_EXECUTE_PRIVILEGE:
+ return "REVOKE EXECUTE PRIVILEGE";
default:
if (SanityManager.DEBUG)
Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/RoutinePrivilegeInfo.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/RoutinePrivilegeInfo.java?rev=428930&r1=428929&r2=428930&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/RoutinePrivilegeInfo.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/RoutinePrivilegeInfo.java Fri Aug 4 17:22:39 2006
@@ -79,7 +79,7 @@
{
String grantee = (String) itr.next();
if (dd.addRemovePermissionsDescriptor( grant, routinePermsDesc, grantee, tc))
- dd.getDependencyManager().invalidateFor(routinePermsDesc, DependencyManager.REVOKE_PRIVILEGE, lcc);
+ dd.getDependencyManager().invalidateFor(routinePermsDesc, DependencyManager.REVOKE_EXECUTE_PRIVILEGE, lcc);
}
} // end of executeConstantAction
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=428930&r1=428929&r2=428930&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 17:22:39 2006
@@ -710,6 +710,8 @@
0 rows inserted/updated/deleted
ij(MAMTA3)> -- Write some views based on a routine
set connection mamta1;
+ij(MAMTA1)> drop function f_abs1;
+ERROR: Failed with SQLSTATE 42Y55
ij(MAMTA1)> CREATE FUNCTION F_ABS1(P1 INT)
RETURNS INT NO SQL
RETURNS NULL ON NULL INPUT
@@ -721,6 +723,8 @@
-----------
5
1 row selected
+ij(MAMTA1)> drop view v11;
+ERROR: Failed with SQLSTATE X0X05
ij(MAMTA1)> create view v11(c111) as values mamta1.f_abs1(-5);
0 rows inserted/updated/deleted
ij(MAMTA1)> grant select on v11 to mamta2;
@@ -731,6 +735,8 @@
5
1 row selected
ij(MAMTA1)> set connection mamta2;
+ij(MAMTA2)> drop view v24;
+ERROR: Failed with SQLSTATE X0X05
ij(MAMTA2)> create view v24 as select * from mamta1.v11;
0 rows inserted/updated/deleted
ij(MAMTA2)> select * from v24;
@@ -738,6 +744,8 @@
-----------
5
1 row selected
+ij(MAMTA2)> drop view v25;
+ERROR: Failed with SQLSTATE X0X05
ij(MAMTA2)> -- following will fail because no execute permissions on mamta1.f_abs1
create view v25(c251) as (values mamta1.f_abs1(-1));
ERROR: Failed with SQLSTATE 2850A
@@ -745,14 +753,44 @@
ij(MAMTA1)> grant execute on function f_abs1 to mamta2;
0 rows inserted/updated/deleted
ij(MAMTA1)> set connection mamta2;
-ij(MAMTA2)> create view v25(c251) as (values mamta1.f_abs1(-1));
+ij(MAMTA2)> -- this view creation will pass now because have execute privileges on the function
+create view v25(c251) as (values mamta1.f_abs1(-1));
0 rows inserted/updated/deleted
ij(MAMTA2)> select * from v25;
C251
-----------
1
1 row selected
-ij(MAMTA2)> -- try column level privileges and views
+ij(MAMTA2)> set connection mamta1;
+ij(MAMTA1)> -- try revoke execute privilege. Since there are dependent objects, the revoke shold fail
+revoke execute on function f_abs1 from mamta2 restrict;
+ERROR: Failed with SQLSTATE X0Y23
+ij(MAMTA1)> -- drop the dependent objects on the execute privilege and then try to revoke the execute privilege
+set connection mamta2;
+ij(MAMTA2)> drop view v25;
+0 rows inserted/updated/deleted
+ij(MAMTA2)> set connection mamta1;
+ij(MAMTA1)> -- revoke execute privilege should pass this time because no dependents on that permission.
+revoke execute on function f_abs1 from mamta2 restrict;
+0 rows inserted/updated/deleted
+ij(MAMTA1)> set connection mamta2;
+ij(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
+select * from v24;
+C111
+-----------
+5
+1 row selected
+ij(MAMTA2)> -- cleanup
+drop view v24;
+0 rows inserted/updated/deleted
+ij(MAMTA2)> set connection mamta1;
+ij(MAMTA1)> drop view v11;
+0 rows inserted/updated/deleted
+ij(MAMTA1)> drop function f_abs1;
+0 rows inserted/updated/deleted
+ij(MAMTA1)> -- try column level privileges and views
-- In this test, user has permission on one column but not on the other
set connection mamta1;
ij(MAMTA1)> create table t14(c141 int, c142 int);
@@ -1772,7 +1810,13 @@
insert into t21ConstraintTest values(3);
1 row inserted/updated/deleted
ij(MAMTA2)> -- cleanup
--- Miscellaneous test
+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)> -- 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;
@@ -1879,6 +1923,8 @@
1 row inserted/updated/deleted
ij(MAMTA1)> insert into t11TriggerTest values(2);
1 row inserted/updated/deleted
+ij(MAMTA1)> drop table t12RoutineTest;
+ERROR: Failed with SQLSTATE 42Y55
ij(MAMTA1)> create table t12RoutineTest (c121 int);
0 rows inserted/updated/deleted
ij(MAMTA1)> insert into t12RoutineTest values (1),(2);
@@ -2074,6 +2120,45 @@
1
1
4 rows selected
+ij(MAMTA1)> delete from t13TriggerTest;
+4 rows inserted/updated/deleted
+ij(MAMTA1)> -- Trying to revoke execute privilege below will fail because mamta3 has created a trigger based on that permission.
+-- Derby supports only RESTRICT form of revoke execute. Which means that it can be revoked only if there are no
+-- objects relying on that permission
+revoke execute on function selectFromSpecificSchema from mamta3 restrict;
+ERROR: Failed with SQLSTATE X0Y25
+ij(MAMTA1)> -- now try the insert and make sure the insert trigger still fires
+set connection mamta2;
+ij(MAMTA2)> insert into mamta3.t31TriggerTest values(1);
+1 row inserted/updated/deleted
+ij(MAMTA2)> set connection mamta1;
+ij(MAMTA1)> -- If number of rows returned by following select is 1, then we know insert trigger did get fire.
+-- Insert's trigger's action is to insert into following table.
+select * from t13TriggerTest;
+C131
+-----------
+1
+1 row selected
+ij(MAMTA1)> set connection mamta3;
+ij(MAMTA3)> -- drop the trigger manually
+drop trigger tr31t31;
+0 rows inserted/updated/deleted
+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
+ij(MAMTA1)> set connection mamta3;
+ij(MAMTA3)> -- cleanup
+drop table t31TriggerTest;
+0 rows inserted/updated/deleted
+ij(MAMTA3)> set connection mamta1;
+ij(MAMTA1)> -- cleanup
+drop table t12RoutineTest;
+0 rows inserted/updated/deleted
+ij(MAMTA1)> drop table t13TriggerTest;
+0 rows inserted/updated/deleted
+ij(MAMTA1)> drop function selectFromSpecificSchema;
+0 rows inserted/updated/deleted
ij(MAMTA1)> -- Test routine and view combination. Thing to note is views always
-- run with definer's privileges whereas routines always run with
-- session user's privileges. So, eventhough a routine might be
@@ -2082,7 +2167,7 @@
-- privileges will get used.
set connection mamta1;
ij(MAMTA1)> drop table t12RoutineTest;
-0 rows inserted/updated/deleted
+ERROR: Failed with SQLSTATE 42Y55
ij(MAMTA1)> create table t12RoutineTest (c121 int);
0 rows inserted/updated/deleted
ij(MAMTA1)> insert into t12RoutineTest values (1),(2);
@@ -2090,7 +2175,7 @@
ij(MAMTA1)> grant select on t12RoutineTest to mamta3;
0 rows inserted/updated/deleted
ij(MAMTA1)> drop function selectFromSpecificSchema;
-0 rows inserted/updated/deleted
+ERROR: Failed with SQLSTATE 42X94
ij(MAMTA1)> CREATE FUNCTION selectFromSpecificSchema (P1 INT)
RETURNS INT
RETURNS NULL ON NULL INPUT
@@ -2154,7 +2239,7 @@
0 rows inserted/updated/deleted
ij(MAMTA2)> set connection mamta3;
ij(MAMTA3)> drop table t31TriggerTest;
-0 rows inserted/updated/deleted
+ERROR: Failed with SQLSTATE 42Y55
ij(MAMTA3)> create table t31TriggerTest (c311 int);
0 rows inserted/updated/deleted
ij(MAMTA3)> grant insert on t31TriggerTest to mamta4;
@@ -2222,7 +2307,7 @@
ij(MAMTA3)> -- Test constraints
set connection mamta1;
ij(MAMTA1)> drop table t11ConstraintTest;
-0 rows inserted/updated/deleted
+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);
@@ -2233,7 +2318,7 @@
0 rows inserted/updated/deleted
ij(MAMTA1)> set connection mamta2;
ij(MAMTA2)> drop table t21ConstraintTest;
-0 rows inserted/updated/deleted
+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);
@@ -2471,6 +2556,89 @@
1 row inserted/updated/deleted
ij(MAMTA1)> -- cleanup
drop table t11TriggerRevokeTest;
+0 rows inserted/updated/deleted
+ij(MAMTA1)> -- Define couple triggers on a table relying on privilege on different tables. If a revoke is issued, only the dependent triggers
+-- should get dropped, the rest of the triggers should stay active.
+set connection mamta1;
+ij(MAMTA1)> drop table t11TriggerRevokeTest;
+ERROR: Failed with SQLSTATE 42Y55
+ij(MAMTA1)> create table t11TriggerRevokeTest (c111 int);
+0 rows inserted/updated/deleted
+ij(MAMTA1)> insert into t11TriggerRevokeTest values(1),(2);
+2 rows inserted/updated/deleted
+ij(MAMTA1)> grant INSERT on t11TriggerRevokeTest to mamta2;
+0 rows inserted/updated/deleted
+ij(MAMTA1)> drop table t12TriggerRevokeTest;
+ERROR: Failed with SQLSTATE 42Y55
+ij(MAMTA1)> create table t12TriggerRevokeTest (c121 int);
+0 rows inserted/updated/deleted
+ij(MAMTA1)> insert into t12TriggerRevokeTest values(1),(2);
+2 rows inserted/updated/deleted
+ij(MAMTA1)> grant INSERT on t12TriggerRevokeTest to mamta2;
+0 rows inserted/updated/deleted
+ij(MAMTA1)> set connection mamta2;
+ij(MAMTA2)> drop table t21TriggerRevokeTest;
+ERROR: Failed with SQLSTATE 42Y55
+ij(MAMTA2)> create table t21TriggerRevokeTest (c211 int);
+0 rows inserted/updated/deleted
+ij(MAMTA2)> insert into t21TriggerRevokeTest values(1);
+1 row inserted/updated/deleted
+ij(MAMTA2)> -- following will pass because mamta2 has required permissions on mamta1.t11TriggerRevokeTest
+create trigger tr211t21 after insert on t21TriggerRevokeTest for each statement mode db2sql
+ insert into mamta1.t11TriggerRevokeTest values(99);
+0 rows inserted/updated/deleted
+ij(MAMTA2)> -- following will pass because mamta2 has required permissions on mamta1.t11TriggerRevokeTest
+create trigger tr212t21 after insert on t21TriggerRevokeTest for each statement mode db2sql
+ insert into mamta1.t12TriggerRevokeTest values(99);
+0 rows inserted/updated/deleted
+ij(MAMTA2)> insert into t21TriggerRevokeTest values(1);
+1 row inserted/updated/deleted
+ij(MAMTA2)> set connection mamta1;
+ij(MAMTA1)> -- there should be 1 new row in each of the tables because of 2 insert triggers
+select * from t11TriggerRevokeTest;
+C111
+-----------
+1
+2
+99
+3 rows selected
+ij(MAMTA1)> select * from t12TriggerRevokeTest;
+C121
+-----------
+1
+2
+99
+3 rows selected
+ij(MAMTA1)> delete from t11TriggerRevokeTest;
+3 rows inserted/updated/deleted
+ij(MAMTA1)> delete from t12TriggerRevokeTest;
+3 rows inserted/updated/deleted
+ij(MAMTA1)> -- only one trigger(tr211t21) should get dropped because of following revoke
+revoke insert on t11TriggerRevokeTest from mamta2;
+0 rows inserted/updated/deleted
+ij(MAMTA1)> set connection mamta2;
+ij(MAMTA2)> insert into t21TriggerRevokeTest values(1);
+1 row inserted/updated/deleted
+ij(MAMTA2)> set connection mamta1;
+ij(MAMTA1)> -- there should be no row in this table
+select * from t11TriggerRevokeTest;
+C111
+-----------
+0 rows selected
+ij(MAMTA1)> -- there should be one new row in mamta1.t12TriggerRevokeTest
+select * from t12TriggerRevokeTest;
+C121
+-----------
+99
+1 row selected
+ij(MAMTA1)> -- cleanup
+set connection mamta2;
+ij(MAMTA2)> drop table t21TriggerRevokeTest;
+0 rows inserted/updated/deleted
+ij(MAMTA2)> set connection mamta1;
+ij(MAMTA1)> drop table t12TriggerRevokeTest;
+0 rows inserted/updated/deleted
+ij(MAMTA1)> drop table t11TriggerRevokeTest;
0 rows inserted/updated/deleted
ij(MAMTA1)> --- Test automatic dropping of dependent permission descriptors when objects they refer to is dropped.
--- Dropping of a table, for example, should drop all table and column permission descriptors on it.
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=428930&r1=428929&r2=428930&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 17:22:39 2006
@@ -482,25 +482,49 @@
-- Write some views based on a routine
set connection mamta1;
+drop function f_abs1;
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;
values f_abs1(-5);
+drop view v11;
create view v11(c111) as values mamta1.f_abs1(-5);
grant select on v11 to mamta2;
select * from v11;
set connection mamta2;
+drop view v24;
create view v24 as select * from mamta1.v11;
select * from v24;
+drop view v25;
-- following will fail because no execute permissions on mamta1.f_abs1
create view v25(c251) as (values mamta1.f_abs1(-1));
set connection mamta1;
grant execute on function f_abs1 to mamta2;
set connection mamta2;
+-- this view creation will pass now because have execute privileges on the function
create view v25(c251) as (values mamta1.f_abs1(-1));
select * from v25;
+set connection mamta1;
+-- try revoke execute privilege. Since there are dependent objects, the revoke shold fail
+revoke execute on function f_abs1 from mamta2 restrict;
+-- drop the dependent objects on the execute privilege and then try to revoke the execute privilege
+set connection mamta2;
+drop view v25;
+set connection mamta1;
+-- revoke execute privilege should pass this time because no dependents on that permission.
+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
+select * from v24;
+-- cleanup
+drop view v24;
+set connection mamta1;
+drop view v11;
+drop function f_abs1;
-- try column level privileges and views
-- In this test, user has permission on one column but not on the other
@@ -1170,6 +1194,10 @@
-- 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;
-- Miscellaneous test
-- test1
@@ -1233,6 +1261,7 @@
create table t11TriggerTest (c111 int not null primary key);
insert into t11TriggerTest values(1);
insert into t11TriggerTest values(2);
+drop table t12RoutineTest;
create table t12RoutineTest (c121 int);
insert into t12RoutineTest values (1),(2);
grant select on t11TriggerTest to mamta2;
@@ -1327,6 +1356,32 @@
set connection mamta1;
select * from t12RoutineTest;
select * from t13TriggerTest;
+delete from t13TriggerTest;
+-- Trying to revoke execute privilege below will fail because mamta3 has created a trigger based on that permission.
+-- Derby supports only RESTRICT form of revoke execute. Which means that it can be revoked only if there are no
+-- objects relying on that permission
+revoke execute on function selectFromSpecificSchema from mamta3 restrict;
+-- now try the insert and make sure the insert trigger still fires
+set connection mamta2;
+insert into mamta3.t31TriggerTest values(1);
+set connection mamta1;
+-- If number of rows returned by following select is 1, then we know insert trigger did get fire.
+-- Insert's trigger's action is to insert into following table.
+select * from t13TriggerTest;
+set connection mamta3;
+-- drop the trigger manually
+drop trigger tr31t31;
+set connection 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;
+set connection mamta3;
+-- cleanup
+drop table t31TriggerTest;
+set connection mamta1;
+-- cleanup
+drop table t12RoutineTest;
+drop table t13TriggerTest;
+drop function selectFromSpecificSchema;
-- Test routine and view combination. Thing to note is views always
-- run with definer's privileges whereas routines always run with
@@ -1515,6 +1570,7 @@
drop table t11TriggerRevokeTest;
+
-- Define a trigger on a table, then revoke a privilege on the table which trigger doesn't
-- really depend on. The trigger still gets dropped automatically. This will be fixed in
-- subsequent patch
@@ -1565,6 +1621,51 @@
-- following insert won't fire an insert trigger because one doesn't exist
insert into t11TriggerRevokeTest values(6);
-- cleanup
+drop table t11TriggerRevokeTest;
+
+
+-- Define couple triggers on a table relying on privilege on different tables. If a revoke is issued, only the dependent triggers
+-- should get dropped, the rest of the triggers should stay active.
+set connection mamta1;
+drop table t11TriggerRevokeTest;
+create table t11TriggerRevokeTest (c111 int);
+insert into t11TriggerRevokeTest values(1),(2);
+grant INSERT on t11TriggerRevokeTest to mamta2;
+drop table t12TriggerRevokeTest;
+create table t12TriggerRevokeTest (c121 int);
+insert into t12TriggerRevokeTest values(1),(2);
+grant INSERT on t12TriggerRevokeTest to mamta2;
+set connection mamta2;
+drop table t21TriggerRevokeTest;
+create table t21TriggerRevokeTest (c211 int);
+insert into t21TriggerRevokeTest values(1);
+-- following will pass because mamta2 has required permissions on mamta1.t11TriggerRevokeTest
+create trigger tr211t21 after insert on t21TriggerRevokeTest for each statement mode db2sql
+ insert into mamta1.t11TriggerRevokeTest values(99);
+-- following will pass because mamta2 has required permissions on mamta1.t11TriggerRevokeTest
+create trigger tr212t21 after insert on t21TriggerRevokeTest for each statement mode db2sql
+ insert into mamta1.t12TriggerRevokeTest values(99);
+insert into t21TriggerRevokeTest values(1);
+set connection mamta1;
+-- there should be 1 new row in each of the tables because of 2 insert triggers
+select * from t11TriggerRevokeTest;
+select * from t12TriggerRevokeTest;
+delete from t11TriggerRevokeTest;
+delete from t12TriggerRevokeTest;
+-- only one trigger(tr211t21) should get dropped because of following revoke
+revoke insert on t11TriggerRevokeTest from mamta2;
+set connection mamta2;
+insert into t21TriggerRevokeTest values(1);
+set connection mamta1;
+-- there should be no row in this table
+select * from t11TriggerRevokeTest;
+-- there should be one new row in mamta1.t12TriggerRevokeTest
+select * from t12TriggerRevokeTest;
+-- cleanup
+set connection mamta2;
+drop table t21TriggerRevokeTest;
+set connection mamta1;
+drop table t12TriggerRevokeTest;
drop table t11TriggerRevokeTest;