Return-Path: Delivered-To: apmail-db-derby-commits-archive@www.apache.org Received: (qmail 97850 invoked from network); 27 Feb 2006 18:54:56 -0000 Received: from hermes.apache.org (HELO mail.apache.org) (209.237.227.199) by minotaur.apache.org with SMTP; 27 Feb 2006 18:54:56 -0000 Received: (qmail 36945 invoked by uid 500); 27 Feb 2006 18:54:56 -0000 Delivered-To: apmail-db-derby-commits-archive@db.apache.org Received: (qmail 36869 invoked by uid 500); 27 Feb 2006 18:54:55 -0000 Mailing-List: contact derby-commits-help@db.apache.org; run by ezmlm Precedence: bulk list-help: list-unsubscribe: List-Post: Reply-To: "Derby Development" List-Id: Delivered-To: mailing list derby-commits@db.apache.org Received: (qmail 36857 invoked by uid 99); 27 Feb 2006 18:54:55 -0000 Received: from asf.osuosl.org (HELO asf.osuosl.org) (140.211.166.49) by apache.org (qpsmtpd/0.29) with ESMTP; Mon, 27 Feb 2006 10:54:55 -0800 X-ASF-Spam-Status: No, hits=-9.4 required=10.0 tests=ALL_TRUSTED,NO_REAL_NAME X-Spam-Check-By: apache.org Received: from [209.237.227.194] (HELO minotaur.apache.org) (209.237.227.194) by apache.org (qpsmtpd/0.29) with SMTP; Mon, 27 Feb 2006 10:54:54 -0800 Received: (qmail 97703 invoked by uid 65534); 27 Feb 2006 18:54:33 -0000 Message-ID: <20060227185433.97688.qmail@minotaur.apache.org> Content-Type: text/plain; charset="utf-8" MIME-Version: 1.0 Content-Transfer-Encoding: 7bit Subject: svn commit: r381408 - in /db/derby/code/trunk/java: engine/org/apache/derby/iapi/sql/dictionary/ engine/org/apache/derby/impl/sql/catalog/ engine/org/apache/derby/impl/sql/compile/ testing/org/apache/derbyTesting/functionTests/master/ testing/org/apach... Date: Mon, 27 Feb 2006 18:54:31 -0000 To: derby-commits@db.apache.org From: bandaram@apache.org X-Mailer: svnmailer-1.0.7 X-Virus-Checked: Checked by ClamAV on apache.org X-Spam-Rating: minotaur.apache.org 1.6.2 0/1000/N Author: bandaram Date: Mon Feb 27 10:54:27 2006 New Revision: 381408 URL: http://svn.apache.org/viewcvs?rev=381408&view=rev Log: DERBY-464: Enable routine privilege checking. Most of the code for routine privileges is already in trunk. Just enable this checking, add tests to both grantRevokeDDL and grantRevoke tests. Permission scheme for system routines still need to be resolved. For now, everyone has execute permission on all system routines in system schemas. Submitted by Satheesh Bandaram (satheesh@sourcery.org) Modified: db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/dictionary/StatementRoutinePermission.java db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/catalog/PermissionsCacheable.java db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CompilerContextImpl.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/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/iapi/sql/dictionary/StatementRoutinePermission.java URL: http://svn.apache.org/viewcvs/db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/dictionary/StatementRoutinePermission.java?rev=381408&r1=381407&r2=381408&view=diff ============================================================================== --- db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/dictionary/StatementRoutinePermission.java (original) +++ db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/dictionary/StatementRoutinePermission.java Mon Feb 27 10:54:27 2006 @@ -55,6 +55,9 @@ { RoutinePermsDescriptor perms = dd.getRoutinePermissions( routineUUID, authorizationId); if( perms == null || ! perms.getHasExecutePermission()) + perms = dd.getRoutinePermissions(routineUUID, Authorizer.PUBLIC_AUTHORIZATION_ID); + + if( perms == null || ! perms.getHasExecutePermission()) { AliasDescriptor ad = dd.getAliasDescriptor( routineUUID); if( ad == null) Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/catalog/PermissionsCacheable.java URL: http://svn.apache.org/viewcvs/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/catalog/PermissionsCacheable.java?rev=381408&r1=381407&r2=381408&view=diff ============================================================================== --- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/catalog/PermissionsCacheable.java (original) +++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/catalog/PermissionsCacheable.java Mon Feb 27 10:54:27 2006 @@ -117,8 +117,16 @@ { AliasDescriptor ad = dd.getAliasDescriptor( routinePermsKey.getRoutineUUID()); SchemaDescriptor sd = dd.getSchemaDescriptor( ad.getSchemaUUID(), - ConnectionUtil.getCurrentLCC().getTransactionExecute()); - if( routinePermsKey.getGrantee().equals( sd.getAuthorizationId())) + ConnectionUtil.getCurrentLCC().getTransactionExecute()); + // GrantRevoke TODO: This needs to be changed. Shouldn't allow execute on all system + // routines. + if (sd.isSystemSchema()) + permissions = new RoutinePermsDescriptor( dd, + routinePermsKey.getGrantee(), + (String) null, + routinePermsKey.getRoutineUUID(), + true); + else if( routinePermsKey.getGrantee().equals( sd.getAuthorizationId())) permissions = new RoutinePermsDescriptor( dd, routinePermsKey.getGrantee(), Authorizer.SYSTEM_AUTHORIZATION_ID, Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CompilerContextImpl.java URL: http://svn.apache.org/viewcvs/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CompilerContextImpl.java?rev=381408&r1=381407&r2=381408&view=diff ============================================================================== --- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CompilerContextImpl.java (original) +++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CompilerContextImpl.java Mon Feb 27 10:54:27 2006 @@ -772,10 +772,8 @@ if( requiredRoutinePrivileges == null || routine == null) return; - /* GrantRevoke TODO: Implement routine privilege checks. Commented out for now. if (requiredRoutinePrivileges.get(routine.getUUID()) == null) requiredRoutinePrivileges.put(routine.getUUID(), ReuseFactory.getInteger(1)); - */ } /** 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=381408&r1=381407&r2=381408&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 10:54:27 2006 @@ -225,9 +225,73 @@ 0 rows selected ij(SWIPERCONNECTION)> select * from satheesh.table1, (select j from satheesh.tsat) table2; ERROR 28508: User 'SWIPER' does not have select permission on column 'J' of table 'SATHEESH'.'TSAT'. -ij(SWIPERCONNECTION)> -- GrantRevoke TODO: This one should pass, but currently fails. Not sure how to handle this yet. +ij(SWIPERCONNECTION)> -- GrantRevoke TODO: This one should pass, but currently fails. Bind update expression in two steps. update satheesh.tsat set j=i; 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 simple routine tests. See GrantRevoke.java for more tests +set connection satConnection; +ij(SATCONNECTION)> values f_abs(-5); +1 +----------- +5 +1 row selected +ij(SATCONNECTION)> select f_abs(-4) from sys.systables where tablename like 'SYSTAB%'; +1 +----------- +4 +4 +2 rows selected +ij(SATCONNECTION)> -- Same tests should fail +set connection swiperConnection; +ij(SWIPERCONNECTION)> set schema satheesh; +0 rows inserted/updated/deleted +ij(SWIPERCONNECTION)> values f_abs(-5); +ERROR 2850A: User 'SWIPER' does not have execute permission on FUNCTION 'SATHEESH'.'F_ABS'. +ij(SWIPERCONNECTION)> select f_abs(-4) from sys.systables where tablename like 'SYSTAB%'; +ERROR 2850A: User 'SWIPER' does not have execute permission on FUNCTION 'SATHEESH'.'F_ABS'. +ij(SWIPERCONNECTION)> -- Now grant execute permission and try again +set connection satConnection; +ij(SATCONNECTION)> grant execute on function f_abs to swiper; +0 rows inserted/updated/deleted +ij(SATCONNECTION)> set connection swiperConnection; +ij(SWIPERCONNECTION)> -- Should pass now +values f_abs(-5); +1 +----------- +5 +1 row selected +ij(SWIPERCONNECTION)> select f_abs(-4) from sys.systables where tablename like 'SYSTAB%'; +1 +----------- +4 +4 +2 rows selected +ij(SWIPERCONNECTION)> -- Now revoke permission and try +set connection satConnection; +ij(SATCONNECTION)> revoke execute on function f_abs from swiper RESTRICT; +0 rows inserted/updated/deleted +ij(SATCONNECTION)> set connection swiperConnection; +ij(SWIPERCONNECTION)> values f_abs(-5); +ERROR 2850A: User 'SWIPER' does not have execute permission on FUNCTION 'SATHEESH'.'F_ABS'. +ij(SWIPERCONNECTION)> select f_abs(-4) from sys.systables where tablename like 'SYSTAB%'; +ERROR 2850A: User 'SWIPER' does not have execute permission on FUNCTION 'SATHEESH'.'F_ABS'. +ij(SWIPERCONNECTION)> -- Now try public permission +set connection satConnection; +ij(SATCONNECTION)> grant execute on function f_abs to public; +0 rows inserted/updated/deleted +ij(SATCONNECTION)> set connection swiperConnection; +ij(SWIPERCONNECTION)> -- Should pass again +values f_abs(-5); +1 +----------- +5 +1 row selected +ij(SWIPERCONNECTION)> select f_abs(-4) from sys.systables where tablename like 'SYSTAB%'; +1 +----------- +4 +4 +2 rows selected ij(SWIPERCONNECTION)> 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=381408&r1=381407&r2=381408&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 10:54:27 2006 @@ -363,41 +363,41 @@ " no sql called on null input" }; - private static int s1F1() + public static int s1F1() { routineCalled = true; return 1; } - private static int s2F1a() + public static int s2F1a() { routineCalled = true; return 1; } - private static int s2F1b( String s) + public static int s2F1b( String s) { routineCalled = true; return 1; } - private static int s2F1c( String s1, String s2) + public static int s2F1c( String s1, String s2) { routineCalled = true; return 1; } - private static int s2F1d( int i) + public static int s2F1d( int i) { routineCalled = true; return 1; } - private static int s2F2() + public static int s2F2() { routineCalled = true; return 1; } - private static void s1F1P( ) + public static void s1F1P( ) { routineCalled = true; } - private static void s1P1( ) + public static void s1P1( ) { routineCalled = true; } @@ -781,7 +781,7 @@ */ "create procedure r1.f1()" + " language java parameter style java" + - " external name 'org.apache.derbyTesting.functionTests.tests.lang.grantRevoke.s1F1'" + + " external name 'org.apache.derbyTesting.functionTests.tests.lang.grantRevoke.s1P1'" + " no sql called on null input", "create function r2.f1() returns int" + " language java parameter style java" + @@ -1021,6 +1021,9 @@ private static final String[] tablePrivErrMsgFixedSegs = { "User '", "' does not have ", " permission on table '", "'.'", "'."}; + private static final String[] executePrivErrMsgFixedSegs + = { "User '", "' does not have execute permission on ", " '", "'.'", "'."}; + private abstract class TablePrivCheck extends PrivCheck { String table; @@ -1452,7 +1455,7 @@ private void checkUser( User user, StringBuffer sb, String testLabel) throws SQLException { -System.out.println("SelectPrivCheck: " + sb.toString()); + System.out.println("SelectPrivCheck: " + sb.toString()); PreparedStatement ps = user.getConnection().prepareStatement( sb.toString()); try { @@ -1592,7 +1595,7 @@ sb.append(")"); boolean savedAutoCommit = user.getConnection().getAutoCommit(); user.getConnection().setAutoCommit( false); -System.out.println("InsertPrivCheck: " + sb.toString()); + System.out.println("InsertPrivCheck: " + sb.toString()); PreparedStatement ps = user.getConnection().prepareStatement( sb.toString()); try { @@ -1680,7 +1683,7 @@ } appendAColumnValue( sb, rs.getInt(5)); rs.close(); -System.out.println("UpdatePrivCheck: " + sb.toString()); + System.out.println("UpdatePrivCheck: " + sb.toString()); PreparedStatement ps = user.getConnection().prepareStatement( sb.toString()); try { @@ -1877,8 +1880,65 @@ */ void checkUser( User user, String testLabel) throws SQLException { - // RESOLVE + StringBuffer sb = new StringBuffer(); + if (isFunction) + sb.append( "values \""); + else + sb.append( "call \""); + sb.append(schema); + sb.append("\".\""); + sb.append(routine); + sb.append("\""); + sb.append("()"); + + boolean savedAutoCommit = user.getConnection().getAutoCommit(); + user.getConnection().setAutoCommit(false); + System.out.println("ExecutePrivCheck: " + sb.toString()); + PreparedStatement ps = user.getConnection().prepareStatement(sb.toString()); + try + { + if (isFunction) + { + ResultSet rs = ps.executeQuery(); + rs.close(); + } + else + ps.executeUpdate(); + if( ! (privIsPublic || expectPriv)) + reportFailure( "An execute was performed without permission. (" + testLabel + ")"); + } + catch( SQLException sqle) + { + checkExecutePermissionMsg( sqle, user, testLabel); + } + finally + { + try + { + user.getConnection().rollback(); + } + finally + { + user.getConnection().setAutoCommit( savedAutoCommit); + } + } } // end of checkUser + + /* Check that the error message looks right. It should be + * User '{user}' does not have {action} permission on table '{schema}'.'{table}'. + */ + protected void checkExecutePermissionMsg( SQLException sqle, + User user, + String testLabel) + { + checkSQLException( sqle, ! expectPriv, "2850A", testLabel, + executePrivErrMsgFixedSegs, + new String[][]{ new String[] { user.name}, + new String[] { (isFunction)?"FUNCTION":"PROCEDURE"}, + new String[] { schema}, + new String[] { routine}}, + new boolean[]{true, true, false, false}); + } // end of checkTablePermissionMsg } // end of class ExecutePrivCheck } @@ -1895,7 +1955,7 @@ this.name = name; this.password = password; isPublic = "public".equalsIgnoreCase( name); -System.out.println("name = "+name+" password = "+password); + System.out.println("name = "+name+" password = "+password); } boolean isPublic() 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=381408&r1=381407&r2=381408&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 10:54:27 2006 @@ -172,8 +172,55 @@ select * from satheesh.table1, (select i from satheesh.tsat) table2; select * from satheesh.table1, (select j from satheesh.tsat) table2; --- GrantRevoke TODO: This one should pass, but currently fails. Not sure how to handle this yet. +-- GrantRevoke TODO: This one should pass, but currently fails. Bind update expression in two steps. update satheesh.tsat set j=i; create table my_tsat (i int not null, c char(10), constraint fk foreign key(i) references satheesh.tsat); + +-- Some simple routine tests. See GrantRevoke.java for more tests +set connection satConnection; + +values f_abs(-5); + +select f_abs(-4) from sys.systables where tablename like 'SYSTAB%'; + +-- Same tests should fail +set connection swiperConnection; +set schema satheesh; + +values f_abs(-5); + +select f_abs(-4) from sys.systables where tablename like 'SYSTAB%'; + +-- Now grant execute permission and try again + +set connection satConnection; + +grant execute on function f_abs to swiper; + +set connection swiperConnection; + +-- Should pass now +values f_abs(-5); + +select f_abs(-4) from sys.systables where tablename like 'SYSTAB%'; + +-- Now revoke permission and try + +set connection satConnection; +revoke execute on function f_abs from swiper RESTRICT; + +set connection swiperConnection; +values f_abs(-5); +select f_abs(-4) from sys.systables where tablename like 'SYSTAB%'; + +-- Now try public permission +set connection satConnection; +grant execute on function f_abs to public; +set connection swiperConnection; + +-- Should pass again +values f_abs(-5); + +select f_abs(-4) from sys.systables where tablename like 'SYSTAB%';