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'')'; 0 rows inserted/updated/deleted 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'')'; 0 rows inserted/updated/deleted 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'')'; 0 rows inserted/updated/deleted ij(CONNECTION2)> execute p2 using 'values(''derby.user.dan'', ''makeItFaster'')'; 0 rows inserted/updated/deleted ij(CONNECTION2)> execute p2 using 'values(''derby.user.mamta'', ''ieScape'')'; 0 rows inserted/updated/deleted ij(CONNECTION2)> execute p2 using 'values(''derby.database.propertiesOnly'', ''true'')'; 0 rows inserted/updated/deleted 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'')'; 0 rows inserted/updated/deleted ij(CONNECTION3)> -- -- only allow these 3 users execute p5 using 'values(''derby.database.fullAccessUsers'', ''system,jeff,howardR'')'; 0 rows inserted/updated/deleted ij(CONNECTION3)> execute p5 using 'values(''derby.database.readOnlyAccessUsers'', ''francois'')'; 0 rows inserted/updated/deleted ij(CONNECTION3)> -- no access to Jamie only as he's a well known hooligan execute p5 using 'values(''derby.database.defaultConnectionMode'', ''noAccess'')'; 0 rows inserted/updated/deleted 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: Database 'wombat' shutdown. ij> connect 'guestSchemeDB;user=system;password=manager;shutdown=true'; ERROR 08006: Database 'guestSchemeDB' shutdown. ij> connect 'derbySchemeDB;user=system;password=manager;shutdown=true'; ERROR 08004: Connection refused : Invalid authentication. ij> connect 'simpleSchemeDB;user=system;password=manager;shutdown=true'; ERROR 08006: 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'; WARNING 01J01: Database 'wombat' not created, connection made to existing database instead. 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:wombat CONNECTION1 - jdbc:derby:wombat CONNECTION2 - jdbc:derby:wombat CONNECTION3 - jdbc:derby:wombat CONNECTION4* - jdbc:derby: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:wombat CONNECTION1 - jdbc:derby:wombat CONNECTION2 - jdbc:derby:wombat CONNECTION3 - jdbc:derby:wombat CONNECTION4 - jdbc:derby:wombat CONNECTION5 - jdbc:derby:guestSchemeDB CONNECTION6 - jdbc:derby:guestSchemeDB CONNECTION7 - jdbc:derby:guestSchemeDB CONNECTION8 - jdbc:derby:guestSchemeDB CONNECTION9* - jdbc:derby: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 refused : Invalid authentication. ij(CONNECTION12)> connect 'derbySchemeDB;user=francois;password=paceesalute'; ERROR 08004: Connection refused : Invalid authentication. ij(CONNECTION12)> show connections; CONNECTION0 - jdbc:derby:wombat CONNECTION1 - jdbc:derby:wombat CONNECTION10 - jdbc:derby:derbySchemeDB CONNECTION11 - jdbc:derby:derbySchemeDB CONNECTION12* - jdbc:derby:derbySchemeDB CONNECTION2 - jdbc:derby:wombat CONNECTION3 - jdbc:derby:wombat CONNECTION4 - jdbc:derby:wombat CONNECTION5 - jdbc:derby:guestSchemeDB CONNECTION6 - jdbc:derby:guestSchemeDB CONNECTION7 - jdbc:derby:guestSchemeDB CONNECTION8 - jdbc:derby:guestSchemeDB CONNECTION9 - jdbc:derby: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: Database connection refused. ij(CONNECTION15)> connect 'simpleSchemeDB;user=dan;password=makeItFaster'; ERROR 08004: Connection refused : Invalid authentication. ij(CONNECTION15)> connect 'simpleSchemeDB;user=francois;password=corsica'; ERROR 08004: Connection refused : Invalid authentication. ij(CONNECTION15)> show connections; CONNECTION0 - jdbc:derby:wombat CONNECTION1 - jdbc:derby:wombat CONNECTION10 - jdbc:derby:derbySchemeDB CONNECTION11 - jdbc:derby:derbySchemeDB CONNECTION12 - jdbc:derby:derbySchemeDB CONNECTION13 - jdbc:derby:simpleSchemeDB CONNECTION14 - jdbc:derby:simpleSchemeDB CONNECTION15* - jdbc:derby:simpleSchemeDB CONNECTION2 - jdbc:derby:wombat CONNECTION3 - jdbc:derby:wombat CONNECTION4 - jdbc:derby:wombat CONNECTION5 - jdbc:derby:guestSchemeDB CONNECTION6 - jdbc:derby:guestSchemeDB CONNECTION7 - jdbc:derby:guestSchemeDB CONNECTION8 - jdbc:derby:guestSchemeDB CONNECTION9 - jdbc:derby: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 refused : Invalid authentication. ij> show connections; No connections available. ij> -- Database shutdown - check user - should succeed connect 'wombat;user=jeff;password=homeRun;shutdown=true'; ERROR 08006: Database 'wombat' shutdown. ij> connect 'guestSchemeDB;user=kreg;password=IwasBornReady;shutdown=true'; ERROR 08006: Database 'guestSchemeDB' shutdown. ij> connect 'derbySchemeDB;user=mamta;password=ieScape;shutdown=true'; ERROR 08006: Database 'derbySchemeDB' shutdown. ij> connect 'simpleSchemeDB;user=jeff;password=homeRun;shutdown=true'; ERROR 08006: 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 refused : Invalid authentication. ij> disconnect all; ij> -- Derby system shutdown - check user - should succeed connect ';user=system;password=manager;shutdown=true'; ERROR XJ015: Derby system shutdown. ij>