db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From banda...@apache.org
Subject svn commit: r386283 - 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/ engine/org/apache/derby/loc/ shared/org/apache/derby/shared/common/refe...
Date Thu, 16 Mar 2006 08:12:44 GMT
Author: bandaram
Date: Thu Mar 16 00:12:41 2006
New Revision: 386283

URL: http://svn.apache.org/viewcvs?rev=386283&view=rev
Log:
DERBY-464: More checkins to implement Grant & Revoke functionality as defined
by functional spec. This round of changes address:

1) Adding schema creation privilege checks for implicitly created schemas as
well. Previous checkin addressed explicitly created schemas.
2) Disable grant/revoke operations on system tables or system routines not in
SQLJ or SYSCS_UTIL schemas. These objects should always be available for
SELECT or EXECUTE operations as approriate.
3) Add RoutinePermDescriptors during database creation for routines that can
be executed by public. Currently 5 routines have EXECUTE privilege to PUBLIC.
It should be possible to execute these routines on a new database.

Add functional tests to test each of these.

Submitted by Satheesh Bandaram (satheesh@sourcery.org)

Modified:
    db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/dictionary/SchemaDescriptor.java
    db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/dictionary/StatementSchemaPermission.java
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/catalog/DataDictionaryImpl.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/DDLStatementNode.java
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/PrivilegeNode.java
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/StaticMethodCallNode.java
    db/derby/code/trunk/java/engine/org/apache/derby/loc/messages_en.properties
    db/derby/code/trunk/java/shared/org/apache/derby/shared/common/reference/SQLState.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/grantRevokeDDL.sql

Modified: db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/dictionary/SchemaDescriptor.java
URL: http://svn.apache.org/viewcvs/db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/dictionary/SchemaDescriptor.java?rev=386283&r1=386282&r2=386283&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/dictionary/SchemaDescriptor.java
(original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/dictionary/SchemaDescriptor.java
Thu Mar 16 00:12:41 2006
@@ -322,6 +322,22 @@
 		return(isSystem);
 	}
 
+	/**
+	 * Indicate whether this is a system schema with grantable routines
+	 *
+	 * @return true/false
+	 */
+	public boolean isSchemaWithGrantableRoutines()
+	{
+		if (!isSystem)
+			return true;
+
+		if (name.equals(STD_SQLJ_SCHEMA_NAME) || name.equals(STD_SYSTEM_UTIL_SCHEMA_NAME))
+			return true;
+
+		return false;
+	}
+
 	public boolean isSYSIBM()
 	{
 		return isSYSIBM;

Modified: db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/dictionary/StatementSchemaPermission.java
URL: http://svn.apache.org/viewcvs/db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/dictionary/StatementSchemaPermission.java?rev=386283&r1=386282&r2=386283&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/dictionary/StatementSchemaPermission.java
(original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/dictionary/StatementSchemaPermission.java
Thu Mar 16 00:12:41 2006
@@ -58,7 +58,11 @@
 	{
 		if (privType == Authorizer.MODIFY_SCHEMA_PRIV)
 		{
-			SchemaDescriptor sd = dd.getSchemaDescriptor(schemaName, tc, true);
+			SchemaDescriptor sd = dd.getSchemaDescriptor(schemaName, tc, false);
+			// If schema hasn't been created already, no need to check
+			if (sd == null)
+				return;
+
 			if (!authid.equals(sd.getAuthorizationId()))
 				throw StandardException.newException(
 					SQLState.AUTH_NO_ACCESS_NOT_OWNER, authid, schemaName);

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/catalog/DataDictionaryImpl.java
URL: http://svn.apache.org/viewcvs/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/catalog/DataDictionaryImpl.java?rev=386283&r1=386282&r2=386283&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/catalog/DataDictionaryImpl.java
(original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/catalog/DataDictionaryImpl.java
Thu Mar 16 00:12:41 2006
@@ -8298,9 +8298,11 @@
      * @param return_type   null for procedure.  For functions the return type
      *                      of the function.
      *
+     * @return UUID 		UUID of system routine that got created.
+     *
 	 * @exception  StandardException  Standard exception policy.
      **/
-    private final void createSystemProcedureOrFunction(
+    private final UUID createSystemProcedureOrFunction(
     String                  routine_name,
     UUID                    schema_uuid,
     String[]                arg_names,
@@ -8358,10 +8360,11 @@
                 true,                               // true - calledOnNullInput
                 return_type);
 
+		UUID routine_uuid = getUUIDFactory().createUUID();
         AliasDescriptor ads = 
             new AliasDescriptor(
                 this,
-                getUUIDFactory().createUUID(),
+                routine_uuid,
                 routine_name,
                 schema_uuid,
                 procClass,	
@@ -8376,6 +8379,8 @@
 
         addDescriptor(
             ads, null, DataDictionary.SYSALIASES_CATALOG_NUM, false, tc);
+
+		return routine_uuid;
     }
 
     /**
@@ -8399,6 +8404,7 @@
 		** SYSCS_UTIL routines.
 		*/
 
+		UUID routine_uuid = null;
         // used to put procedure into the SYSCS_UTIL schema
 		UUID sysUtilUUID = getSystemUtilSchemaDescriptor().getUUID();
 
@@ -8449,7 +8455,7 @@
 
             };
 
-            createSystemProcedureOrFunction(
+            routine_uuid = createSystemProcedureOrFunction(
                 "SYSCS_COMPRESS_TABLE",
                 sysUtilUUID,
                 arg_names,
@@ -8459,6 +8465,8 @@
                 RoutineAliasInfo.MODIFIES_SQL_DATA,
                 (TypeDescriptor) null,
                 tc);
+
+			createRoutinePermPublicDescriptor(routine_uuid, tc);
         }
 
         // void SYSCS_UTIL.SYSCS_CHECKPOINT_DATABASE()
@@ -8586,7 +8594,7 @@
                     Types.SMALLINT)
             };
 
-            createSystemProcedureOrFunction(
+            routine_uuid = createSystemProcedureOrFunction(
                 "SYSCS_SET_RUNTIMESTATISTICS",
                 sysUtilUUID,
                 arg_names,
@@ -8596,6 +8604,8 @@
                 RoutineAliasInfo.CONTAINS_SQL,
                 (TypeDescriptor) null,
                 tc);
+
+			createRoutinePermPublicDescriptor(routine_uuid, tc);
         }
 
         // void SYSCS_UTIL.SYSCS_SET_STATISTICS_TIMING(smallint)
@@ -8609,7 +8619,7 @@
                     Types.SMALLINT)
             };
 
-            createSystemProcedureOrFunction(
+            routine_uuid = createSystemProcedureOrFunction(
                 "SYSCS_SET_STATISTICS_TIMING",
                 sysUtilUUID,
                 arg_names,
@@ -8619,6 +8629,8 @@
                 RoutineAliasInfo.CONTAINS_SQL,
                 (TypeDescriptor) null,
                 tc);
+
+			createRoutinePermPublicDescriptor(routine_uuid, tc);
         }
 
 
@@ -8683,7 +8695,7 @@
         // CLOB SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS()
         {
 
-            createSystemProcedureOrFunction(
+            routine_uuid = createSystemProcedureOrFunction(
                 "SYSCS_GET_RUNTIMESTATISTICS",
                 sysUtilUUID,
                 null,
@@ -8701,6 +8713,8 @@
                     Types.CLOB, Limits.DB2_LOB_MAXWIDTH),
                 */
                 tc);
+
+			createRoutinePermPublicDescriptor(routine_uuid, tc);
         }
 
 
@@ -9471,6 +9485,30 @@
 
 	}
 
+	/**
+	 * Create RoutinePermDescriptor to grant access to PUBLIC for
+	 * this system routine. Currently only SYSUTIL routines need access
+	 * granted to execute them when a database is created.
+	 *
+	 * @param routineUUID   uuid of the routine
+	 * @param tc			TransactionController to use
+	 *
+	 * @exception  StandardException  Standard exception policy.
+	 */
+	void createRoutinePermPublicDescriptor(
+	UUID routineUUID,
+	TransactionController tc) throws StandardException
+	{
+		RoutinePermsDescriptor routinePermDesc =
+			new RoutinePermsDescriptor(
+				this,
+				"PUBLIC",
+				authorizationDBA,
+				routineUUID);
+
+		addDescriptor(
+   			routinePermDesc, null, DataDictionary.SYSROUTINEPERMS_CATALOG_NUM, false, tc);
+	}
 
     /**
      * Create system procedures added in version 10.1.
@@ -9489,6 +9527,8 @@
 		throws StandardException
     {
 
+		UUID routine_uuid = null;
+
         // void SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE(
         //     IN SCHEMANAME        VARCHAR(128), 
         //     IN TABLENAME         VARCHAR(128),
@@ -9519,7 +9559,7 @@
                     Types.SMALLINT)
             };
 
-            createSystemProcedureOrFunction(
+            routine_uuid = createSystemProcedureOrFunction(
                 "SYSCS_INPLACE_COMPRESS_TABLE",
                 sysUtilUUID,
                 arg_names,
@@ -9529,6 +9569,8 @@
                 RoutineAliasInfo.MODIFIES_SQL_DATA,
                 (TypeDescriptor) null,
                 tc);
+
+			createRoutinePermPublicDescriptor(routine_uuid, tc);
         }
     }
 

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=386283&r1=386282&r2=386283&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
Thu Mar 16 00:12:41 2006
@@ -118,9 +118,7 @@
 					AliasDescriptor ad = dd.getAliasDescriptor( routinePermsKey.getRoutineUUID());
 					SchemaDescriptor sd = dd.getSchemaDescriptor( ad.getSchemaUUID(),
 											  ConnectionUtil.getCurrentLCC().getTransactionExecute());
-					// GrantRevoke TODO: This needs to be changed. Shouldn't allow execute on all system
-					// routines.
-					if (sd.isSystemSchema())
+					if (sd.isSystemSchema() && !sd.isSchemaWithGrantableRoutines())
 						permissions = new RoutinePermsDescriptor( dd,
 																  routinePermsKey.getGrantee(),
                                                                   (String) null,

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/DDLStatementNode.java
URL: http://svn.apache.org/viewcvs/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/DDLStatementNode.java?rev=386283&r1=386282&r2=386283&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/DDLStatementNode.java
(original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/DDLStatementNode.java
Thu Mar 16 00:12:41 2006
@@ -27,6 +27,7 @@
 import org.apache.derby.iapi.sql.dictionary.DataDictionary;
 import org.apache.derby.iapi.sql.dictionary.SchemaDescriptor;
 import org.apache.derby.iapi.sql.dictionary.TableDescriptor;
+import org.apache.derby.iapi.sql.compile.CompilerContext;
 import org.apache.derby.iapi.sql.conn.Authorizer;
 import org.apache.derby.iapi.reference.SQLState;
 import org.apache.derby.iapi.error.StandardException;
@@ -224,6 +225,7 @@
 		//boolean needError = !(implicitCreateSchema || (schemaName == null));
 		boolean needError = !implicitCreateSchema;
 		SchemaDescriptor sd = getSchemaDescriptor(schemaName, needError);
+		CompilerContext cc = getCompilerContext();
 
 		if (sd == null) {
 			/* Disable creating schemas starting with SYS */
@@ -235,10 +237,12 @@
 
 			sd  = new SchemaDescriptor(getDataDictionary(), schemaName,
 				(String) null, (UUID)null, false);
+
+			cc.addRequiredSchemaPriv(schemaName, null, Authorizer.CREATE_SCHEMA_PRIV);
 		}
 
 		if (ownerCheck)
-			getCompilerContext().addRequiredSchemaPriv(sd.getSchemaName(), null,
+			cc.addRequiredSchemaPriv(sd.getSchemaName(), null,
 						Authorizer.MODIFY_SCHEMA_PRIV);
 
 		/*

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/PrivilegeNode.java
URL: http://svn.apache.org/viewcvs/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/PrivilegeNode.java?rev=386283&r1=386282&r2=386283&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/PrivilegeNode.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/PrivilegeNode.java Thu
Mar 16 00:12:41 2006
@@ -110,6 +110,9 @@
         case TABLE_PRIVILEGES:
             TableName tableName = (TableName) objectOfPrivilege;
             sd = getSchemaDescriptor( tableName.getSchemaName(), true);
+            if (sd.isSystemSchema())
+                throw StandardException.newException(SQLState.AUTH_GRANT_REVOKE_NOT_ALLOWED,
tableName.getFullTableName());
+				
             TableDescriptor td = getTableDescriptor( tableName.getTableName(), sd);
             if( td == null)
                 throw StandardException.newException( SQLState.LANG_TABLE_NOT_FOUND, tableName);
@@ -127,6 +130,9 @@
             RoutineDesignator rd = (RoutineDesignator) objectOfPrivilege;
             sd = getSchemaDescriptor( rd.name.getSchemaName(), true);
 
+            if (!sd.isSchemaWithGrantableRoutines())
+                throw StandardException.newException(SQLState.AUTH_GRANT_REVOKE_NOT_ALLOWED,
rd.name.getFullTableName());
+				
             AliasDescriptor proc = null;
             RoutineAliasInfo routineInfo = null;
             java.util.List list = getDataDictionary().getRoutineList(

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/StaticMethodCallNode.java
URL: http://svn.apache.org/viewcvs/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/StaticMethodCallNode.java?rev=386283&r1=386282&r2=386283&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/StaticMethodCallNode.java
(original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/StaticMethodCallNode.java
Thu Mar 16 00:12:41 2006
@@ -235,6 +235,7 @@
 
 
 		alreadyBound = true;
+		getCompilerContext().addRequiredRoutinePriv(ad);
 
 		// If this is a function call with a variable length
 		// return type, then we need to push a CAST node.
@@ -284,7 +285,6 @@
 			}
 		}
 
-		getCompilerContext().addRequiredRoutinePriv(ad);
 		return this;
 	}
 
@@ -1097,5 +1097,13 @@
 			}
 
 		}
+	}
+
+	/**
+	 * Set default privilege of EXECUTE for this node. 
+	 */
+	int getPrivType()
+	{
+		return Authorizer.EXECUTE_PRIV;
 	}
 }

Modified: db/derby/code/trunk/java/engine/org/apache/derby/loc/messages_en.properties
URL: http://svn.apache.org/viewcvs/db/derby/code/trunk/java/engine/org/apache/derby/loc/messages_en.properties?rev=386283&r1=386282&r2=386283&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/loc/messages_en.properties (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/loc/messages_en.properties Thu Mar 16
00:12:41 2006
@@ -1060,6 +1060,7 @@
 2850C=User ''{0}'' is not the owner of {1} ''{2}''.''{3}''.
 2850D=User ''{0}'' can not perform the operation in schema ''{1}''.
 2850E=User ''{0}'' can not create schema ''{1}''. Only database owner could issue this statement.
+2850F=Grant or Revoke operation is not allowed on object ''{0}''.
 04501.C=Database connection refused.
 
 

Modified: db/derby/code/trunk/java/shared/org/apache/derby/shared/common/reference/SQLState.java
URL: http://svn.apache.org/viewcvs/db/derby/code/trunk/java/shared/org/apache/derby/shared/common/reference/SQLState.java?rev=386283&r1=386282&r2=386283&view=diff
==============================================================================
--- db/derby/code/trunk/java/shared/org/apache/derby/shared/common/reference/SQLState.java
(original)
+++ db/derby/code/trunk/java/shared/org/apache/derby/shared/common/reference/SQLState.java
Thu Mar 16 00:12:41 2006
@@ -1368,6 +1368,7 @@
 	String AUTH_NOT_OWNER                                              = "2850C";
 	String AUTH_NO_ACCESS_NOT_OWNER                                    = "2850D";
 	String AUTH_NOT_DATABASE_OWNER                                     = "2850E";
+	String AUTH_GRANT_REVOKE_NOT_ALLOWED                               = "2850F";
 
 	/*
 	** Dependency manager

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=386283&r1=386282&r2=386283&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
Thu Mar 16 00:12:41 2006
@@ -124,6 +124,10 @@
 6 rows selected
 ij(SATCONNECTION)> -- Now connect as different user and try to do DDLs in schema owned
by satheesh
 connect 'grantRevokeDDL;user=Swiper' as swiperConnection;
+ij(SWIPERCONNECTION)> create table swiperTab (i int, j int);
+0 rows inserted/updated/deleted
+ij(SWIPERCONNECTION)> insert into swiperTab values (1,1);
+1 row inserted/updated/deleted
 ij(SWIPERCONNECTION)> set schema satheesh;
 0 rows inserted/updated/deleted
 ij(SWIPERCONNECTION)> -- All these DDLs should fail.
@@ -142,9 +146,6 @@
 ERROR 2850D: User 'SWIPER' can not perform the operation in schema 'SATHEESH'.
 ij(SWIPERCONNECTION)> alter table tsat add column k int;
 ERROR 2850D: User 'SWIPER' can not perform the operation in schema 'SATHEESH'.
-ij(SWIPERCONNECTION)> -- Now create own schema
-create schema swiper;
-0 rows inserted/updated/deleted
 ij(SWIPERCONNECTION)> create table swiper.mytab ( i int, j int);
 0 rows inserted/updated/deleted
 ij(SWIPERCONNECTION)> set schema swiper;
@@ -370,14 +371,22 @@
 xxxxFILTERED-UUIDxxxx|MYSCHEMA                                                          
                                                             |ME                         
                                                                                         
          
 xxxxFILTERED-UUIDxxxx|TESTSCHEMA                                                        
                                                             |TESTSCHEMA                 
                                                                                         
          
 20 rows selected
-ij(SATCONNECTION)> -- Check if DBA can ignore all privilege checks
+ij(SATCONNECTION)> -- Test implicit creation of schemas.. Should fail
 set connection swiperConnection;
-ij(SWIPERCONNECTION)> set schema swiper;
+ij(SWIPERCONNECTION)> create table mywork.t1(i int);
+ERROR 2850E: User 'SWIPER' can not create schema 'MYWORK'. Only database owner could issue
this statement.
+ij(SWIPERCONNECTION)> create view mywork.v1 as select * from swiper.swiperTab;
+ERROR 2850E: User 'SWIPER' can not create schema 'MYWORK'. Only database owner could issue
this statement.
+ij(SWIPERCONNECTION)> -- Implicit schema creation should only work if creating own schema
+connect 'grantRevokeDDL;user=monica' as monicaConnection;
+ij(MONICACONNECTION)> create table mywork.t1 ( i int);
+ERROR 2850E: User 'MONICA' can not create schema 'MYWORK'. Only database owner could issue
this statement.
+ij(MONICACONNECTION)> create table monica.shouldPass(c char(10));
 0 rows inserted/updated/deleted
-ij(SWIPERCONNECTION)> create table swiperTab (i int, j int);
+ij(MONICACONNECTION)> -- Check if DBA can ignore all privilege checks
+set connection swiperConnection;
+ij(SWIPERCONNECTION)> set schema swiper;
 0 rows inserted/updated/deleted
-ij(SWIPERCONNECTION)> insert into swiperTab values (1,1);
-1 row inserted/updated/deleted
 ij(SWIPERCONNECTION)> revoke select on swiperTab from satheesh;
 0 rows inserted/updated/deleted
 ij(SWIPERCONNECTION)> revoke insert on swiperTab from satheesh;
@@ -401,4 +410,81 @@
 0 rows inserted/updated/deleted
 ij(SATCONNECTION)> revoke insert on swiper.swiperTab from satheesh;
 0 rows inserted/updated/deleted
+ij(SATCONNECTION)> -- Test system routines. Some don't need explicit grant and others
do
+-- allowing for only DBA use by default
+set connection satConnection;
+ij(SATCONNECTION)> -- Try granting or revoking from system tables. Should fail
+grant select on sys.systables to sam;
+ERROR 2850F: Grant or Revoke operation is not allowed on object 'SYS.SYSTABLES'.
+ij(SATCONNECTION)> grant delete on sys.syscolumns to sam;
+ERROR 2850F: Grant or Revoke operation is not allowed on object 'SYS.SYSCOLUMNS'.
+ij(SATCONNECTION)> grant update(alias) on sys.sysaliases to swiper;
+ERROR 2850F: Grant or Revoke operation is not allowed on object 'SYS.SYSALIASES'.
+ij(SATCONNECTION)> revoke all privileges on sys.systableperms from public;
+ERROR 2850F: Grant or Revoke operation is not allowed on object 'SYS.SYSTABLEPERMS'.
+ij(SATCONNECTION)> revoke trigger on sys.sysroutineperms from sam;
+ERROR 2850F: Grant or Revoke operation is not allowed on object 'SYS.SYSROUTINEPERMS'.
+ij(SATCONNECTION)> -- Try granting or revoking from system routines that is expected fail
+grant execute on procedure sysibm.sqlprocedures to sam;
+ERROR 2850F: Grant or Revoke operation is not allowed on object 'SYSIBM.SQLPROCEDURES'.
+ij(SATCONNECTION)> revoke execute on procedure sysibm.sqlcamessage from public restrict;
+ERROR 2850F: Grant or Revoke operation is not allowed on object 'SYSIBM.SQLCAMESSAGE'.
+ij(SATCONNECTION)> -- Try positive tests
+connect 'grantRevokeDDL;user=sam' as samConnection;
+ij(SAMCONNECTION)> create table samTable(i int);
+0 rows inserted/updated/deleted
+ij(SAMCONNECTION)> insert into samTable values 1,2,3,4,5,6,7;
+7 rows inserted/updated/deleted
+ij(SAMCONNECTION)> -- Following should pass... PUBLIC should have access to these
+call SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1);
+0 rows inserted/updated/deleted
+ij(SAMCONNECTION)> call SYSCS_UTIL.SYSCS_SET_STATISTICS_TIMING(1);
+0 rows inserted/updated/deleted
+ij(SAMCONNECTION)> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
+1                                                                                       
                                       
+--------------------------------------------------------------------------------------------------------------------------------
+Statement Name: 
+	null
+Statement Text: 
+	call SYSCS_UTIL.SYSCS_SET_STATISTICS_TIMING(1)
+Parse Time: 0
+Bind Time: 0
+Optimize Tim&
+1 row selected
+ij(SAMCONNECTION)> call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('SAM', 'SAMTABLE', 1);
+0 rows inserted/updated/deleted
+ij(SAMCONNECTION)> call SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE('SAM', 'SAMTABLE', 1,
1, 1);
+0 rows inserted/updated/deleted
+ij(SAMCONNECTION)> -- Try compressing tables not owned...
+-- INPLACE_COMPRESS currently passes, pending DERBY-1062
+call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('SWIPER', 'MYTAB', 1);
+ERROR 38000: The exception 'SQL Exception: User 'SAM' can not perform the operation in schema
'SWIPER'.' was thrown while evaluating an expression.
+ERROR 2850D: User 'SAM' can not perform the operation in schema 'SWIPER'.
+ij(SAMCONNECTION)> call SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE('SWIPER', 'MYTAB', 1,
1, 1);
+0 rows inserted/updated/deleted
+ij(SAMCONNECTION)> -- Try other system routines. All should fail
+call SYSCS_UTIL.SYSCS_EXPORT_TABLE('SAM', 'SAMTABLE' , 'table.dat', null, null, null);
+ERROR 2850A: User 'SAM' does not have execute permission on PROCEDURE 'SYSCS_UTIL'.'SYSCS_EXPORT_TABLE'.
+ij(SAMCONNECTION)> call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.pageSize',
'4096');
+ERROR 2850A: User 'SAM' does not have execute permission on PROCEDURE 'SYSCS_UTIL'.'SYSCS_SET_DATABASE_PROPERTY'.
+ij(SAMCONNECTION)> values SYSCS_UTIL.SYSCS_GET_DATABASE_PROPERTY('derby.storage.pageSize');
+ERROR 2850A: User 'SAM' does not have execute permission on FUNCTION 'SYSCS_UTIL'.'SYSCS_GET_DATABASE_PROPERTY'.
+ij(SAMCONNECTION)> -- Try after DBA grants permissions
+set connection satConnection;
+ij(SATCONNECTION)> grant execute on procedure SYSCS_UTIL.SYSCS_EXPORT_TABLE to public;
+0 rows inserted/updated/deleted
+ij(SATCONNECTION)> grant execute on procedure SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY to
sam;
+0 rows inserted/updated/deleted
+ij(SATCONNECTION)> grant execute on function SYSCS_UTIL.SYSCS_GET_DATABASE_PROPERTY to
sam;
+0 rows inserted/updated/deleted
+ij(SATCONNECTION)> -- Now these should pass
+call SYSCS_UTIL.SYSCS_EXPORT_TABLE('SAM', 'SAMTABLE' , 'table.dat', null, null, null);
+0 rows inserted/updated/deleted
+ij(SATCONNECTION)> call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.pageSize',
'4096');
+0 rows inserted/updated/deleted
+ij(SATCONNECTION)> values SYSCS_UTIL.SYSCS_GET_DATABASE_PROPERTY('derby.storage.pageSize');
+1                                                                                       
                                       
+--------------------------------------------------------------------------------------------------------------------------------
+4096                                                                                    
                                       
+1 row selected
 ij(SATCONNECTION)> 

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=386283&r1=386282&r2=386283&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
Thu Mar 16 00:12:41 2006
@@ -91,6 +91,9 @@
 -- Now connect as different user and try to do DDLs in schema owned by satheesh
 connect 'grantRevokeDDL;user=Swiper' as swiperConnection;
 
+create table swiperTab (i int, j int);
+insert into swiperTab values (1,1);
+
 set schema satheesh;
 
 -- All these DDLs should fail.
@@ -109,9 +112,6 @@
 
 alter table tsat add column k int;
 
--- Now create own schema
-create schema swiper;
-
 create table swiper.mytab ( i int, j int);
 
 set schema swiper;
@@ -272,13 +272,21 @@
 
 select * from sys.sysschemas;
 
+-- Test implicit creation of schemas.. Should fail
+set connection swiperConnection;
+create table mywork.t1(i int);
+create view mywork.v1 as select * from swiper.swiperTab;
+
+-- Implicit schema creation should only work if creating own schema
+connect 'grantRevokeDDL;user=monica' as monicaConnection;
+create table mywork.t1 ( i int);
+create table monica.shouldPass(c char(10));
+
 -- Check if DBA can ignore all privilege checks
 
 set connection swiperConnection;
 
 set schema swiper;
-create table swiperTab (i int, j int);
-insert into swiperTab values (1,1);
 
 revoke select on swiperTab from satheesh;
 
@@ -293,4 +301,59 @@
 
 grant select on swiper.swiperTab to sam;
 revoke insert on swiper.swiperTab from satheesh;
+
+
+-- Test system routines. Some don't need explicit grant and others do
+-- allowing for only DBA use by default
+
+set connection satConnection;
+
+-- Try granting or revoking from system tables. Should fail
+
+grant select on sys.systables to sam;
+grant delete on sys.syscolumns to sam;
+grant update(alias) on sys.sysaliases to swiper;
+revoke all privileges on sys.systableperms from public;
+revoke trigger on sys.sysroutineperms from sam;
+
+-- Try granting or revoking from system routines that is expected fail
+
+grant execute on procedure sysibm.sqlprocedures to sam;
+revoke execute on procedure sysibm.sqlcamessage from public restrict;
+
+-- Try positive tests
+connect 'grantRevokeDDL;user=sam' as samConnection;
+
+create table samTable(i int);
+insert into samTable values 1,2,3,4,5,6,7;
+
+-- Following should pass... PUBLIC should have access to these
+call SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1);
+call SYSCS_UTIL.SYSCS_SET_STATISTICS_TIMING(1);
+values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
+call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('SAM', 'SAMTABLE', 1);
+call SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE('SAM', 'SAMTABLE', 1, 1, 1);
+
+-- Try compressing tables not owned...
+-- INPLACE_COMPRESS currently passes, pending DERBY-1062
+call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('SWIPER', 'MYTAB', 1);
+call SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE('SWIPER', 'MYTAB', 1, 1, 1);
+
+-- Try other system routines. All should fail
+
+call SYSCS_UTIL.SYSCS_EXPORT_TABLE('SAM', 'SAMTABLE' , 'table.dat', null, null, null);
+call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.pageSize', '4096');
+values SYSCS_UTIL.SYSCS_GET_DATABASE_PROPERTY('derby.storage.pageSize');
+
+-- Try after DBA grants permissions
+set connection satConnection;
+
+grant execute on procedure SYSCS_UTIL.SYSCS_EXPORT_TABLE to public;
+grant execute on procedure SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY to sam;
+grant execute on function SYSCS_UTIL.SYSCS_GET_DATABASE_PROPERTY to sam;
+
+-- Now these should pass
+call SYSCS_UTIL.SYSCS_EXPORT_TABLE('SAM', 'SAMTABLE' , 'table.dat', null, null, null);
+call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.pageSize', '4096');
+values SYSCS_UTIL.SYSCS_GET_DATABASE_PROPERTY('derby.storage.pageSize');
 



Mime
View raw message