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 ma...@apache.org on 2010/06/26 16:16:04 UTC
svn commit: r958230 - in /db/derby/code/branches/10.5: ./
java/engine/org/apache/derby/iapi/sql/conn/
java/engine/org/apache/derby/iapi/sql/dictionary/
java/engine/org/apache/derby/impl/sql/catalog/
java/engine/org/apache/derby/impl/sql/compile/ java/t...
Author: mamta
Date: Sat Jun 26 14:16:04 2010
New Revision: 958230
URL: http://svn.apache.org/viewvc?rev=958230&view=rev
Log:
DERBY-4191 ( Lack of SELECT privilege does not prevent SELECT COUNT(*)) Merging the changes into 10.5 codeline
Modified:
db/derby/code/branches/10.5/ (props changed)
db/derby/code/branches/10.5/java/engine/org/apache/derby/iapi/sql/conn/Authorizer.java
db/derby/code/branches/10.5/java/engine/org/apache/derby/iapi/sql/dictionary/StatementColumnPermission.java
db/derby/code/branches/10.5/java/engine/org/apache/derby/iapi/sql/dictionary/StatementTablePermission.java
db/derby/code/branches/10.5/java/engine/org/apache/derby/impl/sql/catalog/DataDictionaryImpl.java
db/derby/code/branches/10.5/java/engine/org/apache/derby/impl/sql/compile/CompilerContextImpl.java
db/derby/code/branches/10.5/java/engine/org/apache/derby/impl/sql/compile/CursorNode.java
db/derby/code/branches/10.5/java/engine/org/apache/derby/impl/sql/compile/SubqueryNode.java
db/derby/code/branches/10.5/java/testing/org/apache/derbyTesting/functionTests/tests/lang/GrantRevokeDDLTest.java
db/derby/code/branches/10.5/java/testing/org/apache/derbyTesting/functionTests/tests/lang/RolesConferredPrivilegesTest.java
Propchange: db/derby/code/branches/10.5/
------------------------------------------------------------------------------
--- svn:mergeinfo (original)
+++ svn:mergeinfo Sat Jun 26 14:16:04 2010
@@ -1,2 +1,2 @@
/db/derby/code/branches/10.6:957000
-/db/derby/code/trunk:769596,769602,769606,769962,772090,772337,772449,772534,774281,777105,779681,782991,785131,785139,785163,785570,785662,788369,788670,788674,788968,789264,790218,792434,793089,793588,794106,794303,794955,795166,796020,796027,796316,796372,797147,798347,798742,800523,803548,803948,805696,808494,808850,809643,810860,812669,816531,816536,819006,822289,823659,824694,829022,832379,833430,835286,881074,881444,882732,884163,887246,892912,897161,901165,901648,901760,903108,908418,911315,915733,916075,916897,918359,921028,927430,928065,942286,942476,942480,942587,946794,948045,948069,951346,954748,955001,955634,956075,956445,956659
+/db/derby/code/trunk:769596,769602,769606,769962,772090,772337,772449,772534,774281,777105,779681,782991,785131,785139,785163,785570,785662,788369,788670,788674,788968,789264,790218,792434,793089,793588,794106,794303,794955,795166,796020,796027,796316,796372,797147,798347,798742,800523,803548,803948,805696,808494,808850,809643,810860,812669,816531,816536,819006,822289,823659,824694,829022,832379,833430,835286,881074,881444,882732,884163,887246,892912,897161,898635,901165,901648,901760,903108,908418,911315,915733,916075,916897,918359,921028,927430,928065,942286,942476,942480,942587,946794,948045,948069,951346,954748,955001,955634,956075,956445,956659
Modified: db/derby/code/branches/10.5/java/engine/org/apache/derby/iapi/sql/conn/Authorizer.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.5/java/engine/org/apache/derby/iapi/sql/conn/Authorizer.java?rev=958230&r1=958229&r2=958230&view=diff
==============================================================================
--- db/derby/code/branches/10.5/java/engine/org/apache/derby/iapi/sql/conn/Authorizer.java (original)
+++ db/derby/code/branches/10.5/java/engine/org/apache/derby/iapi/sql/conn/Authorizer.java Sat Jun 26 14:16:04 2010
@@ -57,7 +57,22 @@ public interface Authorizer
public static final int DELETE_PRIV = 4;
public static final int TRIGGER_PRIV = 5;
public static final int EXECUTE_PRIV = 6;
- public static final int PRIV_TYPE_COUNT = 7;
+ /*
+ * DERBY-4191
+ * Used to check if user has a table level select privilege/any column
+ * level select privilege to fulfill the requirements for following kind
+ * of queries
+ * select count(*) from t1
+ * select count(1) from t1
+ * select 1 from t1
+ * select t1.c1 from t1, t2
+ * DERBY-4191 was added for Derby bug where for first 3 queries above,
+ * we were not requiring any select privilege on t1. And for the 4th
+ * query, we were not requiring any select privilege on t2 since no
+ * column was selected from t2
+ */
+ public static final int MIN_SELECT_PRIV = 7;
+ public static final int PRIV_TYPE_COUNT = 8;
/* Used to check who can create schemas or who can modify objects in schema */
public static final int CREATE_SCHEMA_PRIV = 16;
Modified: db/derby/code/branches/10.5/java/engine/org/apache/derby/iapi/sql/dictionary/StatementColumnPermission.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.5/java/engine/org/apache/derby/iapi/sql/dictionary/StatementColumnPermission.java?rev=958230&r1=958229&r2=958230&view=diff
==============================================================================
--- db/derby/code/branches/10.5/java/engine/org/apache/derby/iapi/sql/dictionary/StatementColumnPermission.java (original)
+++ db/derby/code/branches/10.5/java/engine/org/apache/derby/iapi/sql/dictionary/StatementColumnPermission.java Sat Jun 26 14:16:04 2010
@@ -122,6 +122,17 @@ public class StatementColumnPermission e
true /* grantable permissions */,
authorizationId,
permittedColumns);
+
+ //DERBY-4191
+ //If we are looking for select privilege on ANY column,
+ //then we can quit as soon as we find some column with select
+ //privilege. This is needed for queries like
+ //select count(*) from t1
+ //select count(1) from t1
+ //select 1 from t1
+ //select t1.c1 from t1, t2
+ if (privType == Authorizer.MIN_SELECT_PRIV && permittedColumns != null)
+ return;
FormatableBitSet unresolvedColumns = (FormatableBitSet)columns.clone();
@@ -181,18 +192,59 @@ public class StatementColumnPermission e
while (unresolvedColumns.anySetBit() >= 0 &&
(r = rci.next()) != null ) {
+ //The user does not have needed privilege directly
+ //granted to it, so let's see if he has that privilege
+ //available to him/her through his roles.
+ permittedColumns = tryRole(lcc, dd, forGrant, r);
+ //DERBY-4191
+ //If we are looking for select privilege on ANY column,
+ //then we can quit as soon as we find some column with select
+ //privilege through this role. This is needed for queries like
+ //select count(*) from t1
+ //select count(1) from t1
+ //select 1 from t1
+ //select t1.c1 from t1, t2
+ if (privType == Authorizer.MIN_SELECT_PRIV && permittedColumns != null) {
+ DependencyManager dm = dd.getDependencyManager();
+ RoleGrantDescriptor rgd =
+ dd.getRoleDefinitionDescriptor(role);
+ ContextManager cm = lcc.getContextManager();
+
+ dm.addDependency(ps, rgd, cm);
+ dm.addDependency(activation, rgd, cm);
+ return;
+ }
- unresolvedColumns = tryRole(lcc, dd, forGrant,
- r, unresolvedColumns);
+ //Use the privileges obtained through the role to satisfy
+ //the column level privileges we need. If all the remaining
+ //column level privileges are satisfied through this role,
+ //we will quit out of this while loop
+ for(int i = unresolvedColumns.anySetBit();
+ i >= 0;
+ i = unresolvedColumns.anySetBit(i)) {
+
+ if(permittedColumns != null && permittedColumns.get(i)) {
+ unresolvedColumns.clear(i);
+ }
+ }
}
}
}
+ TableDescriptor td = getTableDescriptor(dd);
+ //if we are still here, then that means that we didn't find any select
+ //privilege on the table or any column in the table
+ if (privType == Authorizer.MIN_SELECT_PRIV)
+ throw StandardException.newException( forGrant ? SQLState.AUTH_NO_TABLE_PERMISSION_FOR_GRANT
+ : SQLState.AUTH_NO_TABLE_PERMISSION,
+ authorizationId,
+ getPrivName(),
+ td.getSchemaName(),
+ td.getName());
int remains = unresolvedColumns.anySetBit();
if (remains >= 0) {
// No permission on this column.
- TableDescriptor td = getTableDescriptor(dd);
ColumnDescriptor cd = td.getColumnDescriptor(remains + 1);
if(cd == null) {
@@ -378,23 +430,20 @@ public class StatementColumnPermission e
/**
- * Given the set of yet unresolved column permissions, try to use
- * the supplied role r to resolve them. After this is done, return
- * the set of columns still unresolved. If the role is used for
- * anything, record a dependency.
+ * Try to use the supplied role r to see what column privileges are we
+ * entitled to.
*
* @param lcc language connection context
* @param dd data dictionary
* @param forGrant true of a GRANTable permission is sought
* @param r the role to inspect to see if it can supply the required
* privileges
- * @param unresolvedColumns the set of columns yet unaccounted for
+ * return the set of columns on which we have privileges through this role
*/
private FormatableBitSet tryRole(LanguageConnectionContext lcc,
DataDictionary dd,
boolean forGrant,
- String r,
- FormatableBitSet unresolvedColumns)
+ String r)
throws StandardException {
FormatableBitSet permittedColumns = null;
@@ -407,17 +456,7 @@ public class StatementColumnPermission e
// if grantable is given, applicable in both cases, so use union
permittedColumns = addPermittedColumns(dd, true, r, permittedColumns);
-
- for(int i = unresolvedColumns.anySetBit();
- i >= 0;
- i = unresolvedColumns.anySetBit(i)) {
-
- if(permittedColumns != null && permittedColumns.get(i)) {
- unresolvedColumns.clear(i);
- }
- }
-
- return unresolvedColumns;
+ return permittedColumns;
}
Modified: db/derby/code/branches/10.5/java/engine/org/apache/derby/iapi/sql/dictionary/StatementTablePermission.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.5/java/engine/org/apache/derby/iapi/sql/dictionary/StatementTablePermission.java?rev=958230&r1=958229&r2=958230&view=diff
==============================================================================
--- db/derby/code/branches/10.5/java/engine/org/apache/derby/iapi/sql/dictionary/StatementTablePermission.java (original)
+++ db/derby/code/branches/10.5/java/engine/org/apache/derby/iapi/sql/dictionary/StatementTablePermission.java Sat Jun 26 14:16:04 2010
@@ -247,6 +247,7 @@ public class StatementTablePermission ex
switch( privType)
{
case Authorizer.SELECT_PRIV:
+ case Authorizer.MIN_SELECT_PRIV:
priv = perms.getSelectPriv();
break;
case Authorizer.UPDATE_PRIV:
@@ -292,6 +293,7 @@ public class StatementTablePermission ex
switch( privType)
{
case Authorizer.SELECT_PRIV:
+ case Authorizer.MIN_SELECT_PRIV:
return "SELECT";
case Authorizer.UPDATE_PRIV:
return "UPDATE";
Modified: db/derby/code/branches/10.5/java/engine/org/apache/derby/impl/sql/catalog/DataDictionaryImpl.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.5/java/engine/org/apache/derby/impl/sql/catalog/DataDictionaryImpl.java?rev=958230&r1=958229&r2=958230&view=diff
==============================================================================
--- db/derby/code/branches/10.5/java/engine/org/apache/derby/impl/sql/catalog/DataDictionaryImpl.java (original)
+++ db/derby/code/branches/10.5/java/engine/org/apache/derby/impl/sql/catalog/DataDictionaryImpl.java Sat Jun 26 14:16:04 2010
@@ -11862,6 +11862,8 @@ public final class DataDictionaryImpl
static {
colPrivTypeMap = new String[ Authorizer.PRIV_TYPE_COUNT];
colPrivTypeMapForGrant = new String[ Authorizer.PRIV_TYPE_COUNT];
+ colPrivTypeMap[ Authorizer.MIN_SELECT_PRIV] = "s";
+ colPrivTypeMapForGrant[ Authorizer.MIN_SELECT_PRIV] = "S";
colPrivTypeMap[ Authorizer.SELECT_PRIV] = "s";
colPrivTypeMapForGrant[ Authorizer.SELECT_PRIV] = "S";
colPrivTypeMap[ Authorizer.UPDATE_PRIV] = "u";
Modified: db/derby/code/branches/10.5/java/engine/org/apache/derby/impl/sql/compile/CompilerContextImpl.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.5/java/engine/org/apache/derby/impl/sql/compile/CompilerContextImpl.java?rev=958230&r1=958229&r2=958230&view=diff
==============================================================================
--- db/derby/code/branches/10.5/java/engine/org/apache/derby/impl/sql/compile/CompilerContextImpl.java (original)
+++ db/derby/code/branches/10.5/java/engine/org/apache/derby/impl/sql/compile/CompilerContextImpl.java Sat Jun 26 14:16:04 2010
@@ -736,7 +736,7 @@ public class CompilerContextImpl extends
{
currPrivType = ((Integer) privTypeStack.pop()).intValue();
}
-
+
/**
* Add a column privilege to the list of used column privileges.
*
@@ -776,6 +776,36 @@ public class CompilerContextImpl extends
}
UUID tableUUID = td.getUUID();
+
+ //DERBY-4191
+ if( currPrivType == Authorizer.MIN_SELECT_PRIV){
+ //If we are here for MIN_SELECT_PRIV requirement, then first
+ //check if there is already a SELECT privilege requirement on any
+ //of the columns in the table. If yes, then we do not need to add
+ //MIN_SELECT_PRIV requirement for the table because that
+ //requirement is already getting satisfied with the already
+ //existing SELECT privilege requirement
+ StatementTablePermission key = new StatementTablePermission(
+ tableUUID, Authorizer.SELECT_PRIV);
+ StatementColumnPermission tableColumnPrivileges
+ = (StatementColumnPermission) requiredColumnPrivileges.get( key);
+ if( tableColumnPrivileges != null)
+ return;
+ }
+ if( currPrivType == Authorizer.SELECT_PRIV){
+ //If we are here for SELECT_PRIV requirement, then first check
+ //if there is already any MIN_SELECT_PRIV privilege required
+ //on this table. If yes, then that requirement will be fulfilled
+ //by the SELECT_PRIV requirement we are adding now. Because of
+ //that, remove the MIN_SELECT_PRIV privilege requirement
+ StatementTablePermission key = new StatementTablePermission(
+ tableUUID, Authorizer.MIN_SELECT_PRIV);
+ StatementColumnPermission tableColumnPrivileges
+ = (StatementColumnPermission) requiredColumnPrivileges.get( key);
+ if( tableColumnPrivileges != null)
+ requiredColumnPrivileges.remove(key);
+ }
+
StatementTablePermission key = new StatementTablePermission( tableUUID, currPrivType);
StatementColumnPermission tableColumnPrivileges
= (StatementColumnPermission) requiredColumnPrivileges.get( key);
@@ -804,6 +834,20 @@ public class CompilerContextImpl extends
return; // no priv needed, it is per session anyway
}
+ if( currPrivType == Authorizer.SELECT_PRIV){
+ //DERBY-4191
+ //Check if there is any MIN_SELECT_PRIV select privilege required
+ //on this table. If yes, then that requirement will be fulfilled
+ //by the SELECT_PRIV requirement we are adding now. Because of
+ //that, remove the MIN_SELECT_PRIV privilege requirement
+ StatementTablePermission key = new StatementTablePermission(
+ table.getUUID(), Authorizer.MIN_SELECT_PRIV);
+ StatementColumnPermission tableColumnPrivileges
+ = (StatementColumnPermission) requiredColumnPrivileges.get( key);
+ if( tableColumnPrivileges != null)
+ requiredColumnPrivileges.remove(key);
+ }
+
StatementTablePermission key = new StatementTablePermission( table.getUUID(), currPrivType);
requiredTablePrivileges.put(key, key);
}
Modified: db/derby/code/branches/10.5/java/engine/org/apache/derby/impl/sql/compile/CursorNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.5/java/engine/org/apache/derby/impl/sql/compile/CursorNode.java?rev=958230&r1=958229&r2=958230&view=diff
==============================================================================
--- db/derby/code/branches/10.5/java/engine/org/apache/derby/impl/sql/compile/CursorNode.java (original)
+++ db/derby/code/branches/10.5/java/engine/org/apache/derby/impl/sql/compile/CursorNode.java Sat Jun 26 14:16:04 2010
@@ -30,6 +30,7 @@ import org.apache.derby.iapi.services.co
import org.apache.derby.iapi.services.sanity.SanityManager;
import org.apache.derby.iapi.sql.ResultColumnDescriptor;
import org.apache.derby.iapi.sql.compile.C_NodeTypes;
+import org.apache.derby.iapi.sql.conn.Authorizer;
import org.apache.derby.iapi.sql.dictionary.DataDictionary;
import org.apache.derby.iapi.sql.dictionary.TableDescriptor;
import org.apache.derby.impl.sql.CursorInfo;
@@ -263,6 +264,28 @@ public class CursorNode extends DMLState
+ fromList.size()
+ " on return from RS.bindExpressions()");
}
+
+ //DERBY-4191 Make sure that we have minimum select privilege on
+ //each of the tables in the query.
+ getCompilerContext().pushCurrentPrivType(Authorizer.MIN_SELECT_PRIV);
+ FromList resultSetFromList = resultSet.getFromList();
+ for (int index = 0; index < resultSetFromList.size(); index++) {
+ FromTable fromTable = (FromTable) resultSetFromList.elementAt(index);
+ if (fromTable.isPrivilegeCollectionRequired() && fromTable instanceof FromBaseTable)
+ //We ask for MIN_SELECT_PRIV requirement of the first
+ //column in the table. The first column is just a
+ //place holder. What we really do at execution time when
+ //we see we are looking for MIN_SELECT_PRIV privilege is
+ //as follows
+ //1)we will look for SELECT privilege at table level
+ //2)If not found, we will look for SELECT privilege on
+ //ANY column, not necessarily the first column. But since
+ //the constructor for column privilege requires us to pass
+ //a column descriptor, we just choose the first column for
+ //MIN_SELECT_PRIV requirement.
+ getCompilerContext().addRequiredColumnPriv(fromTable.getTableDescriptor().getColumnDescriptor(1));
+ }
+ getCompilerContext().popCurrentPrivType();
}
finally
{
Modified: db/derby/code/branches/10.5/java/engine/org/apache/derby/impl/sql/compile/SubqueryNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.5/java/engine/org/apache/derby/impl/sql/compile/SubqueryNode.java?rev=958230&r1=958229&r2=958230&view=diff
==============================================================================
--- db/derby/code/branches/10.5/java/engine/org/apache/derby/impl/sql/compile/SubqueryNode.java (original)
+++ db/derby/code/branches/10.5/java/engine/org/apache/derby/impl/sql/compile/SubqueryNode.java Sat Jun 26 14:16:04 2010
@@ -28,6 +28,7 @@ import org.apache.derby.iapi.sql.compile
import org.apache.derby.iapi.sql.compile.Visitable;
import org.apache.derby.iapi.sql.compile.Visitor;
import org.apache.derby.iapi.sql.compile.C_NodeTypes;
+import org.apache.derby.iapi.sql.conn.Authorizer;
import org.apache.derby.iapi.sql.dictionary.DataDictionary;
import org.apache.derby.iapi.reference.SQLState;
@@ -466,6 +467,17 @@ public class SubqueryNode extends ValueN
* any *'s have been replaced, so that they don't get expanded.
*/
CompilerContext cc = getCompilerContext();
+ /* DERBY-4191
+ * We should make sure that we require select privileges
+ * on the tables in the underlying subquery and not the
+ * parent sql's privilege. eg
+ * update t1 set c1=(select c2 from t2)
+ * For the query above, when working with the subquery, we should
+ * require select privilege on t2.c2 rather than update privilege.
+ * Prior to fix for DERBY-4191, we were collecting update privilege
+ * requirement for t2.c2 rather than select privilege
+ */
+ cc.pushCurrentPrivType(Authorizer.SELECT_PRIV);
resultSet = resultSet.bindNonVTITables(getDataDictionary(), fromList);
resultSet = resultSet.bindVTITables(fromList);
@@ -533,6 +545,7 @@ public class SubqueryNode extends ValueN
/* Add this subquery to the subquery list */
subqueryList.addSubqueryNode(this);
+ cc.popCurrentPrivType();
return this;
}
Modified: db/derby/code/branches/10.5/java/testing/org/apache/derbyTesting/functionTests/tests/lang/GrantRevokeDDLTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.5/java/testing/org/apache/derbyTesting/functionTests/tests/lang/GrantRevokeDDLTest.java?rev=958230&r1=958229&r2=958230&view=diff
==============================================================================
--- db/derby/code/branches/10.5/java/testing/org/apache/derbyTesting/functionTests/tests/lang/GrantRevokeDDLTest.java (original)
+++ db/derby/code/branches/10.5/java/testing/org/apache/derbyTesting/functionTests/tests/lang/GrantRevokeDDLTest.java Sat Jun 26 14:16:04 2010
@@ -10045,6 +10045,173 @@ public final class GrantRevokeDDLTest ex
/**
+ * DERBY-4191
+ * Make sure that we check for atleast table level select privilege or
+ * any column level select privilege for following kind of queries
+ * select count(*) from t1
+ * select count(1) from t1
+ * select 1 from t1
+ * select t1.c1 from t1, t2
+ */
+ public void testMinimumSelectPrivilegeRequirement() throws SQLException {
+ Connection user1 = openUserConnection("user1");
+ Statement user1St = user1.createStatement();
+
+ Connection user2 = openUserConnection("user2");
+ Statement user2St = user2.createStatement();
+
+ ResultSet rs = null;
+
+ //user1 creates table t4191 and t4191_table2
+ user1St.executeUpdate("create table t4191(x int, y int)");
+ user1St.executeUpdate("create table t4191_table2(z int)");
+ user1St.executeUpdate("create table t4191_table3(c31 int, c32 int)");
+ user1St.executeUpdate("create view view_t4191_table3(v31, v32) " +
+ "as select c31, c32 from t4191_table3");
+
+ user1St.execute("grant update on t4191_table3 to public");
+ user1St.execute("grant insert on t4191_table3 to public");
+ user1St.execute("grant delete on t4191_table3 to public");
+ //none of following DMLs will work because there is no select
+ //privilege available on the view to user2.
+ assertStatementError("42502", user2St, "update user1.t4191_table3 "+
+ "set c31 = ( select max(v31) from user1.view_t4191_table3 )");
+ assertStatementError("42502", user2St, "update user1.t4191_table3 "+
+ "set c31 = ( select count(*) from user1.view_t4191_table3 )");
+ assertStatementError("42502", user2St, "update user1.t4191_table3 "+
+ "set c31 = ( select 1 from user1.view_t4191_table3 )");
+ //Following should succeed
+ user2St.execute("delete from user1.t4191_table3");
+
+ //Grant select privilege on view so the above DMLs will start working
+ user1St.execute("grant select on view_t4191_table3 to public");
+ user2St.execute("update user1.t4191_table3 "+
+ "set c31 = ( select max(v31) from user1.view_t4191_table3 )");
+ user2St.execute("update user1.t4191_table3 "+
+ "set c31 = ( select count(*) from user1.view_t4191_table3 )");
+ user2St.execute("update user1.t4191_table3 "+
+ "set c31 = ( select 1 from user1.view_t4191_table3 )");
+
+ //none of following selects will work because there is no select
+ //privilege available to user2 yet.
+ assertStatementError("42500", user2St, "select count(*) from user1.t4191");
+ assertStatementError("42500", user2St, "select count(1) from user1.t4191");
+ assertStatementError("42502", user2St, "select count(y) from user1.t4191");
+ assertStatementError("42500", user2St, "select 1 from user1.t4191");
+ //update below should fail because user2 does not have update
+ //privileges on user1.t4191
+ assertStatementError("42502", user2St, "update user1.t4191 set x=0");
+ //update with subquery should fail too
+ assertStatementError("42502", user2St, "update user1.t4191 set x=" +
+ " ( select max(x) + 2 from user1.t4191 )");
+ assertStatementError("42502", user2St, "update user1.t4191 set x=" +
+ " ( select z from user1.t4191_table2 )");
+
+ //grant select on user1.t4191(x) to user2 and now the above select
+ //statements will work
+ user1St.execute("grant select(x) on t4191 to user2");
+ String[][] expRS = new String [][]
+ {
+ {"0"}
+ };
+ rs = user2St.executeQuery("select count(*) from user1.t4191");
+ JDBC.assertFullResultSet(rs, expRS, true);
+ rs = user2St.executeQuery("select count(1) from user1.t4191");
+ JDBC.assertFullResultSet(rs, expRS, true);
+ rs = user2St.executeQuery("select 1 from user1.t4191");
+ JDBC.assertEmpty(rs);
+
+ //user2 does not have select privilege on 2nd column from user1.t4191
+ assertStatementError("42502", user2St, "select count(y) from user1.t4191");
+ //user2 does not have any select privilege on user1.table t4191_table2
+ assertStatementError("42500", user2St, "select x from user1.t4191_table2, user1.t4191");
+
+ //grant select privilege on a column in user1.table t4191_table2 to user2
+ user1St.execute("grant select(z) on t4191_table2 to user2");
+ //now the following should run fine without any privilege issues
+ rs = user2St.executeQuery("select x from user1.t4191_table2, user1.t4191");
+ JDBC.assertEmpty(rs);
+
+ //revoke some column level privileges from user2
+ user1St.execute("revoke select(x) on t4191 from user2");
+ user1St.execute("revoke select(z) on t4191_table2 from user2");
+ //update below should fail because user2 does not have update
+ //privileges on user1.t4191
+ assertStatementError("42502", user2St, "update user1.t4191 set x=0");
+ //update with subquery should fail too
+ assertStatementError("42502", user2St, "update user1.t4191 set x=" +
+ " ( select max(x) + 2 from user1.t4191 )");
+ assertStatementError("42502", user2St, "update user1.t4191 set x=" +
+ " ( select z from user1.t4191_table2 )");
+ //grant update on user1.t4191 to user2
+ user1St.execute("grant update on t4191 to user2");
+ //following update will now work because it has the required update
+ //privilege
+ assertUpdateCount(user2St, 0, "update user1.t4191 set x=0");
+ //folowing will still fail because there is no select privilege on
+ //user1.t4191(x)
+ assertStatementError("42502", user2St, "update user1.t4191 set x=" +
+ " ( select max(x) + 2 from user1.t4191 )");
+ //following update will fail because there is no select privilege
+ //on user1.t4191_table2
+ assertStatementError("42502", user2St, "update user1.t4191 set x=" +
+ " ( select z from user1.t4191_table2 )");
+ user1St.execute("grant select(y) on t4191 to user2");
+ //folowing will still fail because there is no select privilege on
+ //user1.t4191(x)
+ assertStatementError("42502", user2St, "update user1.t4191 set x=" +
+ " ( select max(x) + 2 from user1.t4191 )");
+ user1St.execute("grant select(x) on t4191 to user2");
+ //following will now work because we have all the required privileges
+ assertUpdateCount(user2St, 0, "update user1.t4191 set x=" +
+ " ( select max(x) + 2 from user1.t4191 )");
+ //folowing will still fail because there is no select privilege on
+ //user1.t4191(x)
+ assertStatementError("42502", user2St, "update user1.t4191 set x=" +
+ " ( select z from user1.t4191_table2 )");
+ user1St.execute("grant select on t4191_table2 to user2");
+ //following will now pass
+ assertUpdateCount(user2St, 0, "update user1.t4191 set x=" +
+ " ( select z from user1.t4191_table2 )");
+
+ //take away select privilege from one column and grant privilege on
+ //another column in user1.t4191 to user2
+ user1St.execute("revoke select(x) on t4191 from user2");
+ //the following update will work because we still have update
+ //privilege granted to user2
+ assertUpdateCount(user2St, 0, "update user1.t4191 set x=0");
+ //but following update won't work because there are no select
+ //privileges available to user2 on user1.t4191(x)
+ assertStatementError("42502", user2St, "update user1.t4191 set x=" +
+ " ( select max(x) + 2 from user1.t4191 )");
+ user1St.execute("grant select(y) on t4191 to user2");
+ //following update still won't work because the select is granted on
+ //user1.t4191(y) and not user1.t4191(x)
+ assertStatementError("42502", user2St, "update user1.t4191 set x=" +
+ " ( select max(x) + 2 from user1.t4191 )");
+ //following queries will still work because there is still a
+ //select privilege on user1.t4191 available to user2
+ rs = user2St.executeQuery("select count(*) from user1.t4191");
+ JDBC.assertFullResultSet(rs, expRS, true);
+ rs = user2St.executeQuery("select count(1) from user1.t4191");
+ JDBC.assertFullResultSet(rs, expRS, true);
+ rs = user2St.executeQuery("select 1 from user1.t4191");
+ JDBC.assertEmpty(rs);
+ rs = user2St.executeQuery("select count(y) from user1.t4191");
+ JDBC.assertFullResultSet(rs, expRS, true);
+ //grant select privilege on user1.t4191(x) back to user2 so following
+ //update can succeed
+ user1St.execute("grant select(x) on t4191 to user2");
+ assertUpdateCount(user2St, 0, "update user1.t4191 set x=" +
+ " ( select max(x) + 2 from user1.t4191 )");
+
+ user1St.execute("drop table t4191");
+ user1.close();
+ user2.close();
+}
+
+
+ /**
* DERBY-3266
*/
public void testGlobalTempTables() throws SQLException {
Modified: db/derby/code/branches/10.5/java/testing/org/apache/derbyTesting/functionTests/tests/lang/RolesConferredPrivilegesTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.5/java/testing/org/apache/derbyTesting/functionTests/tests/lang/RolesConferredPrivilegesTest.java?rev=958230&r1=958229&r2=958230&view=diff
==============================================================================
--- db/derby/code/branches/10.5/java/testing/org/apache/derbyTesting/functionTests/tests/lang/RolesConferredPrivilegesTest.java (original)
+++ db/derby/code/branches/10.5/java/testing/org/apache/derbyTesting/functionTests/tests/lang/RolesConferredPrivilegesTest.java Sat Jun 26 14:16:04 2010
@@ -63,7 +63,7 @@ public class RolesConferredPrivilegesTes
private final static String TRIGGERDROPPED = "01502";
private final static String UNRELIABLE = "42Y39";
- private final static String[] users = {"test_dbo", "DonaldDuck"};
+ private final static String[] users = {"test_dbo", "DonaldDuck", "MickeyMouse"};
/**
* Create a new instance of RolesConferredPrivilegesTest.
@@ -255,7 +255,7 @@ public class RolesConferredPrivilegesTes
*
* @throws SQLException
*/
- public void testConferredPrivileges() throws SQLException
+ public void atestConferredPrivileges() throws SQLException
{
Connection dboConn = getConnection();
Statement s = dboConn.createStatement();
@@ -1086,6 +1086,104 @@ public class RolesConferredPrivilegesTes
dboConn.close();
}
+ /**
+ * DERBY-4191
+ * There are times when no column is selected from a table in the from
+ * list. At such a time, we should make sure that we make sure there
+ * is atleast some kind of select privilege available on that table for
+ * the query to succeed. eg of such queries
+ * select count(*) from t1
+ * select count(1) from t1
+ * select 1 from t1
+ * select t1.c1 from t1, t2
+ *
+ * In addition, the subquery inside of a NON-select query should require
+ * select privilege on the tables involved in the subquery eg
+ * update dbo.t set a = ( select max(a1) + 2 from dbo.t1 )
+ * update dbo.t set a = ( select max(b1) + 2 from dbo.t2 )
+ * For both the queries above, in addition to update privilege requirement
+ * on dbo.t(a), we need to require select privileges on columns/tables
+ * within the select list. So for first query, the user should have select
+ * privilege on dbo.t1 or dbo.t1(a1). Similarly, for 2nd query, the user
+ * should have select privilege on dbo.t2 or dbo.t2(b1)
+ * @throws SQLException
+ */
+ public void testMinimumSelectPrivilege() throws SQLException {
+ Connection dboConn = getConnection();
+ Statement stmtDBO = dboConn.createStatement();
+
+ Connection cDD = openUserConnection("DonaldDuck");
+ Statement stmtDD = cDD.createStatement();
+
+ Connection cMM = openUserConnection("MickeyMouse");
+ Statement stmtMM = cMM.createStatement();
+
+ stmtDBO.executeUpdate("create role role1");
+ stmtDBO.executeUpdate("grant role1 to MickeyMouse");
+
+ stmtDD.executeUpdate("create table DDtable1(c11 int, c12 int)");
+ stmtDD.executeUpdate("insert into DDtable1 values(1, 2)");
+ stmtDD.executeUpdate("create table DDtable2(c21 int, c22 int)");
+ stmtDD.executeUpdate("insert into DDtable2 values(3, 4)");
+
+ stmtMM.executeUpdate("set role role1");
+ try {
+ stmtMM.executeQuery("select c11 from DonaldDuck.DDtable1");
+ fail("select should have failed");
+ } catch (SQLException e) {
+ assertSQLState("42502", e);
+ }
+ try {
+ stmtMM.executeUpdate("update DonaldDuck.DDtable1 set c11 = " +
+ " (select c21 from DonaldDuck.DDtable2)");
+ fail("select should have failed");
+ } catch (SQLException e) {
+ assertSQLState("42502", e);
+ }
+
+ stmtDD.executeUpdate("grant select(c12) on DDtable1 to role1");
+ stmtDD.executeUpdate("grant update on DDtable1 to role1");
+ stmtMM.executeQuery("select c12 from DonaldDuck.DDtable1");
+ try {
+ stmtMM.executeQuery("select c11 from DonaldDuck.DDtable1");
+ fail("select should have failed");
+ } catch (SQLException e) {
+ assertSQLState("42502", e);
+ }
+ try {
+ stmtMM.executeUpdate("update DonaldDuck.DDtable1 set c11 = " +
+ " (select c21 from DonaldDuck.DDtable2)");
+ fail("select should have failed");
+ } catch (SQLException e) {
+ assertSQLState("42502", e);
+ }
+
+ stmtDD.executeUpdate("grant select(c11) on DDtable1 to role1");
+ stmtMM.executeQuery("select c12 from DonaldDuck.DDtable1");
+ stmtMM.executeQuery("select c11 from DonaldDuck.DDtable1");
+ try {
+ stmtMM.executeQuery("select c11 from DonaldDuck.DDtable1, " +
+ "DonaldDuck.DDtable2");
+ fail("select should have failed");
+ } catch (SQLException e) {
+ assertSQLState("42500", e);
+ }
+ try {
+ stmtMM.executeQuery("update DonaldDuck.DDtable1 set c11 = " +
+ " (select c21 from DonaldDuck.DDtable2)");
+ fail("select should have failed");
+ } catch (SQLException e) {
+ assertSQLState("42502", e);
+ }
+
+ stmtDD.executeUpdate("grant select(c21) on DDtable2 to role1");
+ stmtMM.executeQuery("select c12 from DonaldDuck.DDtable1");
+ stmtMM.executeQuery("select c11 from DonaldDuck.DDtable1");
+ stmtMM.executeQuery("select c11 from DonaldDuck.DDtable1, " +
+ "DonaldDuck.DDtable2");
+ stmtMM.executeUpdate("update DonaldDuck.DDtable1 set c11 = " +
+ " (select c21 from DonaldDuck.DDtable2)");
+ }
/**
* Test that a prepared statement can no longer execute after its required
@@ -2040,8 +2138,108 @@ public class RolesConferredPrivilegesTes
String schema,
String table,
String[] columns) throws SQLException {
- assertSelectPrivilege
- (hasPrivilege, c, schema, table, columns, NOCOLUMNPERMISSION);
+ assertSelectPrivilege(hasPrivilege, c, schema,
+ table, columns, NOCOLUMNPERMISSION);
+ assertSelectConstantPrivilege(hasPrivilege, c, schema,
+ table, NOTABLEPERMISSION);
+ assertSelectCountPrivilege(hasPrivilege, c, schema,
+ table, columns, NOTABLEPERMISSION);
+ }
+
+ /**
+ * Assert that a user has select privilege at the table(s) level or
+ * atleast on one column from each of the tables involved in the
+ * query when running a select query which selects count(*) or
+ * count(constant) from the tables.
+ *
+ * @param hasPrivilege whether or not the user has the privilege
+ * @param c connection to use
+ * @param schema the schema to check
+ * @param table the table to check
+ * @param columns used for error handling if ran into exception
+ * @param sqlState expected state if hasPrivilege == NOPRIV
+ * @throws SQLException throws all exceptions
+ */
+ private void assertSelectCountPrivilege(int hasPrivilege,
+ Connection c,
+ String schema,
+ String table,
+ String[] columns,
+ String sqlState) throws SQLException {
+ Statement s = c.createStatement();
+
+ try {
+ s.execute("select count(*) from " + schema + "." + table);
+
+ if (hasPrivilege == NOPRIV) {
+ fail("expected no SELECT privilege on table " +
+ formatArgs(c, schema, table, columns));
+ }
+ } catch (SQLException e) {
+ if (hasPrivilege == NOPRIV) {
+ assertSQLState(sqlState, e);
+ } else {
+ fail("Unexpected lack of select privilege. " +
+ formatArgs(c, schema, table, columns), e);
+ }
+ }
+
+ try {
+ s.execute("select count('a') from " + schema + "." + table);
+
+ if (hasPrivilege == NOPRIV) {
+ fail("expected no SELECT privilege on table " +
+ formatArgs(c, schema, table, columns));
+ }
+ } catch (SQLException e) {
+ if (hasPrivilege == NOPRIV) {
+ assertSQLState(sqlState, e);
+ } else {
+ fail("Unexpected lack of select privilege. " +
+ formatArgs(c, schema, table, columns), e);
+ }
+ }
+
+ s.close();
+ }
+
+ /**
+ * Assert that a user has select privilege at the table(s) level or
+ * atleast on one column from each of the tables involved in the
+ * query when running a select query which only selects constants from
+ * the tables.
+ *
+ * @param hasPrivilege whether or not the user has the privilege
+ * @param c connection to use
+ * @param schema the schema to check
+ * @param table the table to check
+ * @param sqlState expected state if hasPrivilege == NOPRIV
+ * @throws SQLException throws all exceptions
+ */
+ private void assertSelectConstantPrivilege(int hasPrivilege,
+ Connection c,
+ String schema,
+ String table,
+ String sqlState) throws SQLException {
+ Statement s = c.createStatement();
+
+ try {
+ s.execute("select 1 from " + schema + "." + table);
+
+ if (hasPrivilege == NOPRIV) {
+ fail("expected no SELECT privilege on table " +
+ formatArgs(c, schema, table));
+ }
+ } catch (SQLException e) {
+ if (hasPrivilege == NOPRIV) {
+ assertSQLState(sqlState, e);
+ } else {
+ fail("Unexpected lack of select privilege. " +
+ formatArgs(c, schema, table), e);
+ }
+ }
+
+ s.close();
}