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 ba...@apache.org on 2006/02/28 05:42:04 UTC
svn commit: r381562 - in /db/derby/code/trunk/java:
engine/org/apache/derby/impl/sql/compile/
testing/org/apache/derbyTesting/functionTests/master/
testing/org/apache/derbyTesting/functionTests/tests/lang/
Author: bandaram
Date: Mon Feb 27 20:42:01 2006
New Revision: 381562
URL: http://svn.apache.org/viewcvs?rev=381562&view=rev
Log:
DERBY-464: Enforce TRIGGER privilege checks and add tests.
Change createTriggerNode to expect TRIGGER privilege on triggering table.
Submitted by Satheesh Bandaram (satheesh@sourcery.org)
Modified:
db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CreateTriggerNode.java
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/grantRevoke.out
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/grantRevoke.java
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/grantRevokeDDL.sql
Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CreateTriggerNode.java
URL: http://svn.apache.org/viewcvs/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CreateTriggerNode.java?rev=381562&r1=381561&r2=381562&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CreateTriggerNode.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CreateTriggerNode.java Mon Feb 27 20:42:01 2006
@@ -38,6 +38,7 @@
import org.apache.derby.iapi.sql.dictionary.TableDescriptor;
import org.apache.derby.iapi.sql.dictionary.TriggerDescriptor;
+import org.apache.derby.iapi.sql.conn.Authorizer;
import org.apache.derby.iapi.sql.conn.LanguageConnectionContext;
import org.apache.derby.iapi.sql.depend.Dependent;
@@ -246,6 +247,10 @@
{
throw StandardException.newException(SQLState.LANG_OPERATION_NOT_ALLOWED_ON_SESSION_SCHEMA_TABLES);
}
+
+ compilerContext.pushCurrentPrivType(Authorizer.TRIGGER_PRIV);
+ compilerContext.addRequiredTablePriv(triggerTableDescriptor);
+ compilerContext.popCurrentPrivType();
/*
** Regenerates the actionText and actionNode if necessary.
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/grantRevoke.out
URL: http://svn.apache.org/viewcvs/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/grantRevoke.out?rev=381562&r1=381561&r2=381562&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/grantRevoke.out (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/grantRevoke.out Mon Feb 27 20:42:01 2006
@@ -21,6 +21,7 @@
UpdatePrivCheck: update S2.T1 set C1=0
UpdatePrivCheck: update S2.T1 set C2=0
UpdatePrivCheck: update S2.T1 set C3=0
+TriggerPrivCheck: create trigger "T1Trig" after insert on "S2"."T1" for each row mode db2sql values 1
SelectPrivCheck: select * from S2.T1
SelectPrivCheck: select count(*) from "S2"."T1" where (C1 is null) or (C2 is null) or (C3 is null)
DeletePrivCheck: delete from "S2"."T1"
@@ -28,6 +29,7 @@
UpdatePrivCheck: update S2.T1 set C1=0
UpdatePrivCheck: update S2.T1 set C2=0
UpdatePrivCheck: update S2.T1 set C3=0
+TriggerPrivCheck: create trigger "T1Trig" after insert on "S2"."T1" for each row mode db2sql values 1
SelectPrivCheck: select * from S2.T1
SelectPrivCheck: select count(*) from "S2"."T1" where (C1 is null) or (C2 is null) or (C3 is null)
DeletePrivCheck: delete from "S2"."T1"
@@ -35,6 +37,7 @@
UpdatePrivCheck: update S2.T1 set C1=0
UpdatePrivCheck: update S2.T1 set C2=0
UpdatePrivCheck: update S2.T1 set C3=0
+TriggerPrivCheck: create trigger "T1Trig" after insert on "S2"."T1" for each row mode db2sql values 1
SelectPrivCheck: select * from S1.T1
SelectPrivCheck: select count(*) from "S1"."T1" where (C1 is null) or (C2 is null) or (C3 is null)
SelectPrivCheck: select * from S2.T2
@@ -120,6 +123,7 @@
UpdatePrivCheck: update R2.T1 set C3=0
InsertPrivCheck: insert into "R2"."T1" values(0,0,0)
DeletePrivCheck: delete from "R2"."T1"
+TriggerPrivCheck: create trigger "T1Trig" after insert on "R2"."T1" for each row mode db2sql values 1
Revoke test: single table privilege, one user
SelectPrivCheck: select * from R2.T1
SelectPrivCheck: select count(*) from "R2"."T1" where (C1 is null) or (C2 is null) or (C3 is null)
@@ -129,6 +133,7 @@
UpdatePrivCheck: update R2.T1 set C3=0
InsertPrivCheck: insert into "R2"."T1" values(0,0,0)
DeletePrivCheck: delete from "R2"."T1"
+TriggerPrivCheck: create trigger "T1Trig" after insert on "R2"."T1" for each row mode db2sql values 1
SelectPrivCheck: select * from R1.T1
SelectPrivCheck: select count(*) from "R1"."T1" where (C1 is null) or (C2 is null) or (C3 is null)
SelectPrivCheck: select * from R1.T1
@@ -139,6 +144,8 @@
UpdatePrivCheck: update R1.T1 set C2=0
UpdatePrivCheck: update R1.T1 set C3=0
UpdatePrivCheck: update R1.T1 set C3=0
+TriggerPrivCheck: create trigger "T1Trig" after insert on "R1"."T1" for each row mode db2sql values 1
+TriggerPrivCheck: create trigger "T1Trig" after insert on "R1"."T1" for each row mode db2sql values 1
Revoke test: multiple table permissions, multiple users
SelectPrivCheck: select * from R1.T1
SelectPrivCheck: select count(*) from "R1"."T1" where (C1 is null) or (C2 is null) or (C3 is null)
@@ -152,6 +159,8 @@
UpdatePrivCheck: update R1.T1 set C1=0
UpdatePrivCheck: update R1.T1 set C2=0
UpdatePrivCheck: update R1.T1 set C3=0
+TriggerPrivCheck: create trigger "T1Trig" after insert on "R1"."T1" for each row mode db2sql values 1
+TriggerPrivCheck: create trigger "T1Trig" after insert on "R1"."T1" for each row mode db2sql values 1
Revoke test: table privilege implies column privileges
UpdatePrivCheck: update R1.T1 set C1=0
UpdatePrivCheck: update R1.T1 set C1=0
@@ -161,6 +170,7 @@
UpdatePrivCheck: update R1.T1 set C1=0
UpdatePrivCheck: update R1.T1 set C2=0
UpdatePrivCheck: update R1.T1 set C3=0
+TriggerPrivCheck: create trigger "T1Trig" after insert on "R1"."T1" for each row mode db2sql values 1
ExecutePrivCheck: values "R1"."F1"()
ExecutePrivCheck: values "R1"."F1"()
ExecutePrivCheck: call "R1"."F1"()
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/grantRevokeDDL.out
URL: http://svn.apache.org/viewcvs/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/grantRevokeDDL.out?rev=381562&r1=381561&r2=381562&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 Mon Feb 27 20:42:01 2006
@@ -230,6 +230,49 @@
ERROR 28508: User 'SWIPER' does not have update permission on column 'I' of table 'SATHEESH'.'TSAT'.
ij(SWIPERCONNECTION)> create table my_tsat (i int not null, c char(10), constraint fk foreign key(i) references satheesh.tsat);
0 rows inserted/updated/deleted
+ij(SWIPERCONNECTION)> -- Some TRIGGER privilege checks. See GrantRevoke.java for more tests
+set connection swiperConnection;
+ij(SWIPERCONNECTION)> -- Should fail
+create trigger trig_sat1 after update on satheesh.tsat for each statement mode db2sql values 1;
+ERROR 28506: User 'SWIPER' does not have trigger permission on table 'SATHEESH'.'TSAT'.
+ij(SWIPERCONNECTION)> create trigger trig_sat2 no cascade before delete on satheesh.tsat for each statement mode db2sql values 1;
+ERROR 28506: User 'SWIPER' does not have trigger permission on table 'SATHEESH'.'TSAT'.
+ij(SWIPERCONNECTION)> -- Grant trigger privilege
+set connection satConnection;
+ij(SATCONNECTION)> grant trigger on tsat to swiper;
+0 rows inserted/updated/deleted
+ij(SATCONNECTION)> -- Try now
+set connection swiperConnection;
+ij(SWIPERCONNECTION)> create trigger trig_sat1 after update on satheesh.tsat for each statement mode db2sql values 1;
+0 rows inserted/updated/deleted
+ij(SWIPERCONNECTION)> create trigger trig_sat2 no cascade before delete on satheesh.tsat for each statement mode db2sql values 1;
+0 rows inserted/updated/deleted
+ij(SWIPERCONNECTION)> drop trigger trig_sat1;
+0 rows inserted/updated/deleted
+ij(SWIPERCONNECTION)> drop trigger trig_sat2;
+0 rows inserted/updated/deleted
+ij(SWIPERCONNECTION)> -- Now revoke and try again
+set connection satConnection;
+ij(SATCONNECTION)> revoke trigger on tsat from swiper;
+0 rows inserted/updated/deleted
+ij(SATCONNECTION)> set connection swiperConnection;
+ij(SWIPERCONNECTION)> create trigger trig_sat1 after update on satheesh.tsat for each statement mode db2sql values 1;
+ERROR 28506: User 'SWIPER' does not have trigger permission on table 'SATHEESH'.'TSAT'.
+ij(SWIPERCONNECTION)> create trigger trig_sat2 no cascade before delete on satheesh.tsat for each statement mode db2sql values 1;
+ERROR 28506: User 'SWIPER' does not have trigger permission on table 'SATHEESH'.'TSAT'.
+ij(SWIPERCONNECTION)> -- Now grant access to public and try again
+set connection satConnection;
+ij(SATCONNECTION)> grant trigger on tsat to public;
+0 rows inserted/updated/deleted
+ij(SATCONNECTION)> set connection swiperConnection;
+ij(SWIPERCONNECTION)> create trigger trig_sat1 after update on satheesh.tsat for each statement mode db2sql values 1;
+0 rows inserted/updated/deleted
+ij(SWIPERCONNECTION)> create trigger trig_sat2 no cascade before delete on satheesh.tsat for each statement mode db2sql values 1;
+0 rows inserted/updated/deleted
+ij(SWIPERCONNECTION)> drop trigger trig_sat1;
+0 rows inserted/updated/deleted
+ij(SWIPERCONNECTION)> drop trigger trig_sat2;
+0 rows inserted/updated/deleted
ij(SWIPERCONNECTION)> -- Some simple routine tests. See GrantRevoke.java for more tests
set connection satConnection;
ij(SATCONNECTION)> values f_abs(-5);
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/grantRevoke.java
URL: http://svn.apache.org/viewcvs/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/grantRevoke.java?rev=381562&r1=381561&r2=381562&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/grantRevoke.java (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/grantRevoke.java Mon Feb 27 20:42:01 2006
@@ -1141,6 +1141,31 @@
}
} // end of checkSQL
+ String getUserCurrentSchema(User user) throws SQLException
+ {
+ String schemaString = null;
+
+ Statement s = user.getConnection().createStatement();
+ ResultSet rs = s.executeQuery("values current schema");
+ while (rs.next())
+ schemaString = rs.getString(1);
+ return schemaString;
+ }
+
+ void setUserCurrentSchema(User user, String schema) throws SQLException
+ {
+ Statement s = user.getConnection().createStatement();
+ try {
+ s.executeUpdate("set schema "+schema);
+ } catch (SQLException sqle) {
+ // If schema not present, create it and try again
+ if (sqle.getSQLState() == "42Y07") {
+ s.executeUpdate("create schema "+schema);
+ s.executeUpdate("set schema "+schema);
+ }
+ }
+ }
+
private HashMap columnHash;
FormatableBitSet getColBitSet( ) throws SQLException
@@ -1515,7 +1540,7 @@
sb.append( "\"");
boolean savedAutoCommit = user.getConnection().getAutoCommit();
user.getConnection().setAutoCommit( false);
-System.out.println("DeletePrivCheck: " + sb.toString());
+ System.out.println("DeletePrivCheck: " + sb.toString());
PreparedStatement ps = user.getConnection().prepareStatement( sb.toString());
try
{
@@ -1793,9 +1818,51 @@
*
* @exception SQLException Indicates a problem with the test program. Should not happen.
*/
- void checkUser( User user, String testLabel) throws SQLException
+ void checkUser(User user, String testLabel) throws SQLException
{
- // RESOLVE
+ StringBuffer sb = new StringBuffer();
+ sb.append("create trigger ");
+ sb.append("\"");
+ sb.append(table+"Trig");
+ sb.append("\"");
+ sb.append(" after insert on ");
+
+ sb.append("\"");
+ sb.append(schema);
+ sb.append("\".\"");
+ sb.append(table);
+ sb.append("\"");
+ sb.append(" for each row mode db2sql values 1");
+
+ boolean savedAutoCommit = user.getConnection().getAutoCommit();
+ String currentSchema = getUserCurrentSchema(user);
+ // DDLs can only be issued in their own schema
+ setUserCurrentSchema(user, user.toString());
+ user.getConnection().setAutoCommit(false);
+ System.out.println("TriggerPrivCheck: " + sb.toString());
+ PreparedStatement ps = user.getConnection().prepareStatement(sb.toString());
+ try
+ {
+ ps.executeUpdate();
+ if( ! (privIsPublic || expectPriv))
+ reportFailure( "An execute was performed without permission. (" + testLabel + ")");
+ }
+ catch( SQLException sqle)
+ {
+ checkTablePermissionMsg( sqle, user, "trigger", testLabel);
+ }
+ finally
+ {
+ try
+ {
+ user.getConnection().rollback();
+ }
+ finally
+ {
+ user.getConnection().setAutoCommit( savedAutoCommit);
+ setUserCurrentSchema(user, currentSchema);
+ }
+ }
} // end of checkUser
} // end of class TriggerPrivCheck
@@ -1925,7 +1992,7 @@
} // end of checkUser
/* Check that the error message looks right. It should be
- * User '{user}' does not have {action} permission on table '{schema}'.'{table}'.
+ * User '{user}' does not have execute permission on FUNCTION/PROCEDURE '{schema}'.'{table}'.
*/
protected void checkExecutePermissionMsg( SQLException sqle,
User user,
@@ -1938,7 +2005,7 @@
new String[] { schema},
new String[] { routine}},
new boolean[]{true, true, false, false});
- } // end of checkTablePermissionMsg
+ } // end of checkExecutePermissionMsg
} // end of class ExecutePrivCheck
}
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/grantRevokeDDL.sql
URL: http://svn.apache.org/viewcvs/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/grantRevokeDDL.sql?rev=381562&r1=381561&r2=381562&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 Mon Feb 27 20:42:01 2006
@@ -177,6 +177,43 @@
create table my_tsat (i int not null, c char(10), constraint fk foreign key(i) references satheesh.tsat);
+-- Some TRIGGER privilege checks. See GrantRevoke.java for more tests
+set connection swiperConnection;
+-- Should fail
+create trigger trig_sat1 after update on satheesh.tsat for each statement mode db2sql values 1;
+create trigger trig_sat2 no cascade before delete on satheesh.tsat for each statement mode db2sql values 1;
+
+-- Grant trigger privilege
+set connection satConnection;
+grant trigger on tsat to swiper;
+
+-- Try now
+set connection swiperConnection;
+create trigger trig_sat1 after update on satheesh.tsat for each statement mode db2sql values 1;
+create trigger trig_sat2 no cascade before delete on satheesh.tsat for each statement mode db2sql values 1;
+
+drop trigger trig_sat1;
+drop trigger trig_sat2;
+
+-- Now revoke and try again
+set connection satConnection;
+revoke trigger on tsat from swiper;
+
+set connection swiperConnection;
+create trigger trig_sat1 after update on satheesh.tsat for each statement mode db2sql values 1;
+create trigger trig_sat2 no cascade before delete on satheesh.tsat for each statement mode db2sql values 1;
+
+-- Now grant access to public and try again
+set connection satConnection;
+grant trigger on tsat to public;
+
+set connection swiperConnection;
+create trigger trig_sat1 after update on satheesh.tsat for each statement mode db2sql values 1;
+create trigger trig_sat2 no cascade before delete on satheesh.tsat for each statement mode db2sql values 1;
+
+drop trigger trig_sat1;
+drop trigger trig_sat2;
+
-- Some simple routine tests. See GrantRevoke.java for more tests
set connection satConnection;