Google Git
Sign in
apache / derby / 08cc0f89fe077234078430bf9b93ebc9dfd70ac5 / . / java / testing / org / apache / derbyTesting / functionTests / tests / lang / NativeAuthenticationServiceTest.java
blob: a14f1b1fa0f3c41dd12e23143d2556e621473d13 [file] [log] [blame]
/*
Derby - Class org.apache.derbyTesting.functionTests.tests.lang.NativeAuthenticationServiceTest
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.io.File;
import java.net.URL;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.SQLWarning;
import java.util.Properties;
import javax.sql.DataSource;
import junit.framework.Test;
import org.apache.derbyTesting.junit.BaseTestSuite;
import org.apache.derbyTesting.junit.ClasspathSetup;
import org.apache.derbyTesting.junit.DatabaseChangeSetup;
import org.apache.derbyTesting.junit.JDBC;
import org.apache.derbyTesting.junit.JDBCDataSource;
import org.apache.derbyTesting.junit.SupportFilesSetup;
import org.apache.derbyTesting.junit.SystemPropertyTestSetup;
import org.apache.derbyTesting.junit.TestConfiguration;
import org.apache.derbyTesting.junit.TimeZoneTestSetup;
/**
* <p>
* Tests for the NATIVE authentication service introduced by DERBY-866.
* </p>
*/
public class NativeAuthenticationServiceTest extends GeneratedColumnsHelper
{
///////////////////////////////////////////////////////////////////////////////////
//
// CONSTANTS
//
///////////////////////////////////////////////////////////////////////////////////
// location of credentials db
private static final int FILE = 0;
private static final int JAR = 1;
private static final int CLASSPATH = 2;
private static final int NONE = 3;
private static final int JAR_ENCRYPTED = 4;
// settings for constructor options
private static final boolean NATIVE = true;
private static final boolean NO_AUTH = false;
private static final boolean LOCAL = true;
private static final boolean SYSTEM_WIDE = false;
private static final boolean DISABLE_AUTHORIZATION = true;
private static final boolean DONT_DISABLE_AUTH = false;
// fruits are legal users. nuts are not
private static final String DBO = "KIWI";
private static final String APPLE_USER = "APPLE";
private static final String PEAR_USER = "PEAR";
private static final String PRICKLY_PEAR_USER = "PeAr";
private static final String ORANGE_USER = "ORANGE";
private static final String BANANA_USER = "BANANA";
private static final String GRAPE_USER = "GRAPE";
private static final String PINEAPPLE_USER = "PINEAPPLE";
private static final String CAMEL_CASE_DBO = "kIwI";
private static final String WALNUT_USER = "WALNUT";
private static final String BUILTIN_USER = "PECAN";
private static final String CREDENTIALS_DB = "credDB";
private static final String SECOND_DB = "secondDB";
private static final String THIRD_DB = "thirdDB";
private static final String FOURTH_DB = "fourthDB";
private static final String FIFTH_DB = "fifthDB";
private static final String SIXTH_DB = "sixthDB";
private static final String SEVENTH_DB = "seventhDB";
private static final String EIGHTH_DB = "eighthDB";
private static final String NINTH_DB = "ninthDB";
private static final String TENTH_DB = "tenthDB";
private static final String ELEVENTH_DB = "eleventhDB";
private static final String TWELTH_DB = "twelthDB";
private static final String THIRTEENTH_DB = "thirteenthDB";
private static final String FOURTEENTH_DB = "fourteenthDB";
private static final String FIFTEENTH_DB = "fifteenthDB";
private static final String NAST1_JAR_FILE = "nast1.jar";
private static final String NAST2_JAR_FILE = "nast2.jar";
private static final String[] SUPPORT_FILES_SOURCE =
{
"functionTests/tests/lang/" + NAST1_JAR_FILE,
"functionTests/tests/lang/" + NAST1_JAR_FILE,
};
private static final String[] SUPPORT_FILES_TARGET =
{
NAST1_JAR_FILE,
NAST2_JAR_FILE,
};
private static final String PROVIDER_PROPERTY = "derby.authentication.provider";
private static final String REQUIRE_AUTHENTICATION_PROPERTY = "derby.connection.requireAuthentication";
private static final String SQL_AUTHORIZATION_PROPERTY = "derby.database.sqlAuthorization";
private static final String UPGRADE_TO_BETA_PROPERTY = "derby.database.allowPreReleaseUpgrade";
private static final String CREDENTIALS_DB_DOES_NOT_EXIST = "4251I";
private static final String BAD_NETWORK_AUTHENTICATION = "08001";
private static final String INVALID_AUTHENTICATION = "08004";
private static final String DBO_ONLY_OPERATION = "4251D";
private static final String INVALID_PROVIDER_CHANGE = "XCY05";
private static final String CANT_DROP_DBO = "4251F";
private static final String NO_COLUMN_PERMISSION = "42502";
private static final String NO_EXECUTE_PERMISSION = "42504";
private static final String PASSWORD_EXPIRING = "01J15";
private static final String DBO_PASSWORD_EXPIRING = "01J16";
private static final String BAD_PASSWORD_PROPERTY = "4251J";
private static final String BAD_PROPERTY_CHANGE = "XCY02";
private static final String SQL_AUTHORIZATION_NOT_ON = "42Z60";
private static final String CANT_BOOT_DATABASE = "XJ040";
private static final String MISSING_USER = "XK001";
private static final String BAD_USER_AUTHENTICATOR_CLASS = "XBM0M";
private static final String USER_ALREADY_EXISTS = "X0Y68";
private static final String WEAK_AUTHENTICATION = "4251G";
private static final String HASHING_FORMAT_10_9 = "3b62";
private static final int HEX_CHARS_PER_BYTE = 2;
///////////////////////////////////////////////////////////////////////////////////
//
// STATE
//
///////////////////////////////////////////////////////////////////////////////////
private final int _credentialsDBLocation;
private final boolean _nativeAuthentication;
private final boolean _localAuthentication;
private final boolean _turnOffAuthenticationAndAuthorization;
private String _credentialsDBPhysicalName;
private DatabaseChangeSetup _fourthDBSetup;
private DatabaseChangeSetup _fifthDBSetup;
private DatabaseChangeSetup _sixthDBSetup;
private DatabaseChangeSetup _seventhDBSetup;
private DatabaseChangeSetup _eighthDBSetup;
private DatabaseChangeSetup _ninthDBSetup;
private DatabaseChangeSetup _fifteenthDBSetup;
private String _derbySystemHome;
private String _fullBackupDir;
///////////////////////////////////////////////////////////////////////////////////
//
// CONSTRUCTOR
//
///////////////////////////////////////////////////////////////////////////////////
public NativeAuthenticationServiceTest
(
int credentialsDBLocation,
boolean nativeAuthentication,
boolean localAuthentication,
boolean turnOffAuthenticationAndAuthorization
)
{
super( "testAll" );
_credentialsDBLocation = credentialsDBLocation;
_nativeAuthentication = nativeAuthentication;
_localAuthentication = localAuthentication;
_turnOffAuthenticationAndAuthorization = turnOffAuthenticationAndAuthorization;
}
///////////////////////////////////////////////////////////////////////////////////
//
// SETUP BEHAVIOR
//
///////////////////////////////////////////////////////////////////////////////////
public void setUp() throws Exception
{
super.setUp();
_derbySystemHome = getSystemProperty( "derby.system.home" );
_fullBackupDir = _derbySystemHome + "/backupDir";
}
/**
* <p>
* Return the system properties to be used in a particular test run.
* </p>
*/
private Properties systemProperties( String physicalDatabaseName )
throws Exception
{
Properties result = new Properties();
String authenticationProvider;
switch ( _credentialsDBLocation )
{
case JAR:
case JAR_ENCRYPTED:
_credentialsDBPhysicalName = jarDBName( _credentialsDBLocation );
break;
case CLASSPATH:
_credentialsDBPhysicalName = classpathDBName();
break;
case FILE:
case NONE:
_credentialsDBPhysicalName = physicalDatabaseName;
break;
default:
throw new Exception( "Unknown location of credentials db: " + _credentialsDBLocation );
}
if ( !_nativeAuthentication )
{
authenticationProvider = "NONE";
}
else
{
authenticationProvider = "NATIVE:" + _credentialsDBPhysicalName;
if ( _localAuthentication ) { authenticationProvider = authenticationProvider + ":LOCAL"; }
}
result.put( UPGRADE_TO_BETA_PROPERTY, "true" );
result.put( PROVIDER_PROPERTY, authenticationProvider );
//
// This is to verify that NATIVE authentication trumps attempts
// to disable these features at the system level.
//
if ( _turnOffAuthenticationAndAuthorization )
{
result.put( REQUIRE_AUTHENTICATION_PROPERTY, "false" );
result.put( SQL_AUTHORIZATION_PROPERTY, "false" );
}
return result;
}
/**
* <p>
* Return true if the credentials db is accessed via a jar or classpath subprotocol.
* </p>
*/
private boolean credentialsViaSubprotocol()
{
switch ( _credentialsDBLocation )
{
case JAR:
case JAR_ENCRYPTED:
case CLASSPATH:
return true;
default:
return false;
}
}
/**
* <p>
* Construct the name of this test (useful for error messages).
* </p>
*/
private String nameOfTest()
{
String dbLocation = "";
switch ( _credentialsDBLocation )
{
case JAR:
dbLocation = "JAR, ";
break;
case JAR_ENCRYPTED:
dbLocation = "JAR_ENCRYPTED, ";
break;
case CLASSPATH:
dbLocation = "CLASSPATH, ";
break;
case FILE:
case NONE:
dbLocation = "FILE, ";
break;
default:
dbLocation = "UNKNOWN, ";
break;
}
String authType = _nativeAuthentication ?
"NATIVE authentication on, " :
"Authentication off, ";
String local = _localAuthentication ?
"LOCAL authentication ON, " :
"LOCAL authentication OFF, ";
String authOverrides = _turnOffAuthenticationAndAuthorization ?
"Authentication/Authorization turned OFF, " :
"Authentication/Authorization DEFAULT, ";
String embedded = isEmbedded() ?
"Embedded" :
"Client/Server";
return "[ " + dbLocation + authType + local + authOverrides + embedded + " ]";
}
/** Return true if the test is running embedded */
public boolean isEmbedded() { return getTestConfiguration().getJDBCClient().isEmbedded(); }
///////////////////////////////////////////////////////////////////////////////////
//
// JUnit BEHAVIOR
//
///////////////////////////////////////////////////////////////////////////////////
/**
* Construct top level suite in this JUnit test
*/
public static Test suite() throws Exception
{
BaseTestSuite suite = new BaseTestSuite();
//
// Special version of the test which uses an encrypted database for credentials.
// Only run on platforms where we can expect cleanup of support files
// to succeed (DERBY-5618).
//
if (enableSubprotocolTests())
{
suite.addTest
(
(
new NativeAuthenticationServiceTest
( JAR_ENCRYPTED, NATIVE, LOCAL, DONT_DISABLE_AUTH )
).decorate( false )
);
}
suite.addTest( allConfigurations( false ) );
if ( !JDBC.vmSupportsJSR169() ) { suite.addTest( allConfigurations( true ) ); }
// DERBY-5966: Run the test in the GMT time zone to avoid instabilities
// around transition to or from DST. Once DERBY-5974 has been fixed, it
// should be OK to run in the local time zone.
return new TimeZoneTestSetup(suite, "GMT");
}
/**
* Check whether or not tests for subprotocols should be enabled
* on this platform.
*/
private static boolean enableSubprotocolTests() {
// DERBY-5618: On Windows, we are not able to delete the jar files
// used by the tests for the classpath subsubprotocol if we don't
// close the URLClassLoader when we're done. Closing the class loader
// can only be done on Java 7 and higher.
//
// Run the tests if the Java version supports URLClassLoader.close(),
// or if we're on a non-Windows platform (where the jar files can be
// deleted even if the class loader hasn't been closed).
return ClasspathSetup.supportsClose() || !isWindowsPlatform();
}
/**
* <p>
* Create a suite of all test configurations.
* </p>
*/
private static Test allConfigurations(boolean clientServer) throws Exception
{
BaseTestSuite suite = new BaseTestSuite();
//
// No authentication.
//
suite.addTest
(
(
new NativeAuthenticationServiceTest
( NONE, NO_AUTH, SYSTEM_WIDE, DONT_DISABLE_AUTH )
).decorate( clientServer )
);
//
// NATIVE/LOCAL authentication with credentials in read/write dbs
//
suite.addTest
(
(
new NativeAuthenticationServiceTest
( FILE, NATIVE, LOCAL, DISABLE_AUTHORIZATION )
).decorate( clientServer )
);
suite.addTest
(
(
new NativeAuthenticationServiceTest
( FILE, NATIVE, LOCAL, DONT_DISABLE_AUTH )
).decorate( clientServer )
);
//
// NATIVE system-wide authentication with credentials in read/write dbs
//
suite.addTest
(
(
new NativeAuthenticationServiceTest
( FILE, NATIVE, SYSTEM_WIDE, DISABLE_AUTHORIZATION )
).decorate( clientServer )
);
suite.addTest
(
(
new NativeAuthenticationServiceTest
( FILE, NATIVE, SYSTEM_WIDE, DONT_DISABLE_AUTH )
).decorate( clientServer )
);
//
// For testing subprotocols. Only run on platforms where we can
// expect cleanup of support files to succeed (DERBY-5618).
//
if (enableSubprotocolTests())
{
//
// NATIVE authentication with credentials in read-only databases accessed via jar subprotocol
//
suite.addTest
(
(
new NativeAuthenticationServiceTest
( JAR, NATIVE, SYSTEM_WIDE, DONT_DISABLE_AUTH )
).decorate( clientServer )
);
suite.addTest
(
(
new NativeAuthenticationServiceTest
( JAR, NATIVE, LOCAL, DONT_DISABLE_AUTH )
).decorate( clientServer )
);
//
// NATIVE authentication with credentials in read-only databases accessed via classpath subprotocol
//
suite.addTest
(
(
new NativeAuthenticationServiceTest
( CLASSPATH, NATIVE, SYSTEM_WIDE, DONT_DISABLE_AUTH )
).decorate( clientServer )
);
suite.addTest
(
(
new NativeAuthenticationServiceTest
( CLASSPATH, NATIVE, LOCAL, DONT_DISABLE_AUTH )
).decorate( clientServer )
);
}
return suite;
}
/**
* <p>
* Wrap base test with standard decorators in order to setup system
* properties and allow for the creation of multiple databases with
* stored properties that can't be removed at tearDown time.
* </p>
*/
private Test decorate(boolean clientServer) throws Exception
{
String credentialsDBPhysicalName = TestConfiguration.generateUniqueDatabaseName();
Test result = this;
//
// Putting the clientServer decorator on the inside allows the server-side
// embedded driver to be re-registered after engine shutdown. If you put
// this decorator outside the SystemProperty decorator, then engine shutdown
// unregisters the server-side embedded driver and it can't be found by
// the next test.
//
if ( clientServer ) { result = TestConfiguration.clientServerDecorator( result ); }
//
// Turn on the property which enables NATIVE authentication. This will trigger
// an engine shutdown at the end of the test. We want to shutdown the engine
// before deleting the physical databases. This is because we need one of the
// databases (the credentials db) in order to authenticate engine shutdown.
//
Properties systemProperties = null;
try {
systemProperties = systemProperties( credentialsDBPhysicalName );
} catch (Exception e) { printStackTrace( e ); }
println( nameOfTest() );
println( " NativeAuthenticationServiceTest.decorate() systemProperties = " + systemProperties );
result = new SystemPropertyTestSetup( result, systemProperties, true );
// DERBY-5580: We should also shut down the engine before deleting
// the database if we don't set any system properties.
//result = new TestSetup(result) {
// protected void tearDown() {
// TestConfiguration.getCurrent().shutdownEngine();
// }
// };
//
// For testing subprotocols. Only run on platforms where we can
// expect cleanup of support files to succeed (DERBY-5618).
//
if (enableSubprotocolTests())
{
// Add a jar file to the classpath so that we can test the classpath subprotocol.
URL nast2Jar = SupportFilesSetup.getReadOnlyURL(NAST2_JAR_FILE);
result = new ClasspathSetup(result, nast2Jar);
// Add the jar files needed for testing jar and classpath subprotocols.
result = new SupportFilesSetup( result, SUPPORT_FILES_SOURCE, null, SUPPORT_FILES_TARGET, null );
}
//
// Register temporary databases, where the test will do its work.
// We can't use the default, re-usable database because NATIVE authentication stores
// persistent properties which cannot be turned off.
//
result = TestConfiguration.additionalDatabaseDecoratorNoShutdown
( result, CREDENTIALS_DB, credentialsDBPhysicalName );
result = TestConfiguration.additionalDatabaseDecoratorNoShutdown( result, SECOND_DB );
result = TestConfiguration.additionalDatabaseDecoratorNoShutdown( result, THIRD_DB );
result = _fourthDBSetup = TestConfiguration.additionalDatabaseDecoratorNoShutdown( result, FOURTH_DB, true );
result = _fifthDBSetup = TestConfiguration.additionalDatabaseDecoratorNoShutdown( result, FIFTH_DB, true );
result = _sixthDBSetup = TestConfiguration.additionalDatabaseDecoratorNoShutdown( result, SIXTH_DB, true );
result = _seventhDBSetup = TestConfiguration.additionalDatabaseDecoratorNoShutdown( result, SEVENTH_DB, true );
result = _eighthDBSetup = TestConfiguration.additionalDatabaseDecoratorNoShutdown( result, EIGHTH_DB, true );
result = _ninthDBSetup = TestConfiguration.additionalDatabaseDecoratorNoShutdown( result, NINTH_DB, true );
result = TestConfiguration.additionalDatabaseDecoratorNoShutdown( result, TENTH_DB );
result = TestConfiguration.additionalDatabaseDecoratorNoShutdown( result, ELEVENTH_DB );
result = TestConfiguration.additionalDatabaseDecoratorNoShutdown( result, TWELTH_DB );
result = TestConfiguration.additionalDatabaseDecoratorNoShutdown( result, THIRTEENTH_DB );
result = TestConfiguration.additionalDatabaseDecoratorNoShutdown( result, FOURTEENTH_DB );
result = _fifteenthDBSetup = TestConfiguration.additionalDatabaseDecoratorNoShutdown( result, FIFTEENTH_DB, true );
result = TestConfiguration.changeUserDecorator( result, DBO, getPassword( DBO ) );
return result;
}
///////////////////////////////////////////////////////////////////////////////////
//
// TESTS
//
///////////////////////////////////////////////////////////////////////////////////
/**
* <p>
* Entry point for tests.
* </p>
*/
public void testAll() throws Exception
{
println( nameOfTest() );
println( "Credentials DB physical name = " + _credentialsDBPhysicalName );
println( PROVIDER_PROPERTY + " = " + getSystemProperty( PROVIDER_PROPERTY ) );
if ( _credentialsDBLocation == JAR_ENCRYPTED )
{
vetEncryptedCredentialsDB();
}
else if ( credentialsViaSubprotocol() )
{
vetCredentialsViaSubprotocol();
}
else
{
vetCoreBehavior();
vetCasing();
vetSystemWideOperations();
if ( !_nativeAuthentication ) { vetProviderChanges(); }
// only run this for local authentication so that we don't have to shutdown
// the system-wide credentials db. also only run this embedded so that we
// don't have to deal with the problems of shutting down a database
// across the network.
if ( _localAuthentication && isEmbedded() ) { vetPasswordLifetime(); }
}
}
/**
* <p>
* Verify behavior when credentials live in an encrypted db.
* </p>
*/
private void vetEncryptedCredentialsDB() throws Exception
{
// can't create a database if the encrypted credentials db hasn't been booted yet
Connection badConn = getConnection( true, true, THIRTEENTH_DB, DBO, CANT_BOOT_DATABASE );
// now boot the encrypted credentials db
Properties props = new Properties();
props.setProperty( "bootPassword", "clo760uds2caPe" );
Connection dboConn = openConnection( jarDBName( _credentialsDBLocation ), DBO, false, props );
// credentials db is booted. now we can create databases with good credentials.
Connection grapeConn = getConnection( false, true, THIRTEENTH_DB, GRAPE_USER, null );
// but we can't create a database with bad credentials
getConnection( true, true, FOURTEENTH_DB, WALNUT_USER, INVALID_AUTHENTICATION );
}
/**
* <p>
* Verify that credentials work when they are stored in a db accessed via
* the jar or classpath subprotocols.
* </p>
*/
private void vetCredentialsViaSubprotocol() throws Exception
{
// create a new database
Connection grapeConn = openConnection( TENTH_DB, GRAPE_USER, true, null );
String[][] legalUsers = _localAuthentication ?
new String[][] { { GRAPE_USER } } : new String[][] {};
assertResults
(
grapeConn,
"select username from sys.sysusers order by username",
legalUsers,
false
);
// Databases can't be created by users who don't have credentials stored in the credentials database
Connection walnutConn = getConnection
( true, true, ELEVENTH_DB, WALNUT_USER, INVALID_AUTHENTICATION );
}
/**
* <p>
* Verify the core behavior of NATIVE authentication.
* </p>
*/
private void vetCoreBehavior() throws Exception
{
// can't create any database until the credentials db has been created
Connection secondDBConn = getConnection
( _nativeAuthentication, true, SECOND_DB, APPLE_USER, CREDENTIALS_DB_DOES_NOT_EXIST );
// can't create a credentials db with an empty username or password
if ( _nativeAuthentication ) { vetEmptyCredentials(); }
// create the credentials database
Connection sysadminConn = openConnection( CREDENTIALS_DB, DBO, true, null );
// null password should not generate NPE
getConnection( _nativeAuthentication, true, CREDENTIALS_DB, DBO, null, INVALID_AUTHENTICATION );
// add the dbo as a user if she wasn't created when the database was created
if ( !_nativeAuthentication )
{
// verify that only the DBO can create credentials for the DBO
Connection pineappleConn = openConnection( CREDENTIALS_DB, PINEAPPLE_USER, true, null );
addUser( pineappleConn, DBO, DBO_ONLY_OPERATION ); // this should fail
addUser( sysadminConn, DBO );
}
// add another legal user
addUser( sysadminConn, APPLE_USER );
addUser( sysadminConn, BANANA_USER );
//
// Creating the credentials db should have stored the following information in it:
//
// 1) The DBO's credentials should have been stored in SYSUSERS.
// 2) The authentication provider should have been set to NATIVE::LOCAL
//
String[][] legalUsers = new String[][] { { APPLE_USER }, { BANANA_USER } , { DBO } };
assertResults
(
sysadminConn,
"select username from sys.sysusers order by username",
legalUsers,
false
);
String[][] authenticationProvider = new String[][] { { "NATIVE::LOCAL" } };
assertResults
(
sysadminConn,
"values ( syscs_util.syscs_get_database_property( 'derby.authentication.provider' ) )",
authenticationProvider,
false
);
// there should be no need to explicitly set the sql authorization property
String[][] sqlAuthorization = new String[][] { { null } };
assertResults
(
sysadminConn,
"values ( syscs_util.syscs_get_database_property( 'derby.database.sqlAuthorization' ) )",
sqlAuthorization,
false
);
vetSQLAuthorizationOn();
// Sanity-check that the creator of the credentials db is the DBO
String[][] dboName = new String[][] { { DBO } };
assertResults
(
sysadminConn,
"select authorizationID from sys.sysschemas where schemaName = 'SYS'",
dboName,
false
);
// verify password hashing
if ( _localAuthentication )
{
passwordHashingTests( sysadminConn );
}
// Databases can't be created by users who don't have credentials stored in the credentials database
Connection thirdDBConn = getConnection
( _nativeAuthentication, true, THIRD_DB, WALNUT_USER, INVALID_AUTHENTICATION );
// Now let the other valid user create a database
if ( secondDBConn == null )
{
secondDBConn = getConnection( false, true, SECOND_DB, APPLE_USER, null );
}
// verify that the other valid user is the dbo in the database he just created
assertResults
(
secondDBConn,
"select authorizationID from sys.sysschemas where schemaName = 'SYS'",
new String[][] { { APPLE_USER } },
false
);
// NATIVE authentication turns on SQL authorization in the second database
assertResults
(
secondDBConn,
"values ( syscs_util.syscs_get_database_property( 'derby.database.sqlAuthorization' ) )",
sqlAuthorization,
false
);
//
// If LOCAL authentication was specified...
//
// 1) It will be turned on in the second database too.
// 2) The other legal user's credentials (as the database dbo) will be stored.
//
authenticationProvider = _localAuthentication ? new String[][] { { "NATIVE::LOCAL" } } : new String[][] { { null } };
assertResults
(
secondDBConn,
"values ( syscs_util.syscs_get_database_property( 'derby.authentication.provider' ) )",
authenticationProvider,
false
);
legalUsers = _localAuthentication ? new String[][] { { APPLE_USER } } : new String[][] {};
assertResults
(
secondDBConn,
"select username from sys.sysusers order by username",
legalUsers,
false
);
///////////////////////////////////////////////////////////////////////////////////
//
// You can't turn off authentication or SQL authorization when NATIVE authentication
// is set in the database. If NATIVE authentication is set at the system level,
// you can still override it with database only properties.
//
///////////////////////////////////////////////////////////////////////////////////
Connection seventhDBOConn = openConnection( SEVENTH_DB, DBO, true, null );
if ( _localAuthentication) { addUser( seventhDBOConn, APPLE_USER ); }
goodStatement( seventhDBOConn, "create table t1( a int )" );
goodStatement( seventhDBOConn, "insert into t1( a ) values ( 10 )" );
// at this point, you can't even force SQL authorization off on disk
setDatabaseProperty
( _nativeAuthentication, seventhDBOConn, "derby.database.sqlAuthorization", "false", BAD_PROPERTY_CHANGE );
// you can change these properties, but the change will be overridden by NATIVE authentication
goodStatement
( seventhDBOConn, "call syscs_util.syscs_set_database_property( 'derby.connection.requireAuthentication', 'false' )" );
goodStatement
( seventhDBOConn, "call syscs_util.syscs_set_database_property( 'derby.database.propertiesOnly', 'true' )" );
// now bring down the database so that the new property settings take effect
_seventhDBSetup.getTestConfiguration().shutdownDatabase();
// if NATIVE authentication is set in the database, then sql authorization prevents this legal user from viewing private data
Connection seventhAppleConn = openConnection( SEVENTH_DB, APPLE_USER, true, null );
vetStatement( _localAuthentication, seventhAppleConn, "select * from " + DBO + ".t1", NO_COLUMN_PERMISSION );
// if NATIVE authentication is set in the database, then authentication still prevents this user from logging in
Connection seventhWalnutConn = getConnection
( _localAuthentication, true, SEVENTH_DB, WALNUT_USER, INVALID_AUTHENTICATION );
///////////////////////////////////////////////////////////////////////////////////
//
// Using database only properties, you can override system-specified NATIVE
// authentication as long as the NATIVE authentication is not LOCAL, i.e., not set
// at the database level.
//
///////////////////////////////////////////////////////////////////////////////////
Connection eighthDBOConn = openConnection( EIGHTH_DB, DBO, true, null );
// create BUILTIN credentials and set BULTIN authentication via database only properties
setDatabaseProperty
( _localAuthentication, eighthDBOConn, "derby.authentication.provider", "BUILTIN", INVALID_PROVIDER_CHANGE );
setDatabaseProperty( false, eighthDBOConn, "derby.connection.requireAuthentication", "true", null );
setDatabaseProperty( false, eighthDBOConn, "derby.database.propertiesOnly", "true", null );
addBuiltinUser( eighthDBOConn, DBO );
addBuiltinUser( eighthDBOConn, BUILTIN_USER );
// now bring down the database so that the new property settings take effect
_eighthDBSetup.getTestConfiguration().shutdownDatabase();
// succeeds unless LOCAL authentication overrides database-specified BUILTIN authentication
Connection builtinConn = getConnection
( _localAuthentication, true, EIGHTH_DB, BUILTIN_USER, INVALID_AUTHENTICATION );
///////////////////////////////////////////////////////////////////////////////////
//
// Vet databases accessed via subprotocols.
//
///////////////////////////////////////////////////////////////////////////////////
if (enableSubprotocolTests())
{
// database accessed via jar subprotocol
vetProtocol( jarDBName( _credentialsDBLocation ) );
// database accessed via classpath subprotocol
vetProtocol( classpathDBName() );
}
///////////////////////////////////////////////////////////////////////////////////
//
// Verify that the DBO can grant and revoke execute privilege on the NATIVE procedures.
//
///////////////////////////////////////////////////////////////////////////////////
Connection twelthDBOConn = openConnection( TWELTH_DB, DBO, true, null );
if ( _localAuthentication ) { addUser( twelthDBOConn, APPLE_USER ); }
Connection twelthAppleConn = openConnection( TWELTH_DB, APPLE_USER, true, null );
// these fail without the appropriate grant
if ( _localAuthentication )
{
vetStatement( _nativeAuthentication, twelthAppleConn,
"call syscs_util.syscs_create_user( 'JULIUS', 'juliuspassword' )", NO_EXECUTE_PERMISSION );
vetStatement( _nativeAuthentication, twelthAppleConn,
"call syscs_util.syscs_reset_password( 'JULIUS', 'foopassword' )", NO_EXECUTE_PERMISSION );
vetStatement( _nativeAuthentication, twelthAppleConn,
"call syscs_util.syscs_drop_user( 'JULIUS' )", NO_EXECUTE_PERMISSION );
}
// but you always have permission to change your own password
vetStatement( !_localAuthentication, twelthAppleConn,
"call syscs_util.syscs_modify_password( 'foo' )", MISSING_USER );
// grant execute permission and retry the test
vetStatement
( !_nativeAuthentication, twelthDBOConn,
"grant execute on procedure syscs_util.syscs_create_user to " + APPLE_USER, SQL_AUTHORIZATION_NOT_ON );
vetStatement
( !_nativeAuthentication, twelthDBOConn,
"grant execute on procedure syscs_util.syscs_reset_password to " + APPLE_USER, SQL_AUTHORIZATION_NOT_ON );
vetStatement
( !_nativeAuthentication, twelthDBOConn,
"grant execute on procedure syscs_util.syscs_drop_user to " + APPLE_USER, SQL_AUTHORIZATION_NOT_ON );
if ( _localAuthentication )
{
goodStatement( twelthAppleConn, "call syscs_util.syscs_create_user( 'HORACE', 'horacepassword' )" );
goodStatement( twelthAppleConn, "call syscs_util.syscs_reset_password( 'HORACE', 'foopassword' )" );
goodStatement( twelthAppleConn, "call syscs_util.syscs_drop_user( 'HORACE' )" );
}
// revoke execute permission and retry the test
vetStatement
( !_nativeAuthentication, twelthDBOConn,
"revoke execute on procedure syscs_util.syscs_create_user from " + APPLE_USER + " restrict",
SQL_AUTHORIZATION_NOT_ON );
vetStatement
( !_nativeAuthentication, twelthDBOConn,
"revoke execute on procedure syscs_util.syscs_reset_password from " + APPLE_USER + " restrict",
SQL_AUTHORIZATION_NOT_ON );
vetStatement
( !_nativeAuthentication, twelthDBOConn,
"revoke execute on procedure syscs_util.syscs_drop_user from " + APPLE_USER + " restrict",
SQL_AUTHORIZATION_NOT_ON );
if ( _localAuthentication )
{
vetStatement( _nativeAuthentication, twelthAppleConn,
"call syscs_util.syscs_create_user( 'CORNELIA', 'corneliapassword' )", NO_EXECUTE_PERMISSION );
vetStatement( _nativeAuthentication, twelthAppleConn,
"call syscs_util.syscs_reset_password( 'CORNELIA', 'foopassword' )", NO_EXECUTE_PERMISSION );
vetStatement( _nativeAuthentication, twelthAppleConn,
"call syscs_util.syscs_drop_user( 'CORNELIA' )", NO_EXECUTE_PERMISSION );
}
}
private static String jarDBName( int credentialsDBLocation ) throws Exception
{
String dbName = (credentialsDBLocation == JAR_ENCRYPTED) ? "nastEncrypted" : "nast";
return "jar:(" + SupportFilesSetup.getReadOnlyFileName( NAST1_JAR_FILE ) + ")" + dbName;
}
private static String classpathDBName() { return "classpath:nast"; }
private void vetEmptyCredentials() throws Exception
{
vetEmptyCredentials( null, null );
vetEmptyCredentials( "", null );
vetEmptyCredentials( null, "" );
vetEmptyCredentials( "", "" );
vetEmptyCredentials( "foo", null );
vetEmptyCredentials( "foo", "" );
if ( isEmbedded() ) { vetEmptyCredentials( null, "bar" ); } // the network server fabricates a username of APP
vetEmptyCredentials( "", "bar" );
}
private void vetEmptyCredentials( String user, String password ) throws Exception
{
String[] expectedSQLStates = new String[] { INVALID_AUTHENTICATION,BAD_NETWORK_AUTHENTICATION };
getConnection( true, true, CREDENTIALS_DB, user, password, expectedSQLStates );
}
private void addBuiltinUser( Connection conn, String user ) throws Exception
{
String key = "derby.user." + user;
String value = getPassword( user );
setDatabaseProperty( false, conn, key, value, null );
}
/**
* <p>
* Verify that databases in jar files and on the classpath can be opened
* and respect NATIVE/LOCAL settings.
* </p>
*/
private void vetProtocol( String protocolDBName ) throws Exception
{
///////////////////////////////////////////////////////////////////////////////////
//
// Verify that regardless of any other settings, NATIVE authentication is on in the
// database stored in a jar file or accessed over the classpath
//
///////////////////////////////////////////////////////////////////////////////////
Connection grapeConn = getConnection( false, false, protocolDBName, GRAPE_USER, null );
vetStatement( true, grapeConn, "select * from " + DBO + ".t", NO_COLUMN_PERMISSION );
getConnection( true, false, protocolDBName, WALNUT_USER, INVALID_AUTHENTICATION );
}
/**
* <p>
* Verify the casing behavior of user identifiers.
* </p>
*/
private void vetCasing() throws Exception
{
// only run this if we are using NATIVE authentication and the DBO's creds are already
// in the credentials DB. this is just to make sure that we are in a database where
// the DBO's creds already exist.
if ( !_nativeAuthentication ) { return; }
if ( _localAuthentication ) { return; }
String originalPricklyPearPassword = "prickly_pear_password";
// connect to the credentials database
Connection dboConn = openConnection( CREDENTIALS_DB, DBO, true, null );
// can't add redundant credentials for an authorization id by changing the casing
addUser( dboConn, CAMEL_CASE_DBO, USER_ALREADY_EXISTS );
// using double-quotes, however, you can create a camel-case user name
addUser( dboConn, PEAR_USER, null );
assertFalse( PEAR_USER.equals( PRICKLY_PEAR_USER ) );
assertTrue( PEAR_USER.toUpperCase().equals( PRICKLY_PEAR_USER.toUpperCase() ) );
goodStatement
(
dboConn,
"call syscs_util.syscs_create_user( '\"" + PRICKLY_PEAR_USER + "\"', '" + originalPricklyPearPassword + "' )"
);
String[][] legalUsers = new String[][] { { PEAR_USER }, { PRICKLY_PEAR_USER } };
assertResults
(
dboConn,
"select username from sys.sysusers where username like 'P%' order by username",
legalUsers,
false
);
// schema name for an unquoted user name should be upper case
Connection pearConn = openConnection( CREDENTIALS_DB, PEAR_USER, true, null );
assertResults
(
pearConn,
"values current schema",
new String[][] { { PEAR_USER.toUpperCase() } },
false
);
// schema name for a quoted user name should be camel case
Connection pricklyPearConn = openConnection
( CREDENTIALS_DB, doubleQuote( PRICKLY_PEAR_USER ), originalPricklyPearPassword, true, null );
assertResults
(
pricklyPearConn,
"values current schema",
new String[][] { { PRICKLY_PEAR_USER } },
false
);
//
// Verify the casing on the USERNAME argument to the NATIVE procedures
//
// modify password
String rev2_password = "password_rev2";
goodStatement
(
pricklyPearConn,
"call syscs_util.syscs_modify_password( '" + rev2_password + "' )"
);
getConnection
( true, true, CREDENTIALS_DB, doubleQuote( PRICKLY_PEAR_USER ), originalPricklyPearPassword, INVALID_AUTHENTICATION );
pricklyPearConn = openConnection
( CREDENTIALS_DB, doubleQuote( PRICKLY_PEAR_USER ), rev2_password, true, null );
// reset password
String rev3_password = "password_rev3";
goodStatement
(
dboConn,
"call syscs_util.syscs_reset_password( '" + doubleQuote( PRICKLY_PEAR_USER ) + "', '" + rev3_password + "' )"
);
getConnection
( true, true, CREDENTIALS_DB, doubleQuote( PRICKLY_PEAR_USER ), rev2_password, INVALID_AUTHENTICATION );
pricklyPearConn = openConnection
( CREDENTIALS_DB, doubleQuote( PRICKLY_PEAR_USER ), rev3_password, true, null );
// drop user
int userCount = countUsers( dboConn );
goodStatement
(
dboConn,
"call syscs_util.syscs_drop_user( '" + doubleQuote( PRICKLY_PEAR_USER ) + "' )"
);
assertEquals( userCount - 1, countUsers( dboConn ) );
getConnection
( true, true, CREDENTIALS_DB, doubleQuote( PRICKLY_PEAR_USER ), rev3_password, INVALID_AUTHENTICATION );
}
private String doubleQuote( String raw ) { return "\"" + raw + "\""; }
private int countUsers( Connection conn ) throws Exception
{
PreparedStatement ps = chattyPrepare( conn, "select count(*) from sys.sysusers" );
ResultSet rs = ps.executeQuery();
rs.next();
try { return rs.getInt( 1 ); }
finally
{
rs.close();
ps.close();
}
}
/**
* <p>
* The vetCoreBehavior() method verifies credentials-checking for the
* following system-wide operations:
* </p>
*
* <ul>
* <li>Database creation.</li>
* <li>Engine shutdown.</li>
* <li>Server shutdown. The default credentials are embedded inside the NetworkServerControl
* created by NetworkServerTestSetup.</li>
* </ul>
*
* <p>
* This method verifies credentials-checking for this additional
* system-wide operation:
* </p>
*
* <ul>
* <li>Database restoration.</li>
* </ul>
*/
private void vetSystemWideOperations() throws Exception
{
// create a database which we will backup and restore
Connection dboConn = openConnection( SIXTH_DB, DBO, true, null );
// add another user who can perform restores successfully
if ( _localAuthentication ) { addUser( dboConn, BANANA_USER ); }
// add a table which we will backup and then drain. this is so that later on we can
// verify that we really restored the database rather than just reconnected to the
// original version.
goodStatement( dboConn, "create table t( a int )" );
goodStatement( dboConn, "insert into t( a ) values ( 1000 )" );
if ( _nativeAuthentication)
{
goodStatement( dboConn, "grant select on table t to public" );
goodStatement( dboConn, "grant insert on table t to public" );
}
goodStatement( dboConn, "call syscs_util.syscs_backup_database( '" + _fullBackupDir + "' )" );
goodStatement( dboConn, "delete from t" );
// this user is valid both in the system-wide credentials db and in the local db
shutdownAndRestoreDB( true, BANANA_USER, null );
//
// If we are doing local authentication, then restoration will fail when we use
// the credentials of a user who is in the system-wide SYSUSERS but not
// in the SYSUSERS of the database being restored. Restoration involves two
// authentication attempts: First we authenticate system-wide in order to
// verify that it's ok to proceed with the restoration. After that, we attempt
// to connect to the restored database. It is the authentication of the second
// attempt which may fail here.
//
shutdownAndRestoreDB( !_localAuthentication, APPLE_USER, INVALID_AUTHENTICATION );
// delete the backup directory
assertDirectoryDeleted( new File( _fullBackupDir ) );
}
private void shutdownAndRestoreDB( boolean shouldSucceed, String user, String expectedSQLState ) throws Exception
{
// shutdown the database. the restore will overwrite it.
_sixthDBSetup.getTestConfiguration().shutdownDatabase();
// the physical database name has some parent directories in it.
// we need to strip these off because backup ignores them.
String dbName = _sixthDBSetup.physicalDatabaseName();
int slashIdx = dbName.lastIndexOf( "/" );
if ( slashIdx >= 0 ) { dbName = dbName.substring( slashIdx + 1 ); }
DataSource ds = JDBCDataSource.getDataSourceLogical( SIXTH_DB );
String fullRestoreDir = _fullBackupDir + "/" + dbName;
JDBCDataSource.setBeanProperty( ds, "connectionAttributes", "restoreFrom=" + fullRestoreDir );
Connection conn = null;
try {
conn = ds.getConnection( user, getPassword( user ) );
if ( !shouldSucceed ) { fail( tagError( "Database restoration should have failed." ) ); }
}
catch (SQLException se)
{
if ( shouldSucceed ) { fail( tagError( "Database restoration unexpectedly failed." ) );}
else { assertSQLState( expectedSQLState, se ); }
}
if ( conn != null )
{
// verify that this is the version which was backed up, not the original
assertResults
(
conn,
"select a from " + DBO + ".t",
new String[][] { { "1000" } },
false
);
// add another tuple to distinguish the database from later attempts
// to re-initialize it from the backup
goodStatement( conn, "insert into " + DBO + ".t( a ) values ( 2000 )" );
}
}
/**
* <p>
* Try changing the value of the provider property on disk.
* These tests are run only if authentication is turned off.
* </p>
*/
private void vetProviderChanges() throws Exception
{
// create an empty database without authentication turned on
String dbo = ORANGE_USER;
Connection dboConn = openConnection( FOURTH_DB, dbo, true, null );
// NATIVE authentication isn't on, so you can store oddball values for the authentication provider
goodStatement
( dboConn, "call syscs_util.syscs_set_database_property( 'derby.authentication.provider', 'com.acme.AcmeAuthenticator' )" );
// can't turn on NATIVE authentication until you have stored credentials for the dbo
expectExecutionError
(
dboConn, INVALID_PROVIDER_CHANGE,
"call syscs_util.syscs_set_database_property( 'derby.authentication.provider', 'NATIVE::LOCAL' )"
);
// NATIVE::LOCAL is the only legal value which the authentication provider property can take on disk
expectExecutionError
(
dboConn, INVALID_PROVIDER_CHANGE,
"call syscs_util.syscs_set_database_property( 'derby.authentication.provider', 'NATIVE:db:LOCAL' )"
);
expectExecutionError
(
dboConn, INVALID_PROVIDER_CHANGE,
"call syscs_util.syscs_set_database_property( 'derby.authentication.provider', 'NATIVE:LOCAL' )"
);
// store credentials for the DBO
addUser( dboConn, dbo );
addUser( dboConn, PEAR_USER );
// verify that you can't drop the dbo
expectExecutionError
(
dboConn, CANT_DROP_DBO,
"call syscs_util.syscs_drop_user( '" + dbo + "' )"
);
String[][] legalUsers = new String[][] { { dbo }, { PEAR_USER } };
assertResults
(
dboConn,
"select username from sys.sysusers order by username",
legalUsers,
false
);
// creating the DBO turned on NATIVE + LOCAL
// once set, you can't unset or change it
expectExecutionError
(
dboConn, INVALID_PROVIDER_CHANGE,
"call syscs_util.syscs_set_database_property( 'derby.authentication.provider', 'NATIVE::LOCAL' )"
);
expectExecutionError
(
dboConn, INVALID_PROVIDER_CHANGE,
"call syscs_util.syscs_set_database_property( 'derby.authentication.provider', null )"
);
expectExecutionError
(
dboConn, INVALID_PROVIDER_CHANGE,
"call syscs_util.syscs_set_database_property( 'derby.authentication.provider', 'com.acme.AcmeAuthenticator' )"
);
// verify that the authentication provider property has the value we expect
String[][] authenticationProvider = new String[][] { { "NATIVE::LOCAL" } };
assertResults
(
dboConn,
"values ( syscs_util.syscs_get_database_property( 'derby.authentication.provider' ) )",
authenticationProvider,
false
);
// create a table for a simple authorization check later on
goodStatement( dboConn, "create table t( a int )" );
// shutdown this database so that the on-disk properties will take effect on reboot
_fourthDBSetup.getTestConfiguration().shutdownDatabase();
// can't connect to the database with credentials which aren't stored in it.
Connection appleConn = getConnection( true, true, FOURTH_DB, APPLE_USER, INVALID_AUTHENTICATION );
// ...but these credentials work
Connection pearConn = openConnection( FOURTH_DB, PEAR_USER, true, null );
// should get authorization errors trying to select from a table private to the DBO
// and from trying to view the credentials table
expectExecutionError( pearConn, NO_COLUMN_PERMISSION, "select * from " + dbo + ".t" );
expectCompilationError( pearConn, DBO_ONLY_OPERATION, "select username from sys.sysusers" );
// verify a sensible error if you try to set the authentication provider
// to the missing classname NATIVE. we only check this for the embedded
// case because the network scrunches exceptions together and we lose
// the structure which allows us to look for nested SQLStates.
if ( isEmbedded() )
{
Connection fifteenthConn = openConnection( FIFTEENTH_DB, dbo, true, null );
goodStatement
( fifteenthConn, "call syscs_util.syscs_set_database_property( 'derby.connection.requireAuthentication', 'true' )" );
goodStatement
( fifteenthConn, "call syscs_util.syscs_set_database_property( 'derby.authentication.provider', 'NATIVE' )" );
_fifteenthDBSetup.getTestConfiguration().shutdownDatabase();
getConnection( true, true, FIFTEENTH_DB, DBO, BAD_USER_AUTHENTICATOR_CLASS );
}
}
/**
* <p>
* Verify that password lifetimes are checked.
* </p>
*/
private void vetPasswordLifetime() throws Exception
{
// create another database
Connection dboConn = openConnection( FIFTH_DB, DBO, true, null );
// add another legal user
addUser( dboConn, APPLE_USER );
Connection appleConn = passwordExpiring( false, FIFTH_DB, APPLE_USER );
// setup so that passwords are expiring after the db is rebooted.
// shutdown the database in this test so that the new property settings take effect.
goodStatement
( dboConn, "call syscs_util.syscs_set_database_property( 'derby.authentication.native.passwordLifetimeMillis', '86400000' )" );
goodStatement
( dboConn, "call syscs_util.syscs_set_database_property( 'derby.authentication.native.passwordLifetimeThreshold', '2.0' )" );
_fifthDBSetup.getTestConfiguration().shutdownDatabase();
// password should be expiring
dboConn = passwordExpiring( true, FIFTH_DB, DBO );
appleConn = passwordExpiring( true, FIFTH_DB, APPLE_USER );
// setup so that passwords have expired after we reboot the database.
// shutdown the database so that the new property settings take effect.
goodStatement
( dboConn, "call syscs_util.syscs_set_database_property( 'derby.authentication.native.passwordLifetimeMillis', '1' )" );
_fifthDBSetup.getTestConfiguration().shutdownDatabase();
// the DBO's password does not expire
dboConn = openConnection( FIFTH_DB, DBO, true, null );
// but the other user's password has expired
appleConn = getConnection( true, true, FIFTH_DB, APPLE_USER, INVALID_AUTHENTICATION );
// setup so that passwords don't expire after we reboot the database.
// shutdown the database so that the new property settings take effect.
goodStatement
( dboConn, "call syscs_util.syscs_set_database_property( 'derby.authentication.native.passwordLifetimeMillis', '0' )" );
_fifthDBSetup.getTestConfiguration().shutdownDatabase();
// passwords should NOT be expiring or expired
dboConn = passwordExpiring( false, FIFTH_DB, DBO );
appleConn = passwordExpiring( false, FIFTH_DB, APPLE_USER );
// check that invalid property settings are caught
expectExecutionError
(
dboConn, BAD_PASSWORD_PROPERTY,
"call syscs_util.syscs_set_database_property( 'derby.authentication.native.passwordLifetimeMillis', 'rabbit' )"
);
expectExecutionError
(
dboConn, BAD_PASSWORD_PROPERTY,
"call syscs_util.syscs_set_database_property( 'derby.authentication.native.passwordLifetimeThreshold', '-1' )"
);
}
private void passwordHashingTests
( Connection dboConnection )
throws Exception
{
// set a message digest algorithm in case it was unset by a previous test.
// a message digest algorithm must be set in order to use NATIVE authentication
String originalDigestAlgorithm = getDatabaseProperty( dboConnection, "derby.authentication.builtin.algorithm" );
if ( originalDigestAlgorithm == null )
{
setDatabaseProperty( dboConnection, "derby.authentication.builtin.algorithm", "SHA-1" );
}
String defaultDigestAlgorithm = getDatabaseProperty( dboConnection, "derby.authentication.builtin.algorithm" );
goodStatement( dboConnection, "call syscs_util.syscs_create_user( 'pht', 'phtpassword' )" );
vetHashingScheme( dboConnection, "pht", HASHING_FORMAT_10_9, 16, 1000, defaultDigestAlgorithm );
int saltLength = 5;
setDatabaseProperty( dboConnection, "derby.authentication.builtin.saltLength", Integer.toString( saltLength ) );
goodStatement( dboConnection, "call syscs_util.syscs_reset_password( 'pht', 'newsaltlength' )" );
vetHashingScheme( dboConnection, "pht", HASHING_FORMAT_10_9, saltLength, 1000, defaultDigestAlgorithm );
int iterations = 10;
setDatabaseProperty( dboConnection, "derby.authentication.builtin.iterations", Integer.toString( iterations ) );
goodStatement( dboConnection, "call syscs_util.syscs_reset_password( 'pht', 'newiterations' )" );
vetHashingScheme( dboConnection, "pht", HASHING_FORMAT_10_9, saltLength, iterations, defaultDigestAlgorithm );
String digestAlgorithm = "SHA-1";
setDatabaseProperty( dboConnection, "derby.authentication.builtin.algorithm", digestAlgorithm );
goodStatement( dboConnection, "call syscs_util.syscs_reset_password( 'pht', 'newiterations' )" );
vetHashingScheme( dboConnection, "pht", HASHING_FORMAT_10_9, saltLength, iterations, digestAlgorithm );
setDatabaseProperty( dboConnection, "derby.authentication.builtin.algorithm", null );
expectExecutionError( dboConnection, WEAK_AUTHENTICATION, "call syscs_util.syscs_reset_password( 'pht', 'badalgorithm' )" );
setDatabaseProperty( dboConnection, "derby.authentication.builtin.saltLength", null );
setDatabaseProperty( dboConnection, "derby.authentication.builtin.iterations", null );
setDatabaseProperty( dboConnection, "derby.authentication.builtin.algorithm", defaultDigestAlgorithm );
goodStatement( dboConnection, "call syscs_util.syscs_drop_user( 'pht' )" );
// reset the message digest algorithm in order to not disrupt the existing tests
if ( originalDigestAlgorithm == null )
{
setDatabaseProperty( dboConnection, "derby.authentication.builtin.algorithm", null );
}
}
private void setDatabaseProperty( Connection conn, String key, String value )
throws Exception
{
if ( value == null ) { value = "cast ( null as varchar( 32672 ) )"; }
else { value = "'" + value + "'"; }
String command = "call syscs_util.syscs_set_database_property( '" + key + "', " + value + " )";
goodStatement( conn, command );
}
private String getDatabaseProperty( Connection conn, String key )
throws Exception
{
PreparedStatement ps = chattyPrepare( conn, "values( syscs_util.syscs_get_database_property( '" + key + "' ) )" );
ResultSet rs = ps.executeQuery();
try {
rs.next();
return rs.getString( 1 );
}
finally
{
rs.close();
ps.close();
}
}
private void vetHashingScheme
(
Connection conn,
String userName,
String expectedHashingFormat,
int expectedSaltLength,
int expectedIterations,
String expectedDigestAlgorithm
)
throws Exception
{
String hashingScheme = getHashingScheme( conn, userName );
int firstColonPosition = hashingScheme.indexOf( ":" );
int secondColonPosition = hashingScheme.indexOf( ":", firstColonPosition + 1 );
int thirdColonPosition = hashingScheme.indexOf( ":", secondColonPosition + 1 );
String actualHashingFormat = hashingScheme.substring( 0, firstColonPosition );
String salt = hashingScheme.substring( firstColonPosition + 1, secondColonPosition );
String iterationString = hashingScheme.substring( secondColonPosition + 1, thirdColonPosition );
String actualDigestAlgorithm = hashingScheme.substring( thirdColonPosition + 1 );
int actualSaltLength = salt.length();
int actualIterations = Integer.parseInt( iterationString );
assertEquals( expectedHashingFormat, actualHashingFormat );
assertEquals( expectedSaltLength * HEX_CHARS_PER_BYTE, actualSaltLength );
assertEquals( expectedIterations, actualIterations );
assertEquals( expectedDigestAlgorithm, actualDigestAlgorithm );
}
private String getHashingScheme( Connection conn, String userName )
throws Exception
{
PreparedStatement ps = conn.prepareStatement( "select userName, hashingScheme from sys.sysusers" );
ResultSet rs = ps.executeQuery();
// normalize the user name
userName = userName.toUpperCase();
try {
while ( rs.next() )
{
if ( userName.equals( rs.getString( 1 ) ) ) { return rs.getString( 2 ); }
}
} finally
{
rs.close();
ps.close();
}
fail( "Could not find credentials for " + userName );
// never get here
return null;
}
private void vetSQLAuthorizationOn() throws Exception
{
Connection nonDBOConn = openConnection( CREDENTIALS_DB, APPLE_USER, true, null );
String query = "select username from sys.sysusers" ;
try {
chattyPrepare( nonDBOConn, query );
if ( _nativeAuthentication ) { fail( "SQL Authorization not on!" ); }
}
catch (SQLException se)
{
if ( _nativeAuthentication )
{
assertSQLState( DBO_ONLY_OPERATION, se );
}
else
{
fail( "Caught unexpected SQLException: " + se.getSQLState() + ": " + se.getMessage() );
}
}
}
private Connection getConnection
( boolean shouldFail, boolean isLogicalName, String dbName, String user, String expectedSQLState )
throws Exception
{
return getConnection( shouldFail, isLogicalName, dbName, user, getPassword( user ), expectedSQLState );
}
private Connection getConnection
( boolean shouldFail, boolean isLogicalName, String dbName, String user, String password, String expectedSQLState )
throws Exception
{
return getConnection( shouldFail, isLogicalName, dbName, user, password, new String[] { expectedSQLState } );
}
private Connection getConnection
( boolean shouldFail, boolean isLogicalName, String dbName, String user, String password, String[] expectedSQLStates )
throws Exception
{
Connection conn = null;
reportConnectionAttempt( dbName, user, password, isLogicalName );
try {
conn = openConnection( dbName, user, password, isLogicalName, null );
if ( shouldFail ) { fail( tagError( "Connection to " + dbName + " should have failed." ) ); }
}
catch (Throwable t)
{
if ( shouldFail && (t instanceof SQLException) )
{
SQLException se = (SQLException) t;
StringBuffer buffer = new StringBuffer();
// ok if the sqlstate is one of the expected ones
for ( int i = 0; i < expectedSQLStates.length; i++ )
{
String expected = expectedSQLStates[ i ];
buffer.append( " " + expected );
if ( vetSQLState( se, expected ) ) { return null; }
}
fail( tagError( "SQLState not in expected list: " + buffer.toString() ) );
}
else
{
printStackTrace( t );
fail( tagError( "Connection to " + dbName + " unexpectedly failed." ) );
}
}
return conn;
}
// look for a sql state in a SQLException and its chained exceptions. returns true if found
private boolean vetSQLState( SQLException actual, String expectedSQLState )
throws Exception
{
if ( actual == null ) { return false; }
if ( expectedSQLState.equals( actual.getSQLState() ) ) { return true; }
Throwable t = actual.getCause();
if ( t instanceof SQLException )
{
if ( vetSQLState( (SQLException) t, expectedSQLState ) ) { return true; }
}
return vetSQLState( actual.getNextException(), expectedSQLState );
}
// connect but expect a warning that the password is about to expire
private Connection passwordExpiring( boolean expiring, String dbName, String user )
throws Exception
{
Connection conn = null;
reportConnectionAttempt( dbName, user, getPassword( user ), true );
String expectedSQLState = DBO.equals( user ) ? DBO_PASSWORD_EXPIRING : PASSWORD_EXPIRING;
conn = openConnection( dbName, user, true, null );
SQLWarning warning = conn.getWarnings();
if ( expiring )
{
assertNotNull( tagError( "Should have seen a warning" ), warning );
assertSQLState( expectedSQLState, warning );
}
else
{
assertNull( tagError( "Should not have seen a warning" ), warning );
}
return conn;
}
private void reportConnectionAttempt( String dbName, String user, String password, boolean isLogicalName )
{
String message = "User '" + user + "' with password '" + password + "' attempting to get connection to database " + dbName;
if ( isLogicalName ) { message = message + " aka " + getTestConfiguration().getPhysicalDatabaseName( dbName ) ; }
println( message );
}
private void addUser( Connection conn, String user ) throws Exception
{
addUser( conn, user, null );
}
private void addUser( Connection conn, String user, String expectedSQLState ) throws Exception
{
boolean shouldFail = (expectedSQLState != null);
String password = getPassword( user );
String statement = "call syscs_util.syscs_create_user( '" + user + "', '" + password + "' )";
if ( shouldFail )
{
println( "Expecting " + expectedSQLState + " when executing '" + statement + "'" );
}
try {
goodStatement( conn, statement );
if ( shouldFail ) { fail( tagError( "Should not have been allowed to create user " + user ) ); }
}
catch (SQLException se)
{
if ( shouldFail ) { assertSQLState( expectedSQLState, se ); }
else { fail( tagError( "Unexpectedly failed to create user " + user ) );}
}
}
private void setDatabaseProperty( boolean shouldFail, Connection conn, String key, String value, String expectedSQLState )
throws Exception
{
if ( value == null ) { value = "cast ( null as varchar( 32672 ) )"; }
else { value = "'" + value + "'"; }
String command = "call syscs_util.syscs_set_database_property( '" + key + "', " + value + " )";
try {
goodStatement( conn, command );
if ( shouldFail ) { fail( tagError( "Property setting should have failed." ) ); }
}
catch (SQLException se)
{
if ( shouldFail ) { assertSQLState( expectedSQLState, se ); }
else { fail( tagError( "Property setting unexpectedly failed." ) );}
}
}
private void vetStatement( boolean shouldFail, Connection conn, String command, String expectedSQLState )
throws Exception
{
try {
goodStatement( conn, command );
if ( shouldFail ) { fail( tagError( "Statement should have failed." ) ); }
}
catch (SQLException se)
{
if ( shouldFail ) { assertSQLState( expectedSQLState, se ); }
else
{
String error = "Statement unexpectedly failed. SQLState = " + se.getSQLState() + ": " + se.getMessage();
fail( tagError( error ) );
}
}
}
///////////////////////////////////////////////////////////////////////////////////
//
// MINIONS
//
///////////////////////////////////////////////////////////////////////////////////
/** Open a connection to a database using the supplied credentials */
private Connection openConnection( String dbName, String user, boolean isLogicalName, Properties props )
throws SQLException
{
return openConnection( dbName, user, getPassword( user ), isLogicalName, props );
}
private Connection openConnection( String dbName, String user, String password, boolean isLogicalName, Properties props )
throws SQLException
{
if ( isLogicalName )
{
return getTestConfiguration().openConnection( dbName, user, password );
}
else
{
return getTestConfiguration().openPhysicalConnection( dbName, user, password, props );
}
}
/** Get the password for a user */
private static String getPassword( String user ) { return user + "_password"; }
/** Tag an error with the name of the test configuration */
private String tagError( String text ) { return nameOfTest() + ": " + text; }
}