| /* |
| |
| Derby - Class |
| org.apache.derbyTesting.functionTests.tests.jdbcapi.AuthenticationTest |
| |
| 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.jdbcapi; |
| |
| import java.security.MessageDigest; |
| import java.security.NoSuchAlgorithmException; |
| import java.sql.CallableStatement; |
| import java.sql.Connection; |
| import java.sql.PreparedStatement; |
| import java.sql.ResultSet; |
| import java.sql.SQLException; |
| import java.sql.Statement; |
| import java.util.HashSet; |
| import java.util.Locale; |
| import java.util.Properties; |
| import javax.sql.DataSource; |
| import junit.framework.Test; |
| import org.apache.derbyTesting.junit.BaseJDBCTestCase; |
| import org.apache.derbyTesting.junit.BaseTestSuite; |
| import org.apache.derbyTesting.junit.DatabasePropertyTestSetup; |
| import org.apache.derbyTesting.junit.JDBC; |
| import org.apache.derbyTesting.junit.JDBCDataSource; |
| import org.apache.derbyTesting.junit.SystemPropertyTestSetup; |
| import org.apache.derbyTesting.junit.TestConfiguration; |
| |
| /** |
| * Tests authentication and connection level authorization. |
| * |
| */ |
| public class AuthenticationTest extends BaseJDBCTestCase { |
| |
| private static final String PASSWORD_SUFFIX = "suf2ix"; |
| private static final String USERS[] = |
| {"APP","dan","kreg","jeff","ames","jerry","francois","jamie","howardR", |
| "\"eVe\"","\"fred@derby.com\"", "\"123\"" }; |
| |
| private static final String zeus = "\u0396\u0395\u03A5\u03A3"; |
| private static final String apollo = "\u0391\u09A0\u039F\u039B\u039B\u039A\u0390"; |
| |
| private static final String BUILTIN_ALGO_PROP = |
| "derby.authentication.builtin.algorithm"; |
| |
| private static final String BUILTIN_SALT_LENGTH_PROP = |
| "derby.authentication.builtin.saltLength"; |
| |
| private static final String BUILTIN_ITERATIONS_PROP = |
| "derby.authentication.builtin.iterations"; |
| |
| private static final String USER_PREFIX = "derby.user."; |
| |
| private static final String NO_SUCH_ALGO = "XBCXW"; |
| |
| /** Creates a new instance of the Test */ |
| public AuthenticationTest(String name) { |
| super(name); |
| } |
| |
| /** |
| * Ensure all connections are not in auto commit mode. |
| */ |
| protected void initializeConnection(Connection conn) throws SQLException { |
| conn.setAutoCommit(false); |
| } |
| |
| public static Test suite() { |
| BaseTestSuite suite = new BaseTestSuite("AuthenticationTest"); |
| suite.addTest(baseSuite("AuthenticationTest:embedded")); |
| suite.addTest(TestConfiguration.clientServerDecorator( |
| baseSuite("AuthenticationTest:client"))); |
| return suite; |
| } |
| |
| public static Test baseSuite(String name) { |
| BaseTestSuite suite = new BaseTestSuite("AuthenticationTest"); |
| |
| Test test = new AuthenticationTest( |
| "testConnectShutdownAuthentication"); |
| setBaseProps(suite, test); |
| |
| test = new AuthenticationTest("testUserCasing"); |
| setBaseProps(suite, test); |
| |
| test = new AuthenticationTest("testUserFunctions"); |
| setBaseProps(suite, test); |
| |
| test = new AuthenticationTest("testNotFullAccessUsers"); |
| setBaseProps(suite, test); |
| |
| test = new AuthenticationTest("testUserAccessRoutines"); |
| setBaseProps(suite, test); |
| |
| test = new AuthenticationTest( |
| "testChangePasswordAndDatabasePropertiesOnly"); |
| setBaseProps(suite, test); |
| |
| // only part of this fixture runs with network server / client |
| test = new AuthenticationTest("testGreekCharacters"); |
| setBaseProps(suite, test); |
| |
| test = new AuthenticationTest("testSystemShutdown"); |
| setBaseProps(suite, test); |
| |
| test = new AuthenticationTest("testDefaultHashAlgorithm"); |
| setBaseProps(suite, test); |
| |
| test = new AuthenticationTest("testDerby5507PlaintextPassword"); |
| setBaseProps(suite, test); |
| |
| // The test cases below test the configurable hash authentication |
| // mechanism added in DERBY-4483. Set the property that specifies the |
| // hash algorithm to some valid value for these tests. Not all tests |
| // depend on the property being set prior to their invocation, but by |
| // setting it in a decorator we ensure that it will be automatically |
| // cleared on tear down, so that it will be safe for all of these tests |
| // to change the property without worrying about resetting it later. |
| Properties confHashProps = new Properties(); |
| confHashProps.setProperty(BUILTIN_ALGO_PROP, "MD5"); |
| |
| test = new AuthenticationTest("testVariousBuiltinAlgorithms"); |
| setBaseProps(suite, test, confHashProps); |
| |
| test = new AuthenticationTest("testNoCollisionsWithConfigurableHash"); |
| setBaseProps(suite, test, confHashProps); |
| |
| test = new AuthenticationTest("testInvalidAlgorithmName"); |
| setBaseProps(suite, test, confHashProps); |
| |
| // This test needs to run in a new single use database as we're setting |
| // a number of properties |
| return TestConfiguration.singleUseDatabaseDecorator(suite); |
| } |
| |
| protected static void setBaseProps(BaseTestSuite suite, Test test) |
| { |
| setBaseProps(suite, test, null); |
| } |
| |
| private static void setBaseProps( |
| BaseTestSuite suite, Test test, Properties extraDbProps) |
| { |
| // Use DatabasePropertyTestSetup.builtinAuthentication decorator |
| // to set the user properties required by this test (and shutdown |
| // the database for the property to take effect). |
| // DatabasePropertyTestSetup uses SYSCS_SET_DATABASE_PROPERTY |
| // so that is database level setting. |
| // Additionally use DatabasePropertyTestSetup to add some |
| // possibly useful settings |
| // Finally SystemPropertyTestSetup sets up system level users |
| Properties props = new Properties(); |
| props.setProperty("derby.infolog.append", "true"); |
| props.setProperty("derby.debug.true", "AuthenticationTrace"); |
| |
| if (extraDbProps != null) { |
| props.putAll(extraDbProps); |
| } |
| |
| Properties sysprops = new Properties(); |
| sysprops.put("derby.user.system", "admin"); |
| sysprops.put("derby.user.mickey", "mouse"); |
| test = DatabasePropertyTestSetup.builtinAuthentication(test, |
| USERS, PASSWORD_SUFFIX); |
| test = new DatabasePropertyTestSetup (test, props, true); |
| suite.addTest(new SystemPropertyTestSetup (test, sysprops)); |
| } |
| |
| protected void setUp() throws Exception { |
| |
| setDatabaseProperty("derby.database.defaultConnectionMode", |
| null, getConnection()); |
| setDatabaseProperty("derby.database.readOnlyAccessUsers", |
| null, getConnection()); |
| setDatabaseProperty("derby.database.fullAccessUsers", |
| null, getConnection()); |
| |
| commit(); |
| |
| } |
| |
| protected void tearDown() throws Exception { |
| removeSystemProperty("derby.connection.requireAuthentication"); |
| removeSystemProperty("derby.user." +apollo); |
| super.tearDown(); |
| } |
| |
| /** |
| * Test how user names behave with casing. |
| * @throws SQLException |
| */ |
| public void testUserCasing() throws SQLException |
| { |
| for (int i = 0; i < USERS.length; i++) |
| { |
| String jdbcUserName = USERS[i]; |
| boolean delimited = jdbcUserName.charAt(0) == '"'; |
| String normalUserName; |
| if (delimited) |
| { |
| normalUserName = jdbcUserName.substring(1, |
| jdbcUserName.length() - 1); |
| } |
| else |
| { |
| normalUserName = jdbcUserName.toUpperCase(Locale.ENGLISH); |
| } |
| |
| String password = USERS[i] + PASSWORD_SUFFIX; |
| |
| userCasingTest(jdbcUserName, normalUserName, password); |
| |
| if (!delimited) |
| { |
| |
| if (!normalUserName.equals(jdbcUserName)) |
| { |
| // Test connecting via the normalized name |
| // but only if it wasn't already tested. |
| // E.g. connect as "DAN" for user DAN as opposed |
| // to the user being defined as dan (regular identifier). |
| |
| // DERBY-3150 disable this test until bug is fixed. |
| //userCasingTest(normalUserName, normalUserName, password); |
| } |
| |
| // Test with the normalized name quoted as a delimited identifer. |
| // E.g. connect as "DAN" for user DAN |
| |
| // DERBY-3150 disable this test until bug is fixed. |
| // userCasingTest("\"" + normalUserName + "\"", |
| // normalUserName, password); |
| } |
| |
| } |
| |
| // Now test that setting the user connection authorizaton |
| // with the various names works correctly. Use the first user |
| // to set the access on others to avoid setting a user to read-only |
| // and then not being able to reset it. |
| |
| PreparedStatement psGetAccess = prepareStatement( |
| "VALUES SYSCS_UTIL.SYSCS_GET_USER_ACCESS(?)"); |
| CallableStatement csSetAccess = prepareCall( |
| "CALL SYSCS_UTIL.SYSCS_SET_USER_ACCESS(?, ?)"); |
| |
| setDatabaseProperty("derby.database.fullAccessUsers", |
| USERS[0], getConnection()); |
| setDatabaseProperty("derby.database.readOnlyAccessUsers", |
| null, getConnection()); |
| commit(); |
| |
| // Yes - skip the first user, see above. |
| for (int i = 1; i < USERS.length; i++) |
| { |
| String jdbcUserName = USERS[i]; |
| |
| boolean delimited = jdbcUserName.charAt(0) == '"'; |
| String normalUserName; |
| if (delimited) |
| { |
| normalUserName = jdbcUserName.substring(1, |
| jdbcUserName.length() - 1); |
| } |
| else |
| { |
| normalUserName = jdbcUserName.toUpperCase(Locale.ENGLISH); |
| } |
| |
| String password = USERS[i] + PASSWORD_SUFFIX; |
| |
| // Set the access with the database property |
| setDatabaseProperty("derby.database.readOnlyAccessUsers", |
| jdbcUserName, getConnection()); |
| commit(); |
| |
| |
| Connection connUser = openDefaultConnection(jdbcUserName, password); |
| |
| // DERBY-2738 (network client always returns false for isReadOnly) |
| if (usingEmbedded()) |
| assertTrue(jdbcUserName + ":isReadOnly()", |
| connUser.isReadOnly()); |
| |
| |
| connUser.close(); |
| |
| psGetAccess.setString(1, normalUserName); |
| JDBC.assertSingleValueResultSet(psGetAccess.executeQuery(), |
| "READONLYACCESS"); |
| commit(); |
| |
| // clear the property. |
| setDatabaseProperty("derby.database.readOnlyAccessUsers", |
| null, getConnection()); |
| commit(); |
| |
| // Test it was reset back |
| connUser = openDefaultConnection(jdbcUserName, password); |
| assertFalse(connUser.isReadOnly()); |
| connUser.close(); |
| |
| psGetAccess.setString(1, normalUserName); |
| JDBC.assertSingleValueResultSet(psGetAccess.executeQuery(), |
| "FULLACCESS"); |
| commit(); |
| |
| |
| // Set to be read-only via the procedure which uses |
| // the normal user name. |
| csSetAccess.setString(1, normalUserName); |
| csSetAccess.setString(2, "READONLYACCESS"); |
| csSetAccess.executeUpdate(); |
| commit(); |
| |
| connUser = openDefaultConnection(jdbcUserName, password); |
| // DERBY-2738 (network client always returns false for isReadOnly) |
| if (usingEmbedded()) |
| assertTrue(jdbcUserName + ":isReadOnly()", |
| connUser.isReadOnly()); |
| |
| connUser.close(); |
| |
| psGetAccess.setString(1, normalUserName); |
| JDBC.assertSingleValueResultSet(psGetAccess.executeQuery(), |
| "READONLYACCESS"); |
| commit(); |
| |
| } |
| } |
| |
| /** |
| * Test the user casing obtaining connections a variety of ways. |
| * @param jdbcUserName User name to be used to obtain the connection via JDBC |
| * @param normalUserName Normalized form of the user connection. |
| * @param password Password for the user. |
| * @throws SQLException |
| */ |
| private void userCasingTest(String jdbcUserName, String normalUserName, |
| String password) throws SQLException |
| { |
| // Default test mechanism to get a connection. |
| userCasingTest(jdbcUserName, normalUserName, |
| openDefaultConnection(jdbcUserName, password)); |
| |
| |
| DataSource ds = JDBCDataSource.getDataSource(); |
| |
| // DataSource using explict user |
| userCasingTest(jdbcUserName, normalUserName, |
| ds.getConnection(jdbcUserName, password)); |
| |
| JDBCDataSource.setBeanProperty(ds, "user", jdbcUserName); |
| JDBCDataSource.setBeanProperty(ds, "password", password); |
| userCasingTest(jdbcUserName, normalUserName, |
| ds.getConnection()); |
| } |
| |
| /** |
| * |
| * @param jdbcUserName User name as passed into the JDBC connection request. |
| * @param normalUserName Normalized user name. |
| * @param connUser Connection for the user, closed by this method. |
| * @throws SQLException |
| */ |
| private void userCasingTest(String jdbcUserName, String normalUserName, |
| Connection connUser) throws SQLException |
| { |
| assertNormalUserName(normalUserName, connUser); |
| |
| // DatabaseMetaData.getUserName() returns the user name used |
| // to make the request via JDBC. |
| assertEquals("DatabaseMetaData.getUserName()", |
| jdbcUserName, connUser.getMetaData().getUserName()); |
| |
| |
| Statement s = connUser.createStatement(); |
| |
| s.executeUpdate("CALL SYSCS_UTIL.SYSCS_SET_USER_ACCESS(" + |
| "CURRENT_USER, 'FULLACCESS')"); |
| |
| s.close(); |
| |
| JDBC.cleanup(connUser); |
| } |
| |
| /** |
| * Assert that the user name returned by various mechanisms |
| * matches the normal user name. |
| * @param normalUserName |
| * @param connUser |
| * @throws SQLException |
| */ |
| private void assertNormalUserName(String normalUserName, Connection connUser) |
| throws SQLException |
| { |
| Statement s = connUser.createStatement(); |
| |
| JDBC.assertSingleValueResultSet(s.executeQuery("VALUES CURRENT_USER"), |
| normalUserName); |
| JDBC.assertSingleValueResultSet(s.executeQuery("VALUES SESSION_USER"), |
| normalUserName); |
| JDBC.assertSingleValueResultSet(s.executeQuery("VALUES {fn user()}"), |
| normalUserName); |
| s.close(); |
| |
| } |
| |
| |
| // roughly based on old functionTests test users.sql, except that |
| // test used 2 databases. Possibly that was on the off-chance that |
| // a second database would not work correctly - but that will not |
| // be tested now. |
| public void testConnectShutdownAuthentication() throws SQLException { |
| |
| String dbName = TestConfiguration.getCurrent().getDefaultDatabaseName(); |
| |
| // check connections while fullAccess (default) is set |
| // note that builtinAuthentication has been set, as well as |
| // authentication=true. |
| |
| // first try connection without user password |
| assertConnectionFail(dbName); |
| assertConnectionOK(dbName, "system", ("admin")); |
| assertConnectionWOUPOK(dbName, "system", ("admin")); |
| assertConnectionOK(dbName, "dan", ("dan" + PASSWORD_SUFFIX)); |
| assertConnectionWOUPOK(dbName, "dan", ("dan" + PASSWORD_SUFFIX)); |
| // try shutdown as non-owner |
| assertShutdownOK(dbName, "dan", ("dan" + PASSWORD_SUFFIX)); |
| assertConnectionOK(dbName, "system", ("admin")); |
| assertShutdownWOUPOK(dbName, "dan", ("dan" + PASSWORD_SUFFIX)); |
| assertConnectionOK(dbName, "system", ("admin")); |
| assertShutdownOK(dbName, "system", "admin"); |
| assertConnectionOK(dbName, "system", ("admin")); |
| assertShutdownWOUPOK(dbName, "system", "admin"); |
| assertConnectionOK(dbName, "system", ("admin")); |
| // try shutdown as owner |
| assertShutdownUsingConnAttrsOK(dbName, "APP", ("APP" + PASSWORD_SUFFIX)); |
| |
| // ensure that a password is encrypted |
| Connection conn1 = openDefaultConnection( |
| "dan", ("dan" + PASSWORD_SUFFIX)); |
| Statement stmt = conn1.createStatement(); |
| ResultSet rs = stmt.executeQuery( |
| "values SYSCS_UTIL.SYSCS_GET_DATABASE_PROPERTY('derby.user.dan')"); |
| rs.next(); |
| assertNotSame(("dan"+PASSWORD_SUFFIX), rs.getString(1)); |
| conn1.commit(); |
| conn1.close(); |
| |
| // specify full-access users. |
| conn1 = openDefaultConnection("dan", ("dan" + PASSWORD_SUFFIX)); |
| setDatabaseProperty( |
| "derby.database.fullAccessUsers", |
| "APP,system,nomen,francois,jeff", conn1); |
| setDatabaseProperty( |
| "derby.database.defaultConnectionMode","NoAccess", conn1); |
| conn1.commit(); |
| conn1.close(); |
| |
| // check the system wide user |
| assertConnectionOK(dbName, "system", "admin"); |
| // check the non-existent, but allowed user |
| assertConnectionFail("08004", dbName, "nomen", "nescio"); |
| assertConnectionWOUPFail("08004", dbName, "nomen", "nescio"); |
| // attempt to shutdown db as one of the allowed users, but not db owner |
| assertShutdownOK(dbName, "francois", ("francois" + PASSWORD_SUFFIX)); |
| // attempt shutdown as db owner |
| assertConnectionOK(dbName, "system", "admin"); |
| assertShutdownWOUPOK(dbName, "APP", ("APP" + PASSWORD_SUFFIX)); |
| // check simple connect ok as another allowed user, also revive db |
| assertConnectionOK(dbName, "jeff", ("jeff" + PASSWORD_SUFFIX)); |
| // but dan wasn't on the list |
| assertConnectionFail("08004", dbName, "dan", ("dan" + PASSWORD_SUFFIX)); |
| assertShutdownFail("08004", dbName, "dan", ("dan" + PASSWORD_SUFFIX)); |
| |
| // now change fullAccessUsers & test again |
| conn1 = |
| openDefaultConnection("francois", ("francois" + PASSWORD_SUFFIX)); |
| setDatabaseProperty("derby.database.fullAccessUsers", |
| "jeff,dan,francois,jamie", conn1); |
| conn1.commit(); |
| conn1.close(); |
| assertConnectionOK(dbName, "dan", ("dan" + PASSWORD_SUFFIX)); |
| assertShutdownOK(dbName, "dan", ("dan" + PASSWORD_SUFFIX)); |
| assertConnectionOK(dbName, "dan", ("dan" + PASSWORD_SUFFIX)); |
| // but dbo was not on list... |
| assertShutdownFail("08004", dbName, "APP", ("APP" + PASSWORD_SUFFIX)); |
| // now add dbo back in... |
| conn1 = openDefaultConnection("francois", ("francois" + PASSWORD_SUFFIX)); |
| setDatabaseProperty( |
| "derby.database.defaultConnectionMode","NoAccess", conn1); |
| setDatabaseProperty( |
| "derby.database.fullAccessUsers", |
| "APP,jeff,dan,francois,jamie", conn1); |
| conn1.commit(); |
| conn1.close(); |
| |
| // Invalid logins |
| // bad user |
| assertConnectionFail("08004", dbName, "badUser", "badPwd"); |
| // just checking that it's still not working if we try again |
| assertConnectionFail("08004", dbName, "badUser", "badPwd"); |
| // system is not on the list... |
| assertConnectionFail("08004", dbName, "system", "admin"); |
| // dan's on the list, but this isn't the pwd |
| assertConnectionFail("08004", dbName, "dan", "badPwd"); |
| assertConnectionFail("08004", dbName, "jamie", ("dan" + PASSWORD_SUFFIX)); |
| // check some shutdowns |
| assertShutdownFail("08004", dbName, "system", "admin"); |
| assertShutdownFail("08004", dbName, "badUser", "badPwd"); |
| assertShutdownFail("08004", dbName, "dan", "badPwd"); |
| assertShutdownFail("08004", dbName, "badUser", ("dan" + PASSWORD_SUFFIX)); |
| |
| // try some system shutdowns. Note, that all these work, because |
| // we have not set require authentication at system level. |
| // try system shutdown with wrong user - should work |
| assertSystemShutdownOK("", "badUser", ("dan" + PASSWORD_SUFFIX)); |
| openDefaultConnection("dan", ("dan" + PASSWORD_SUFFIX)).close(); // revive |
| // with 'allowed' user but bad pwd - will succeed |
| assertSystemShutdownOK("", "dan", ("jeff" + PASSWORD_SUFFIX)); |
| openDefaultConnection("dan", ("dan" + PASSWORD_SUFFIX)).close(); // revive |
| // dbo, but bad pwd - will succeed |
| assertSystemShutdownOK("", "APP", ("POO")); |
| openDefaultConnection("dan", ("dan" + PASSWORD_SUFFIX)).close(); // revive |
| // allowed user but not dbo - will also succeed |
| assertSystemShutdownOK("", "dan", ("dan" + PASSWORD_SUFFIX)); |
| openDefaultConnection("dan", ("dan" + PASSWORD_SUFFIX)).close(); // revive |
| // expect Derby system shutdown, which gives XJ015 error. |
| assertSystemShutdownOK("", "APP", ("APP" + PASSWORD_SUFFIX)); |
| |
| // so far so good. set back security properties |
| conn1 = openDefaultConnection("dan", ("dan" + PASSWORD_SUFFIX)); |
| setDatabaseProperty( |
| "derby.database.defaultConnectionMode","fullAccess", conn1); |
| setDatabaseProperty( |
| "derby.connection.requireAuthentication","false", conn1); |
| conn1.commit(); |
| stmt.close(); |
| conn1.close(); |
| } |
| |
| // Experiment using USER, CURRENT_USER, etc. |
| // also tests actual write activity |
| public void testUserFunctions() throws SQLException |
| { |
| // use valid user/pwd to set the full accessusers. |
| Connection conn1 = openDefaultConnection( |
| "dan", ("dan" + PASSWORD_SUFFIX)); |
| setDatabaseProperty( |
| "derby.database.fullAccessUsers", |
| "francois,jeff,ames,jerry,jamie,dan,system", conn1); |
| setDatabaseProperty( |
| "derby.database.defaultConnectionMode","NoAccess", conn1); |
| conn1.commit(); |
| |
| // we should still be connected as dan |
| Statement stmt = conn1.createStatement(); |
| assertUpdateCount(stmt, 0, |
| "create table APP.t1(c1 varchar(30) check (UPPER(c1) <> 'JAMIE'))"); |
| assertUpdateCount(stmt, 1, "insert into APP.t1 values USER"); |
| |
| conn1.commit(); |
| stmt.close(); |
| conn1.close(); |
| |
| useUserValue(1, "jeff", "insert into APP.t1 values CURRENT_USER"); |
| useUserValue(1, "ames", "insert into APP.t1 values SESSION_USER"); |
| useUserValue(1, "jerry", "insert into APP.t1 values {fn user()}"); |
| assertUserValue(new String[] {"DAN","JEFF","AMES","JERRY"}, |
| "dan", "select * from APP.t1"); |
| // attempt some usage in where clause |
| useUserValue(1, |
| "dan", "update APP.t1 set c1 = 'edward' where c1 = USER"); |
| assertUserValue(new String[] {"JEFF"},"jeff", |
| "select * from APP.t1 where c1 like CURRENT_USER"); |
| useUserValue(1, "ames", |
| "update APP.t1 set c1 = 'sema' where SESSION_USER = c1"); |
| useUserValue(1, "jerry", |
| "update APP.t1 set c1 = 'yrrej' where c1 like {fn user()}"); |
| assertUserValue(new String[] {"edward","JEFF","sema","yrrej"}, |
| "dan", "select * from APP.t1"); |
| useUserValue(4, "francois", "update APP.T1 set c1 = USER"); |
| assertUserValue( |
| new String[] {"FRANCOIS","FRANCOIS","FRANCOIS","FRANCOIS"}, |
| "dan", "select * from APP.t1"); |
| |
| // check that attempt to insert 'jamie' gives a check violation |
| conn1 = openDefaultConnection("jamie", ("jamie" + PASSWORD_SUFFIX)); |
| stmt = conn1.createStatement(); |
| try { |
| stmt.execute("insert into APP.t1 values CURRENT_USER"); |
| } catch (SQLException sqle) { |
| assertSQLState("23513", sqle); |
| } |
| stmt.close(); |
| conn1.rollback(); |
| conn1.close(); |
| |
| // Note: there is not much point in attempting to write with an invalid |
| // user, that's already tested in the testConnectionShutdown fixture |
| |
| // reset |
| conn1 = openDefaultConnection("dan", ("dan" + PASSWORD_SUFFIX)); |
| setDatabaseProperty( |
| "derby.database.defaultConnectionMode","fullAccess", conn1); |
| setDatabaseProperty( |
| "derby.connection.requireAuthentication","false", conn1); |
| stmt = conn1.createStatement(); |
| assertUpdateCount(stmt, 0, "drop table APP.t1"); |
| conn1.commit(); |
| stmt.close(); |
| conn1.close(); |
| } |
| |
| public void testChangePasswordAndDatabasePropertiesOnly() |
| throws SQLException |
| { |
| String dbName = TestConfiguration.getCurrent().getDefaultDatabaseName(); |
| |
| // use valid user/pwd to set the full accessusers. |
| Connection conn1 = openDefaultConnection( |
| "dan", ("dan" + PASSWORD_SUFFIX)); |
| setDatabaseProperty("derby.database.fullAccessUsers", |
| "dan,jeff,system", conn1); |
| setDatabaseProperty( |
| "derby.database.defaultConnectionMode","NoAccess", conn1); |
| setDatabaseProperty( |
| "derby.database.requireAuthentication","true", conn1); |
| |
| conn1.commit(); |
| |
| // check the system wide user |
| assertConnectionOK(dbName, "system", "admin"); |
| assertConnectionFail("08004", dbName, "system", "otherSysPwd"); |
| assertConnectionOK(dbName, "jeff", ("jeff" + PASSWORD_SUFFIX)); |
| assertConnectionFail("08004", dbName, "jeff", "otherPwd"); |
| setDatabaseProperty("derby.user.jeff", "otherPwd", conn1); |
| conn1.commit(); |
| // should have changed ok. |
| assertConnectionOK(dbName, "jeff", "otherPwd"); |
| |
| // note: if we do this: |
| // setDatabaseProperty("derby.user.system", "scndSysPwd", conn1); |
| // conn1.commit(); |
| // i.e. adding the same user (but different pwd) at database level, |
| // then we cannot connect anymore using that user name, not with |
| // either password. |
| |
| // force database props only |
| setDatabaseProperty( |
| "derby.database.propertiesOnly","true", conn1); |
| conn1.commit(); |
| |
| // now, should not be able to logon as system user |
| assertConnectionFail("08004", dbName, "system", "admin"); |
| |
| // reset propertiesOnly |
| setDatabaseProperty( |
| "derby.database.propertiesOnly","false", conn1); |
| conn1.commit(); |
| conn1.close(); |
| assertConnectionOK(dbName, "system", "admin"); |
| |
| // try changing system's pwd |
| setSystemProperty("derby.user.system", "thrdSysPwd"); |
| |
| // can we get in as system user with changed pwd |
| assertConnectionOK(dbName, "system", "thrdSysPwd"); |
| |
| // reset |
| // first change system's pwd back |
| setSystemProperty("derby.user.system", "admin"); |
| |
| conn1 = openDefaultConnection("dan", ("dan" + PASSWORD_SUFFIX)); |
| setDatabaseProperty( |
| "derby.database.defaultConnectionMode","fullAccess", conn1); |
| setDatabaseProperty( |
| "derby.connection.requireAuthentication","false", conn1); |
| setDatabaseProperty( |
| "derby.database.propertiesOnly","false", conn1); |
| conn1.commit(); |
| conn1.close(); |
| } |
| |
| public void testNotFullAccessUsers() throws SQLException |
| { |
| // use valid user/pwd to set the full accessusers. |
| Connection conn1 = openDefaultConnection( |
| "dan", ("dan" + PASSWORD_SUFFIX)); |
| |
| // Test duplicates on the list of users |
| try { |
| setDatabaseProperty("derby.database.fullAccessUsers", |
| "dan,jamie,dan", conn1); |
| fail("Duplicate allowed on derby.database.fullAccessUsers"); |
| } catch (SQLException e) { |
| assertSQLState("4250D", e); |
| } |
| try { |
| setDatabaseProperty("derby.database.fullAccessUsers", |
| "dan,jamie,DaN", conn1); |
| fail("Duplicate allowed on derby.database.fullAccessUsers"); |
| } catch (SQLException e) { |
| assertSQLState("4250D", e); |
| } |
| try { |
| setDatabaseProperty("derby.database.fullAccessUsers", |
| "dan,jamie,\"DAN\"", conn1); |
| fail("Duplicate allowed on derby.database.fullAccessUsers"); |
| } catch (SQLException e) { |
| assertSQLState("4250D", e); |
| } |
| try { |
| setDatabaseProperty("derby.database.fullAccessUsers", |
| "\"dan\",jamie,\"dan\"", conn1); |
| fail("Duplicate allowed on derby.database.fullAccessUsers"); |
| } catch (SQLException e) { |
| assertSQLState("4250D", e); |
| } |
| |
| try { |
| setDatabaseProperty("derby.database.readOnlyAccessUsers", |
| "dan,jamie,dan", conn1); |
| fail("Duplicate allowed on derby.database.readOnlyAccessUsers"); |
| } catch (SQLException e) { |
| assertSQLState("4250D", e); |
| } |
| try { |
| setDatabaseProperty("derby.database.readOnlyAccessUsers", |
| "dan,jamie,DaN", conn1); |
| fail("Duplicate allowed on derby.database.readOnlyAccessUsers"); |
| } catch (SQLException e) { |
| assertSQLState("4250D", e); |
| } |
| try { |
| setDatabaseProperty("derby.database.readOnlyAccessUsers", |
| "dan,jamie,\"DAN\"", conn1); |
| fail("Duplicate allowed on derby.database.readOnlyAccessUsers"); |
| } catch (SQLException e) { |
| assertSQLState("4250D", e); |
| } |
| try { |
| setDatabaseProperty("derby.database.readOnlyAccessUsers", |
| "\"dan\",jamie,\"dan\"", conn1); |
| fail("Duplicate allowed on derby.database.readOnlyAccessUsers"); |
| } catch (SQLException e) { |
| assertSQLState("4250D", e); |
| } |
| |
| setDatabaseProperty("derby.database.fullAccessUsers", |
| "dan,jamie,system", conn1); |
| // cannot set a user to both full and readonly access... |
| assertFailSetDatabaseProperty( |
| "derby.database.readOnlyAccessUsers", "jamie", conn1); |
| setDatabaseProperty( |
| "derby.database.readOnlyAccessUsers", "ames,mickey", conn1); |
| setDatabaseProperty( |
| "derby.database.defaultConnectionMode","NoAccess", conn1); |
| setDatabaseProperty( |
| "derby.database.requireAuthentication","true", conn1); |
| conn1.commit(); |
| |
| |
| PreparedStatement psGetAccess = conn1.prepareStatement( |
| "VALUES SYSCS_UTIL.SYSCS_GET_USER_ACCESS(?)"); |
| psGetAccess.setString(1, "JAMIE"); |
| JDBC.assertSingleValueResultSet(psGetAccess.executeQuery(), "FULLACCESS"); |
| |
| psGetAccess.setString(1, "DAN"); |
| JDBC.assertSingleValueResultSet(psGetAccess.executeQuery(), "FULLACCESS"); |
| |
| psGetAccess.setString(1, "SYSTEM"); |
| JDBC.assertSingleValueResultSet(psGetAccess.executeQuery(), "FULLACCESS"); |
| |
| psGetAccess.setString(1, "AMES"); |
| JDBC.assertSingleValueResultSet(psGetAccess.executeQuery(), "READONLYACCESS"); |
| |
| psGetAccess.setString(1, "MICKEY"); |
| JDBC.assertSingleValueResultSet(psGetAccess.executeQuery(), "READONLYACCESS"); |
| |
| // unknown user |
| psGetAccess.setString(1, "hagrid"); |
| JDBC.assertSingleValueResultSet(psGetAccess.executeQuery(), "NOACCESS"); |
| |
| conn1.commit(); |
| |
| // now add/switch some names using the utility method |
| CallableStatement csSetAccess = conn1.prepareCall( |
| "CALL SYSCS_UTIL.SYSCS_SET_USER_ACCESS(?, ?)"); |
| |
| // Change AMES, everyone else is unchanged |
| csSetAccess.setString(1, "AMES"); |
| csSetAccess.setString(2, "FULLACCESS"); |
| csSetAccess.execute(); |
| |
| psGetAccess.setString(1, "AMES"); |
| JDBC.assertSingleValueResultSet(psGetAccess.executeQuery(), "FULLACCESS"); |
| |
| psGetAccess.setString(1, "MICKEY"); |
| JDBC.assertSingleValueResultSet(psGetAccess.executeQuery(), "READONLYACCESS"); |
| psGetAccess.setString(1, "JAMIE"); |
| JDBC.assertSingleValueResultSet(psGetAccess.executeQuery(), "FULLACCESS"); |
| psGetAccess.setString(1, "DAN"); |
| JDBC.assertSingleValueResultSet(psGetAccess.executeQuery(), "FULLACCESS"); |
| psGetAccess.setString(1, "SYSTEM"); |
| JDBC.assertSingleValueResultSet(psGetAccess.executeQuery(), "FULLACCESS"); |
| |
| // and change AMES back again |
| csSetAccess.setString(1, "AMES"); |
| csSetAccess.setString(2, "READONLYACCESS"); |
| csSetAccess.execute(); |
| |
| psGetAccess.setString(1, "AMES"); |
| JDBC.assertSingleValueResultSet(psGetAccess.executeQuery(), "READONLYACCESS"); |
| |
| psGetAccess.setString(1, "MICKEY"); |
| JDBC.assertSingleValueResultSet(psGetAccess.executeQuery(), "READONLYACCESS"); |
| psGetAccess.setString(1, "JAMIE"); |
| JDBC.assertSingleValueResultSet(psGetAccess.executeQuery(), "FULLACCESS"); |
| psGetAccess.setString(1, "DAN"); |
| JDBC.assertSingleValueResultSet(psGetAccess.executeQuery(), "FULLACCESS"); |
| psGetAccess.setString(1, "SYSTEM"); |
| JDBC.assertSingleValueResultSet(psGetAccess.executeQuery(), "FULLACCESS"); |
| |
| // add a new users in |
| csSetAccess.setString(1, "BOND"); |
| csSetAccess.setString(2, "FULLACCESS"); |
| csSetAccess.execute(); |
| csSetAccess.setString(1, "JAMES"); |
| csSetAccess.setString(2, "READONLYACCESS"); |
| csSetAccess.execute(); |
| conn1.commit(); |
| |
| psGetAccess.setString(1, "BOND"); |
| JDBC.assertSingleValueResultSet(psGetAccess.executeQuery(), "FULLACCESS"); |
| psGetAccess.setString(1, "JAMES"); |
| JDBC.assertSingleValueResultSet(psGetAccess.executeQuery(), "READONLYACCESS"); |
| conn1.commit(); |
| |
| // and remove them |
| csSetAccess.setString(1, "BOND"); |
| csSetAccess.setString(2, null); |
| csSetAccess.execute(); |
| csSetAccess.setString(1, "JAMES"); |
| csSetAccess.setString(2, null); |
| csSetAccess.execute(); |
| conn1.commit(); |
| |
| psGetAccess.setString(1, "BOND"); |
| JDBC.assertSingleValueResultSet(psGetAccess.executeQuery(), "NOACCESS"); |
| psGetAccess.setString(1, "JAMES"); |
| JDBC.assertSingleValueResultSet(psGetAccess.executeQuery(), "NOACCESS"); |
| conn1.commit(); |
| |
| |
| psGetAccess.close(); |
| csSetAccess.close(); |
| |
| |
| // we should still be connected as dan |
| Statement stmt = conn1.createStatement(); |
| assertUpdateCount(stmt, 0, |
| "create table APP.t1(c1 varchar(30) check (UPPER(c1) <> 'JAMIE'))"); |
| assertUpdateCount(stmt, 1, "insert into APP.t1 values USER"); |
| |
| conn1.commit(); |
| stmt.close(); |
| conn1.close(); |
| |
| // check full access system level user can update |
| conn1 = openDefaultConnection("system", "admin"); |
| stmt = conn1.createStatement(); |
| assertUpdateCount(stmt, 1, "update APP.t1 set c1 = USER"); |
| conn1.commit(); |
| stmt.close(); |
| conn1.close(); |
| |
| // read only users |
| assertUserValue(new String[] {"SYSTEM"},"ames", |
| "select * from APP.t1"); // should succeed |
| conn1 = openDefaultConnection("ames", ("ames"+PASSWORD_SUFFIX)); |
| |
| // DERBY-2738 (network client always returns false for isReadOnly) |
| if (usingEmbedded()) |
| assertTrue(conn1.isReadOnly()); |
| stmt = conn1.createStatement(); |
| assertStatementError( |
| "25502", stmt, "delete from APP.t1 where c1 = 'SYSTEM'"); |
| assertStatementError("25502", stmt, "insert into APP.t1 values USER"); |
| assertStatementError( |
| "25502", stmt, "update APP.t1 set c1 = USER where c1 = 'SYSTEM'"); |
| assertStatementError("25503", stmt, "create table APP.t2 (c1 int)"); |
| conn1.commit(); |
| stmt.close(); |
| conn1.close(); |
| |
| // read-only system level user |
| conn1 = openDefaultConnection("mickey", "mouse"); |
| // DERBY-2738 (network client always returns false for isReadOnly) |
| if (usingEmbedded()) |
| assertTrue(conn1.isReadOnly()); |
| stmt = conn1.createStatement(); |
| assertStatementError( |
| "25502", stmt, "delete from APP.t1 where c1 = 'SYSTEM'"); |
| conn1.rollback(); |
| conn1.close(); |
| |
| // reset |
| conn1 = openDefaultConnection("dan", ("dan" + PASSWORD_SUFFIX)); |
| setDatabaseProperty( |
| "derby.database.defaultConnectionMode","fullAccess", conn1); |
| setDatabaseProperty( |
| "derby.connection.requireAuthentication","false", conn1); |
| stmt = conn1.createStatement(); |
| assertUpdateCount(stmt, 0, "drop table APP.t1"); |
| conn1.commit(); |
| stmt.close(); |
| conn1.close(); |
| } |
| |
| /** |
| * Test the procedure and function that provide short-cuts |
| * to setting and getting connection level access. |
| * @throws SQLException |
| */ |
| public void testUserAccessRoutines() throws SQLException |
| { |
| // use valid user/pwd to set the full accessusers. |
| Connection conn1 = openDefaultConnection( |
| "dan", ("dan" + PASSWORD_SUFFIX)); |
| |
| PreparedStatement psGetAccess = conn1.prepareStatement( |
| "VALUES SYSCS_UTIL.SYSCS_GET_USER_ACCESS(?)"); |
| CallableStatement csSetAccess = conn1.prepareCall( |
| "CALL SYSCS_UTIL.SYSCS_SET_USER_ACCESS(?, ?)"); |
| |
| csSetAccess.setString(1, "DAN"); |
| csSetAccess.setString(2, "FULLACCESS"); |
| csSetAccess.execute(); |
| |
| // Invalid users |
| csSetAccess.setString(1, null); |
| csSetAccess.setString(2, "FULLACCESS"); |
| assertStatementError("28502", csSetAccess); |
| |
| |
| // Random user will now have only READONLYACCESS |
| setDatabaseProperty( |
| "derby.database.defaultConnectionMode","READONLYACCESS", conn1); |
| conn1.commit(); |
| psGetAccess.setString(1, "TONYBLAIR"); |
| JDBC.assertSingleValueResultSet(psGetAccess.executeQuery(), "READONLYACCESS"); |
| conn1.commit(); |
| |
| // Random user will now have FULLACCESS |
| setDatabaseProperty( |
| "derby.database.defaultConnectionMode","FULLACCESS", conn1); |
| conn1.commit(); |
| psGetAccess.setString(1, "TONYBLAIR"); |
| JDBC.assertSingleValueResultSet(psGetAccess.executeQuery(), "FULLACCESS"); |
| conn1.commit(); |
| |
| // and still full access |
| setDatabaseProperty( |
| "derby.database.defaultConnectionMode", null, conn1); |
| conn1.commit(); |
| psGetAccess.setString(1, "TONYBLAIR"); |
| JDBC.assertSingleValueResultSet(psGetAccess.executeQuery(), "FULLACCESS"); |
| conn1.commit(); |
| |
| conn1.close(); |
| |
| } |
| |
| public void testGreekCharacters() throws SQLException { |
| |
| String dbName = TestConfiguration.getCurrent().getDefaultDatabaseName(); |
| |
| setSystemProperty("derby.user." + apollo, zeus); |
| |
| Connection conn1 = openDefaultConnection( |
| "dan", ("dan" + PASSWORD_SUFFIX)); |
| // add a database level user |
| setDatabaseProperty(("derby.user." + zeus), apollo, conn1); |
| setDatabaseProperty("derby.database.fullAccessUsers", |
| ("dan,system,APP" + zeus + "," + apollo) , conn1); |
| conn1.commit(); |
| conn1.close(); |
| |
| |
| assertConnectionOK(dbName, zeus, apollo); |
| assertConnectionFail("08004", dbName, apollo, apollo); |
| // shutdown as non-dbo |
| assertShutdownOK(dbName, zeus, apollo); |
| assertConnectionOK(dbName, apollo, zeus); |
| // wrong credentials |
| assertShutdownFail("08004", dbName, zeus, zeus); |
| // shutdown as non-dbo |
| assertShutdownOK(dbName, apollo, zeus); |
| assertConnectionOK(dbName, apollo, zeus); |
| // shutdown as dbo |
| assertShutdownUsingSetShutdownOK( |
| dbName, "APP", ("APP" + PASSWORD_SUFFIX)); |
| |
| conn1 = openDefaultConnection(zeus, apollo); |
| Statement stmt = conn1.createStatement(); |
| assertUpdateCount(stmt, 0, |
| "create table APP.t1(c1 varchar(30))"); |
| assertUpdateCount(stmt, 1, "insert into APP.t1 values USER"); |
| conn1.commit(); |
| assertUserValue(new String[] {zeus}, zeus, apollo, |
| "select * from APP.t1 where c1 like CURRENT_USER"); |
| stmt.close(); |
| conn1.close(); |
| |
| // reset |
| conn1 = openDefaultConnection("dan", ("dan" + PASSWORD_SUFFIX)); |
| setDatabaseProperty( |
| "derby.database.defaultConnectionMode","fullAccess", conn1); |
| setDatabaseProperty( |
| "derby.connection.requireAuthentication","false", conn1); |
| stmt = conn1.createStatement(); |
| if (usingEmbedded()) |
| assertUpdateCount(stmt, 0, "drop table APP.t1"); |
| conn1.commit(); |
| stmt.close(); |
| conn1.close(); |
| } |
| |
| // tests system shutdown with setting required authentication at |
| // system level |
| public void testSystemShutdown() throws SQLException |
| { |
| String dbName = TestConfiguration.getCurrent().getDefaultDatabaseName(); |
| |
| // just for the setting the stage, recheck connections while fullAccess |
| // (default) is set at database level. |
| |
| // first try connection with valid user/password |
| assertConnectionOK(dbName, "system", ("admin")); |
| assertConnectionOK(dbName, "dan", ("dan" + PASSWORD_SUFFIX)); |
| |
| // try ensuring system level is set for authentication |
| setSystemProperty("derby.connection.requireAuthentication", "true"); |
| |
| // bring down the database |
| assertShutdownUsingSetShutdownOK( |
| dbName, "APP", "APP" + PASSWORD_SUFFIX); |
| // recheck |
| assertConnectionOK(dbName, "system", "admin"); |
| assertConnectionOK(dbName, "dan", ("dan" + PASSWORD_SUFFIX)); |
| // bring down server to ensure settings take effect |
| assertSystemShutdownOK("", "badUser", ("dan" + PASSWORD_SUFFIX)); |
| openDefaultConnection("dan", ("dan" + PASSWORD_SUFFIX)).close(); // revive |
| |
| // try system shutdown with wrong user |
| assertSystemShutdownFail("08004", "", "badUser", ("dan" + PASSWORD_SUFFIX)); |
| // with 'allowed' user but bad pwd |
| assertSystemShutdownFail("08004", "", "dan", ("jeff" + PASSWORD_SUFFIX)); |
| // APP, but bad pwd |
| assertSystemShutdownFail("08004", "", "APP", ("POO")); |
| // note: we don't have a database, so no point checking for dbo. |
| // expect Derby system shutdown, which gives XJ015 error. |
| assertSystemShutdownOK("", "system", "admin"); |
| |
| // reset. |
| Connection conn1 = openDefaultConnection("dan", ("dan" + PASSWORD_SUFFIX)); |
| setDatabaseProperty( |
| "derby.database.defaultConnectionMode","fullAccess", conn1); |
| setDatabaseProperty( |
| "derby.connection.requireAuthentication","false", conn1); |
| |
| setSystemProperty("derby.connection.requireAuthentication", "false"); |
| |
| conn1.commit(); |
| conn1.close(); |
| openDefaultConnection("system", "admin").close(); |
| assertShutdownUsingSetShutdownOK( |
| dbName, "APP", "APP" + PASSWORD_SUFFIX); |
| assertSystemShutdownOK("", "system", "admin"); |
| openDefaultConnection("system", "admin").close(); // just so teardown works. |
| } |
| |
| /** |
| * DERBY-4483: Test that the database by default has the configurable |
| * hash authentication scheme enabled. |
| */ |
| public void testDefaultHashAlgorithm() throws SQLException { |
| // SHA-256 should be the default hash algorithm now, if it's supported |
| // on the platform. Otherwise, we fall back to SHA-1. |
| String expected = supportsAlgorithm("SHA-256") ? "SHA-256" : "SHA-1"; |
| assertEquals(expected, getDatabaseProperty(BUILTIN_ALGO_PROP)); |
| } |
| |
| /** |
| * Check if a message digest algorithm is supported on this platform. |
| * |
| * @param algorithm the algorithm to check |
| * @return true if the algorithm is supported, false otherwise |
| */ |
| private boolean supportsAlgorithm(String algorithm) { |
| try { |
| MessageDigest.getInstance(algorithm); |
| return true; |
| } catch (NoSuchAlgorithmException nsae) { |
| return false; |
| } |
| } |
| |
| /** |
| * DERBY-4483: Test that setting the property |
| * {@code derby.authentication.builtin.algorithm} changes which hash |
| * algorithm is used to protect the stored password token. |
| */ |
| public void testVariousBuiltinAlgorithms() throws SQLException { |
| setAutoCommit(true); |
| String[] algorithms = { null, "MD5", "SHA-1", "SHA-256", "SHA-512" }; |
| for (int i = 0; i < algorithms.length; i++) { |
| String algo = algorithms[i]; |
| |
| if (algo != null && !supportsAlgorithm(algo)) { |
| // DERBY-4602: Skip algorithms not supported on this platform |
| continue; |
| } |
| |
| // Test the algorithm with and without key stretching (added in |
| // DERBY-5539) |
| testVariousBuiltinAlgorithms(algo, true); |
| testVariousBuiltinAlgorithms(algo, false); |
| } |
| } |
| |
| /** |
| * Worker method for {@link #testVariousBuiltinAlgorithms()}. |
| * |
| * @param algo the name of the hash algorithm to test |
| * @param keyStretching whether or not to use the authentication scheme that |
| * performs key stretching |
| */ |
| private void testVariousBuiltinAlgorithms(String algo, boolean keyStretching) |
| throws SQLException { |
| setDatabaseProperty(BUILTIN_ALGO_PROP, algo); |
| |
| if (keyStretching) { |
| // Unset the properties specifying salt length and iterations, so |
| // we get the default scheme (with key stretching) |
| setDatabaseProperty(BUILTIN_SALT_LENGTH_PROP, null); |
| setDatabaseProperty(BUILTIN_ITERATIONS_PROP, null); |
| } else { |
| // Disable salt and use a single iteration |
| setDatabaseProperty(BUILTIN_SALT_LENGTH_PROP, "0"); |
| setDatabaseProperty(BUILTIN_ITERATIONS_PROP, "1"); |
| } |
| |
| for (int i = 0; i < USERS.length; i++) { |
| String user = USERS[i]; |
| String password = user + PASSWORD_SUFFIX; |
| String userProp = USER_PREFIX + user; |
| |
| // Set the password for the user |
| setDatabaseProperty(userProp, password); |
| |
| // Get the stored password token and verify that it |
| // hashed the way we expect it to be |
| String token = getDatabaseProperty(userProp); |
| if (algo == null) { |
| assertTrue("Expected old authentication scheme: " + token, |
| token.startsWith("3b60")); |
| } else { |
| if (keyStretching) { |
| assertTrue("Expected configurable hash scheme with "+ |
| "key stretching: " + token, |
| token.startsWith("3b62")); |
| } else { |
| assertTrue("Expected configurable hash scheme: " + token, |
| token.startsWith("3b61")); |
| } |
| assertTrue("Expected algorithm " + algo + ":" + token, |
| token.endsWith(":" + algo)); |
| } |
| |
| // Verify that we can still connect as that user |
| openDefaultConnection(user, password).close(); |
| } |
| } |
| |
| /** |
| * DERBY-4483: Test that slightly different passwords result in different |
| * hashes, and also that using the same password for different users |
| * results in a unique hashes with the configurable hash authentication |
| * scheme. |
| */ |
| public void testNoCollisionsWithConfigurableHash() throws SQLException { |
| assertNotNull("hash algorithm not set up", |
| getDatabaseProperty(BUILTIN_ALGO_PROP)); |
| |
| // Store a set of generated password tokens to detect collisions |
| HashSet<String> tokens = new HashSet<String>(); |
| |
| for (int i = 0; i < USERS.length; i++) { |
| String user = USERS[i]; |
| String userProp = USER_PREFIX + user; |
| assertNotNull("missing user " + user, |
| getDatabaseProperty(userProp)); |
| |
| // Start with the password "testing", and then change one of the |
| // characters |
| char[] pw = new char[] { 't', 'e', 's', 't', 'i', 'n', 'g' }; |
| for (int j = 0; j < 100; j++) { |
| String pass = new String(pw); |
| setDatabaseProperty(userProp, pass); |
| |
| assertTrue("collision detected", |
| tokens.add(getDatabaseProperty(userProp))); |
| pw[pw.length / 2]++; |
| } |
| } |
| } |
| |
| /** |
| * DERBY-4483: Test that we fail gracefully if an invalid algorithm name |
| * is specified in {@code derby.authentication.builtin.algorithm}. |
| */ |
| public void testInvalidAlgorithmName() throws SQLException { |
| setDatabaseProperty(BUILTIN_ALGO_PROP, "not-a-valid-name"); |
| |
| for (int i = 0; i < USERS.length; i++) { |
| try { |
| setDatabaseProperty(USER_PREFIX + USERS[i], "abcdef"); |
| fail(); |
| } catch (SQLException sqle) { |
| assertSQLState(NO_SUCH_ALGO, sqle); |
| } |
| } |
| } |
| |
| /** |
| * DERBY-5507: Setting a user's password as a database property, when |
| * that user's password is already set as a system property, used to make |
| * the password getting stored in plaintext in the database. |
| */ |
| public void testDerby5507PlaintextPassword() throws SQLException { |
| // This user account is defined in a system property |
| String key = "derby.user.mickey"; |
| |
| // Now define the account in a database property |
| String pw = "M0u$e"; |
| setDatabaseProperty(key, pw); |
| |
| // This used to return the plaintext password, but it should |
| // return a hashed token |
| String pwToken = getDatabaseProperty(key); |
| assertFalse("Password stored in plaintext", pwToken.equals(pw)); |
| } |
| |
| protected void assertFailSetDatabaseProperty( |
| String propertyName, String value, Connection conn) |
| throws SQLException { |
| CallableStatement setDBP = conn.prepareCall( |
| "CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY(?, ?)"); |
| setDBP.setString(1, propertyName); |
| setDBP.setString(2, value); |
| // user jamie cannot be both readOnly and fullAccess |
| assertStatementError("4250C", setDBP); |
| setDBP.close(); |
| } |
| |
| protected void setDatabaseProperty( |
| String propertyName, String value, Connection conn) |
| throws SQLException { |
| CallableStatement setDBP = conn.prepareCall( |
| "CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY(?, ?)"); |
| setDBP.setString(1, propertyName); |
| setDBP.setString(2, value); |
| setDBP.execute(); |
| setDBP.close(); |
| } |
| |
| /** |
| * Set a database property in the default connection. |
| */ |
| void setDatabaseProperty(String propertyName, String value) |
| throws SQLException |
| { |
| setDatabaseProperty(propertyName, value, getConnection()); |
| } |
| |
| protected void useUserValue(int expectedUpdateCount, String user, String sql) |
| throws SQLException |
| { |
| Connection conn1 = openDefaultConnection(user, user + PASSWORD_SUFFIX); |
| Statement stmt = conn1.createStatement(); |
| assertUpdateCount(stmt, expectedUpdateCount, sql); |
| conn1.commit(); |
| stmt.close(); |
| conn1.close(); |
| } |
| |
| // verify that the return value is the expected value, and |
| // we have the expected number of rows returning from the query |
| // in this test, it will be one of the user names through |
| // use of CURRENT_USER, SESSION_USER etc. |
| protected void assertUserValue( |
| String[] expected, String user, String password, String sql) |
| throws SQLException |
| { |
| Connection conn1 = openDefaultConnection(user, password); |
| Statement stmt = conn1.createStatement(); |
| ResultSet rs = stmt.executeQuery(sql); |
| int i = 0; |
| while (rs.next()) |
| { |
| assertEquals(expected[i],rs.getString(1)); |
| i++; |
| } |
| assertEquals(expected.length, i); |
| conn1.commit(); |
| stmt.close(); |
| conn1.close(); |
| } |
| |
| // convenience method, password is often using PASSWORD_SUFFIX |
| protected void assertUserValue(String[] expected, String user, String sql) |
| throws SQLException { |
| assertUserValue(expected, user, (user + PASSWORD_SUFFIX), sql); |
| } |
| |
| // get a connection using ds.getConnection(user, password) |
| protected void assertConnectionOK( |
| String dbName, String user, String password) |
| throws SQLException |
| { |
| DataSource ds = JDBCDataSource.getDataSource(dbName); |
| Connection conn = ds.getConnection(user, password); |
| assertNotNull(conn); |
| conn.close(); |
| } |
| |
| // get a connection, using setUser / setPassword, and ds.getConnection() |
| protected void assertConnectionWOUPOK( |
| String dbName, String user, String password) |
| throws SQLException |
| { |
| DataSource ds = JDBCDataSource.getDataSource(dbName); |
| JDBCDataSource.setBeanProperty(ds, "user", user); |
| JDBCDataSource.setBeanProperty(ds, "password", password); |
| Connection conn = ds.getConnection(); |
| assertNotNull(conn); |
| conn.close(); |
| } |
| |
| protected void assertConnectionFail( |
| String expectedSqlState, String dbName, String user, String password) |
| throws SQLException |
| { |
| DataSource ds = JDBCDataSource.getDataSource(dbName); |
| try { |
| ds.getConnection(user, password); |
| fail("Connection should've been refused/failed"); |
| } |
| catch (SQLException e) { |
| assertSQLState(expectedSqlState, e); |
| } |
| } |
| |
| // same action as with assertConnectionFail, but using ds.getConnection() |
| // instead of ds.getConnection(user, password). So, setting user and |
| // password using appropriate ds.set method. |
| protected void assertConnectionWOUPFail( |
| String expectedError, String dbName, String user, String password) |
| throws SQLException |
| { |
| DataSource ds = JDBCDataSource.getDataSource(dbName); |
| JDBCDataSource.setBeanProperty(ds, "user", user); |
| JDBCDataSource.setBeanProperty(ds, "password", password); |
| try { |
| ds.getConnection(); |
| fail("Connection should've been refused/failed"); |
| } |
| catch (SQLException e) { |
| assertSQLState(expectedError, e); |
| } |
| } |
| |
| protected void assertShutdownUsingSetShutdownOK( |
| String dbName, String user, String password) throws SQLException { |
| DataSource ds = JDBCDataSource.getDataSource(dbName); |
| JDBCDataSource.setBeanProperty(ds, "shutdownDatabase", "shutdown"); |
| try { |
| ds.getConnection(user, password); |
| fail("expected shutdown to fail"); |
| } catch (SQLException e) { |
| // expect 08006 on successful shutdown |
| assertSQLState("08006", e); |
| } |
| } |
| |
| protected void assertShutdownUsingConnAttrsOK( |
| String dbName, String user, String password) throws SQLException { |
| |
| DataSource ds = JDBCDataSource.getDataSource(dbName); |
| JDBCDataSource.setBeanProperty( |
| ds, "connectionAttributes", "shutdown=true"); |
| try { |
| ds.getConnection(user, password); |
| fail("expected shutdown to fail"); |
| } catch (SQLException e) { |
| // expect 08006 on successful shutdown |
| assertSQLState("08006", e); |
| } |
| } |
| |
| // same action as with assertShutdownOK, but using ds.getConnection() |
| // instead of ds.getConnection(user, password). So, setting user and |
| // password using appropriate ds.set method. |
| protected void assertShutdownWOUPOK( |
| String dbName, String user, String password) |
| throws SQLException { |
| |
| DataSource ds = JDBCDataSource.getDataSource(dbName); |
| JDBCDataSource.setBeanProperty(ds, "shutdownDatabase", "shutdown"); |
| JDBCDataSource.setBeanProperty(ds, "user", user); |
| JDBCDataSource.setBeanProperty(ds, "password", password); |
| try { |
| ds.getConnection(); |
| fail("expected shutdown to fail"); |
| } catch (SQLException e) { |
| // expect 08006 on successful shutdown |
| assertSQLState("08006", e); |
| } |
| } |
| |
| protected void assertShutdownFail( |
| String expectedSqlState, String dbName, String user, String password) |
| throws SQLException |
| { |
| DataSource ds = JDBCDataSource.getDataSource(dbName); |
| JDBCDataSource.setBeanProperty(ds, "shutdownDatabase", "shutdown"); |
| try { |
| ds.getConnection(user, password); |
| fail("expected shutdown to fail"); |
| } catch (SQLException e) { |
| assertSQLState(expectedSqlState, e); |
| } |
| } |
| |
| protected void assertShutdownOK( |
| String dbName, String user, String password) |
| throws SQLException |
| { |
| DataSource ds = JDBCDataSource.getDataSource(dbName); |
| JDBCDataSource.setBeanProperty(ds, "shutdownDatabase", "shutdown"); |
| try { |
| ds.getConnection(user, password); |
| fail("expected shutdown to fail"); |
| } catch (SQLException e) { |
| assertSQLState("08006", e); |
| } |
| } |
| |
| protected void assertShutdownWOUPFail( |
| String expectedSqlState, String dbName, String user, String password) |
| throws SQLException |
| { |
| DataSource ds = JDBCDataSource.getDataSource(dbName); |
| JDBCDataSource.setBeanProperty(ds, "shutdownDatabase", "shutdown"); |
| JDBCDataSource.setBeanProperty(ds, "user", user); |
| JDBCDataSource.setBeanProperty(ds, "password", password); |
| try { |
| ds.getConnection(); |
| fail("expected shutdown to fail"); |
| } catch (SQLException e) { |
| assertSQLState(expectedSqlState, e); |
| } |
| } |
| |
| protected void assertSystemShutdownOK( |
| String dbName, String user, String password) |
| throws SQLException { |
| DataSource ds; |
| if (usingEmbedded()) |
| { |
| // we cannot use JDBCDataSource.getDataSource() (which uses the |
| // default database name), unless we specifically clear the |
| // databaseName. Otherwise, only the database will be shutdown. |
| // The alternative is to use jDBCDataSource.getDatasource(dbName), |
| // where dbName is an empty string - this will in the current code |
| // be interpreted as a system shutdown. |
| |
| ds = JDBCDataSource.getDataSource(); |
| JDBCDataSource.clearStringBeanProperty(ds, "databaseName"); |
| } |
| else |
| { |
| // With client, we cannot user clearStringBeanProperty on the |
| // databaseName, that will result in error 08001 - |
| // Required DataSource property databaseName not set. |
| // So, we pass an empty string as databaseName, which the current |
| // code interprets as a system shutdown. |
| ds = JDBCDataSource.getDataSource(dbName); |
| } |
| |
| JDBCDataSource.setBeanProperty(ds, "shutdownDatabase", "shutdown"); |
| try { |
| ds.getConnection(user, password); |
| fail("expected system shutdown resulting in XJ015 error"); |
| } catch (SQLException e) { |
| // expect XJ015, system shutdown, on successful shutdown |
| assertSQLState("XJ015", e); |
| } |
| } |
| |
| protected void assertSystemShutdownFail( |
| String expectedError, String dbName, String user, String password) |
| throws SQLException { |
| DataSource ds; |
| if (usingEmbedded()) |
| { |
| ds = JDBCDataSource.getDataSource(); |
| JDBCDataSource.clearStringBeanProperty(ds, "databaseName"); |
| } |
| else |
| { |
| // note: with network server/client, you can't set the databaseName |
| // to null, that results in error 08001 - Required DataSource |
| // property databaseName not set. |
| // so, we rely on passing of an empty string for databaseName, |
| // which in the current code is interpreted as system shutdown. |
| ds = JDBCDataSource.getDataSource(dbName); |
| } |
| JDBCDataSource.setBeanProperty(ds, "shutdownDatabase", "shutdown"); |
| JDBCDataSource.setBeanProperty(ds, "user", user); |
| JDBCDataSource.setBeanProperty(ds, "password", password); |
| try { |
| ds.getConnection(); |
| fail("expected shutdown to fail"); |
| } catch (SQLException e) { |
| assertSQLState(expectedError, e); |
| } |
| } |
| |
| public void assertConnectionFail(String dbName) throws SQLException { |
| |
| // Get the default data source but clear the user and |
| // password set by the configuration. |
| DataSource ds = JDBCDataSource.getDataSource(dbName); |
| |
| // Reset to no user/password though client requires |
| // a valid name, so reset to the default |
| if (usingDerbyNetClient()) |
| JDBCDataSource.setBeanProperty(ds, "user", "APP"); |
| else |
| JDBCDataSource.clearStringBeanProperty(ds, "user"); |
| JDBCDataSource.clearStringBeanProperty(ds, "password"); |
| |
| try { |
| ds.getConnection(); |
| fail("expected connection to fail"); |
| } catch (SQLException e) { |
| assertSQLState("08004", e); |
| } |
| } |
| } |