users2.out

derby database source code.good for you.

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://localhost:1527/wombat;user=system;password=manager
CONNECTION1 - 	jdbc:derby:net://localhost:1527/wombat;user=kreg;password=visualWhat?
CONNECTION2 - 	jdbc:derby:net://localhost:1527/wombat;user=jeff;password=HomeRun61
CONNECTION3 - 	jdbc:derby:net://localhost:1527/wombat;user=ames;password=AnyVolunteer?
CONNECTION4 - 	jdbc:derby:net://localhost:1527/wombat;user=howardR;password=IamBetterAtTennis
CONNECTION5* - 	jdbc:derby:net://localhost: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://localhost:1527/myDB;user=system;password=manager
CONNECTION1 - 	jdbc:derby:net://localhost:1527/myDB;user=jerry;password=SacreBleu
CONNECTION2 - 	jdbc:derby:net://localhost:1527/myDB;user=kreg;password=visualWhat?
CONNECTION3 - 	jdbc:derby:net://localhost:1527/myDB;user=ames;password=AnyVolunteer?
CONNECTION4 - 	jdbc:derby:net://localhost:1527/myDB;user=dan;password=MakeItFaster
CONNECTION5 - 	jdbc:derby:net://localhost:1527/myDB;user=francois;password=paceesalute
CONNECTION6* - 	jdbc:derby:net://localhost: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://localhost: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://localhost:1527/wombat;user=jamie;password=MrNamePlates
CONNECTION1 - 	jdbc:derby:net://localhost:1527/wombat;user=francois;password=paceesalute
CONNECTION2* - 	jdbc:derby:net://localhost: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>