db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Daniel John Debrunner (JIRA)" <derby-...@db.apache.org>
Subject [jira] Commented: (DERBY-1729) Invoking Java stored procedure that contains GRANT or REVOKE statement with CONTAINS SQL from a trigger should fail.
Date Wed, 06 Sep 2006 13:16:23 GMT
    [ http://issues.apache.org/jira/browse/DERBY-1729?page=comments#action_12432840 ] 
            
Daniel John Debrunner commented on DERBY-1729:
----------------------------------------------

Mamta wrote:

"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. "

Why use SYSTABLEPERMS  as the test mechanism, this is an indirect test of the expected behaviour?
Why not perform the actual test of seeing if the user can or cannot access the table etc,
along with checking the expected successful or exception thrown by the procedure?

> 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

        

Mime
View raw message