db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From kmars...@apache.org
Subject svn commit: r331885 - in /db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/j9_foundation: secureUsers.out users2.out
Date Tue, 08 Nov 2005 20:53:50 GMT
Author: kmarsden
Date: Tue Nov  8 12:53:46 2005
New Revision: 331885

URL: http://svn.apache.org/viewcvs?rev=331885&view=rev
Log:
DERBY-648 ij 'show connections' statement does not work in JSR169
master updates


Added:
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/j9_foundation/secureUsers.out
  (with props)
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/j9_foundation/users2.out
  (with props)

Added: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/j9_foundation/secureUsers.out
URL: http://svn.apache.org/viewcvs/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/j9_foundation/secureUsers.out?rev=331885&view=auto
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/j9_foundation/secureUsers.out
(added)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/j9_foundation/secureUsers.out
Tue Nov  8 12:53:46 2005
@@ -0,0 +1,206 @@
+ij> --
+-- 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
+CONNECTION1
+CONNECTION2
+CONNECTION3
+CONNECTION4*
+* = 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
+CONNECTION1
+CONNECTION2
+CONNECTION3
+CONNECTION4
+CONNECTION5
+CONNECTION6
+CONNECTION7
+CONNECTION8
+CONNECTION9*
+* = 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
+CONNECTION1
+CONNECTION10
+CONNECTION11
+CONNECTION12*
+CONNECTION2
+CONNECTION3
+CONNECTION4
+CONNECTION5
+CONNECTION6
+CONNECTION7
+CONNECTION8
+CONNECTION9
+* = 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
+CONNECTION1
+CONNECTION10
+CONNECTION11
+CONNECTION12
+CONNECTION13
+CONNECTION14
+CONNECTION15*
+CONNECTION2
+CONNECTION3
+CONNECTION4
+CONNECTION5
+CONNECTION6
+CONNECTION7
+CONNECTION8
+CONNECTION9
+* = 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> 

Propchange: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/j9_foundation/secureUsers.out
------------------------------------------------------------------------------
    svn:eol-style = native

Added: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/j9_foundation/users2.out
URL: http://svn.apache.org/viewcvs/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/j9_foundation/users2.out?rev=331885&view=auto
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/j9_foundation/users2.out
(added)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/j9_foundation/users2.out
Tue Nov  8 12:53:46 2005
@@ -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'')';
+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
+CONNECTION1
+CONNECTION2
+CONNECTION3
+CONNECTION4
+CONNECTION5*
+* = 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
+CONNECTION1
+CONNECTION2
+CONNECTION3
+CONNECTION4
+CONNECTION5
+CONNECTION6*
+* = 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*
+* = 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
+CONNECTION1
+CONNECTION2*
+* = 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> 

Propchange: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/j9_foundation/users2.out
------------------------------------------------------------------------------
    svn:eol-style = native



Mime
View raw message