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 2008/10/10 02:20:12 UTC
svn commit: r703298 - in /db/derby/code/trunk/java:
engine/org/apache/derby/impl/sql/compile/
testing/org/apache/derbyTesting/functionTests/tests/upgradeTests/
Author: dag
Date: Thu Oct 9 17:20:12 2008
New Revision: 703298
URL: http://svn.apache.org/viewvc?rev=703298&view=rev
Log:
SQL roles: Move upgrade logic from 10.4 to 10.5
Patch derby-3901-2, which accomplishing this task, and also adds
release 10.3.3.0 to the set of releases to test upgrade from (was
missing).
Modified:
db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj
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/Changes10_5.java
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/upgradeTests/UpgradeRun.java
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/upgradeTests/_Suite.java
Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj?rev=703298&r1=703297&r2=703298&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj Thu Oct 9 17:20:12 2008
@@ -7476,7 +7476,7 @@
{
<CURRENT_ROLE>
{
- checkVersion( DataDictionary.DD_VERSION_DERBY_10_4, "ROLES");
+ checkVersion( DataDictionary.DD_VERSION_DERBY_10_5, "ROLES");
checkSqlStandardAccess("CURRENT_ROLE");
return (ValueNode) nodeFactory.getNode(
C_NodeTypes.CURRENT_ROLE_NODE,
@@ -9707,7 +9707,7 @@
*/
<ROLE> roleName = identifier(Limits.MAX_IDENTIFIER_LENGTH, true)
{
- checkVersion( DataDictionary.DD_VERSION_DERBY_10_4, "ROLES");
+ checkVersion( DataDictionary.DD_VERSION_DERBY_10_5, "ROLES");
checkSqlStandardAccess("CREATE ROLE");
// Reserve namespace for system roles
@@ -11520,7 +11520,7 @@
setRoleSpecification() throws StandardException :
{
String roleName = null;
- checkVersion( DataDictionary.DD_VERSION_DERBY_10_4, "ROLES");
+ checkVersion( DataDictionary.DD_VERSION_DERBY_10_5, "ROLES");
checkSqlStandardAccess("SET ROLE");
}
{
@@ -12185,7 +12185,7 @@
*/
<ROLE> roleName = identifier(Limits.MAX_IDENTIFIER_LENGTH, true)
{
- checkVersion( DataDictionary.DD_VERSION_DERBY_10_4, "ROLES");
+ checkVersion( DataDictionary.DD_VERSION_DERBY_10_5, "ROLES");
checkSqlStandardAccess("DROP ROLE");
return (StatementNode) nodeFactory.getNode(
@@ -13092,7 +13092,7 @@
grantees = granteeList()
{
checkSqlStandardAccess("GRANT <role>");
- checkVersion( DataDictionary.DD_VERSION_DERBY_10_4, "ROLES");
+ checkVersion( DataDictionary.DD_VERSION_DERBY_10_5, "ROLES");
return (StatementNode) nodeFactory.getNode
(C_NodeTypes.GRANT_ROLE_NODE,
@@ -13178,7 +13178,7 @@
!isPrivilegeKeywordExceptTrigger(getToken(2).kind)) } )
<REVOKE>
{
- checkVersion( DataDictionary.DD_VERSION_DERBY_10_4, "ROLES");
+ checkVersion( DataDictionary.DD_VERSION_DERBY_10_5, "ROLES");
checkSqlStandardAccess("REVOKE <role>");
}
( node = roleRevokeStatement() )
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=703298&r1=703297&r2=703298&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 Oct 9 17:20:12 2008
@@ -31,7 +31,6 @@
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
-import java.sql.CallableStatement;
import java.sql.Types;
import javax.sql.DataSource;
@@ -227,156 +226,6 @@
}
/**
- * 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:
- // 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;
- }
- }
-
-
- /**
* Tests whether or not indexes are exibiting their expected behaviour.
*
* @param s Statement
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/upgradeTests/Changes10_5.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/upgradeTests/Changes10_5.java?rev=703298&r1=703297&r2=703298&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/upgradeTests/Changes10_5.java (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/upgradeTests/Changes10_5.java Thu Oct 9 17:20:12 2008
@@ -22,8 +22,13 @@
import org.apache.derbyTesting.junit.SupportFilesSetup;
+import org.apache.derbyTesting.junit.JDBCDataSource;
import java.sql.SQLException;
import java.sql.Statement;
+import java.sql.Connection;
+import java.sql.CallableStatement;
+
+import javax.sql.DataSource;
import junit.framework.Test;
import junit.framework.TestSuite;
@@ -181,4 +186,211 @@
}
}
+ /**
+ * Check that you must be hard-upgraded to 10.5 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";
+
+ if (getOldMajor() == 10 && getOldMinor() == 4) {
+ // In 10.4 the roles commands were present but just gave "not
+ // implemented".
+ switch (getPhase()) {
+ case PH_CREATE:
+ assertStatementError("0A000", s, createRoleText );
+ break;
+
+ case PH_SOFT_UPGRADE:
+ // needs hard upgrade
+ assertStatementError("XCL47", s, createRoleText );
+ break;
+
+ case PH_POST_SOFT_UPGRADE:
+ assertStatementError("0A000", s, createRoleText );
+ break;
+
+ case PH_HARD_UPGRADE:
+ // not supported because SQL authorization not set
+ assertStatementError("42Z60", s, createRoleText );
+ break;
+ }
+
+ } else {
+ 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:
+ // not supported because SQL authorization not set
+ assertStatementError("42Z60", s, createRoleText );
+ break;
+ }
+ }
+
+
+ s.close();
+ }
+
+ /**
+ * Check that when hard-upgraded to 10.5 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, meaningfully use
+ // and drop roles. If so, we can presume SYS.SYSROLES has been upgraded
+ // correctly. If upgrading from 10.4, SYS.SYSROLES are already present,
+ // but roles were not activated, cf. test in POST_SOFT_UPGRADE.
+
+ DataSource ds = JDBCDataSource.getDataSourceLogical("ROLES_10_5");
+ 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();
+
+ cs.setString(1, "derby.user.jon");
+ cs.setString(2, "theOwner");
+ 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, "create role foo" );
+ conn.close();
+
+ JDBCDataSource.shutdownDatabase(ds);
+ }
+ break;
+
+ case PH_POST_SOFT_UPGRADE:
+ conn = ds.getConnection();
+ s = conn.createStatement();
+
+ if (getOldMajor() == 10 && getOldMinor() == 4) {
+ // not implemented
+ assertStatementError("0A000", s, "create role foo" );
+ } else {
+ // syntax error
+ assertStatementError("42X01", s, "create role foo" );
+ }
+
+ conn.close();
+
+ JDBCDataSource.shutdownDatabase(ds);
+ break;
+
+ case PH_HARD_UPGRADE:
+ JDBCDataSource.setBeanProperty(
+ ds, "connectionAttributes", "upgrade=true");
+ conn = ds.getConnection();
+ s = conn.createStatement();
+
+ // Roles should work; basic sanity test
+
+ // garfield is dbo
+ s.execute("create role foo");
+ s.execute("create table cats(specie varchar(30))");
+ s.execute("insert into cats " +
+ "values 'lynx', 'tiger', 'persian', 'garfield'");
+ s.execute("grant select on cats to foo");
+ s.execute("grant foo to jon");
+
+ // Connect as jon (not owner) and employ jon's newfound role
+ JDBCDataSource.clearStringBeanProperty(ds, "connectionAttributes");
+ JDBCDataSource.setBeanProperty(ds, "user", "jon");
+ JDBCDataSource.setBeanProperty(ds, "password", "theOwner");
+ Connection jon = ds.getConnection();
+
+ Statement jonStm = jon.createStatement();
+ // Still, no privilege available for poor jon..
+ assertStatementError
+ ("42502", jonStm, "select * from garfield.cats");
+
+ jonStm.execute("set role foo");
+ // Now, though:
+ jonStm.execute("select * from garfield.cats");
+ jonStm.close();
+ jon.close();
+
+ s.execute("drop table cats");
+ s.execute("drop role foo");
+ conn.close();
+
+ println("Roles work after hard upgrade");
+
+ // Owner garfield shuts down
+ JDBCDataSource.setBeanProperty(ds, "user", "garfield");
+ JDBCDataSource.setBeanProperty(ds, "password", "theCat");
+ JDBCDataSource.shutdownDatabase(ds);
+ break;
+ }
+ }
}
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=703298&r1=703297&r2=703298&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 Oct 9 17:20:12 2008
@@ -77,7 +77,7 @@
// based collation
new AdditionalDb("NO_ENCRYPT_10_2", true),
new AdditionalDb("ENCRYPT_10_2", true),
- new AdditionalDb("ROLES_10_4", false)
+ new AdditionalDb("ROLES_10_5", false)
};
static final String jarPath;
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/upgradeTests/_Suite.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/upgradeTests/_Suite.java?rev=703298&r1=703297&r2=703298&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/upgradeTests/_Suite.java (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/upgradeTests/_Suite.java Thu Oct 9 17:20:12 2008
@@ -115,6 +115,7 @@
{10, 2, 1, 6}, // 10.2.1.6 (Oct 02, 2006 / SVN 452058)
{10, 2, 2, 0}, // 10.2.2.0 (Dec 12, 2006 / SVN 485682)
{10, 3, 1, 4}, // 10.3.1.4 (Aug 1, 2007 / SVN 561794)
+ {10, 3, 3, 0}, // 10.3.3.0 (May 12, 2008 / SVN 652961)
{10, 4, 1, 3}, // 10.4.1.3 (April 24, 2008 / SVN 648739)
{10, 4, 2, 0}, // 10.4.2.0 (September 05, 2008 / SVN 693552)
};