/** * Check that even though we have set schema to a user schema, the metadata queries get run with * compilation schema as SYS. DERBY-2946 Test added for 10.4. * * @throws SQLException */ public void testMetaDataQueryRunInSYScompilationSchema() throws SQLException { // This test is for databases with territory based collation. That // feature was added in 10.3 codeline and hence there is no point in // doing any testing with pre-10.3 databases. if (!oldAtLeast(10, 3)) return; DataSource ds = JDBCDataSource.getDataSourceLogical("COLLATED_DB_10_3"); switch (getPhase()) { case PH_CREATE: // create the database if it was not already created. Note the // JDBC url attributes. JDBCDataSource.setBeanProperty( ds, "ConnectionAttributes", "create=true;territory=no;collation=TERRITORY_BASED"); ds.getConnection().close(); break; case PH_SOFT_UPGRADE: case PH_POST_SOFT_UPGRADE: case PH_HARD_UPGRADE: Connection con = ds.getConnection(); // First make the current schema as a user schema. And then run a // metadata query to make sure that it runs fine. If it does (which // is the expected behavior), then it will mean that the metadata // query is getting run with SYS as the compilation schema rather // than the current schema which is APP. Statement s = con.createStatement(); s.execute("SET SCHEMA APP"); DatabaseMetaData dmd = con.getMetaData(); ResultSet rs = dmd.getTables(null, "APP", null, null); JDBC.assertDrainResults(rs); s.close(); break; } }
/** * Check that when hard-upgraded to 10.4 or later SQL roles can be declared if DB has * sqlAuthorization. * * @throws SQLException */ public void disabledTill10_5_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, "gemfirexd.authentication.required"); cs.setString(2, "true"); cs.execute(); cs.setString(1, "gemfirexd.authentication.provider"); cs.setString(2, "BUILTIN"); cs.execute(); cs.setString(1, "gemfirexd.sql-authorization"); cs.setString(2, "true"); cs.execute(); cs.setString(1, "gemfirexd.distributedsystem.propertiesOnly"); cs.setString(2, "true"); cs.execute(); cs.setString(1, "gemfirexd.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; } }