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/27 19:54:31 UTC
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...
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%';