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 rh...@apache.org on 2010/01/07 14:58:17 UTC
svn commit: r896883 - 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/
testing/org/apache/derbyTesting/functionTests/tests/lang/ testing/org/apac...
Author: rhillegas
Date: Thu Jan 7 13:56:35 2010
New Revision: 896883
URL: http://svn.apache.org/viewvc?rev=896883&view=rev
Log:
DERBY-712: Wire in USAGE privilege for SEQUENCEs.
Added:
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/SequencePermsTest.java (with props)
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/NextSequenceNode.java
db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/DDLConstantAction.java
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/_Suite.java
db/derby/code/trunk/java/testing/org/apache/derbyTesting/junit/JDBC.java
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?rev=896883&r1=896882&r2=896883&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 Jan 7 13:56:35 2010
@@ -2254,7 +2254,7 @@
{
if (isSchemaReferenced(tc, getNonCoreTI(SYSSEQUENCES_CATALOG_NUM),
SYSSEQUENCESRowFactory.SYSSEQUENCES_INDEX2_ID,
- 2,
+ 1,
schemaIdOrderable))
{
return false;
Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/NextSequenceNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/NextSequenceNode.java?rev=896883&r1=896882&r2=896883&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/NextSequenceNode.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/NextSequenceNode.java Thu Jan 7 13:56:35 2010
@@ -74,8 +74,16 @@
ValueNode returnNode = this;
- return returnNode;
+ // set up dependency on sequence and compile a check for USAGE
+ // priv if needed
+ getCompilerContext().createDependency( sequenceDescriptor );
+
+ if ( isPrivilegeCollectionRequired() )
+ {
+ getCompilerContext().addRequiredUsagePriv( sequenceDescriptor );
+ }
+ return returnNode;
}
Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/DDLConstantAction.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/DDLConstantAction.java?rev=896883&r1=896882&r2=896883&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/DDLConstantAction.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/DDLConstantAction.java Thu Jan 7 13:56:35 2010
@@ -55,6 +55,7 @@
import org.apache.derby.iapi.sql.dictionary.SchemaDescriptor;
import org.apache.derby.iapi.sql.dictionary.StatementColumnPermission;
import org.apache.derby.iapi.sql.dictionary.StatementPermission;
+import org.apache.derby.iapi.sql.dictionary.StatementGenericPermission;
import org.apache.derby.iapi.sql.dictionary.StatementSchemaPermission;
import org.apache.derby.iapi.sql.dictionary.StatementRolePermission;
import org.apache.derby.iapi.sql.dictionary.StatementRoutinePermission;
@@ -332,7 +333,7 @@
PermissionsDescriptor permDesc;
// Now, it is time to add into dependency system the FOREIGN
// constraint's dependency on REFERENCES privilege, or, if it is a
- // CHECK constraint, any EXECUTE privileges. If the REFERENCES is
+ // CHECK constraint, any EXECUTE or USAGE privileges. If the REFERENCES is
// revoked from the constraint owner, the constraint will get
// dropped automatically.
List requiredPermissionsList = activation.getPreparedStatement().getRequiredPermissionsList();
@@ -361,7 +362,8 @@
if (!statementTablePermission.getTableUUID().equals(refTableUUID))
continue;
} else if (statPerm instanceof StatementSchemaPermission
- || statPerm instanceof StatementRolePermission) {
+ || statPerm instanceof StatementRolePermission
+ || statPerm instanceof StatementGenericPermission ) {
continue;
} else {
if (SanityManager.DEBUG) {
@@ -380,14 +382,14 @@
}
- // We know that we are working with a REFERENCES or EXECUTE
+ // We know that we are working with a REFERENCES, EXECUTE, or USAGE
// privilege. Find all the PermissionDescriptors for this
// privilege and make constraint depend on it through
// dependency manager. The REFERENCES privilege could be
// defined at the table level or it could be defined at
// individual column levels. In addition, individual column
// REFERENCES privilege could be available at the user
- // level, PUBLIC or role level. EXECUTE privilege could be
+ // level, PUBLIC or role level. EXECUTE and USAGE privileges could be
// available at the user level, PUBLIC or role level.
permDesc = statPerm.getPermissionDescriptor(lcc.getAuthorizationId(), dd);
if (permDesc == null)
Added: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/SequencePermsTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/SequencePermsTest.java?rev=896883&view=auto
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/SequencePermsTest.java (added)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/SequencePermsTest.java Thu Jan 7 13:56:35 2010
@@ -0,0 +1,327 @@
+/*
+
+ Derby - Class org.apache.derbyTesting.functionTests.tests.lang.SequencePermsTest
+
+ Licensed to the Apache Software Foundation (ASF) under one or more
+ contributor license agreements. See the NOTICE file distributed with
+ this work for additional information regarding copyright ownership.
+ The ASF licenses this file to you under the Apache License, Version 2.0
+ (the "License"); you may not use this file except in compliance with
+ the License. You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+ Unless required by applicable law or agreed to in writing, software
+ distributed under the License is distributed on an "AS IS" BASIS,
+ WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ See the License for the specific language governing permissions and
+ limitations under the License.
+
+ */
+
+package org.apache.derbyTesting.functionTests.tests.lang;
+
+import java.sql.SQLException;
+import java.sql.SQLWarning;
+import java.sql.Connection;
+import java.sql.Statement;
+import java.sql.PreparedStatement;
+import java.sql.ResultSet;
+import java.sql.DriverManager;
+import java.util.ArrayList;
+import junit.framework.Test;
+import junit.framework.TestSuite;
+import org.apache.derby.iapi.util.StringUtil;
+import org.apache.derby.catalog.DefaultInfo;
+import org.apache.derbyTesting.junit.BaseJDBCTestCase;
+import org.apache.derbyTesting.junit.JDBC;
+import org.apache.derbyTesting.junit.DatabasePropertyTestSetup;
+import org.apache.derbyTesting.junit.JDBC;
+import org.apache.derbyTesting.junit.TestConfiguration;
+import org.apache.derbyTesting.junit.CleanDatabaseTestSetup;
+import org.apache.derbyTesting.junit.JDBC;
+
+/**
+ * <p>
+ * Test permissions on sequences. See DERBY-712.
+ * </p>
+ */
+public class SequencePermsTest extends GeneratedColumnsHelper
+{
+ ///////////////////////////////////////////////////////////////////////////////////
+ //
+ // CONSTANTS
+ //
+ ///////////////////////////////////////////////////////////////////////////////////
+
+ private static final String TEST_DBO = "TEST_DBO";
+ private static final String RUTH = "RUTH";
+ private static final String ALICE = "ALICE";
+ private static final String FRANK = "FRANK";
+ private static final String IRMA = "IRMA";
+ private static final String[] LEGAL_USERS = { TEST_DBO, ALICE, RUTH, FRANK, IRMA };
+
+ ///////////////////////////////////////////////////////////////////////////////////
+ //
+ // STATE
+ //
+ ///////////////////////////////////////////////////////////////////////////////////
+
+ ///////////////////////////////////////////////////////////////////////////////////
+ //
+ // CONSTRUCTOR
+ //
+ ///////////////////////////////////////////////////////////////////////////////////
+
+ /**
+ * Create a new instance.
+ */
+
+ public SequencePermsTest(String name)
+ {
+ super(name);
+ }
+
+ ///////////////////////////////////////////////////////////////////////////////////
+ //
+ // JUnit BEHAVIOR
+ //
+ ///////////////////////////////////////////////////////////////////////////////////
+
+
+ /**
+ * Construct top level suite in this JUnit test
+ */
+ public static Test suite()
+ {
+ TestSuite suite = (TestSuite) TestConfiguration.embeddedSuite(SequencePermsTest.class);
+
+ Test cleanTest = new CleanDatabaseTestSetup( suite );
+ Test authenticatedTest = DatabasePropertyTestSetup.builtinAuthentication
+ ( cleanTest, LEGAL_USERS, "sequencePermissions" );
+ Test authorizedTest = TestConfiguration.sqlAuthorizationDecorator( authenticatedTest );
+
+ return authorizedTest;
+ }
+
+ ///////////////////////////////////////////////////////////////////////////////////
+ //
+ // TESTS
+ //
+ ///////////////////////////////////////////////////////////////////////////////////
+
+ /**
+ * <p>
+ * Test that you need USAGE privilege on a sequence in order to issue a NEXT VALUE FOR
+ * on it and in order to declare objects which mention that type.
+ * </p>
+ */
+ public void test_001_basicGrant()
+ throws Exception
+ {
+ Connection dboConnection = openUserConnection( TEST_DBO );
+ Connection ruthConnection = openUserConnection( RUTH );
+ Connection aliceConnection = openUserConnection( ALICE );
+ Connection frankConnection = openUserConnection( FRANK );
+
+ //
+ // Create a sequence and view. Make the view
+ // public. Verify that it is still not generally usable because the
+ // sequence is not public yet.
+ //
+ goodStatement
+ (
+ ruthConnection,
+ "create sequence seq_01\n"
+ );
+ goodStatement
+ (
+ ruthConnection,
+ "create table t_01( c int )\n"
+ );
+ goodStatement
+ (
+ ruthConnection,
+ "insert into t_01( c ) values ( 1 )\n"
+ );
+ goodStatement
+ (
+ ruthConnection,
+ "create view v_01( a, b ) as select c, next value for seq_01 from t_01\n"
+ );
+ goodStatement
+ (
+ ruthConnection,
+ "grant select on v_01 to alice\n"
+ );
+
+ expectExecutionError
+ (
+ aliceConnection,
+ LACK_USAGE_PRIV,
+ "values ( next value for ruth.seq_01 )\n"
+ );
+ expectExecutionError
+ (
+ aliceConnection,
+ LACK_COLUMN_PRIV,
+ "select * from ruth.t_01\n"
+ );
+
+ // but this succeeds because of definer's rights on the view
+ goodStatement
+ (
+ ruthConnection,
+ "select * from ruth.v_01\n"
+ );
+
+ //
+ // The DBO however is almighty.
+ //
+ goodStatement
+ (
+ ruthConnection,
+ "values ( next value for ruth.seq_01 )\n"
+ );
+
+ //
+ // Now grant USAGE on the sequence. User Alice should now have all the
+ // privileges she needs.
+ //
+ goodStatement
+ (
+ ruthConnection,
+ "grant usage on sequence seq_01 to alice\n"
+ );
+ goodStatement
+ (
+ aliceConnection,
+ "values( next value for ruth.seq_01 )\n"
+ );
+
+ }
+
+ /**
+ * <p>
+ * Test that you need USAGE privilege on a sequence in order to issue a NEXT VALUE FOR
+ * on it the privilege can't be revoked while the object still exists.
+ * </p>
+ */
+ public void test_002_basicRevoke()
+ throws Exception
+ {
+ Connection ruthConnection = openUserConnection( RUTH );
+ Connection frankConnection = openUserConnection( FRANK );
+
+ goodStatement
+ (
+ ruthConnection,
+ "create sequence seq_02\n"
+ );
+ goodStatement
+ (
+ frankConnection,
+ "create table t_01( c int )\n"
+ );
+ expectExecutionError
+ (
+ frankConnection,
+ LACK_USAGE_PRIV,
+ "values ( next value for ruth.seq_02 )\n"
+ );
+
+ //
+ // Only RESTRICTed revokes allowed.
+ //
+ goodStatement
+ (
+ ruthConnection,
+ "grant usage on sequence seq_02 to public\n"
+ );
+ expectCompilationError( ruthConnection, SYNTAX_ERROR, "revoke usage on sequence seq_02 from public\n" );
+ goodStatement
+ (
+ ruthConnection,
+ "revoke usage on sequence seq_02 from public restrict\n"
+ );
+
+ //
+ // Now test revokes when objects depend on the sequence.
+ //
+
+ String grantUsage = "grant usage on sequence seq_02 to frank\n";
+ String revokeUsage = "revoke usage on sequence seq_02 from frank restrict\n";
+ String createStatement;
+ String dropStatement;
+ String badRevokeSQLState;
+
+ // view
+ createStatement = "create view v_01( a, b ) as select c, next value for ruth.seq_02 from t_01\n";
+ dropStatement = "drop view v_01\n";
+ badRevokeSQLState = VIEW_DEPENDS_ON_PRIVILEGE;
+ verifyRevokePrivilege
+ (
+ ruthConnection,
+ frankConnection,
+ grantUsage,
+ revokeUsage,
+ createStatement,
+ dropStatement,
+ badRevokeSQLState
+ );
+
+ // trigger
+ createStatement = "create trigger trig_01 after update on t_01 for each statement insert into t_01( c ) values ( next value for ruth.seq_02 )\n";
+ dropStatement = "drop trigger trig_01\n";
+ badRevokeSQLState = OPERATION_FORBIDDEN;
+ verifyRevokePrivilege
+ (
+ ruthConnection,
+ frankConnection,
+ grantUsage,
+ revokeUsage,
+ createStatement,
+ dropStatement,
+ badRevokeSQLState
+ );
+
+ // constraint
+ createStatement = "create table t_02( c int check ( ( next value for ruth.seq_02 ) < c ) )\n";
+ dropStatement = "drop table t_02\n";
+ badRevokeSQLState = OPERATION_FORBIDDEN;
+ verifyRevokePrivilege
+ (
+ ruthConnection,
+ frankConnection,
+ grantUsage,
+ revokeUsage,
+ createStatement,
+ dropStatement,
+ badRevokeSQLState
+ );
+
+
+ }
+
+ /**
+ * <p>
+ * Test that you can't drop a schema if it contains a sequence.
+ * </p>
+ */
+ public void test_003_dropSchema()
+ throws Exception
+ {
+ Connection dboConnection = openUserConnection( TEST_DBO );
+ Connection irmaConnection = openUserConnection( IRMA );
+
+ goodStatement
+ ( irmaConnection, "create sequence seq_01\n" );
+ expectExecutionError( dboConnection, NON_EMPTY_SCHEMA, "drop schema irma restrict\n" );
+
+ goodStatement
+ (irmaConnection, "drop sequence seq_01\n" );
+ goodStatement
+ ( dboConnection, "drop schema irma restrict\n" );
+ }
+
+}
Propchange: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/SequencePermsTest.java
------------------------------------------------------------------------------
svn:eol-style = native
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/_Suite.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/_Suite.java?rev=896883&r1=896882&r2=896883&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/_Suite.java (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/_Suite.java Thu Jan 7 13:56:35 2010
@@ -207,6 +207,7 @@
suite.addTest(UserLobTest.suite());
suite.addTest(OffsetFetchNextTest.suite());
suite.addTest(SequenceTest.suite());
+ suite.addTest(SequencePermsTest.suite());
suite.addTest(OrderByInSubqueries.suite());
suite.addTest(OLAPTest.suite());
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/junit/JDBC.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/junit/JDBC.java?rev=896883&r1=896882&r2=896883&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/junit/JDBC.java (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/junit/JDBC.java Thu Jan 7 13:56:35 2010
@@ -314,6 +314,20 @@
dropUsingDMD(s, rs, schema, "TABLE_NAME", "SYNONYM");
+ // sequences
+ if ( sysSequencesExists( conn ) )
+ {
+ psf = conn.prepareStatement
+ (
+ "SELECT SEQUENCENAME FROM SYS.SYSSEQUENCES A, SYS.SYSSCHEMAS S" +
+ " WHERE A.SCHEMAID = S.SCHEMAID " +
+ " AND S.SCHEMANAME = ?");
+ psf.setString(1, schema);
+ rs = psf.executeQuery();
+ dropUsingDMD(s, rs, schema, "SEQUENCENAME", "SEQUENCE");
+ psf.close();
+ }
+
// Finally drop the schema if it is not APP
if (!schema.equals("APP")) {
s.executeUpdate("DROP SCHEMA " + JDBC.escape(schema) + " RESTRICT");
@@ -321,6 +335,31 @@
conn.commit();
s.close();
}
+
+ /**
+ * Return true if the SYSSEQUENCES table exists.
+ */
+ private static boolean sysSequencesExists( Connection conn ) throws SQLException
+ {
+ PreparedStatement ps = null;
+ ResultSet rs = null;
+ try {
+ ps = conn.prepareStatement
+ (
+ "select count(*) from sys.systables t, sys.sysschemas s\n" +
+ "where t.schemaid = s.schemaid\n" +
+ "and ( cast(s.schemaname as varchar(128)))= 'SYS'\n" +
+ "and ( cast(t.tablename as varchar(128))) = 'SYSSEQUENCES'" );
+ rs = ps.executeQuery();
+ rs.next();
+ return ( rs.getInt( 1 ) > 0 );
+ }
+ finally
+ {
+ if ( rs != null ) { rs.close(); }
+ if ( ps != null ) { ps.close(); }
+ }
+ }
/**
* DROP a set of objects based upon a ResultSet from a