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'')';
Statement executed.
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'')';
Statement executed.
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'')';
Statement executed.
ij(CONNECTION2)> execute p2 using 'values(''derby.user.dan'', ''makeItFaster'')';
Statement executed.
ij(CONNECTION2)> execute p2 using 'values(''derby.user.mamta'', ''ieScape'')';
Statement executed.
ij(CONNECTION2)> execute p2 using 'values(''derby.database.propertiesOnly'', ''true'')';
Statement executed.
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'')';
Statement executed.
ij(CONNECTION3)> --
-----  only allow these 3 users
execute p5 using 'values(''derby.database.fullAccessUsers'', ''system,jeff,howardR'')';
Statement executed.
ij(CONNECTION3)> execute p5 using 'values(''derby.database.readOnlyAccessUsers'', ''francois'')';
Statement executed.
ij(CONNECTION3)> -- no access to Jamie only as he's a well known hooligan
execute p5 using 'values(''derby.database.defaultConnectionMode'', ''noAccess'')';
Statement executed.
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 (no SQLState): Connection authorization failure occurred.  Reason: userid invalid.
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';
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:net://localhost:1527/wombat;create=true;user=kreg;password=IwasBornReady
CONNECTION1 - 	jdbc:derby:net://localhost:1527/wombat;user=jeff;password=homeRun
CONNECTION2 - 	jdbc:derby:net://localhost:1527/wombat;user=howardR;password=takeItEasy
CONNECTION3 - 	jdbc:derby:net://localhost:1527/wombat;user=francois;password=paceesalute
CONNECTION4* - 	jdbc:derby:net://localhost:1527/wombat;user=Jamie;password=theHooligan
* = 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:net://localhost:1527/wombat;create=true;user=kreg;password=IwasBornReady
CONNECTION1 - 	jdbc:derby:net://localhost:1527/wombat;user=jeff;password=homeRun
CONNECTION2 - 	jdbc:derby:net://localhost:1527/wombat;user=howardR;password=takeItEasy
CONNECTION3 - 	jdbc:derby:net://localhost:1527/wombat;user=francois;password=paceesalute
CONNECTION4 - 	jdbc:derby:net://localhost:1527/wombat;user=Jamie;password=theHooligan
CONNECTION5 - 	jdbc:derby:net://localhost:1527/guestSchemeDB;user=kreg;password=IwasBornReady
CONNECTION6 - 	jdbc:derby:net://localhost:1527/guestSchemeDB;user=jeff;password=homeRun
CONNECTION7 - 	jdbc:derby:net://localhost:1527/guestSchemeDB;user=howardR;password=takeItEasy
CONNECTION8 - 	jdbc:derby:net://localhost:1527/guestSchemeDB;user=francois;password=paceesalute
CONNECTION9* - 	jdbc:derby:net://localhost:1527/guestSchemeDB;user=Jamie;password=theHooligan
* = 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 (no SQLState): Connection authorization failure occurred.  Reason: userid invalid.
ij(CONNECTION12)> connect 'derbySchemeDB;user=francois;password=paceesalute';
ERROR (no SQLState): Connection authorization failure occurred.  Reason: userid invalid.
ij(CONNECTION12)> show connections;
CONNECTION0 - 	jdbc:derby:net://localhost:1527/wombat;create=true;user=kreg;password=IwasBornReady
CONNECTION1 - 	jdbc:derby:net://localhost:1527/wombat;user=jeff;password=homeRun
CONNECTION10 - 	jdbc:derby:net://localhost:1527/derbySchemeDB;user=mamta;password=ieScape
CONNECTION11 - 	jdbc:derby:net://localhost:1527/derbySchemeDB;user=dan;password=makeItFaster
CONNECTION12* - 	jdbc:derby:net://localhost:1527/derbySchemeDB;user=martin;password=obfuscateIt
CONNECTION2 - 	jdbc:derby:net://localhost:1527/wombat;user=howardR;password=takeItEasy
CONNECTION3 - 	jdbc:derby:net://localhost:1527/wombat;user=francois;password=paceesalute
CONNECTION4 - 	jdbc:derby:net://localhost:1527/wombat;user=Jamie;password=theHooligan
CONNECTION5 - 	jdbc:derby:net://localhost:1527/guestSchemeDB;user=kreg;password=IwasBornReady
CONNECTION6 - 	jdbc:derby:net://localhost:1527/guestSchemeDB;user=jeff;password=homeRun
CONNECTION7 - 	jdbc:derby:net://localhost:1527/guestSchemeDB;user=howardR;password=takeItEasy
CONNECTION8 - 	jdbc:derby:net://localhost:1527/guestSchemeDB;user=francois;password=paceesalute
CONNECTION9 - 	jdbc:derby:net://localhost:1527/guestSchemeDB;user=Jamie;password=theHooligan
* = 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 (no SQLState): Connection authorization failure occurred.  Reason: userid invalid.
ij(CONNECTION15)> connect 'simpleSchemeDB;user=francois;password=corsica';
ERROR (no SQLState): Connection authorization failure occurred.  Reason: userid invalid.
ij(CONNECTION15)> show connections;
CONNECTION0 - 	jdbc:derby:net://localhost:1527/wombat;create=true;user=kreg;password=IwasBornReady
CONNECTION1 - 	jdbc:derby:net://localhost:1527/wombat;user=jeff;password=homeRun
CONNECTION10 - 	jdbc:derby:net://localhost:1527/derbySchemeDB;user=mamta;password=ieScape
CONNECTION11 - 	jdbc:derby:net://localhost:1527/derbySchemeDB;user=dan;password=makeItFaster
CONNECTION12 - 	jdbc:derby:net://localhost:1527/derbySchemeDB;user=martin;password=obfuscateIt
CONNECTION13 - 	jdbc:derby:net://localhost:1527/simpleSchemeDB;user=jeff;password=homeRun
CONNECTION14 - 	jdbc:derby:net://localhost:1527/simpleSchemeDB;user=howardR;password=takeItEasy
CONNECTION15* - 	jdbc:derby:net://localhost:1527/simpleSchemeDB;user=francois;password=paceesalute
CONNECTION2 - 	jdbc:derby:net://localhost:1527/wombat;user=howardR;password=takeItEasy
CONNECTION3 - 	jdbc:derby:net://localhost:1527/wombat;user=francois;password=paceesalute
CONNECTION4 - 	jdbc:derby:net://localhost:1527/wombat;user=Jamie;password=theHooligan
CONNECTION5 - 	jdbc:derby:net://localhost:1527/guestSchemeDB;user=kreg;password=IwasBornReady
CONNECTION6 - 	jdbc:derby:net://localhost:1527/guestSchemeDB;user=jeff;password=homeRun
CONNECTION7 - 	jdbc:derby:net://localhost:1527/guestSchemeDB;user=howardR;password=takeItEasy
CONNECTION8 - 	jdbc:derby:net://localhost:1527/guestSchemeDB;user=francois;password=paceesalute
CONNECTION9 - 	jdbc:derby:net://localhost:1527/guestSchemeDB;user=Jamie;password=theHooligan
* = current connection
ij(CONNECTION15)> disconnect all;
ij> show connections;
No connections available.
ij> -- Database shutdown - check user - should fail
connect 'derbySchemeDB;shutdown=true';
ERROR (no SQLState): Connection authorization failure occurred.  Reason: userid invalid.
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 (no SQLState): Connection authorization failure occurred.  Reason: userid invalid.
ij> disconnect all;
ij> -- Derby system shutdown - check user - should succeed
connect ';user=system;password=manager;shutdown=true';
ERROR XJ015: Derby system shutdown.
ij>