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 da...@apache.org on 2011/07/04 21:22:53 UTC
svn commit: r1142773 - in /db/derby/code/branches/10.8: ./
java/engine/org/apache/derby/impl/sql/compile/
java/testing/org/apache/derbyTesting/functionTests/tests/lang/
Author: dag
Date: Mon Jul 4 19:22:52 2011
New Revision: 1142773
URL: http://svn.apache.org/viewvc?rev=1142773&view=rev
Log:
DERBY-5292 SQLAuthorisation and views
Backported from trunk as
svn merge -c 1142635 https://svn.apache.org/repos/asf/db/derby/code/trunk
Patch derby5292d: For views, the premissions collection is disabled
from tables in the query from-list since it should run with definer's
rights. However, this disabling did not work for all cases. The way to
reach all the "from" tables has been changed to use a node visitor
instead, so as to be able to also reach tables inside subqueries and
inside explicit JOIN and set operations, cf the repro issues. An added
test case was added to GrantRevokeTest: testViewDefinersRights.
Modified:
db/derby/code/branches/10.8/ (props changed)
db/derby/code/branches/10.8/java/engine/org/apache/derby/impl/sql/compile/FromBaseTable.java
db/derby/code/branches/10.8/java/engine/org/apache/derby/impl/sql/compile/FromSubquery.java
db/derby/code/branches/10.8/java/engine/org/apache/derby/impl/sql/compile/JavaToSQLValueNode.java
db/derby/code/branches/10.8/java/engine/org/apache/derby/impl/sql/compile/QueryTreeNode.java
db/derby/code/branches/10.8/java/engine/org/apache/derby/impl/sql/compile/ResultColumn.java
db/derby/code/branches/10.8/java/engine/org/apache/derby/impl/sql/compile/ResultColumnList.java
db/derby/code/branches/10.8/java/engine/org/apache/derby/impl/sql/compile/ResultSetNode.java
db/derby/code/branches/10.8/java/engine/org/apache/derby/impl/sql/compile/SelectNode.java
db/derby/code/branches/10.8/java/testing/org/apache/derbyTesting/functionTests/tests/lang/GrantRevokeTest.java
Propchange: db/derby/code/branches/10.8/
------------------------------------------------------------------------------
--- svn:mergeinfo (original)
+++ svn:mergeinfo Mon Jul 4 19:22:52 2011
@@ -1,2 +1,2 @@
/db/derby/code/branches/10.7:1061570,1061578,1082235
-/db/derby/code/trunk:1063809,1088633,1091000,1091221,1091285,1092067,1092795,1094315,1094572,1094728,1096741,1096890,1097247,1097249,1097460,1097469,1097471,1101059,1101839,1102620,1102826,1103681,1103718,1103742,1125305,1126358,1126468,1127825,1127883,1129136,1129764,1129797,1130077,1130084,1130632,1130895,1131030,1131272,1132546,1132664,1132860,1132928,1133304,1133317,1133741,1133752,1136371,1136397,1136844,1138201,1138444,1138787,1138795,1139449,1139451,1141924
+/db/derby/code/trunk:1063809,1088633,1091000,1091221,1091285,1092067,1092795,1094315,1094572,1094728,1096741,1096890,1097247,1097249,1097460,1097469,1097471,1101059,1101839,1102620,1102826,1103681,1103718,1103742,1125305,1126358,1126468,1127825,1127883,1129136,1129764,1129797,1130077,1130084,1130632,1130895,1131030,1131272,1132546,1132664,1132860,1132928,1133304,1133317,1133741,1133752,1136371,1136397,1136844,1138201,1138444,1138787,1138795,1139449,1139451,1141924,1142635
Modified: db/derby/code/branches/10.8/java/engine/org/apache/derby/impl/sql/compile/FromBaseTable.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.8/java/engine/org/apache/derby/impl/sql/compile/FromBaseTable.java?rev=1142773&r1=1142772&r2=1142773&view=diff
==============================================================================
--- db/derby/code/branches/10.8/java/engine/org/apache/derby/impl/sql/compile/FromBaseTable.java (original)
+++ db/derby/code/branches/10.8/java/engine/org/apache/derby/impl/sql/compile/FromBaseTable.java Mon Jul 4 19:22:52 2011
@@ -75,14 +75,12 @@ import org.apache.derby.iapi.store.acces
import org.apache.derby.iapi.types.DataValueDescriptor;
-import org.apache.derby.impl.sql.compile.ExpressionClassBuilder;
-import org.apache.derby.impl.sql.compile.ActivationClassBuilder;
-import org.apache.derby.impl.sql.compile.FromSubquery;
import java.util.Enumeration;
import java.util.Properties;
import java.util.Vector;
import java.util.HashSet;
+import java.util.Iterator;
import java.util.Set;
/**
@@ -2304,7 +2302,15 @@ public class FromBaseTable extends FromT
//Following call is marking the query to run with definer
//privileges. This marking will make sure that we do not collect
//any privilege requirement for it.
- fsq.disablePrivilegeCollection();
+ CollectNodesVisitor cnv =
+ new CollectNodesVisitor(QueryTreeNode.class, null);
+
+ fsq.accept(cnv);
+
+ for (Iterator it = cnv.getList().iterator(); it.hasNext(); ) {
+ ((QueryTreeNode)it.next()).disablePrivilegeCollection();
+ }
+
fsq.setOrigTableName(this.getOrigTableName());
// since we reset the compilation schema when we return, we
Modified: db/derby/code/branches/10.8/java/engine/org/apache/derby/impl/sql/compile/FromSubquery.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.8/java/engine/org/apache/derby/impl/sql/compile/FromSubquery.java?rev=1142773&r1=1142772&r2=1142773&view=diff
==============================================================================
--- db/derby/code/branches/10.8/java/engine/org/apache/derby/impl/sql/compile/FromSubquery.java (original)
+++ db/derby/code/branches/10.8/java/engine/org/apache/derby/impl/sql/compile/FromSubquery.java Mon Jul 4 19:22:52 2011
@@ -29,6 +29,7 @@ import org.apache.derby.iapi.sql.diction
import org.apache.derby.iapi.sql.dictionary.SchemaDescriptor;
import org.apache.derby.iapi.services.sanity.SanityManager;
+import org.apache.derby.iapi.sql.compile.Visitor;
import org.apache.derby.iapi.util.JBitSet;
@@ -658,15 +659,6 @@ public class FromSubquery extends FromTa
return rcList;
}
- /**
- * @see QueryTreeNode#disablePrivilegeCollection
- */
- public void disablePrivilegeCollection()
- {
- super.disablePrivilegeCollection();
- subquery.disablePrivilegeCollection();
- }
-
/**
* Search to see if a query references the specifed table name.
*
@@ -730,4 +722,27 @@ public class FromSubquery extends FromTa
public void setOrigCompilationSchema(SchemaDescriptor sd) {
origCompilationSchema = sd;
}
+
+ /**
+ * @see QueryTreeNode#acceptChildren
+ */
+ void acceptChildren(Visitor v)
+ throws StandardException
+ {
+ super.acceptChildren(v);
+
+ subquery.accept(v);
+
+ if (orderByList != null) {
+ orderByList.accept(v);
+ }
+
+ if (offset != null) {
+ offset.accept(v);
+ }
+
+ if (fetchFirst != null) {
+ fetchFirst.accept(v);
+ }
+ }
}
Modified: db/derby/code/branches/10.8/java/engine/org/apache/derby/impl/sql/compile/JavaToSQLValueNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.8/java/engine/org/apache/derby/impl/sql/compile/JavaToSQLValueNode.java?rev=1142773&r1=1142772&r2=1142773&view=diff
==============================================================================
--- db/derby/code/branches/10.8/java/engine/org/apache/derby/impl/sql/compile/JavaToSQLValueNode.java (original)
+++ db/derby/code/branches/10.8/java/engine/org/apache/derby/impl/sql/compile/JavaToSQLValueNode.java Mon Jul 4 19:22:52 2011
@@ -210,16 +210,6 @@ public class JavaToSQLValueNode extends
return javaNode;
}
- /**
- * @see QueryTreeNode#disablePrivilegeCollection
- */
- public void disablePrivilegeCollection()
- {
- super.disablePrivilegeCollection();
- if (javaNode != null)
- javaNode.disablePrivilegeCollection();
- }
-
/**
* Bind this expression. This means binding the sub-expressions,
* as well as figuring out what the return type is for this expression.
Modified: db/derby/code/branches/10.8/java/engine/org/apache/derby/impl/sql/compile/QueryTreeNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.8/java/engine/org/apache/derby/impl/sql/compile/QueryTreeNode.java?rev=1142773&r1=1142772&r2=1142773&view=diff
==============================================================================
--- db/derby/code/branches/10.8/java/engine/org/apache/derby/impl/sql/compile/QueryTreeNode.java (original)
+++ db/derby/code/branches/10.8/java/engine/org/apache/derby/impl/sql/compile/QueryTreeNode.java Mon Jul 4 19:22:52 2011
@@ -547,16 +547,16 @@ public abstract class QueryTreeNode impl
}
/**
- * Triggers, constraints and views get executed with their definer's
- * privileges and they can exist in the system only if their definers'
- * still have all the privileges to creeate them. Based on this, any
+ * Triggers, constraints and views get executed with their definers'
+ * privileges and they can exist in the system only if their definers
+ * still have all the privileges to create them. Based on this, any
* time a trigger/view/constraint is executing, we do not need to waste
* time in checking if the definer still has the right set of privileges.
- * At compile time, we wil make sure that we do not collect the privilege
+ * At compile time, we will make sure that we do not collect the privilege
* requirement for objects accessed with definer privileges by calling the
* following method.
*/
- public void disablePrivilegeCollection()
+ final void disablePrivilegeCollection()
{
isPrivilegeCollectionRequired = false;
}
Modified: db/derby/code/branches/10.8/java/engine/org/apache/derby/impl/sql/compile/ResultColumn.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.8/java/engine/org/apache/derby/impl/sql/compile/ResultColumn.java?rev=1142773&r1=1142772&r2=1142773&view=diff
==============================================================================
--- db/derby/code/branches/10.8/java/engine/org/apache/derby/impl/sql/compile/ResultColumn.java (original)
+++ db/derby/code/branches/10.8/java/engine/org/apache/derby/impl/sql/compile/ResultColumn.java Mon Jul 4 19:22:52 2011
@@ -1361,16 +1361,6 @@ public class ResultColumn extends ValueN
return updatableByCursor;
}
- /**
- * @see QueryTreeNode#disablePrivilegeCollection
- */
- public void disablePrivilegeCollection()
- {
- super.disablePrivilegeCollection();
- if (expression != null)
- expression.disablePrivilegeCollection();
- }
-
/**
* Make a copy of this ResultColumn in a new ResultColumn
*
Modified: db/derby/code/branches/10.8/java/engine/org/apache/derby/impl/sql/compile/ResultColumnList.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.8/java/engine/org/apache/derby/impl/sql/compile/ResultColumnList.java?rev=1142773&r1=1142772&r2=1142773&view=diff
==============================================================================
--- db/derby/code/branches/10.8/java/engine/org/apache/derby/impl/sql/compile/ResultColumnList.java (original)
+++ db/derby/code/branches/10.8/java/engine/org/apache/derby/impl/sql/compile/ResultColumnList.java Mon Jul 4 19:22:52 2011
@@ -2778,18 +2778,6 @@ public class ResultColumnList extends Qu
((ResultColumn) elementAt(index)).markUpdatableByCursor();
}
}
-
- /**
- * @see QueryTreeNode#disablePrivilegeCollection
- */
- public void disablePrivilegeCollection()
- {
- super.disablePrivilegeCollection();
-
- int size = size();
- for (int index = 0; index < size; index++)
- ((ResultColumn) elementAt(index)).disablePrivilegeCollection();
- }
/**
* Verify that all of the column names in this list are contained
Modified: db/derby/code/branches/10.8/java/engine/org/apache/derby/impl/sql/compile/ResultSetNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.8/java/engine/org/apache/derby/impl/sql/compile/ResultSetNode.java?rev=1142773&r1=1142772&r2=1142773&view=diff
==============================================================================
--- db/derby/code/branches/10.8/java/engine/org/apache/derby/impl/sql/compile/ResultSetNode.java (original)
+++ db/derby/code/branches/10.8/java/engine/org/apache/derby/impl/sql/compile/ResultSetNode.java Mon Jul 4 19:22:52 2011
@@ -479,16 +479,6 @@ public abstract class ResultSetNode exte
getContextManager());
}
- /**
- * @see QueryTreeNode#disablePrivilegeCollection
- */
- public void disablePrivilegeCollection()
- {
- super.disablePrivilegeCollection();
- if (resultColumns != null)
- resultColumns.disablePrivilegeCollection();
- }
-
/**
* Bind the result columns of this ResultSetNode when there is no
* base table to bind them to. This is useful for SELECT statements,
Modified: db/derby/code/branches/10.8/java/engine/org/apache/derby/impl/sql/compile/SelectNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.8/java/engine/org/apache/derby/impl/sql/compile/SelectNode.java?rev=1142773&r1=1142772&r2=1142773&view=diff
==============================================================================
--- db/derby/code/branches/10.8/java/engine/org/apache/derby/impl/sql/compile/SelectNode.java (original)
+++ db/derby/code/branches/10.8/java/engine/org/apache/derby/impl/sql/compile/SelectNode.java Mon Jul 4 19:22:52 2011
@@ -24,7 +24,6 @@ package org.apache.derby.impl.sql.compil
import org.apache.derby.iapi.sql.compile.CostEstimate;
import org.apache.derby.iapi.sql.compile.Optimizer;
-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;
@@ -34,8 +33,6 @@ import org.apache.derby.iapi.sql.compile
import org.apache.derby.iapi.sql.dictionary.DataDictionary;
import org.apache.derby.iapi.sql.dictionary.TableDescriptor;
-import org.apache.derby.iapi.types.TypeId;
-import org.apache.derby.iapi.types.DataTypeDescriptor;
import org.apache.derby.iapi.reference.Limits;
import org.apache.derby.iapi.reference.SQLState;
@@ -2262,17 +2259,6 @@ public class SelectNode extends ResultSe
return false;
}
- /**
- * @see QueryTreeNode#disablePrivilegeCollection
- */
- public void disablePrivilegeCollection()
- {
- super.disablePrivilegeCollection();
- int fromListSize = fromList.size();
- for( int i = 0; i < fromListSize; i++)
- ((FromTable) fromList.elementAt(i)).disablePrivilegeCollection();
- }
-
/**
* Return whether or not this ResultSetNode contains a subquery with a
* reference to the specified target table.
Modified: db/derby/code/branches/10.8/java/testing/org/apache/derbyTesting/functionTests/tests/lang/GrantRevokeTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.8/java/testing/org/apache/derbyTesting/functionTests/tests/lang/GrantRevokeTest.java?rev=1142773&r1=1142772&r2=1142773&view=diff
==============================================================================
--- db/derby/code/branches/10.8/java/testing/org/apache/derbyTesting/functionTests/tests/lang/GrantRevokeTest.java (original)
+++ db/derby/code/branches/10.8/java/testing/org/apache/derbyTesting/functionTests/tests/lang/GrantRevokeTest.java Mon Jul 4 19:22:52 2011
@@ -133,6 +133,50 @@ public class GrantRevokeTest extends Bas
" language java parameter style java" +
" external name 'org.apache.derbyTesting.functionTests.tests.lang.GrantRevokeTest.s1P1'" +
" no sql called on null input");
+
+
+ // DERBY-5292: Definer's rights in views
+ s.execute("create schema appl");
+ s.execute(
+ "CREATE TABLE appl.\"TBL_Tasks\"" +
+ "(\"TaskID\" integer NOT NULL " +
+ " GENERATED ALWAYS AS IDENTITY," +
+ " \"Task\" varchar(64) NOT NULL," +
+ " \"AssignedTo\" varchar(64) NOT NULL," +
+ "CONSTRAINT \"PK_Tasks\" PRIMARY KEY (\"TaskID\"))");
+ s.execute(
+ "CREATE TABLE appl.\"TBL_Priorities\"" +
+ "(\"TaskID\" integer NOT NULL," +
+ " \"Priority\" integer NOT NULL," +
+ " \"SeqNbr\" integer NOT NULL," +
+ "CONSTRAINT \"PK_Priorities\" PRIMARY KEY " +
+ " (\"TaskID\", \"Priority\"))");
+ s.execute(
+ "CREATE VIEW appl.\"VW_MyTasks\" AS " +
+ " SELECT * FROM appl.\"TBL_Tasks\" " +
+ "WHERE \"AssignedTo\" = SESSION_USER");
+ s.execute(
+ "CREATE VIEW appl.\"VW_MyPriorityTasks\" AS " +
+ " SELECT t.\"TaskID\", t.\"Task\", p.\"Priority\"" +
+ " FROM appl.\"TBL_Tasks\" AS t," +
+ " appl.\"TBL_Priorities\" AS p " +
+ " WHERE p.\"TaskID\" = t.\"TaskID\" " +
+ " AND t.\"AssignedTo\" = SESSION_USER");
+ s.execute(
+ "CREATE VIEW appl.\"VW2_MyPriorityTasks\" AS " +
+ " SELECT t.\"TaskID\", t.\"Task\", p.\"Priority\" " +
+ " FROM appl.\"TBL_Tasks\" AS t INNER JOIN " +
+ " appl.\"TBL_Priorities\" AS p ON " +
+ " p.\"TaskID\" = t.\"TaskID\" " +
+ " WHERE t.\"AssignedTo\" = SESSION_USER");
+ s.execute(
+ "CREATE VIEW appl.\"VW3_MyPriorityTasks\" AS " +
+ " SELECT t.\"TaskID\", t.\"Task\" " +
+ " FROM appl.\"TBL_Tasks\" AS t " +
+ " WHERE t.\"AssignedTo\" = SESSION_USER " +
+ " AND EXISTS " +
+ " (SELECT * FROM appl.\"TBL_Priorities\" AS p " +
+ " WHERE p.\"TaskID\" = t.\"TaskID\")");
}
};
Test test = DatabasePropertyTestSetup.builtinAuthentication(
@@ -749,7 +793,30 @@ public class GrantRevokeTest extends Bas
assertAllPrivileges(false, users[2], "S1", "T1", null);
}
-
+
+ /**
+ * DERBY-5292
+ */
+ public void testViewDefinersRights () throws Exception {
+
+ grant("select", "appl", "\"VW_MyTasks\"", users[1]);
+ grant("select", "appl", "\"VW_MyPriorityTasks\"", users[1]);
+ grant("select", "appl", "\"VW2_MyPriorityTasks\"", users[1]);
+ grant("select", "appl", "\"VW3_MyPriorityTasks\"", users[1]);
+
+ // OK before fix
+ assertSelectPrivilege(
+ true, users[1], "appl", "\"VW_MyTasks\"", null);
+ assertSelectPrivilege(
+ true, users[1], "appl", "\"VW_MyPriorityTasks\"", null);
+
+ // Failed before fix
+ assertSelectPrivilege(
+ true, users[1], "appl", "\"VW2_MyPriorityTasks\"", null);
+ assertSelectPrivilege(
+ true, users[1], "appl", "\"VW3_MyPriorityTasks\"", null);
+ }
+
/* End testcases from grantRevokeDDL */
/* Begin utility methods specific to grant / revoke */
@@ -1337,9 +1404,10 @@ public class GrantRevokeTest extends Bas
Connection c = openUserConnection(user);
DatabaseMetaData dm = c.getMetaData();
- ResultSet rs = dm.getTablePrivileges(null, schema.toUpperCase(), table.toUpperCase());
+ schema = JDBC.identifierToCNF(schema);
+ table = JDBC.identifierToCNF(table);
+ ResultSet rs = dm.getTablePrivileges(null, schema, table);
boolean found = false;
-
// check getTablePrivileges
if (columns == null) {
while (rs.next())