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 dj...@apache.org on 2006/09/07 18:16:16 UTC
svn commit: r441131 - in /db/derby/code/trunk/java:
engine/org/apache/derby/impl/sql/catalog/
engine/org/apache/derby/impl/sql/compile/
engine/org/apache/derby/impl/sql/execute/ engine/org/apache/derby/loc/
shared/org/apache/derby/shared/common/referen...
Author: djd
Date: Thu Sep 7 09:16:15 2006
New Revision: 441131
URL: http://svn.apache.org/viewvc?view=rev&rev=441131
Log:
DERBY-1686 The original fix retrieves all the relevent providers such as table, view, and routine descriptor(s)
of the given view at bind time and pass this information to runtime later
for it to determine if the privilege is grantable or not by the grantor.
Patch contributed by Yip Ng - yipng168@gmail.com
Modified:
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/compile/GrantNode.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/RevokeNode.java
db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/TablePrivilegesNode.java
db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/TablePrivilegeInfo.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/impl/sql/catalog/DataDictionaryImpl.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/catalog/DataDictionaryImpl.java?view=diff&rev=441131&r1=441130&r2=441131
==============================================================================
--- 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 Sep 7 09:16:15 2006
@@ -9968,7 +9968,7 @@
throws StandardException
{
TablePermsDescriptor key = new TablePermsDescriptor( this, tablePermsUUID);
- return (TablePermsDescriptor) getPermissions( key);
+ return getUncachedTablePermsDescriptor( key );
}
private Object getPermissions( PermissionsDescriptor key) throws StandardException
@@ -9987,7 +9987,7 @@
throws StandardException
{
ColPermsDescriptor key = new ColPermsDescriptor( this, colPermsUUID);
- return (ColPermsDescriptor) getPermissions( key);
+ return getUncachedColPermsDescriptor( key );
}
/**
@@ -10092,7 +10092,7 @@
throws StandardException
{
RoutinePermsDescriptor key = new RoutinePermsDescriptor( this, routinePermsUUID);
- return (RoutinePermsDescriptor) getPermissions( key);
+ return getUncachedRoutinePermsDescriptor( key );
}
/**
Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/GrantNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/GrantNode.java?view=diff&rev=441131&r1=441130&r2=441131
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/GrantNode.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/GrantNode.java Thu Sep 7 09:16:15 2006
@@ -94,7 +94,7 @@
*/
public QueryTreeNode bind() throws StandardException
{
- privileges = (PrivilegeNode) privileges.bind( new HashMap(), grantees);
+ privileges = (PrivilegeNode) privileges.bind( new HashMap(), grantees, true);
return this;
} // end of bind
Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/PrivilegeNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/PrivilegeNode.java?view=diff&rev=441131&r1=441130&r2=441131
==============================================================================
--- 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 Sep 7 09:16:15 2006
@@ -98,12 +98,12 @@
* If the object of this privilege is not in the list then this statement is registered
* as dependent on the object.
* @param grantees The list of grantees
- *
+ * @param isGrant grant if true; revoke if false
* @return the bound node
*
* @exception StandardException Standard error policy.
*/
- public QueryTreeNode bind( HashMap dependencies, List grantees ) throws StandardException
+ public QueryTreeNode bind( HashMap dependencies, List grantees, boolean isGrant ) throws StandardException
{
Provider dependencyProvider = null;
SchemaDescriptor sd = null;
@@ -134,7 +134,7 @@
throw StandardException.newException(SQLState.AUTH_GRANT_REVOKE_NOT_ALLOWED,
td.getQualifiedName());
- specificPrivileges.bind( td);
+ specificPrivileges.bind( td, isGrant);
dependencyProvider = td;
break;
Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/RevokeNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/RevokeNode.java?view=diff&rev=441131&r1=441130&r2=441131
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/RevokeNode.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/RevokeNode.java Thu Sep 7 09:16:15 2006
@@ -94,7 +94,7 @@
*/
public QueryTreeNode bind() throws StandardException
{
- privileges = (PrivilegeNode) privileges.bind( new HashMap(), grantees);
+ privileges = (PrivilegeNode) privileges.bind( new HashMap(), grantees, false);
return this;
} // end of bind
Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/TablePrivilegesNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/TablePrivilegesNode.java?view=diff&rev=441131&r1=441130&r2=441131
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/TablePrivilegesNode.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/TablePrivilegesNode.java Thu Sep 7 09:16:15 2006
@@ -29,6 +29,21 @@
import org.apache.derby.iapi.services.io.FormatableBitSet;
import org.apache.derby.iapi.sql.dictionary.TableDescriptor;
+import org.apache.derby.iapi.sql.depend.DependencyManager;
+import org.apache.derby.iapi.sql.depend.Provider;
+import org.apache.derby.iapi.sql.depend.ProviderInfo;
+import org.apache.derby.iapi.sql.depend.ProviderList;
+import org.apache.derby.iapi.sql.conn.ConnectionUtil;
+import org.apache.derby.iapi.sql.conn.LanguageConnectionContext;
+import org.apache.derby.iapi.sql.dictionary.AliasDescriptor;
+import org.apache.derby.iapi.sql.dictionary.DataDictionary;
+import org.apache.derby.iapi.sql.dictionary.SchemaDescriptor;
+import org.apache.derby.iapi.sql.dictionary.TupleDescriptor;
+import org.apache.derby.iapi.sql.dictionary.ViewDescriptor;
+
+import java.util.ArrayList;
+import java.util.List;
+
/**
* This class represents a set of privileges on one table.
*/
@@ -37,8 +52,9 @@
private boolean[] actionAllowed = new boolean[ TablePrivilegeInfo.ACTION_COUNT];
private ResultColumnList[] columnLists = new ResultColumnList[ TablePrivilegeInfo.ACTION_COUNT];
private FormatableBitSet[] columnBitSets = new FormatableBitSet[ TablePrivilegeInfo.ACTION_COUNT];
- private TableDescriptor td;
-
+ private TableDescriptor td;
+ private List descriptorList;
+
/**
* Add all actions
*/
@@ -74,11 +90,12 @@
* Bind.
*
* @param td The table descriptor
+ * @param isGrant grant if true; revoke if false
*/
- public void bind( TableDescriptor td) throws StandardException
+ public void bind( TableDescriptor td, boolean isGrant) throws StandardException
{
this.td = td;
-
+
for( int action = 0; action < TablePrivilegeInfo.ACTION_COUNT; action++)
{
if( columnLists[ action] != null)
@@ -90,6 +107,11 @@
throw StandardException.newException(SQLState.AUTH_GRANT_REVOKE_NOT_ALLOWED,
td.getQualifiedName());
}
+
+ if (isGrant && td.getTableType() == TableDescriptor.VIEW_TYPE)
+ {
+ bindPrivilegesForView(td);
+ }
}
/**
@@ -97,6 +119,61 @@
*/
public PrivilegeInfo makePrivilegeInfo()
{
- return new TablePrivilegeInfo( td, actionAllowed, columnBitSets);
+ return new TablePrivilegeInfo( td, actionAllowed, columnBitSets,
+ descriptorList);
}
+
+ /**
+ * Retrieve all the underlying stored dependencies such as table(s),
+ * view(s) and routine(s) descriptors which the view depends on.
+ * This information is then passed to the runtime to determine if
+ * the privilege is grantable to the grantees by this grantor at
+ * execution time.
+ *
+ * Go through the providers regardless who the grantor is since
+ * the statement cache may be in effect.
+ *
+ * @param td the TableDescriptor to check
+ *
+ * @exception StandardException standard error policy.
+ */
+ private void bindPrivilegesForView ( TableDescriptor td)
+ throws StandardException
+ {
+ LanguageConnectionContext lcc = getLanguageConnectionContext();
+ DataDictionary dd = lcc.getDataDictionary();
+ ViewDescriptor vd = dd.getViewDescriptor(td);
+ DependencyManager dm = dd.getDependencyManager();
+ ProviderInfo[] pis = dm.getPersistentProviderInfos(vd);
+ this.descriptorList = new ArrayList();
+
+ int siz = pis.length;
+ for (int i=0; i < siz; i++)
+ {
+ try
+ {
+ Provider provider = (Provider) pis[i].getDependableFinder().getDependable(pis[i].getObjectId());
+ if (provider == null)
+ {
+ throw StandardException.newException(
+ SQLState.LANG_OBJECT_NOT_FOUND,
+ "OBJECT",
+ pis[i].getObjectId());
+ }
+
+ if (provider instanceof TableDescriptor ||
+ provider instanceof ViewDescriptor ||
+ provider instanceof AliasDescriptor)
+ {
+ descriptorList.add(provider);
+ }
+ }
+ catch(java.sql.SQLException ex)
+ {
+ throw StandardException.plainWrapException(ex);
+ }
+ }
+ }
+
}
+
Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/TablePrivilegeInfo.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/TablePrivilegeInfo.java?view=diff&rev=441131&r1=441130&r2=441131
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/TablePrivilegeInfo.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/TablePrivilegeInfo.java Thu Sep 7 09:16:15 2006
@@ -28,12 +28,17 @@
import org.apache.derby.iapi.sql.conn.LanguageConnectionContext;
import org.apache.derby.iapi.store.access.TransactionController;
import org.apache.derby.iapi.sql.depend.DependencyManager;
+import org.apache.derby.iapi.sql.dictionary.AliasDescriptor;
import org.apache.derby.iapi.sql.dictionary.PermissionsDescriptor;
+import org.apache.derby.iapi.sql.dictionary.SchemaDescriptor;
import org.apache.derby.iapi.sql.dictionary.TablePermsDescriptor;
import org.apache.derby.iapi.sql.dictionary.ColPermsDescriptor;
import org.apache.derby.iapi.sql.dictionary.TableDescriptor;
+import org.apache.derby.iapi.sql.dictionary.ViewDescriptor;
import org.apache.derby.iapi.sql.dictionary.DataDictionary;
import org.apache.derby.iapi.sql.dictionary.DataDescriptorGenerator;
+import org.apache.derby.iapi.sql.dictionary.TupleDescriptor;
+import org.apache.derby.iapi.reference.SQLState;
import java.util.List;
import java.util.Iterator;
@@ -59,17 +64,123 @@
private TableDescriptor td;
private boolean[] actionAllowed;
private FormatableBitSet[] columnBitSets;
+ private List descriptorList;
/**
* @param actionAllowed actionAllowed[action] is true if action is in the privilege set.
*/
public TablePrivilegeInfo( TableDescriptor td,
boolean[] actionAllowed,
- FormatableBitSet[] columnBitSets)
+ FormatableBitSet[] columnBitSets,
+ List descriptorList)
{
this.actionAllowed = actionAllowed;
this.columnBitSets = columnBitSets;
this.td = td;
+ this.descriptorList = descriptorList;
+ }
+
+ /**
+ * Determines whether a user is the owner of an object
+ * (table, function, or procedure). Note that the database
+ * creator can access database objects without needing to be
+ * their owner.
+ *
+ * @param user authorizationId of current user
+ * @param td table descriptor being checked against
+ * @param sd SchemaDescriptor
+ * @param dd DataDictionary
+ * @param lcc LanguageConnectionContext
+ * @param grant grant if true; revoke if false
+ *
+ * @exception StandardException if user does not own the object
+ */
+ protected void checkOwnership( String user,
+ TableDescriptor td,
+ SchemaDescriptor sd,
+ DataDictionary dd,
+ LanguageConnectionContext lcc,
+ boolean grant)
+ throws StandardException
+ {
+ super.checkOwnership(user, td, sd, dd);
+
+ // additional check specific to this subclass
+ if (grant)
+ {
+ checkPrivileges(user, td, sd, dd, lcc);
+ }
+ }
+
+ /**
+ * Determines if the privilege is grantable by this grantor
+ * for the given view.
+ *
+ * Note that the database owner can access database objects
+ * without needing to be their owner. This method should only
+ * be called if it is a GRANT.
+ *
+ * @param user authorizationId of current user
+ * @param td TableDescriptor to be checked against
+ * @param sd SchemaDescriptor
+ * @param dd DataDictionary
+ * @param lcc LanguageConnectionContext
+ *
+ * @exception StandardException if user does not have permission to grant
+ */
+ private void checkPrivileges( String user,
+ TableDescriptor td,
+ SchemaDescriptor sd,
+ DataDictionary dd,
+ LanguageConnectionContext lcc)
+ throws StandardException
+ {
+ if (user.equals(dd.getAuthorizationDBA())) return;
+
+ // check view specific
+ if (td.getTableType() == TableDescriptor.VIEW_TYPE)
+ {
+ if (descriptorList != null )
+ {
+ TransactionController tc = lcc.getTransactionExecute();
+ int siz = descriptorList.size();
+ for (int i=0; i < siz; i++)
+ {
+ TupleDescriptor p;
+ SchemaDescriptor s = null;
+
+ p = (TupleDescriptor)descriptorList.get(i);
+ if (p instanceof TableDescriptor)
+ {
+ TableDescriptor t = (TableDescriptor)p;
+ s = t.getSchemaDescriptor();
+ }
+ else if (p instanceof ViewDescriptor)
+ {
+ ViewDescriptor v = (ViewDescriptor)p;
+ s = dd.getSchemaDescriptor(v.getCompSchemaId(), tc);
+ }
+ else if (p instanceof AliasDescriptor)
+ {
+ AliasDescriptor a = (AliasDescriptor)p;
+ s = dd.getSchemaDescriptor( a.getSchemaUUID(), tc);
+ }
+
+ if (s != null && !user.equals(s.getAuthorizationId()) )
+ {
+ throw StandardException.newException(
+ SQLState.AUTH_NO_OBJECT_PERMISSION,
+ user,
+ "grant",
+ sd.getSchemaName(),
+ td.getName());
+ }
+
+ // FUTURE: if object is not own by grantor then check if
+ // the grantor have grant option.
+ }
+ }
+ }
}
/**
@@ -90,9 +201,10 @@
DataDictionary dd = lcc.getDataDictionary();
String currentUser = lcc.getAuthorizationId();
TransactionController tc = lcc.getTransactionExecute();
-
+ SchemaDescriptor sd = td.getSchemaDescriptor();
+
// Check that the current user has permission to grant the privileges.
- checkOwnership( currentUser, td, td.getSchemaDescriptor(), dd);
+ checkOwnership( currentUser, td, sd, dd, lcc, grant);
DataDescriptorGenerator ddg = dd.getDataDescriptorGenerator();
Modified: db/derby/code/trunk/java/engine/org/apache/derby/loc/messages_en.properties
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/loc/messages_en.properties?view=diff&rev=441131&r1=441130&r2=441131
==============================================================================
--- 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 Sep 7 09:16:15 2006
@@ -1166,6 +1166,7 @@
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=Specified grant or revoke operation is not allowed on object ''{0}''.
+2850G=User ''{0}'' does not have {1} permission on object ''{2}''.''{3}''.
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/viewvc/db/derby/code/trunk/java/shared/org/apache/derby/shared/common/reference/SQLState.java?view=diff&rev=441131&r1=441130&r2=441131
==============================================================================
--- 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 Sep 7 09:16:15 2006
@@ -1372,6 +1372,7 @@
String AUTH_NO_ACCESS_NOT_OWNER = "2850D";
String AUTH_NOT_DATABASE_OWNER = "2850E";
String AUTH_GRANT_REVOKE_NOT_ALLOWED = "2850F";
+ String AUTH_NO_OBJECT_PERMISSION = "2850G";
/*
** Dependency manager
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/grantRevokeDDL.out
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/grantRevokeDDL.out?view=diff&rev=441131&r1=441130&r2=441131
==============================================================================
--- 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 Sep 7 09:16:15 2006
@@ -705,31 +705,30 @@
ERROR: Failed with SQLSTATE 28508
ij(MAMTA3)> -- connect as mamta2 and give select privilege on v22 to mamta3
set connection mamta2;
-ij(MAMTA2)> grant select on v22 to mamta3;
-0 rows inserted/updated/deleted
+ij(MAMTA2)> -- should fail
+grant select on v22 to mamta3;
+ERROR: Failed with SQLSTATE 2850G
ij(MAMTA2)> set connection mamta3;
-ij(MAMTA3)> -- mamta3 has the required privileges now, so following should work
+ij(MAMTA3)> -- should fail
create view v31 as select * from mamta2.v22;
-0 rows inserted/updated/deleted
-ij(MAMTA3)> -- following will pass because mamta3 has direct access to v22 and public access to t11
+ERROR: Failed with SQLSTATE 28508
+ij(MAMTA3)> -- following will fail because mamta3 has no access to v22
create view v32 as select v22.c111 as a, t11.c111 as b from mamta2.v22 v22, mamta1.t11 t11;
-0 rows inserted/updated/deleted
+ERROR: Failed with SQLSTATE 28508
ij(MAMTA3)> -- following will still fail because mamta3 doesn't have access to mamta1.t12.c121
create view v33 as select v22.c111 as a, t12.c121 as b from mamta2.v22 v22, mamta1.t12 t12;
ERROR: Failed with SQLSTATE 28508
ij(MAMTA3)> -- connect as mamta2 and give select privilege on v23 to mamta3
set connection mamta2;
ij(MAMTA2)> grant select on v23 to mamta3;
-0 rows inserted/updated/deleted
+ERROR: Failed with SQLSTATE 2850G
ij(MAMTA2)> set connection mamta3;
-ij(MAMTA3)> -- although mamta3 doesn't have direct access to mamta1.t12, it can look at it through view mamta2.v23 since mamta3 has select privilege
--- on mamta2.v23
+ij(MAMTA3)> -- should fail
create view v34 as select * from mamta2.v23;
-0 rows inserted/updated/deleted
-ij(MAMTA3)> -- following should work fine because mamta3 has access to all the
--- objects in it's schema
+ERROR: Failed with SQLSTATE 28508
+ij(MAMTA3)> -- should fail
create view v35 as select * from v34;
-0 rows inserted/updated/deleted
+ERROR: Failed with SQLSTATE 42X05
ij(MAMTA3)> -- Write some views based on a routine
set connection mamta1;
ij(MAMTA1)> drop function f_abs1;
@@ -974,21 +973,18 @@
select * from mamta2.v21ViewTest;
ERROR: Failed with SQLSTATE 28508
ij(MAMTA3)> set connection mamta2;
-ij(MAMTA2)> -- give select privileges on the view to mamta3
+ij(MAMTA2)> -- give select privileges on the view to mamta3, should fail
grant select on v21ViewTest to mamta3;
-0 rows inserted/updated/deleted
+ERROR: Failed with SQLSTATE 2850G
ij(MAMTA2)> set connection mamta3;
-ij(MAMTA3)> -- select from mamta2.v21ViewTest will pass this time for mamta3 because mamta3 has select privilege on mamta2.v21ViewTest
+ij(MAMTA3)> -- select from mamta2.v21ViewTest will fail for mamta3 because mamta3 has no select privilege on mamta2.v21ViewTest
select * from mamta2.v21ViewTest;
-C111 |C122
-----------------
-1 |1
-2 |1
-2 rows selected
+ERROR: Failed with SQLSTATE 28508
ij(MAMTA3)> set connection satConnection;
ij(SATCONNECTION)> -- have the dba take away select privilege on mamta2.v21ViewTest from mamta3
revoke select on mamta2.v21ViewTest from mamta3;
0 rows inserted/updated/deleted
+WARNING 01006: Privilege not revoked from MAMTA3.
ij(SATCONNECTION)> set connection mamta3;
ij(MAMTA3)> -- select from mamta2.v21ViewTest will fail this time for mamta3 because dba took away the select privilege on mamta2.v21ViewTest
select * from mamta2.v21ViewTest;
@@ -1198,8 +1194,9 @@
ij(MAMTA1)> set connection mamta2;
ij(MAMTA2)> create view v21ViewTest as select * from mamta1.t11TriggerTest;
0 rows inserted/updated/deleted
-ij(MAMTA2)> grant select on v21ViewTest to mamta3;
-0 rows inserted/updated/deleted
+ij(MAMTA2)> -- should fail
+grant select on v21ViewTest to mamta3;
+ERROR: Failed with SQLSTATE 2850G
ij(MAMTA2)> select * from v21ViewTest;
C111 |C112
-----------------------
@@ -1215,10 +1212,10 @@
ERROR: Failed with SQLSTATE 42Y55
ij(MAMTA3)> create table t32TriggerTest (c321 int);
0 rows inserted/updated/deleted
-ij(MAMTA3)> -- following should pass because all the privileges are in place
+ij(MAMTA3)> -- following should fail because not all the privileges are in place
create trigger tr31t31TriggerTest after insert on t31TriggerTest for each statement mode db2sql
insert into t32TriggerTest values (select c111 from mamta2.v21ViewTest where c112=1);
-0 rows inserted/updated/deleted
+ERROR: Failed with SQLSTATE 28508
ij(MAMTA3)> insert into t31TriggerTest values(1);
1 row inserted/updated/deleted
ij(MAMTA3)> select * from t31TriggerTest;
@@ -1229,8 +1226,7 @@
ij(MAMTA3)> select * from t32TriggerTest;
C321
-----------
-1
-1 row selected
+0 rows selected
ij(MAMTA3)> set connection mamta1;
ij(MAMTA1)> -- This will drop the dependent view
revoke select on t11TriggerTest from mamta2;
@@ -1245,17 +1241,17 @@
-- After DERBY-1613 is fixed, we should consistently get error from insert below because the
-- insert trigger can't find the view it uses.
insert into t31TriggerTest values(1);
-ERROR: Failed with SQLSTATE 42X05
+1 row inserted/updated/deleted
ij(MAMTA3)> select * from t31TriggerTest;
C311
-----------
1
-1 row selected
+1
+2 rows selected
ij(MAMTA3)> select * from t32TriggerTest;
C321
-----------
-1
-1 row selected
+0 rows selected
ij(MAMTA3)> -- cleanup
set connection mamta3;
ij(MAMTA3)> drop table t31TriggerTest;
@@ -1458,40 +1454,31 @@
1 |1
2 |1
2 rows selected
-ij(MAMTA2)> -- grant permission to mamta3 so mamta3 can create a view based on v21ViewTest
+ij(MAMTA2)> -- grant permission to mamta3, should fail
grant select on v21ViewTest to mamta3;
-0 rows inserted/updated/deleted
+ERROR: Failed with SQLSTATE 2850G
ij(MAMTA2)> set connection mamta3;
ij(MAMTA3)> create view v31ViewTest as select * from mamta2.v21ViewTest;
-0 rows inserted/updated/deleted
+ERROR: Failed with SQLSTATE 28508
ij(MAMTA3)> select * from v31ViewTest;
-C111 |C122
-----------------
-1 |1
-2 |1
-2 rows selected
+ERROR: Failed with SQLSTATE 42X05
ij(MAMTA3)> set connection mamta1;
-ij(MAMTA1)> -- can't revoke the privilege because the view that relies on this privilege has another view defined on it and since Derby does not
--- support cascade view drop, we can't automatically drop view relying on the privilege below
+ij(MAMTA1)> -- revoke the privilege from mamta2, should be ok, previous view is not created.
revoke select on t11ViewTest from mamta2;
-ERROR: Failed with SQLSTATE X0Y23
+0 rows inserted/updated/deleted
ij(MAMTA1)> set connection mamta2;
-ij(MAMTA2)> -- view is still around, it couldn't be dropped automatically as a result of the revoke because there is another view dependent on the
--- view below. Need to drop that dependent view first in order for revoke to drop following view automatically
+ij(MAMTA2)> -- this view is not created, should fail
select * from v21ViewTest;
-C111 |C122
-----------------
-1 |1
-2 |1
-2 rows selected
+ERROR: Failed with SQLSTATE 42X05
ij(MAMTA2)> set connection mamta3;
ij(MAMTA3)> -- drop the dependent view
drop view v31ViewTest;
-0 rows inserted/updated/deleted
+ERROR: Failed with SQLSTATE X0X05
ij(MAMTA3)> set connection mamta1;
ij(MAMTA1)> -- revoke privilege will succeed this time and will drop the dependent view on that privilege
revoke select on t11ViewTest from mamta2;
0 rows inserted/updated/deleted
+WARNING 01006: Privilege not revoked from MAMTA2.
ij(MAMTA1)> set connection mamta2;
ij(MAMTA2)> -- view doesn't exist anymore
select * from v21ViewTest;
@@ -2218,26 +2205,20 @@
1
1 row selected
ij(MAMTA3)> grant select on v21ViewTest to mamta2;
-0 rows inserted/updated/deleted
+ERROR: Failed with SQLSTATE 2850G
ij(MAMTA3)> set connection mamta2;
ij(MAMTA2)> -- Although mamta2 has SELECT privileges on mamta3.v21ViewTest, mamta2 doesn't have
-- SELECT privileges on table mamta1.t12RoutineTest accessed by the routine
-- (which is underneath the view) and hence select from view will fail
select * from mamta3.v21ViewTest;
-C211
------------
-ERROR: Failed with SQLSTATE 38000
ERROR: Failed with SQLSTATE 28508
ij(MAMTA2)> set connection mamta1;
ij(MAMTA1)> grant select on t12RoutineTest to mamta2;
0 rows inserted/updated/deleted
ij(MAMTA1)> set connection mamta2;
-ij(MAMTA2)> -- now the view select will succeed
+ij(MAMTA2)> -- should fail
select * from mamta3.v21ViewTest;
-C211
------------
-1
-1 row selected
+ERROR: Failed with SQLSTATE 28508
ij(MAMTA2)> -- In this test, the trigger is accessing a view. Any user that has insert privilege
-- on trigger table will be able to make an insert even if that user doesn't have
-- privileges on objects referenced by the trigger.
@@ -2258,7 +2239,7 @@
ij(MAMTA2)> create view v21ViewTest as select * from mamta1.t11TriggerTest;
0 rows inserted/updated/deleted
ij(MAMTA2)> grant select on v21ViewTest to mamta4;
-0 rows inserted/updated/deleted
+ERROR: Failed with SQLSTATE 2850G
ij(MAMTA2)> set connection mamta3;
ij(MAMTA3)> drop table t31TriggerTest;
ERROR: Failed with SQLSTATE 42Y55
@@ -2275,7 +2256,7 @@
ERROR: Failed with SQLSTATE 42X94
ij(MAMTA4)> create trigger tr41t41 after insert on t41TriggerTest for each statement mode db2sql
insert into mamta3.t31TriggerTest (select * from mamta2.v21ViewTest);
-0 rows inserted/updated/deleted
+ERROR: Failed with SQLSTATE 28508
ij(MAMTA4)> insert into t41TriggerTest values(1);
1 row inserted/updated/deleted
ij(MAMTA4)> insert into t41TriggerTest values(2);
@@ -2304,11 +2285,7 @@
ij(MAMTA3)> select * from t31TriggerTest;
C311
-----------
-1
-2
-1
-2
-4 rows selected
+0 rows selected
ij(MAMTA3)> -- will fail because no permissions on mamta4.t41TriggerTest
insert into mamta4.t41TriggerTest values(1);
ERROR: Failed with SQLSTATE 28506
@@ -2935,6 +2912,34 @@
ij(USER2)> lock table user1.t100 in share mode;
ERROR: Failed with SQLSTATE 28506
ij(USER2)> commit;
+ij(USER2)> autocommit on;
+ij(USER2)> -- DERBY-1686
+set connection user1;
+ij(USER1)> create table t1 (i int);
+0 rows inserted/updated/deleted
+ij(USER1)> insert into t1 values 1,2,3;
+3 rows inserted/updated/deleted
+ij(USER1)> grant select on t1 to user2;
+0 rows inserted/updated/deleted
+ij(USER1)> set connection user2;
+ij(USER2)> create view v1 as select * from user1.t1;
+0 rows inserted/updated/deleted
+ij(USER2)> -- attempt to grant this view to others, should fail since user2
+-- does not have grant privilege on object user1.t1
+grant select on user1.t1 to user3;
+ERROR: Failed with SQLSTATE 2850C
+ij(USER2)> -- expect error
+grant select on v1 to user3;
+ERROR: Failed with SQLSTATE 2850G
+ij(USER2)> -- cleanup
+set connection user2;
+ij(USER2)> drop view v1;
+0 rows inserted/updated/deleted
+ij(USER2)> set connection user1;
+ij(USER1)> drop table t1;
+0 rows inserted/updated/deleted
+ij(USER1)> autocommit on;
+ij(USER1)> set connection user2;
ij(USER2)> autocommit on;
ij(USER2)> -- Simple test case for DERBY-1583: column privilege checking should not
-- assume column descriptors have non-null table references.
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/grantRevokeDDL.sql
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/grantRevokeDDL.sql?view=diff&rev=441131&r1=441130&r2=441131
==============================================================================
--- 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 Sep 7 09:16:15 2006
@@ -468,11 +468,12 @@
-- connect as mamta2 and give select privilege on v22 to mamta3
set connection mamta2;
+-- should fail
grant select on v22 to mamta3;
set connection mamta3;
--- mamta3 has the required privileges now, so following should work
+-- should fail
create view v31 as select * from mamta2.v22;
--- following will pass because mamta3 has direct access to v22 and public access to t11
+-- following will fail because mamta3 has no access to v22
create view v32 as select v22.c111 as a, t11.c111 as b from mamta2.v22 v22, mamta1.t11 t11;
-- following will still fail because mamta3 doesn't have access to mamta1.t12.c121
create view v33 as select v22.c111 as a, t12.c121 as b from mamta2.v22 v22, mamta1.t12 t12;
@@ -481,11 +482,9 @@
set connection mamta2;
grant select on v23 to mamta3;
set connection mamta3;
--- although mamta3 doesn't have direct access to mamta1.t12, it can look at it through view mamta2.v23 since mamta3 has select privilege
--- on mamta2.v23
+-- should fail
create view v34 as select * from mamta2.v23;
--- following should work fine because mamta3 has access to all the
--- objects in it's schema
+-- should fail
create view v35 as select * from v34;
-- Write some views based on a routine
@@ -636,10 +635,10 @@
-- mamta3 has not been granted select privileges on mamta2.v21ViewTest
select * from mamta2.v21ViewTest;
set connection mamta2;
--- give select privileges on the view to mamta3
+-- give select privileges on the view to mamta3, should fail
grant select on v21ViewTest to mamta3;
set connection mamta3;
--- select from mamta2.v21ViewTest will pass this time for mamta3 because mamta3 has select privilege on mamta2.v21ViewTest
+-- select from mamta2.v21ViewTest will fail for mamta3 because mamta3 has no select privilege on mamta2.v21ViewTest
select * from mamta2.v21ViewTest;
set connection satConnection;
-- have the dba take away select privilege on mamta2.v21ViewTest from mamta3
@@ -787,6 +786,7 @@
grant select on t11TriggerTest to mamta2;
set connection mamta2;
create view v21ViewTest as select * from mamta1.t11TriggerTest;
+-- should fail
grant select on v21ViewTest to mamta3;
select * from v21ViewTest;
set connection mamta3;
@@ -794,7 +794,7 @@
create table t31TriggerTest (c311 int);
drop table t32TriggerTest;
create table t32TriggerTest (c321 int);
--- following should pass because all the privileges are in place
+-- following should fail because not all the privileges are in place
create trigger tr31t31TriggerTest after insert on t31TriggerTest for each statement mode db2sql
insert into t32TriggerTest values (select c111 from mamta2.v21ViewTest where c112=1);
insert into t31TriggerTest values(1);
@@ -948,18 +948,16 @@
set connection mamta2;
create view v21ViewTest as select t1.c111, t2.c122 from mamta1.t11ViewTest as t1, mamta1.t12ViewTest as t2;
select * from v21ViewTest;
--- grant permission to mamta3 so mamta3 can create a view based on v21ViewTest
+-- grant permission to mamta3, should fail
grant select on v21ViewTest to mamta3;
set connection mamta3;
create view v31ViewTest as select * from mamta2.v21ViewTest;
select * from v31ViewTest;
set connection mamta1;
--- can't revoke the privilege because the view that relies on this privilege has another view defined on it and since Derby does not
--- support cascade view drop, we can't automatically drop view relying on the privilege below
+-- revoke the privilege from mamta2, should be ok, previous view is not created.
revoke select on t11ViewTest from mamta2;
set connection mamta2;
--- view is still around, it couldn't be dropped automatically as a result of the revoke because there is another view dependent on the
--- view below. Need to drop that dependent view first in order for revoke to drop following view automatically
+-- this view is not created, should fail
select * from v21ViewTest;
set connection mamta3;
-- drop the dependent view
@@ -1423,7 +1421,7 @@
set connection mamta1;
grant select on t12RoutineTest to mamta2;
set connection mamta2;
--- now the view select will succeed
+-- should fail
select * from mamta3.v21ViewTest;
-- In this test, the trigger is accessing a view. Any user that has insert privilege
@@ -1855,6 +1853,27 @@
lock table user1.t100 in exclusive mode;
lock table user1.t100 in share mode;
commit;
+autocommit on;
+
+-- DERBY-1686
+set connection user1;
+create table t1 (i int);
+insert into t1 values 1,2,3;
+grant select on t1 to user2;
+set connection user2;
+create view v1 as select * from user1.t1;
+-- attempt to grant this view to others, should fail since user2
+-- does not have grant privilege on object user1.t1
+grant select on user1.t1 to user3;
+-- expect error
+grant select on v1 to user3;
+-- cleanup
+set connection user2;
+drop view v1;
+set connection user1;
+drop table t1;
+autocommit on;
+set connection user2;
autocommit on;
-- Simple test case for DERBY-1583: column privilege checking should not