db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From banda...@apache.org
Subject 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/
Date Tue, 28 Feb 2006 04:42:04 GMT
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;
 



Mime
View raw message