ij> -- -- 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. -- -- ----- Specifically test SECURE users and various authentication ----- service/scheme configuration for different databases. ----- ----- Configure the 6 different databases with for each ----- of them, a different authentication scheme. ----- ----- 'wombat' - default DERBY scheme & ----- users known at system level. ----- Some authorization restriction. ----- 'guestSchemeDB' - No authentication ----- 'derbySchemeDB' - BUILTIN authentication ----- & some db authorization restriction. ----- 'simpleSchemeDB' - BUILTIN authentication and ----- some db authorization restriction. ----- (was the old Cloudscape 1.5 simple scheme) ----- ----- let's create all the dbs and configure them. ----- we will authenticate using a default system user that we ----- have configured. ----- A typical bad guy who cannot access any database but guest ----- is Jamie. ----- ----- ----- 'guestSchemeDB' database authentication/authorization config ----- connect 'guestSchemeDB;create=true;user=system;password=manager'; ij(CONNECTION1)> -- override requireAuthentication to be turned OFF at the database level autocommit off; ij(CONNECTION1)> prepare p1 as 'CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY(?,?)'; ij(CONNECTION1)> execute p1 using 'values(''derby.connection.requireAuthentication'', ''false'')'; Statement executed. ij(CONNECTION1)> commit; ij(CONNECTION1)> autocommit on; ij(CONNECTION1)> -- ----- 'derbySchemeDB' database authentication/authorization config ----- connect 'derbySchemeDB;create=true;user=system;password=manager'; ij(CONNECTION2)> autocommit off; ij(CONNECTION2)> prepare p2 as 'CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY(?,?)'; ij(CONNECTION2)> execute p2 using 'values(''derby.authentication.provider'', ''BUILTIN'')'; Statement executed. ij(CONNECTION2)> -- let's define users in this database (other than the ones ----- known at the system level. This is for the test ----- These 3 users will only be known in this database execute p2 using 'values(''derby.user.martin'', ''obfuscateIt'')'; Statement executed. ij(CONNECTION2)> execute p2 using 'values(''derby.user.dan'', ''makeItFaster'')'; Statement executed. ij(CONNECTION2)> execute p2 using 'values(''derby.user.mamta'', ''ieScape'')'; Statement executed. ij(CONNECTION2)> execute p2 using 'values(''derby.database.propertiesOnly'', ''true'')'; Statement executed. ij(CONNECTION2)> commit; ij(CONNECTION2)> autocommit on; ij(CONNECTION2)> -- ----- 'simpleSchemeDB' database authentication/authorization config ----- connect 'simpleSchemeDB;create=true;user=system;password=manager'; ij(CONNECTION3)> autocommit off; ij(CONNECTION3)> prepare p5 as 'CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY(?,?)'; ij(CONNECTION3)> execute p5 using 'values(''derby.authentication.provider'', ''BUILTIN'')'; Statement executed. ij(CONNECTION3)> -- ----- only allow these 3 users execute p5 using 'values(''derby.database.fullAccessUsers'', ''system,jeff,howardR'')'; Statement executed. ij(CONNECTION3)> execute p5 using 'values(''derby.database.readOnlyAccessUsers'', ''francois'')'; Statement executed. ij(CONNECTION3)> -- no access to Jamie only as he's a well known hooligan execute p5 using 'values(''derby.database.defaultConnectionMode'', ''noAccess'')'; Statement executed. ij(CONNECTION3)> commit; ij(CONNECTION3)> autocommit on; ij(CONNECTION3)> -- ----- Shutdown the system for database properties to take effect ----- disconnect all; ij> connect 'wombat;user=system;password=manager;shutdown=true'; ERROR 08006: DERBY SQL error: SQLCODE: -1, SQLSTATE: 08006, SQLERRMC: Database 'wombat' shutdown. ij> connect 'guestSchemeDB;user=system;password=manager;shutdown=true'; ERROR 08006: DERBY SQL error: SQLCODE: -1, SQLSTATE: 08006, SQLERRMC: Database 'guestSchemeDB' shutdown. ij> connect 'derbySchemeDB;user=system;password=manager;shutdown=true'; ERROR 08004: Connection authentication failure occurred. Reason: userid or password invalid. ij> connect 'simpleSchemeDB;user=system;password=manager;shutdown=true'; ERROR 08006: DERBY SQL error: SQLCODE: -1, SQLSTATE: 08006, SQLERRMC: Database 'simpleSchemeDB' shutdown. ij> disconnect all; ij> -- shuting down the system causes IJ to loose the protocol, therefore ----- we'd be doomed :( #connect ';shutdown=true;user=system;password=manager'; IJ ERROR: Unable to establish connection ij> -- 1) Valid authentication & authorization requests/ops ----- connect 'wombat;create=true;user=kreg;password=IwasBornReady'; ij> connect 'wombat;user=jeff;password=homeRun'; ij(CONNECTION1)> connect 'wombat;user=howardR;password=takeItEasy'; ij(CONNECTION2)> connect 'wombat;user=francois;password=paceesalute'; ij(CONNECTION3)> -- Invalid ones: connect 'wombat;user=Jamie;password=theHooligan'; ij(CONNECTION4)> show connections; CONNECTION0 - jdbc:derby://xxxFILTERED_HOSTNAMExxx:1527/wombat;create=true CONNECTION1 - jdbc:derby://xxxFILTERED_HOSTNAMExxx:1527/wombat CONNECTION2 - jdbc:derby://xxxFILTERED_HOSTNAMExxx:1527/wombat CONNECTION3 - jdbc:derby://xxxFILTERED_HOSTNAMExxx:1527/wombat CONNECTION4* - jdbc:derby://xxxFILTERED_HOSTNAMExxx:1527/wombat * = current connection ij(CONNECTION4)> connect 'guestSchemeDB;user=kreg;password=IwasBornReady'; ij(CONNECTION5)> connect 'guestSchemeDB;user=jeff;password=homeRun'; ij(CONNECTION6)> connect 'guestSchemeDB;user=howardR;password=takeItEasy'; ij(CONNECTION7)> connect 'guestSchemeDB;user=francois;password=paceesalute'; ij(CONNECTION8)> -- Invalid ones: connect 'guestSchemeDB;user=Jamie;password=theHooligan'; ij(CONNECTION9)> show connections; CONNECTION0 - jdbc:derby://xxxFILTERED_HOSTNAMExxx:1527/wombat;create=true CONNECTION1 - jdbc:derby://xxxFILTERED_HOSTNAMExxx:1527/wombat CONNECTION2 - jdbc:derby://xxxFILTERED_HOSTNAMExxx:1527/wombat CONNECTION3 - jdbc:derby://xxxFILTERED_HOSTNAMExxx:1527/wombat CONNECTION4 - jdbc:derby://xxxFILTERED_HOSTNAMExxx:1527/wombat CONNECTION5 - jdbc:derby://xxxFILTERED_HOSTNAMExxx:1527/guestSchemeDB CONNECTION6 - jdbc:derby://xxxFILTERED_HOSTNAMExxx:1527/guestSchemeDB CONNECTION7 - jdbc:derby://xxxFILTERED_HOSTNAMExxx:1527/guestSchemeDB CONNECTION8 - jdbc:derby://xxxFILTERED_HOSTNAMExxx:1527/guestSchemeDB CONNECTION9* - jdbc:derby://xxxFILTERED_HOSTNAMExxx:1527/guestSchemeDB * = current connection ij(CONNECTION9)> connect 'derbySchemeDB;user=mamta;password=ieScape'; ij(CONNECTION10)> connect 'derbySchemeDB;user=dan;password=makeItFaster'; ij(CONNECTION11)> connect 'derbySchemeDB;user=martin;password=obfuscateIt'; ij(CONNECTION12)> -- Invalid ones: connect 'derbySchemeDB;user=Jamie;password=theHooligan'; ERROR 08004: Connection authentication failure occurred. Reason: userid or password invalid. ij(CONNECTION12)> connect 'derbySchemeDB;user=francois;password=paceesalute'; ERROR 08004: Connection authentication failure occurred. Reason: userid or password invalid. ij(CONNECTION12)> show connections; CONNECTION0 - jdbc:derby://xxxFILTERED_HOSTNAMExxx:1527/wombat;create=true CONNECTION1 - jdbc:derby://xxxFILTERED_HOSTNAMExxx:1527/wombat CONNECTION10 - jdbc:derby://xxxFILTERED_HOSTNAMExxx:1527/derbySchemeDB CONNECTION11 - jdbc:derby://xxxFILTERED_HOSTNAMExxx:1527/derbySchemeDB CONNECTION12* - jdbc:derby://xxxFILTERED_HOSTNAMExxx:1527/derbySchemeDB CONNECTION2 - jdbc:derby://xxxFILTERED_HOSTNAMExxx:1527/wombat CONNECTION3 - jdbc:derby://xxxFILTERED_HOSTNAMExxx:1527/wombat CONNECTION4 - jdbc:derby://xxxFILTERED_HOSTNAMExxx:1527/wombat CONNECTION5 - jdbc:derby://xxxFILTERED_HOSTNAMExxx:1527/guestSchemeDB CONNECTION6 - jdbc:derby://xxxFILTERED_HOSTNAMExxx:1527/guestSchemeDB CONNECTION7 - jdbc:derby://xxxFILTERED_HOSTNAMExxx:1527/guestSchemeDB CONNECTION8 - jdbc:derby://xxxFILTERED_HOSTNAMExxx:1527/guestSchemeDB CONNECTION9 - jdbc:derby://xxxFILTERED_HOSTNAMExxx:1527/guestSchemeDB * = current connection ij(CONNECTION12)> connect 'simpleSchemeDB;user=jeff;password=homeRun'; ij(CONNECTION13)> connect 'simpleSchemeDB;user=howardR;password=takeItEasy'; ij(CONNECTION14)> connect 'simpleSchemeDB;user=francois;password=paceesalute'; ij(CONNECTION15)> -- Read-only user create table t1 (c1 int); ERROR 25503: DDL is not permitted for a read-only connection, user or database. ij(CONNECTION15)> -- Invalid ones: connect 'simpleSchemeDB;user=Jamie;password=theHooligan'; ERROR 04501: DERBY SQL error: SQLCODE: -1, SQLSTATE: 04501, SQLERRMC: Database connection refused. ij(CONNECTION15)> connect 'simpleSchemeDB;user=dan;password=makeItFaster'; ERROR 08004: Connection authentication failure occurred. Reason: userid or password invalid. ij(CONNECTION15)> connect 'simpleSchemeDB;user=francois;password=corsica'; ERROR 08004: Connection authentication failure occurred. Reason: userid or password invalid. ij(CONNECTION15)> show connections; CONNECTION0 - jdbc:derby://xxxFILTERED_HOSTNAMExxx:1527/wombat;create=true CONNECTION1 - jdbc:derby://xxxFILTERED_HOSTNAMExxx:1527/wombat CONNECTION10 - jdbc:derby://xxxFILTERED_HOSTNAMExxx:1527/derbySchemeDB CONNECTION11 - jdbc:derby://xxxFILTERED_HOSTNAMExxx:1527/derbySchemeDB CONNECTION12 - jdbc:derby://xxxFILTERED_HOSTNAMExxx:1527/derbySchemeDB CONNECTION13 - jdbc:derby://xxxFILTERED_HOSTNAMExxx:1527/simpleSchemeDB CONNECTION14 - jdbc:derby://xxxFILTERED_HOSTNAMExxx:1527/simpleSchemeDB CONNECTION15* - jdbc:derby://xxxFILTERED_HOSTNAMExxx:1527/simpleSchemeDB CONNECTION2 - jdbc:derby://xxxFILTERED_HOSTNAMExxx:1527/wombat CONNECTION3 - jdbc:derby://xxxFILTERED_HOSTNAMExxx:1527/wombat CONNECTION4 - jdbc:derby://xxxFILTERED_HOSTNAMExxx:1527/wombat CONNECTION5 - jdbc:derby://xxxFILTERED_HOSTNAMExxx:1527/guestSchemeDB CONNECTION6 - jdbc:derby://xxxFILTERED_HOSTNAMExxx:1527/guestSchemeDB CONNECTION7 - jdbc:derby://xxxFILTERED_HOSTNAMExxx:1527/guestSchemeDB CONNECTION8 - jdbc:derby://xxxFILTERED_HOSTNAMExxx:1527/guestSchemeDB CONNECTION9 - jdbc:derby://xxxFILTERED_HOSTNAMExxx:1527/guestSchemeDB * = current connection ij(CONNECTION15)> disconnect all; ij> show connections; No connections available. ij> -- Database shutdown - check user - should fail connect 'derbySchemeDB;shutdown=true'; ERROR 08004: Connection authentication failure occurred. Reason: userid or password invalid. ij> show connections; No connections available. ij> -- Database shutdown - check user - should succeed connect 'wombat;user=jeff;password=homeRun;shutdown=true'; ERROR 08006: DERBY SQL error: SQLCODE: -1, SQLSTATE: 08006, SQLERRMC: Database 'wombat' shutdown. ij> connect 'guestSchemeDB;user=kreg;password=IwasBornReady;shutdown=true'; ERROR 08006: DERBY SQL error: SQLCODE: -1, SQLSTATE: 08006, SQLERRMC: Database 'guestSchemeDB' shutdown. ij> connect 'derbySchemeDB;user=mamta;password=ieScape;shutdown=true'; ERROR 08006: DERBY SQL error: SQLCODE: -1, SQLSTATE: 08006, SQLERRMC: Database 'derbySchemeDB' shutdown. ij> connect 'simpleSchemeDB;user=jeff;password=homeRun;shutdown=true'; ERROR 08006: DERBY SQL error: SQLCODE: -1, SQLSTATE: 08006, SQLERRMC: Database 'simpleSchemeDB' shutdown. ij> show connections; No connections available. ij> -- Derby system shutdown - check user - should fail connect ';user=jamie;password=LetMeIn;shutdown=true'; ERROR 08004: Connection authentication failure occurred. Reason: userid or password invalid. ij> disconnect all; ij> -- Derby system shutdown - check user - should succeed connect ';user=system;password=manager;shutdown=true'; ERROR XJ015: DERBY SQL error: SQLCODE: -1, SQLSTATE: XJ015, SQLERRMC: Derby system shutdown. ij>