From derby-commits-return-2601-apmail-db-derby-commits-archive=db.apache.org@db.apache.org Wed Mar 15 21:33:17 2006 Return-Path: Delivered-To: apmail-db-derby-commits-archive@www.apache.org Received: (qmail 82936 invoked from network); 15 Mar 2006 21:33:16 -0000 Received: from hermes.apache.org (HELO mail.apache.org) (209.237.227.199) by minotaur.apache.org with SMTP; 15 Mar 2006 21:33:16 -0000 Received: (qmail 93549 invoked by uid 500); 15 Mar 2006 21:33:13 -0000 Delivered-To: apmail-db-derby-commits-archive@db.apache.org Received: (qmail 93124 invoked by uid 500); 15 Mar 2006 21:33:10 -0000 Mailing-List: contact derby-commits-help@db.apache.org; run by ezmlm Precedence: bulk list-help: list-unsubscribe: List-Post: Reply-To: "Derby Development" List-Id: Delivered-To: mailing list derby-commits@db.apache.org Received: (qmail 93069 invoked by uid 99); 15 Mar 2006 21:33:09 -0000 Received: from asf.osuosl.org (HELO asf.osuosl.org) (140.211.166.49) by apache.org (qpsmtpd/0.29) with ESMTP; Wed, 15 Mar 2006 13:33:09 -0800 X-ASF-Spam-Status: No, hits=-9.4 required=10.0 tests=ALL_TRUSTED,NO_REAL_NAME X-Spam-Check-By: apache.org Received: from [209.237.227.194] (HELO minotaur.apache.org) (209.237.227.194) by apache.org (qpsmtpd/0.29) with SMTP; Wed, 15 Mar 2006 13:32:47 -0800 Received: (qmail 82056 invoked by uid 65534); 15 Mar 2006 21:32:26 -0000 Message-ID: <20060315213226.82054.qmail@minotaur.apache.org> Content-Type: text/plain; charset="utf-8" MIME-Version: 1.0 Content-Transfer-Encoding: 7bit Subject: svn commit: r386169 [12/36] - in /db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master: DerbyNet/jdk16/ DerbyNetClient/jdk16/ jdk16/ Date: Wed, 15 Mar 2006 21:31:59 -0000 To: derby-commits@db.apache.org From: davidvc@apache.org X-Mailer: svnmailer-1.0.7 X-Virus-Checked: Checked by ClamAV on apache.org X-Spam-Rating: minotaur.apache.org 1.6.2 0/1000/N Added: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNet/jdk16/testij.out URL: http://svn.apache.org/viewcvs/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNet/jdk16/testij.out?rev=386169&view=auto ============================================================================== --- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNet/jdk16/testij.out (added) +++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNet/jdk16/testij.out Wed Mar 15 13:31:53 2006 @@ -0,0 +1,41 @@ +Testing various ij connections and comments in front of selects +org.apache.derby.tools.ij xxxFILTERED_TESTIJSOURCExxx +ij> driver 'com.ibm.db2.jcc.DB2Driver'; +ij> --Bug 4632 Make the db italian to make sure string selects are working +connect 'jdbc:derby:net://xxxFILTERED_HOSTNAMExxx:1527/wombat;create=true;territory=it:retrieveMessagesFromServerOnGetMessage=true;' USER 'dbadmin' PASSWORD 'dbadmin'; +ij> connect 'jdbc:derby:net://xxxFILTERED_HOSTNAMExxx:1527/wombat' USER 'dbadmin' PASSWORD 'dbadbmin'; +ij(CONNECTION1)> -- this is a comment, a comment in front of a select should not cause an error +select * from sys.systables where 1=0; +TABLEID |TABLENAME |& |SCHEMAID |& +----- +0 rows selected +ij(CONNECTION1)> -- this is a comment, a comment in front of a values clauses should not cause an error +values(1); +1 +----- +1 +1 row selected +ij(CONNECTION1)> -- Try some URL attributes +disconnect all; +ij> connect 'jdbc:derby:net://xxxFILTERED_HOSTNAMExxx:1527/junk;create=true:retrieveMessagesFromServerOnGetMessage=true;' USER 'dbadmin' PASSWORD 'dbadbmin'; +ij> select * from APP.notthere; +ERROR 42X05: Table 'APP.NOTTHERE' does not exist. : +ij> -- examples from the docs +connect 'jdbc:derby:net://xxxFILTERED_HOSTNAMExxx:1527/wombat;create=true:user=judy;password=judy;retrieveMessagesFromServerOnGetMessage=true;'; +ij(CONNECTION1)> connect 'jdbc:derby:net://xxxFILTERED_HOSTNAMExxx:1527/"./wombat":user=judy;password=judy;retrieveMessagesFromServerOnGetMessage=true;'; +ij(CONNECTION2)> connect 'jdbc:derby:net://xxxFILTERED_HOSTNAMExxx:1527/toursDB:retrieveMessagesFromServerOnGetMessage=true;'; +ERROR (no SQLState): null userid not supported +ij(CONNECTION2)> connect 'jdbc:derby:net://xxxFILTERED_HOSTNAMExxx:1527/toursDB:retrieveMessagesFromServerOnGetMessage=true;' USER 'dbadmin' PASSWORD 'dbadbmin'; +ERROR 08004: The application server rejected establishment of the connection. An attempt was made to access a database, toursDB, which was not found. +ij(CONNECTION2)> connect 'jdbc:derby:net://xxxFILTERED_HOSTNAMExxx:1527/wombat' USER 'APP' PASSWORD 'APP'; +ij(CONNECTION3)> connect 'jdbc:derby:net://xxxFILTERED_HOSTNAMExxx:1527/my-db-name;create=true:user=usr;password=pwd;retrieveMessagesFromServerOnGetMessage=true;'; +ij(CONNECTION4)> connect 'jdbc:derby:net://xxxFILTERED_HOSTNAMExxx:1527/my-db-name;upgrade=true:user=usr;password=pwd;retrieveMessagesFromServerOnGetMessage=true;'; +ij(CONNECTION5)> connect 'jdbc:derby:net://xxxFILTERED_HOSTNAMExxx:1527/my-db-name;shutdown=true:user=usr;password=pwd;' +----- Quoted db and attributes +connect 'jdbc:derby:net://xxxFILTERED_HOSTNAMExxx:1527/"./my-dbname;create=true":user=usr;password=pwd;'; +ERROR 08006: DB2 SQL error: SQLCODE: -1, SQLSTATE: 08006, SQLERRMC: Database 'my-db-name' shutdown. +ij(CONNECTION5)> connect 'jdbc:derby:net://xxxFILTERED_HOSTNAMExxx:1527/"./my-dbname;create=true":user=usr;password=pwd;retrieveMessagesFromServerOnGetMessage=true;'; +ij(CONNECTION6)> -- with no user +connect 'jdbc:derby:net://xxxFILTERED_HOSTNAMExxx:1527/wombat;create=true:retrieveMessagesFromServerOnGetMessage=true;'; +ERROR (no SQLState): null userid not supported +ij(CONNECTION6)> Added: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNet/jdk16/unaryArithmeticDynamicParameter.out URL: http://svn.apache.org/viewcvs/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNet/jdk16/unaryArithmeticDynamicParameter.out?rev=386169&view=auto ============================================================================== --- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNet/jdk16/unaryArithmeticDynamicParameter.out (added) +++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNet/jdk16/unaryArithmeticDynamicParameter.out Wed Mar 15 13:31:53 2006 @@ -0,0 +1,172 @@ +Test using parameters for unary minus and unary plus +insert into t1 values(?,+?,-?,?) +? parameter type is INTEGER +unary +? parameter type is SMALLINT +unary -? parameter type is DOUBLE +select * from t1 where -? in (select c21 from t2) +unary -? parameter type is INTEGER + C11,C12,C13,C14 + --- --- --- --- + {1,1,1.1,abc} + {-1,-1,-1.0,def} +select * from t1 where c11 = -? and c12 = +? and c13 = ? +unary -? parameter type is INTEGER +unary +? parameter type is SMALLINT +? parameter type is DOUBLE + C11,C12,C13,C14 + --- --- --- --- + {1,1,1.1,abc} + C11,C12,C13,C14 + --- --- --- --- + {-1,-1,-1.0,def} +select * from t1 where -? = ABS_FUNCT(+?) +unary -? parameter type is INTEGER +unary +? parameter type is INTEGER + C11,C12,C13,C14 + --- --- --- --- + {1,1,1.1,abc} + {-1,-1,-1.0,def} +select * from t1 where -? = max_cni(-5,-1) +unary -? parameter type is INTEGER + C11,C12,C13,C14 + --- --- --- --- + {1,1,1.1,abc} + {-1,-1,-1.0,def} +select * from t1 where -? = max_cni(-?,+?) +unary -? parameter type is INTEGER +unary -? parameter type is INTEGER +unary +? parameter type is INTEGER + C11,C12,C13,C14 + --- --- --- --- + {1,1,1.1,abc} + {-1,-1,-1.0,def} +Try the function again. But use, use sqrt(+?) & abs(-?) functions to send params +select * from t1 where -? = max_cni(abs(-?), sqrt(+?)) +unary -? parameter type is INTEGER +unary -? parameter type is DOUBLE +unary +? parameter type is DOUBLE + C11,C12,C13,C14 + --- --- --- --- + {1,1,1.1,abc} + {-1,-1,-1.0,def} +select * from t1 where c11 between -? and +? +unary -? parameter type is INTEGER +unary +? parameter type is INTEGER + C11,C12,C13,C14 + --- --- --- --- + {1,1,1.1,abc} +select * from t1 where +? not in (-?, +?, 2, ?) +unary +? parameter type is INTEGER +unary -? parameter type is INTEGER +unary +? parameter type is INTEGER +? parameter type is INTEGER + C11,C12,C13,C14 + --- --- --- --- + {1,1,1.1,abc} + {-1,-1,-1.0,def} +select * from t1 where +? < c12 +unary +? parameter type is SMALLINT + C11,C12,C13,C14 + --- --- --- --- + {1,1,1.1,abc} +select * from t1 where -? = c11 + ? +unary -? parameter type is INTEGER +? parameter type is INTEGER + C11,C12,C13,C14 + --- --- --- --- + {-1,-1,-1.0,def} +select * from t1 where c11 + ? = -? +? parameter type is INTEGER +unary -? parameter type is INTEGER + C11,C12,C13,C14 + --- --- --- --- + {-1,-1,-1.0,def} +select * from t1 where c11 + c12 = -? +unary -? parameter type is INTEGER + C11,C12,C13,C14 + --- --- --- --- + {-1,-1,-1.0,def} +select * from t1 where -? not in (select c21+? from t2) +unary -? parameter type is INTEGER +? parameter type is INTEGER + C11,C12,C13,C14 + --- --- --- --- + {1,1,1.1,abc} + {-1,-1,-1.0,def} +select cast(-? as smallint), cast(+? as int) from t1 +unary -? parameter type is SMALLINT +unary +? parameter type is INTEGER + 1,2 + - - + {-2,2} + {-2,2} +select nullif(-?,c11) from t1 +unary -? parameter type is INTEGER + 1 + - + {-22} + {-22} +select sqrt(-?) from t1 +unary -? parameter type is DOUBLE + 1 + - + {8.0} + {8.0} +select * from t1 where c11 = any (select -? from t2) +SQL State : 42X34 +Got expected exception There is a ? parameter in the select list. This is not allowed. : +Negative test - -?/+? at the beginning and/ at the end of where clause +select * from t1 where -? and c11=c11 or +? +SQL State : 42X19 +Got expected exception The WHERE or HAVING clause or CHECK CONSTRAINT definition is a 'PARAMETER' expression. It must be a BOOLEAN expression. : +Negative test - -?/+? in like escape function +select * from sys.systables where tablename like -? escape +? +SQL State : 42X37 +Got expected exception The unary '-' operator is not allowed on the 'VARCHAR' type. : +Negative test - -?/+? in binary timestamp function +select timestamp(-?,+?) from t1 +SQL State : 42X37 +Got expected exception The unary '-' operator is not allowed on the 'DATE' type. : +Negative test - -? in unary timestamp function +select timestamp(-?) from t1 +SQL State : 42X36 +Got expected exception The 'timestamp' operator is not allowed to take a ? parameter as an operand. : +Negative test - -? in views +create view v1 as select * from t1 where c11 = -? +SQL State : 42X98 +Got expected exception Parameters are not allowed in a VIEW definition. : +Negative test - -? in inner join +select * from t1 inner join t1 as t333 on -? +SQL State : 42X37 +Got expected exception The unary '-' operator is not allowed on the 'BOOLEAN' type. : +Negative test - -? by itself in where clause +select * from t1 where -? +SQL State : 42X19 +Got expected exception The WHERE or HAVING clause or CHECK CONSTRAINT definition is a 'PARAMETER' expression. It must be a BOOLEAN expression. : +Negative test - -? is null not allowed because is null allowed on char types only +select * from t1 where -? is null +SQL State : 42X37 +Got expected exception The unary '-' operator is not allowed on the 'VARCHAR' type. : +select case when -?=c11 then -? else c12 end from t1 +unary -? parameter type is INTEGER +unary -? parameter type is SMALLINT + 1 + - + {1} + {-22} +Negative test - unary plus parameters on both sides of / operator +select * from t1 where c11 = ?/-? +SQL State : 42X35 +Got expected exception It is not allowed for both operands of '/' to be ? parameters. : +Negative test - unary plus in | | operation +select c11 | | +? from t1 +SQL State : 42X37 +Got expected exception The unary '+' operator is not allowed on the 'VARCHAR' type. : +Negative test - unary minus for char column +select * from t1 where c14 = -? +SQL State : 42X37 +Got expected exception The unary '-' operator is not allowed on the 'CHAR' type. : +Negative test - unary plus for char column +select * from t1 where c14 like +? +SQL State : 42X37 +Got expected exception The unary '+' operator is not allowed on the 'CHAR' type. : Added: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNet/jdk16/users.out URL: http://svn.apache.org/viewcvs/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNet/jdk16/users.out?rev=386169&view=auto ============================================================================== --- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNet/jdk16/users.out (added) +++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNet/jdk16/users.out Wed Mar 15 13:31:53 2006 @@ -0,0 +1,153 @@ +ij> -- +connect 'wombat;create=true;user=dan;password=MakeItFaster'; +ij(CONNECTION1)> autocommit off; +ij(CONNECTION1)> prepare p1 as 'CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY(?,?)'; +ij(CONNECTION1)> execute p1 using 'values(''derby.database.defaultConnectionMode'', ''noAccess'')'; +Statement executed. +ij(CONNECTION1)> execute p1 using 'values(''derby.database.fullAccessUsers'', ''francois,jeff,howardR,ames,kreg'')'; +Statement executed. +ij(CONNECTION1)> remove p1; +ij(CONNECTION1)> commit; +ij(CONNECTION1)> autocommit on; +ij(CONNECTION1)> disconnect; +ij> connect 'wombat;shutdown=true;user=francois;password=paceesalute'; +ERROR 08006: Database 'wombat' shutdown. +ij> connect 'myDB;create=true;user=dan;password=MakeItFaster'; +ij(CONNECTION1)> autocommit off; +ij(CONNECTION1)> prepare p2 as 'CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY(?,?)'; +ij(CONNECTION1)> execute p2 using 'values(''derby.database.defaultConnectionMode'', ''noAccess'')'; +Statement executed. +ij(CONNECTION1)> execute p2 using 'values(''derby.database.fullAccessUsers'', ''jerry,kreg,dan,jamie,ames,francois'')'; +Statement executed. +ij(CONNECTION1)> remove p2; +ij(CONNECTION1)> commit; +ij(CONNECTION1)> autocommit on; +ij(CONNECTION1)> disconnect; +ij> connect 'myDB;shutdown=true;user=dan;password=MakeItFaster'; +ERROR 08006: Database 'myDB' shutdown. +ij> -- beetle 5468 +disconnect all; +ij> -- Specifically test JBMS users. +----- +----- check allowed users in wombat db. +connect 'wombat;user=kreg;password=visualWhat?'; +ij> connect 'wombat;user=jeff;password=HomeRun61'; +ij(CONNECTION1)> connect 'wombat;user=ames;password=AnyVolunteer?'; +ij(CONNECTION2)> connect 'wombat;user=howardR;password=IamBetterAtTennis'; +ij(CONNECTION3)> connect 'wombat;user=francois;password=paceesalute'; +ij(CONNECTION4)> show connections; +CONNECTION0 - jdbc:derby:net://xxxFILTERED_HOSTNAMExxx:1527/wombat;user=kreg;password=visualWhat? +CONNECTION1 - jdbc:derby:net://xxxFILTERED_HOSTNAMExxx:1527/wombat;user=jeff;password=HomeRun61 +CONNECTION2 - jdbc:derby:net://xxxFILTERED_HOSTNAMExxx:1527/wombat;user=ames;password=AnyVolunteer? +CONNECTION3 - jdbc:derby:net://xxxFILTERED_HOSTNAMExxx:1527/wombat;user=howardR;password=IamBetterAtTennis +CONNECTION4* - jdbc:derby:net://xxxFILTERED_HOSTNAMExxx:1527/wombat;user=francois;password=paceesalute +* = current connection +ij(CONNECTION4)> disconnect all; +ij> -- check allowed users in myDB db. +----- also check USER flavors +connect 'myDB;user=jerry;password=SacreBleu'; +ij> create table APP.t1(c1 char(30) check (UPPER(c1) <> 'JAMIE')); +0 rows inserted/updated/deleted +ij> insert into APP.t1 values CURRENT_USER; +1 row inserted/updated/deleted +ij> connect 'myDB;user=kreg;password=visualWhat?'; +ij(CONNECTION1)> insert into APP.t1 values USER; +1 row inserted/updated/deleted +ij(CONNECTION1)> connect 'myDB;user=ames;password=AnyVolunteer?'; +ij(CONNECTION2)> insert into APP.t1 values SESSION_USER; +1 row inserted/updated/deleted +ij(CONNECTION2)> connect 'myDB;user=dan;password=MakeItFaster'; +ij(CONNECTION3)> select * from APP.t1; +C1 +----- +JERRY +KREG +AMES +ij(CONNECTION3)> update APP.t1 set c1 = {fn user() }; +3 rows inserted/updated/deleted +ij(CONNECTION3)> select * from APP.t1; +C1 +----- +DAN +DAN +DAN +ij(CONNECTION3)> connect 'myDB;user=francois;password=paceesalute'; +ij(CONNECTION4)> update APP.t1 set c1 = USER; +3 rows inserted/updated/deleted +ij(CONNECTION4)> connect 'myDB;user=jamie;password=MrNamePlates'; +ij(CONNECTION5)> select * from APP.t1; +C1 +----- +FRANCOIS +FRANCOIS +FRANCOIS +ij(CONNECTION5)> 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(CONNECTION5)> show connections; +CONNECTION0 - jdbc:derby:net://xxxFILTERED_HOSTNAMExxx:1527/myDB;user=jerry;password=SacreBleu +CONNECTION1 - jdbc:derby:net://xxxFILTERED_HOSTNAMExxx:1527/myDB;user=kreg;password=visualWhat? +CONNECTION2 - jdbc:derby:net://xxxFILTERED_HOSTNAMExxx:1527/myDB;user=ames;password=AnyVolunteer? +CONNECTION3 - jdbc:derby:net://xxxFILTERED_HOSTNAMExxx:1527/myDB;user=dan;password=MakeItFaster +CONNECTION4 - jdbc:derby:net://xxxFILTERED_HOSTNAMExxx:1527/myDB;user=francois;password=paceesalute +CONNECTION5* - jdbc:derby:net://xxxFILTERED_HOSTNAMExxx:1527/myDB;user=jamie;password=MrNamePlates +* = current connection +ij(CONNECTION5)> disconnect all; +ij> -- +----- some negative cases +----- +----- Invalid login's +connect 'wombat'; +ERROR (no SQLState): Connection authorization failure occurred. Reason: userid invalid. +ij> connect 'wombat;user=badUser1;password=YeeHa!'; +ERROR (no SQLState): Connection authorization failure occurred. Reason: userid invalid. +ij> connect 'wombat;user=badUser2;password=YeeHa!'; +ERROR (no SQLState): Connection authorization failure occurred. Reason: userid invalid. +ij> connect 'myDB;user=dan;password=MakeItSlower'; +ERROR (no SQLState): Connection authorization failure occurred. Reason: userid invalid. +ij> connect 'myDB;user=jamie;password=LetMeIn'; +ERROR (no SQLState): Connection authorization failure occurred. Reason: userid invalid. +ij> connect 'wombat;user=francois;password=Corsica'; +ERROR (no SQLState): Connection authorization failure occurred. Reason: userid invalid. +ij> -- Invalid database users +connect 'myDB;user=howardR;password=IamBetterAtTennis'; +ERROR 04501: Database connection refused. +ij> connect 'wombat;user=jerry;password=SacreBleu'; +ERROR 04501: Database connection refused. +ij> connect 'wombat;user=jamie;password=MrNamePlates'; +ERROR 04501: Database connection refused. +ij> show connections; +No connections available. +ij> connect 'wombat;user=francois;password=paceesalute'; +ij> connect 'myDB;user=jerry;password=SacreBleu'; +ij(CONNECTION1)> -- Database shutdown - check user - should fail +connect 'myDB;shutdown=true'; +ERROR (no SQLState): Connection authorization failure occurred. Reason: userid invalid. +ij(CONNECTION1)> connect 'myDB;user=jamie;password=LetMeIn;shutdown=true'; +ERROR (no SQLState): Connection authorization failure occurred. Reason: userid invalid. +ij(CONNECTION1)> connect 'wombat;user=jerry;password=SacreBleu;shutdown=true'; +ERROR 04501: Database connection refused. +ij(CONNECTION1)> show connections; +CONNECTION0 - jdbc:derby:net://xxxFILTERED_HOSTNAMExxx:1527/wombat;user=francois;password=paceesalute +CONNECTION1* - jdbc:derby:net://xxxFILTERED_HOSTNAMExxx:1527/myDB;user=jerry;password=SacreBleu +* = current connection +ij(CONNECTION1)> -- Database shutdown - check user - should succeed +----- beetle 5367 +connect 'wombat;user=francois;password=paceesalute;shutdown=true'; +ERROR 08006: Database 'wombat' shutdown. +ij(CONNECTION1)> connect 'myDB;user=jerry;password=SacreBleu;shutdown=true'; +ERROR 08006: Database 'myDB' shutdown. +ij(CONNECTION1)> show connections; +CONNECTION0 - jdbc:derby:net://xxxFILTERED_HOSTNAMExxx:1527/wombat;user=francois;password=paceesalute +CONNECTION1* - jdbc:derby:net://xxxFILTERED_HOSTNAMExxx:1527/myDB;user=jerry;password=SacreBleu +* = current connection +ij(CONNECTION1)> -- JBMS System shutdown - check user - should fail +connect ';user=jamie;password=LetMeIn;shutdown=true'; +ERROR (no SQLState): Connection authorization failure occurred. Reason: userid invalid. +ij(CONNECTION1)> disconnect all; +ij> -- JBMS System shutdown - check user - should succeed +connect ';user=francois;password=paceesalute;shutdown=true'; +ERROR XJ015: Derby system shutdown. +ij> -- beetle 5390 +----- the server does not shut down properly in network server +; +ij> Added: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNet/jdk16/users2.out URL: http://svn.apache.org/viewcvs/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNet/jdk16/users2.out?rev=386169&view=auto ============================================================================== --- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNet/jdk16/users2.out (added) +++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNet/jdk16/users2.out Wed Mar 15 13:31:53 2006 @@ -0,0 +1,241 @@ +ij> -- +----- 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'')'; +Statement executed. +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?'')'; +Statement executed. +ij> execute p2 using 'values(''derby.user.jeff'', ''HomeRun61'')'; +Statement executed. +ij> execute p2 using 'values(''derby.user.ames'', ''AnyVolunteer?'')'; +Statement executed. +ij> execute p2 using 'values(''derby.user.jamie'', ''MrNamePlates'')'; +Statement executed. +ij> execute p2 using 'values(''derby.user.howardR'', ''IamBetterAtTennis'')'; +Statement executed. +ij> execute p2 using 'values(''derby.user.francois'', ''paceesalute'')'; +Statement executed. +ij> execute p2 using 'values(''derby.database.fullAccessUsers'', ''jeff,howardR,ames,francois,kreg'')'; +Statement executed. +ij> execute p2 using 'values(''derby.database.readOnlyAccessUsers'', ''jamie'')'; +Statement executed. +ij> execute p2 using 'values(''derby.database.defaultConnectionMode'', ''noAccess'')'; +Statement executed. +ij> execute p2 using 'values(''derby.database.propertiesOnly'', ''true'')'; +Statement executed. +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:net://xxxFILTERED_HOSTNAMExxx:1527/wombat;user=system;password=manager +CONNECTION1 - jdbc:derby:net://xxxFILTERED_HOSTNAMExxx:1527/wombat;user=kreg;password=visualWhat? +CONNECTION2 - jdbc:derby:net://xxxFILTERED_HOSTNAMExxx:1527/wombat;user=jeff;password=HomeRun61 +CONNECTION3 - jdbc:derby:net://xxxFILTERED_HOSTNAMExxx:1527/wombat;user=ames;password=AnyVolunteer? +CONNECTION4 - jdbc:derby:net://xxxFILTERED_HOSTNAMExxx:1527/wombat;user=howardR;password=IamBetterAtTennis +CONNECTION5* - jdbc:derby:net://xxxFILTERED_HOSTNAMExxx:1527/wombat;user=jamie;password=MrNamePlates +* = 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'')'; +Statement executed. +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?'')'; +Statement executed. +ij> execute p4 using 'values(''derby.user.dan'', ''MakeItFaster'')'; +Statement executed. +ij> execute p4 using 'values(''derby.user.ames'', ''AnyVolunteer?'')'; +Statement executed. +ij> execute p4 using 'values(''derby.user.jerry'', ''SacreBleu'')'; +Statement executed. +ij> execute p4 using 'values(''derby.user.jamie'', ''MrNamePlates'')'; +Statement executed. +ij> execute p4 using 'values(''derby.user.francois'', ''paceesalute'')'; +Statement executed. +ij> execute p4 using 'values(''derby.database.fullAccessUsers'', ''jerry,dan,kreg,ames,francois,jamie'')'; +Statement executed. +ij> execute p4 using 'values(''derby.database.defaultConnectionMode'', ''noAccess'')'; +Statement executed. +ij> execute p4 using 'values(''derby.database.propertiesOnly'', ''true'')'; +Statement executed. +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:net://xxxFILTERED_HOSTNAMExxx:1527/myDB;user=system;password=manager +CONNECTION1 - jdbc:derby:net://xxxFILTERED_HOSTNAMExxx:1527/myDB;user=jerry;password=SacreBleu +CONNECTION2 - jdbc:derby:net://xxxFILTERED_HOSTNAMExxx:1527/myDB;user=kreg;password=visualWhat? +CONNECTION3 - jdbc:derby:net://xxxFILTERED_HOSTNAMExxx:1527/myDB;user=ames;password=AnyVolunteer? +CONNECTION4 - jdbc:derby:net://xxxFILTERED_HOSTNAMExxx:1527/myDB;user=dan;password=MakeItFaster +CONNECTION5 - jdbc:derby:net://xxxFILTERED_HOSTNAMExxx:1527/myDB;user=francois;password=paceesalute +CONNECTION6* - jdbc:derby:net://xxxFILTERED_HOSTNAMExxx:1527/myDB;user=jamie;password=MrNamePlates +* = current connection +ij(CONNECTION6)> disconnect all; +ij> -- +----- some negative cases +----- +----- Invalid login's +connect 'wombat'; +ERROR (no SQLState): Connection authorization failure occurred. Reason: userid invalid. +ij> connect 'wombat;user=badUser1;password=YeeHa!'; +ERROR (no SQLState): Connection authorization failure occurred. Reason: userid invalid. +ij> connect 'wombat;user=badUser2;password=YeeHa!'; +ERROR (no SQLState): Connection authorization failure occurred. Reason: userid invalid. +ij> connect 'myDB;user=dan;password=MakeItSlower'; +ERROR (no SQLState): Connection authorization failure occurred. Reason: userid invalid. +ij> connect 'myDB;user=jamie;password=LetMeIn'; +ERROR (no SQLState): Connection authorization failure occurred. Reason: userid invalid. +ij> connect 'wombat;user=francois;password=Corsica'; +ERROR (no SQLState): Connection authorization failure occurred. Reason: userid invalid. +ij> -- Invalid database users +connect 'myDB;user=howardR;password=IamBetterAtTennis'; +ERROR (no SQLState): Connection authorization failure occurred. Reason: userid invalid. +ij> connect 'wombat;user=jerry;password=SacreBleu'; +ERROR (no SQLState): Connection authorization failure occurred. Reason: userid invalid. +ij> connect 'wombat;user=jamie;password=MrNamePlates'; +ij> show connections; +CONNECTION0* - jdbc:derby:net://xxxFILTERED_HOSTNAMExxx:1527/wombat;user=jamie;password=MrNamePlates +* = 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 (no SQLState): Connection authorization failure occurred. Reason: userid invalid. +ij(CONNECTION2)> connect 'myDB;user=jamie;password=LetMeIn;shutdown=true'; +ERROR (no SQLState): Connection authorization failure occurred. Reason: userid invalid. +ij(CONNECTION2)> connect 'wombat;user=jerry;password=SacreBleu;shutdown=true'; +ERROR (no SQLState): Connection authorization failure occurred. Reason: userid invalid. +ij(CONNECTION2)> show connections; +CONNECTION0 - jdbc:derby:net://xxxFILTERED_HOSTNAMExxx:1527/wombat;user=jamie;password=MrNamePlates +CONNECTION1 - jdbc:derby:net://xxxFILTERED_HOSTNAMExxx:1527/wombat;user=francois;password=paceesalute +CONNECTION2* - jdbc:derby:net://xxxFILTERED_HOSTNAMExxx:1527/myDB;user=jerry;password=SacreBleu +* = 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 (no SQLState): Connection authorization failure occurred. Reason: userid invalid. +ij> -- JBMS System shutdown - check user - should succeed +connect ';user=system;password=manager;shutdown=true'; +ERROR XJ015: Derby system shutdown. +ij>