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/03/16 09:12:44 UTC
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...
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');