secureusers.out

derby database source code.good for you.

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 - 	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>