[ http://issues.apache.org/jira/browse/DERBY-1729?page=comments#action_12432767 ] Mamta A. Satoor commented on DERBY-1729: ----------------------------------------
Yip, I just finished reviewing the patch fully. Further looking at the test, it seems like you were planning on creating another connection with user name as user2 and have the procedures grant and revoke privileges from that user. Some feedback on the test code changes 1)Have a connection with user2 and test in the code that user2 can look at user1.t1 only after successful execution of grant_select_proc4() through the trigger code. Next, test that user2 can't look at user1.t1 anymore after successful execution of revoke_select_proc4() through the trigger code. 2)Some comments in the test code will be useful, explaining why 6 out of 8 procedure invocatiions from trigger will fail. It makes sense in the mind frame of this jira entry but it will be beneficial for future new users of this test to know what exactly the test is testing w/o having to go through DERBY-1729. 3)Comments in the test code explaining why we are checking SYSTABLEPERMS will be useful ie a row in SYSTABLEPERMS after delete means that grant statement inside the procedure worked correctly and user2 now has the SELECT privileges on user1.t1 Also, comment saying that the row in SYSTABLEPERMS should disappear after successful run of procedure with revoke statement in it. And finally, I think we should consider changing the description of this jira entry to remove excess information about triggers. Like Satheesh mentioned, procedure failure is not related to invocation from trigger. This would happen with stand alone procedures too. > Invoking Java stored procedure that contains GRANT or REVOKE statement with > CONTAINS SQL from a trigger should fail. > --------------------------------------------------------------------------------------------------------------------- > > Key: DERBY-1729 > URL: http://issues.apache.org/jira/browse/DERBY-1729 > Project: Derby > Issue Type: Bug > Components: SQL > Affects Versions: 10.2.1.0 > Environment: Sun JDK 1.4.2 > Reporter: Yip Ng > Assigned To: Yip Ng > Fix For: 10.2.1.0 > > Attachments: derby1729-trunk-diff01.txt, derby1729-trunk-stat01.txt, > repro-trunk-diff01.txt > > > In Derby SQL authorization mode, invoking Java stored procedure that contains > GRANT or REVOKE statement with CONTAINS SQL from a trigger should fail but > in the following test, it successfully executed the trigger action. > Attaching repro patch for trunk. > i.e.: > ij> connect 'triggerProcSQLAuth;create=true' user 'APP' as app; > WARNING 01J14: SQL authorization is being used without first enabling > authentication. > ij> --- setup the environment > --- table used in the procedures > create table t1 (i int primary key, b char(15)); > 0 rows inserted/updated/deleted > ij> insert into t1 values (1, 'XYZ'); > 1 row inserted/updated/deleted > ij> insert into t1 values (2, 'XYZ'); > 1 row inserted/updated/deleted > ij> --- table used in this test > create table t2 (x integer, y integer); > 0 rows inserted/updated/deleted > ij> create procedure grant_select_proc() > parameter style java > dynamic result sets 0 language java > contains sql > external name > 'org.apache.derbyTesting.functionTests.util.ProcedureTest.grantSelect'; > 0 rows inserted/updated/deleted > ij> create procedure revoke_select_proc() > parameter style java > dynamic result sets 0 language java > contains sql > external name > 'org.apache.derbyTesting.functionTests.util.ProcedureTest.revokeSelect'; > 0 rows inserted/updated/deleted > ij> --- tests > create trigger grant_select_trig AFTER delete on t1 > for each STATEMENT mode db2sql call grant_select_proc(); > 0 rows inserted/updated/deleted > ij> --- should fail > delete from t1 where i = 1; > 1 row inserted/updated/deleted > ij> --- check delete failed > select * from t1; > I |B > --------------------------- > 2 |XYZ > 1 row selected > ij> --- check if there are rows in sys.systableperms, should be 0 > select count(*) from SYS.SYSTABLEPERMS; > 1 > ----------- > 1 > 1 row selected > ij> drop trigger grant_select_trig; > 0 rows inserted/updated/deleted > ij> create trigger revoke_select_trig AFTER delete on t1 > for each STATEMENT mode db2sql call revoke_select_proc(); > 0 rows inserted/updated/deleted > ij> --- should fail > delete from t1 where i = 2; > 1 row inserted/updated/deleted > ij> --- check delete failed > select * from t1; > I |B > --------------------------- > 0 rows selected > ij> --- check if there are rows in sys.systableperms, should be 0 > select count(*) from SYS.SYSTABLEPERMS; > 1 > ----------- > 0 > 1 row selected > ij> drop trigger revoke_select_trig; > 0 rows inserted/updated/deleted > ij> > ------------------ Java Information ------------------ > Java Version: 1.4.2_12 > Java Vendor: Sun Microsystems Inc. > Java home: C:\Program Files\Java\j2re1.4.2_12 > Java classpath: derby.jar;derbytools.jar > OS name: Windows XP > OS architecture: x86 > OS version: 5.1 > Java user name: Yip > Java user home: C:\Documents and Settings\Yip > Java user dir: C:\work3\derby\trunk\jars\sane > java.specification.name: Java Platform API Specification > java.specification.version: 1.4 > --------- Derby Information -------- > JRE - JDBC: J2SE 1.4.2 - JDBC 3.0 > [C:\work3\derby\trunk\jars\sane\derby.jar] 10.3.0.0 alpha - (432670M) > [C:\work3\derby\trunk\jars\sane\derbytools.jar] 10.3.0.0 alpha - (432670M) > ------------------------------------------------------ > ----------------- Locale Information ----------------- > Current Locale : [English/United States [en_US]] > Found support for locale: [de_DE] > version: 10.3.0.0 alpha - (432670M) > Found support for locale: [es] > version: 10.3.0.0 alpha - (432670M) > Found support for locale: [fr] > version: 10.3.0.0 alpha - (432670M) > Found support for locale: [it] > version: 10.3.0.0 alpha - (432670M) > Found support for locale: [ja_JP] > version: 10.3.0.0 alpha - (432670M) > Found support for locale: [ko_KR] > version: 10.3.0.0 alpha - (432670M) > Found support for locale: [pt_BR] > version: 10.3.0.0 alpha - (432670M) > Found support for locale: [zh_CN] > version: 10.3.0.0 alpha - (432670M) > Found support for locale: [zh_TW] > version: 10.3.0.0 alpha - (432670M) > ------------------------------------------------------ -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa - For more information on JIRA, see: http://www.atlassian.com/software/jira
