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 Derby users using DERBY scheme -- and by only looking at database properties for authentication -- The only user at the system level is system/manager -- -- check allowed users in wombat db. -- initial connection in sysprop was: -- connect 'wombat;create=true;user=system;password=manager'; -- -- Default to: derby.authentication.provider=BUILTIN -- autocommit off; ij> prepare p1 as 'CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY(?,?)'; ij> execute p1 using 'values(''derby.connection.requireAuthentication'', ''true'')'; 0 rows inserted/updated/deleted ij> commit; ij> remove p1; ij> autocommit on; ij> disconnect all; ij> connect 'wombat;shutdown=true;user=system;password=manager'; ERROR 08006: Database 'wombat' shutdown. ij> -- beetle 5468 disconnect all; ij> connect 'wombat;user=system;password=manager'; ij> -- -- set authentication config for 'wombat' database -- autocommit off; ij> prepare p2 as 'CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY(?,?)'; ij> execute p2 using 'values(''derby.user.kreg'', ''visualWhat?'')'; 0 rows inserted/updated/deleted ij> execute p2 using 'values(''derby.user.jeff'', ''HomeRun61'')'; 0 rows inserted/updated/deleted ij> execute p2 using 'values(''derby.user.ames'', ''AnyVolunteer?'')'; 0 rows inserted/updated/deleted ij> execute p2 using 'values(''derby.user.jamie'', ''MrNamePlates'')'; 0 rows inserted/updated/deleted ij> execute p2 using 'values(''derby.user.howardR'', ''IamBetterAtTennis'')'; 0 rows inserted/updated/deleted ij> execute p2 using 'values(''derby.user.francois'', ''paceesalute'')'; 0 rows inserted/updated/deleted ij> execute p2 using 'values(''derby.database.fullAccessUsers'', ''jeff,howardR,ames,francois,kreg'')'; 0 rows inserted/updated/deleted ij> execute p2 using 'values(''derby.database.readOnlyAccessUsers'', ''jamie'')'; 0 rows inserted/updated/deleted ij> execute p2 using 'values(''derby.database.defaultConnectionMode'', ''noAccess'')'; 0 rows inserted/updated/deleted ij> execute p2 using 'values(''derby.database.propertiesOnly'', ''true'')'; 0 rows inserted/updated/deleted ij> commit; ij> autocommit on; ij> -- Check that the passwords are encrypted -- values getDatabaseProperty('derby.user.francois'); -- values getDatabaseProperty('derby.user.ames'); -- values getDatabaseProperty('derby.user.kreg'); -- values getDatabaseProperty('derby.user.jeff'); -- values getDatabaseProperty('derby.user.howardR'); -- values getDatabaseProperty('derby.user.jamie'); connect 'wombat;user=kreg;password=visualWhat?'; ij(CONNECTION1)> connect 'wombat;user=jeff;password=HomeRun61'; ij(CONNECTION2)> connect 'wombat;user=ames;password=AnyVolunteer?'; ij(CONNECTION3)> connect 'wombat;user=howardR;password=IamBetterAtTennis'; ij(CONNECTION4)> -- should succeed create table APP.t1(c1 char(30)); 0 rows inserted/updated/deleted ij(CONNECTION4)> insert into APP.t1 values CURRENT_USER; 1 row inserted/updated/deleted ij(CONNECTION4)> connect 'wombat;user=jamie;password=MrNamePlates'; ij(CONNECTION5)> -- should fail as readOnly user insert into APP.t1 values CURRENT_USER; ERROR 25502: An SQL data change is not permitted for a read-only connection, user or database. ij(CONNECTION5)> create table APP.t2(c1 char(30)); ERROR 25503: DDL is not permitted for a read-only connection, user or database. ij(CONNECTION5)> 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:wombat * = current connection ij(CONNECTION5)> disconnect all; ij> -- check allowed users in myDB db. -- connect 'myDB;create=true;user=system;password=manager'; ij> autocommit off; ij> prepare p3 as 'CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY(?,?)'; ij> execute p3 using 'values(''derby.connection.requireAuthentication'', ''true'')'; 0 rows inserted/updated/deleted ij> remove p3; ij> autocommit on; ij> disconnect all; ij> connect 'myDB;shutdown=true;user=system;password=manager'; ERROR 08006: Database 'myDB' shutdown. ij> -- beetle 5468 disconnect all; ij> connect 'myDB;user=system;password=manager'; ij> -- -- set authentication config for 'myDB' database -- autocommit off; ij> prepare p4 as 'CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY(?,?)'; ij> execute p4 using 'values(''derby.user.kreg'', ''visualWhat?'')'; 0 rows inserted/updated/deleted ij> execute p4 using 'values(''derby.user.dan'', ''MakeItFaster'')'; 0 rows inserted/updated/deleted ij> execute p4 using 'values(''derby.user.ames'', ''AnyVolunteer?'')'; 0 rows inserted/updated/deleted ij> execute p4 using 'values(''derby.user.jerry'', ''SacreBleu'')'; 0 rows inserted/updated/deleted ij> execute p4 using 'values(''derby.user.jamie'', ''MrNamePlates'')'; 0 rows inserted/updated/deleted ij> execute p4 using 'values(''derby.user.francois'', ''paceesalute'')'; 0 rows inserted/updated/deleted ij> execute p4 using 'values(''derby.database.fullAccessUsers'', ''jerry,dan,kreg,ames,francois,jamie'')'; 0 rows inserted/updated/deleted ij> execute p4 using 'values(''derby.database.defaultConnectionMode'', ''noAccess'')'; 0 rows inserted/updated/deleted ij> execute p4 using 'values(''derby.database.propertiesOnly'', ''true'')'; 0 rows inserted/updated/deleted ij> commit; ij> autocommit on; ij> -- Check that the passwords are encrypted -- values getDatabaseProperty('derby.user.francois'); -- values getDatabaseProperty('derby.user.ames'); -- values getDatabaseProperty('derby.user.kreg'); -- values getDatabaseProperty('derby.user.dan'); -- values getDatabaseProperty('derby.user.jerry'); -- values getDatabaseProperty('derby.user.jamie'); -- -- also check USER flavors -- connect 'myDB;user=jerry;password=SacreBleu'; ij(CONNECTION1)> create table APP.t1(c1 char(30) check (UPPER(c1) <> 'JAMIE')); 0 rows inserted/updated/deleted ij(CONNECTION1)> insert into APP.t1 values CURRENT_USER; 1 row inserted/updated/deleted ij(CONNECTION1)> connect 'myDB;user=kreg;password=visualWhat?'; ij(CONNECTION2)> insert into APP.t1 values USER; 1 row inserted/updated/deleted ij(CONNECTION2)> connect 'myDB;user=ames;password=AnyVolunteer?'; ij(CONNECTION3)> insert into APP.t1 values SESSION_USER; 1 row inserted/updated/deleted ij(CONNECTION3)> connect 'myDB;user=dan;password=MakeItFaster'; ij(CONNECTION4)> select * from APP.t1; C1 ------------------------------ JERRY KREG AMES ij(CONNECTION4)> update APP.t1 set c1 = USER; 3 rows inserted/updated/deleted ij(CONNECTION4)> select * from APP.t1; C1 ------------------------------ DAN DAN DAN ij(CONNECTION4)> connect 'myDB;user=francois;password=paceesalute'; ij(CONNECTION5)> update APP.t1 set c1 = USER; 3 rows inserted/updated/deleted ij(CONNECTION5)> connect 'myDB;user=jamie;password=MrNamePlates'; ij(CONNECTION6)> select * from APP.t1; C1 ------------------------------ FRANCOIS FRANCOIS FRANCOIS ij(CONNECTION6)> update APP.t1 set c1 = USER; ERROR 23513: The check constraint 'xxxxGENERATED-IDxxxx' was violated while performing an INSERT or UPDATE on table 'APP.T1'. ij(CONNECTION6)> show connections; CONNECTION0 - jdbc:derby:myDB CONNECTION1 - jdbc:derby:myDB CONNECTION2 - jdbc:derby:myDB CONNECTION3 - jdbc:derby:myDB CONNECTION4 - jdbc:derby:myDB CONNECTION5 - jdbc:derby:myDB CONNECTION6* - jdbc:derby:myDB * = current connection ij(CONNECTION6)> disconnect all; ij> -- -- some negative cases -- -- Invalid login's connect 'wombat'; ERROR 08004: Connection refused : Invalid authentication. ij> connect 'wombat;user=badUser1;password=YeeHa!'; ERROR 08004: Connection refused : Invalid authentication. ij> connect 'wombat;user=badUser2;password=YeeHa!'; ERROR 08004: Connection refused : Invalid authentication. ij> connect 'myDB;user=dan;password=MakeItSlower'; ERROR 08004: Connection refused : Invalid authentication. ij> connect 'myDB;user=jamie;password=LetMeIn'; ERROR 08004: Connection refused : Invalid authentication. ij> connect 'wombat;user=francois;password=Corsica'; ERROR 08004: Connection refused : Invalid authentication. ij> -- Invalid database users connect 'myDB;user=howardR;password=IamBetterAtTennis'; ERROR 08004: Connection refused : Invalid authentication. ij> connect 'wombat;user=jerry;password=SacreBleu'; ERROR 08004: Connection refused : Invalid authentication. ij> connect 'wombat;user=jamie;password=MrNamePlates'; ij> show connections; CONNECTION0* - jdbc:derby:wombat * = current connection ij> connect 'wombat;user=francois;password=paceesalute'; ij(CONNECTION1)> connect 'myDB;user=jerry;password=SacreBleu'; ij(CONNECTION2)> -- Database shutdown - check user - should fail connect 'myDB;shutdown=true'; ERROR 08004: Connection refused : Invalid authentication. ij(CONNECTION2)> connect 'myDB;user=jamie;password=LetMeIn;shutdown=true'; ERROR 08004: Connection refused : Invalid authentication. ij(CONNECTION2)> connect 'wombat;user=jerry;password=SacreBleu;shutdown=true'; ERROR 08004: Connection refused : Invalid authentication. ij(CONNECTION2)> show connections; CONNECTION0 - jdbc:derby:wombat CONNECTION1 - jdbc:derby:wombat CONNECTION2* - jdbc:derby:myDB * = current connection ij(CONNECTION2)> disconnect all; ij> show connections; No connections available. ij> -- Database shutdown - check user - should succeed connect 'wombat;user=francois;password=paceesalute;shutdown=true'; ERROR 08006: Database 'wombat' shutdown. ij> -- beetle 5468 disconnect all; ij> connect 'myDB;user=jerry;password=SacreBleu;shutdown=true'; ERROR 08006: Database 'myDB' shutdown. ij> -- beetle 5468 disconnect all; ij> -- there should be no connections left here show connections; No connections available. ij> -- JBMS System shutdown - check user - should fail connect ';user=jamie;password=LetMeIn;shutdown=true'; ERROR 08004: Connection refused : Invalid authentication. ij> -- JBMS System shutdown - check user - should succeed connect ';user=system;password=manager;shutdown=true'; ERROR XJ015: Derby system shutdown. ij>