From derby-commits-return-8858-apmail-db-derby-commits-archive=db.apache.org@db.apache.org Thu Nov 22 13:49:50 2007 Return-Path: Delivered-To: apmail-db-derby-commits-archive@www.apache.org Received: (qmail 98261 invoked from network); 22 Nov 2007 13:49:50 -0000 Received: from hermes.apache.org (HELO mail.apache.org) (140.211.11.2) by minotaur.apache.org with SMTP; 22 Nov 2007 13:49:50 -0000 Received: (qmail 6825 invoked by uid 500); 22 Nov 2007 13:49:38 -0000 Delivered-To: apmail-db-derby-commits-archive@db.apache.org Received: (qmail 6788 invoked by uid 500); 22 Nov 2007 13:49:38 -0000 Mailing-List: contact derby-commits-help@db.apache.org; run by ezmlm Precedence: bulk list-help: list-unsubscribe: List-Post: Reply-To: "Derby Development" List-Id: Delivered-To: mailing list derby-commits@db.apache.org Received: (qmail 6777 invoked by uid 99); 22 Nov 2007 13:49:38 -0000 Received: from nike.apache.org (HELO nike.apache.org) (192.87.106.230) by apache.org (qpsmtpd/0.29) with ESMTP; Thu, 22 Nov 2007 05:49:38 -0800 X-ASF-Spam-Status: No, hits=-100.0 required=10.0 tests=ALL_TRUSTED X-Spam-Check-By: apache.org Received: from [140.211.11.3] (HELO eris.apache.org) (140.211.11.3) by apache.org (qpsmtpd/0.29) with ESMTP; Thu, 22 Nov 2007 13:49:46 +0000 Received: by eris.apache.org (Postfix, from userid 65534) id F21EA1A9832; Thu, 22 Nov 2007 05:49:24 -0800 (PST) Content-Type: text/plain; charset="utf-8" MIME-Version: 1.0 Content-Transfer-Encoding: 7bit Subject: svn commit: r597409 - in /db/derby/code/trunk/java: engine/org/apache/derby/impl/sql/catalog/ testing/org/apache/derbyTesting/functionTests/tests/upgradeTests/ testing/org/apache/derbyTesting/junit/ Date: Thu, 22 Nov 2007 13:49:02 -0000 To: derby-commits@db.apache.org From: dag@apache.org X-Mailer: svnmailer-1.0.8 Message-Id: <20071122134924.F21EA1A9832@eris.apache.org> X-Virus-Checked: Checked by ClamAV on apache.org Author: dag Date: Thu Nov 22 05:48:57 2007 New Revision: 597409 URL: http://svn.apache.org/viewvc?rev=597409&view=rev Log: DERBY-3191 SQL roles: add upgrade support Patch DERBY-3191b; adds hard upgrade code for the new roles system table SYS.SYSROLES, plus tests for this functionality to the upgrade tests. Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/catalog/DD_Version.java db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/upgradeTests/Changes10_4.java db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/upgradeTests/PhaseChanger.java db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/upgradeTests/UpgradeRun.java db/derby/code/trunk/java/testing/org/apache/derbyTesting/junit/TestConfiguration.java Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/catalog/DD_Version.java URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/catalog/DD_Version.java?rev=597409&r1=597408&r2=597409&view=diff ============================================================================== --- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/catalog/DD_Version.java (original) +++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/catalog/DD_Version.java Thu Nov 22 05:48:57 2007 @@ -363,6 +363,13 @@ */ + if (fromMajorVersionNumber <= DataDictionary.DD_VERSION_DERBY_10_3) + { + // Add new system catalogs created for roles + bootingDictionary.upgradeMakeCatalog( + tc, DataDictionary.SYSROLES_CATALOG_NUM); + } + if (fromMajorVersionNumber <= DataDictionary.DD_VERSION_DERBY_10_1) { // add catalogs 1st, subsequent procedure adding may depend on Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/upgradeTests/Changes10_4.java URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/upgradeTests/Changes10_4.java?rev=597409&r1=597408&r2=597409&view=diff ============================================================================== --- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/upgradeTests/Changes10_4.java (original) +++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/upgradeTests/Changes10_4.java Thu Nov 22 05:48:57 2007 @@ -29,6 +29,7 @@ import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; +import java.sql.CallableStatement; import javax.sql.DataSource; @@ -180,4 +181,170 @@ s.close(); } + /** + * Check that you must be hard-upgraded to 10.4 or later in order to use + * SQL roles + * @throws SQLException + * + */ + public void testSQLRolesBasic() throws SQLException + { + // The standard upgrade database doesn't have sqlAuthorization + // set, so we can only check if the system tables for roles is + // present. + + Statement s = createStatement(); + String createRoleText = "create role foo"; + + switch (getPhase()) + { + case PH_CREATE: + assertStatementError("42X01", s, createRoleText ); + break; + + case PH_SOFT_UPGRADE: + // needs hard upgrade + assertStatementError("XCL47", s, createRoleText ); + break; + + case PH_POST_SOFT_UPGRADE: + assertStatementError("42X01", s, createRoleText ); + break; + + case PH_HARD_UPGRADE: + case PH_POST_HARD_UPGRADE: + // not supported because SQL authorization not set + assertStatementError("42Z60", s, createRoleText ); + break; + } + + s.close(); + } + + /** + * Check that when hard-upgraded to 10.4 or later SQL roles can be + * declared if DB has sqlAuthorization. + * @throws SQLException + * + */ + public void testSQLRoles() throws SQLException + { + // Do rudimentary sanity checking: that we can create and drop roles + // when we are database owner. If so, we can presume SYS.SYSROLES + // has been upgraded correctly. + + DataSource ds = JDBCDataSource.getDataSourceLogical("ROLES_10_4"); + String createRoleText = "create role foo"; + String dropRoleText = "drop role foo"; + Connection conn = null; + Statement s = null; + boolean supportSqlAuthorization = oldAtLeast(10, 2); + + JDBCDataSource.setBeanProperty(ds, "user", "garfield"); + JDBCDataSource.setBeanProperty(ds, "password", "theCat"); + + switch (getPhase()) { + case PH_CREATE: + // create the database if it was not already created. + JDBCDataSource.setBeanProperty(ds, "createDatabase", "create"); + conn = ds.getConnection(); + + // Make the database have std security, and define + // a database user for the database owner). + CallableStatement cs = conn.prepareCall( + "call syscs_util.syscs_set_database_property(?,?)"); + + cs.setString(1, "derby.connection.requireAuthentication"); + cs.setString(2, "true"); + cs.execute(); + + cs.setString(1, "derby.authentication.provider"); + cs.setString(2, "BUILTIN"); + cs.execute(); + + cs.setString(1, "derby.database.sqlAuthorization"); + cs.setString(2, "true"); + cs.execute(); + + cs.setString(1, "derby.database.propertiesOnly"); + cs.setString(2, "true"); + cs.execute(); + + cs.setString(1, "derby.user.garfield"); + cs.setString(2, "theCat"); + cs.execute(); + + conn.close(); + + JDBCDataSource.shutdownDatabase(ds); + break; + + case PH_SOFT_UPGRADE: + /* We can't always do soft upgrade, because when + * sqlAuthorization is set and we are coming from a + * pre-10.2 database, connecting will fail with a message + * to hard upgrade before setting sqlAuthorization, so we + * skip this step. + */ + if (oldAtLeast(10,2)) { + // needs hard upgrade + conn = ds.getConnection(); + s = conn.createStatement(); + + assertStatementError("XCL47", s, createRoleText ); + conn.close(); + + JDBCDataSource.shutdownDatabase(ds); + } + break; + + case PH_POST_SOFT_UPGRADE: + conn = ds.getConnection(); + s = conn.createStatement(); + + // syntax error + assertStatementError("42X01", s, createRoleText ); + conn.close(); + + JDBCDataSource.shutdownDatabase(ds); + break; + + case PH_HARD_UPGRADE: + JDBCDataSource.setBeanProperty( + ds, "connectionAttributes", "upgrade=true"); + conn = ds.getConnection(); + s = conn.createStatement(); + + // should work now + try { + s.execute(createRoleText); + } catch (SQLException e) { + fail("can't create role on hard upgrade"); + } + + s.execute(dropRoleText); + conn.close(); + + JDBCDataSource.clearStringBeanProperty(ds, "connectionAttributes"); + JDBCDataSource.shutdownDatabase(ds); + break; + + case PH_POST_HARD_UPGRADE: + conn = ds.getConnection(); + s = conn.createStatement(); + + // should work now + try { + s.execute(createRoleText); + } catch (SQLException e) { + fail("can't create role post hard upgrade"); + } + + s.execute(dropRoleText); + conn.close(); + + JDBCDataSource.shutdownDatabase(ds); + break; + } + } } Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/upgradeTests/PhaseChanger.java URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/upgradeTests/PhaseChanger.java?rev=597409&r1=597408&r2=597409&view=diff ============================================================================== --- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/upgradeTests/PhaseChanger.java (original) +++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/upgradeTests/PhaseChanger.java Thu Nov 22 05:48:57 2007 @@ -109,21 +109,25 @@ for (int i = 0; i < UpgradeRun.ADDITIONAL_DBS.length; i++) { ds = JDBCDataSource.getDataSourceLogical( - UpgradeRun.ADDITIONAL_DBS[i]); - - boolean shutdown = true; - try { - ds.getConnection().close(); - } catch (SQLException e) { - // if the database was never created - // don't bother shutting it down - String sqlState = e.getSQLState(); - if ("XJ004".equals(sqlState) || "XJ040".equals(sqlState)) - shutdown = false; - } - - if (shutdown) - JDBCDataSource.shutdownDatabase(ds); + UpgradeRun.ADDITIONAL_DBS[i].logicalName); + + if (UpgradeRun.ADDITIONAL_DBS[i].shutDown) { + boolean shutdown = true; + try { + ds.getConnection().close(); + } catch (SQLException e) { + // if the database was never created + // don't bother shutting it down + String sqlState = e.getSQLState(); + if ("XJ004".equals(sqlState) || + "XJ040".equals(sqlState)) { + shutdown = false; + } + } + + if (shutdown) + JDBCDataSource.shutdownDatabase(ds); + } // else done by test } } Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/upgradeTests/UpgradeRun.java URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/upgradeTests/UpgradeRun.java?rev=597409&r1=597408&r2=597409&view=diff ============================================================================== --- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/upgradeTests/UpgradeRun.java (original) +++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/upgradeTests/UpgradeRun.java Thu Nov 22 05:48:57 2007 @@ -64,14 +64,22 @@ * They are only created if a test opens a * connection against them. In hard upgrade the test * must explictly upgrade the database. - * The databases are shutdown at the end of each phase. + * The databases are shutdown at the end of each phase, unless + * "NoShutDown" is specified. The latter is used by databases + * which need sqlAuthorization (specified by test). This thwarts + * normal shutdown since credentials are required so shutdown is + * done in test, not by the tearDown methods. See + * Changes10_4#testSQLRoles for example. */ - static final String[] ADDITIONAL_DBS = { - "COLLATED_DB_10_3",//db with territory based collation - "NO_ENCRYPT_10_2", - "ENCRYPT_10_2" + + static final AdditionalDb[] ADDITIONAL_DBS = { + new AdditionalDb("COLLATED_DB_10_3", true), // db with territory + // based collation + new AdditionalDb("NO_ENCRYPT_10_2", true), + new AdditionalDb("ENCRYPT_10_2", true), + new AdditionalDb("ROLES_10_4", false) }; - + private static String getTextVersion(int[] iv) { String version = iv[0] + "." + iv[1] + @@ -81,7 +89,6 @@ public final static Test suite(final int[] version) { - ClassLoader oldLoader = (ClassLoader )AccessController.doPrivileged (new java.security.PrivilegedAction(){ @@ -135,8 +142,13 @@ for (int i = 0; i < ADDITIONAL_DBS.length; i++) { - setup = TestConfiguration.additionalDatabaseDecorator(setup, - ADDITIONAL_DBS[i]); + if (ADDITIONAL_DBS[i].shutDown) { + setup = TestConfiguration.additionalDatabaseDecorator( + setup, ADDITIONAL_DBS[i].logicalName); + } else { + setup = TestConfiguration.additionalDatabaseDecoratorNoShutdown( + setup, ADDITIONAL_DBS[i].logicalName); + } } Properties preReleaseUpgrade = new Properties(); @@ -385,5 +397,17 @@ suite.addTest(new DatabaseMetaDataTest(name)); } } + } + +} + +class AdditionalDb +{ + final String logicalName; + final boolean shutDown; + public AdditionalDb(String logicalName, boolean shutDown) + { + this.logicalName = logicalName; + this.shutDown = shutDown; } } Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/junit/TestConfiguration.java URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/junit/TestConfiguration.java?rev=597409&r1=597408&r2=597409&view=diff ============================================================================== --- db/derby/code/trunk/java/testing/org/apache/derbyTesting/junit/TestConfiguration.java (original) +++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/junit/TestConfiguration.java Thu Nov 22 05:48:57 2007 @@ -435,6 +435,35 @@ } /** + * Similar to additionalDatabaseDecorator except the database will + * not be shutdown, only deleted. It is the responsibility of the + * test to shut it down. + * + * @param test Test to be decorated + * @param logicalDbName The logical database name. This name is + * used to identify the database in + * openConnection(String logicalDatabaseName) + * method calls. + * @return decorated test. + */ + public static TestSetup additionalDatabaseDecoratorNoShutdown( + Test test, + String logicalDbName) + { + return new DatabaseChangeSetup( + new DropDatabaseSetup(test, logicalDbName) + { + protected void tearDown() throws Exception { + // the test is responsible for shutdown + removeDatabase(); + } + }, + logicalDbName, + generateUniqueDatabaseName(), + false); + } + + /** * Decorate a test changing the default user name and password. * Typically used along with DatabasePropertyTestSetup.builtinAuthentication. * The tearDown method resets the default user and password value to