authorize.out

derby database source code.good for you.

ij> --
-- Create a database with a table and some data.
--
connect 'authorize;create=true' as c1;
ij> create table AUTH_TEST.t1 (a int);
0 rows inserted/updated/deleted
ij> CREATE FUNCTION AUTH_TEST.resourcefile(packageName VARCHAR(50), resourceName VARCHAR(50), outputFileName VARCHAR(50)) 
       RETURNS VARCHAR(3200) EXTERNAL NAME 'org.apache.derbyTesting.functionTests.util.DbFile.mkFileFromResource' 
       LANGUAGE JAVA PARAMETER STYLE JAVA;
0 rows inserted/updated/deleted
ij> -- need two procedures to run this test, otherwise the read-only connection
-- is not even alowed to call a MODIFIES SQL DATA procedure.
create procedure AUTH_TEST.verifyAccessRW(P1 INT) MODIFIES SQL DATA external name 'org.apache.derbyTesting.functionTests.util.T_Authorize.verifyAccessRW' language java parameter style java;
0 rows inserted/updated/deleted
ij> create procedure AUTH_TEST.verifyAccessRO(P1 INT) READS SQL DATA external name 'org.apache.derbyTesting.functionTests.util.T_Authorize.verifyAccessRO' language java parameter style java;
0 rows inserted/updated/deleted
ij> CREATE FUNCTION AUTH_TEST.getPermission(userName VARCHAR(50)) 
       RETURNS VARCHAR(22) EXTERNAL NAME 'org.apache.derby.database.UserUtility.getPermission' 
       LANGUAGE JAVA PARAMETER STYLE JAVA;
0 rows inserted/updated/deleted
ij> CREATE PROCEDURE AUTH_TEST.addUserUtility(IN userName VARCHAR(50), IN permission VARCHAR(22)) 
       LANGUAGE JAVA PARAMETER STYLE JAVA
       EXTERNAL NAME 'org.apache.derby.database.UserUtility.add';
0 rows inserted/updated/deleted
ij> CREATE PROCEDURE AUTH_TEST.setUserUtility(IN userName VARCHAR(50), IN permission VARCHAR(22)) 
       LANGUAGE JAVA PARAMETER STYLE JAVA
       EXTERNAL NAME 'org.apache.derby.database.UserUtility.set';
0 rows inserted/updated/deleted
ij> CREATE PROCEDURE AUTH_TEST.dropUserUtility(IN userName VARCHAR(50)) 
       LANGUAGE JAVA PARAMETER STYLE JAVA
       EXTERNAL NAME 'org.apache.derby.database.UserUtility.drop';
0 rows inserted/updated/deleted
ij> --
-- Add a jar file for use in verification that jar replace and drop are not
-- allowed in a read only connection.
call sqlj.install_jar(AUTH_TEST.resourcefile('org.apache.derbyTesting.functionTests.testData.v1','j1v1.jar','extinout/j1v1.jar'),
				'APP.IMMUTABLE', 0);
0 rows inserted/updated/deleted
ij> --
-- Configure the database with an invalid default connection mode (should fail);
call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.database.defaultConnectionMode','AsDf');
ERROR 28501: Invalid database authorization property 'derby.database.defaultConnectionMode=AsDf'.
ij> VALUES SYSCS_UTIL.SYSCS_GET_DATABASE_PROPERTY('derby.database.defaultConnectionMode');
1                                                                                                                               
--------------------------------------------------------------------------------------------------------------------------------
NULL                                                                                                                            
ij> --
-- Add a bad list of read only users (should fail).
--
call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.database.readOnlyAccessUsers','fred,0IsABadFirstLetter');
ERROR 28501: Invalid database authorization property 'derby.database.readOnlyAccessUsers=fred,0IsABadFirstLetter'.
ij> VALUES SYSCS_UTIL.SYSCS_GET_DATABASE_PROPERTY('derby.database.readOnlyAccessUsers');
1                                                                                                                               
--------------------------------------------------------------------------------------------------------------------------------
NULL                                                                                                                            
ij> --
-- Add a bad list of full access users (should fail).
--
call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.database.fullAccessUsers','fred,0IsABadFirstLetter');
ERROR 28501: Invalid database authorization property 'derby.database.fullAccessUsers=fred,0IsABadFirstLetter'.
ij> VALUES SYSCS_UTIL.SYSCS_GET_DATABASE_PROPERTY('derby.database.fullAccessUsers');
1                                                                                                                               
--------------------------------------------------------------------------------------------------------------------------------
NULL                                                                                                                            
ij> --
-- Connect and verify the user had full access.
connect 'authorize' as c2;
ij(C2)> call AUTH_TEST.verifyAccessRW(1);
0 rows inserted/updated/deleted
ij(C2)> disconnect;
ij> --
-- Configure the database to disallow access by unknown users
--
set connection c1;
ij> call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.database.defaultConnectionMode','NoAcCeSs');
0 rows inserted/updated/deleted
ij> VALUES SYSCS_UTIL.SYSCS_GET_DATABASE_PROPERTY('derby.database.defaultConnectionMode');
1                                                                                                                               
--------------------------------------------------------------------------------------------------------------------------------
NoAcCeSs                                                                                                                        
ij> --
-- Connect as an unknown user (Should fail)
--
connect 'authorize' user 'fred';
ERROR 04501: Database connection refused.
ij> --
-- Connect as a user with an invalid name (Should fail)
--
connect 'authorize' user '!amber' as c2;
ERROR 28502: The user name '!amber' is not valid. 
ij> --
-- Connect as a known user with a delimited name that is
-- only valid if it is delimited (Should fail)
--
connect 'authorize' user '"!amber"' as c2;
ERROR 04501: Database connection refused.
ij> --
-- Delete the defaultAccessMode property. Verify unknown users
-- have full access.
--
call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.database.defaultConnectionMode',null);
0 rows inserted/updated/deleted
ij> VALUES SYSCS_UTIL.SYSCS_GET_DATABASE_PROPERTY('derby.database.defaultConnectionMode');
1                                                                                                                               
--------------------------------------------------------------------------------------------------------------------------------
NULL                                                                                                                            
ij> connect 'authorize' user '"!amber"' as c2;
ij(C2)> call AUTH_TEST.verifyAccessRW(2);
0 rows inserted/updated/deleted
ij(C2)> disconnect;
ij> --
-- Configure the database to allow full access by unknown users
-- and verify an unknown user has full access.
set connection c1;
ij> call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.database.defaultConnectionMode','fullACCESS');
0 rows inserted/updated/deleted
ij> VALUES SYSCS_UTIL.SYSCS_GET_DATABASE_PROPERTY('derby.database.defaultConnectionMode');
1                                                                                                                               
--------------------------------------------------------------------------------------------------------------------------------
fullACCESS                                                                                                                      
ij> connect 'authorize' user '"!amber"' as c2;
ij(C2)> call AUTH_TEST.verifyAccessRW(3);
0 rows inserted/updated/deleted
ij(C2)> --
-- Configure the database to allow readOnly access by unknown
-- users. Verify existing connections by unknow users retain 
-- thier full access.
set connection c1;
ij(C1)> call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.database.defaultConnectionMode','readOnlyACCESS');
0 rows inserted/updated/deleted
ij(C1)> VALUES SYSCS_UTIL.SYSCS_GET_DATABASE_PROPERTY('derby.database.defaultConnectionMode');
1                                                                                                                               
--------------------------------------------------------------------------------------------------------------------------------
readOnlyACCESS                                                                                                                  
ij(C1)> set connection c2;
ij(C2)> call AUTH_TEST.verifyAccessRW(4);
0 rows inserted/updated/deleted
ij(C2)> disconnect;
ij> --
-- Connect as an unknown user and verify that the connection has
-- readOnly access.
connect 'authorize' as c2;
ij(C2)> call AUTH_TEST.verifyAccessRO(5);
0 rows inserted/updated/deleted
ij(C2)> disconnect;
ij> --
-- Configure the database to have some read only and full access
-- users. 
--
set connection c1;
ij> call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.database.defaultConnectionMode','NoACCESS');
0 rows inserted/updated/deleted
ij> VALUES SYSCS_UTIL.SYSCS_GET_DATABASE_PROPERTY('derby.database.defaultConnectionMode');
1                                                                                                                               
--------------------------------------------------------------------------------------------------------------------------------
NoACCESS                                                                                                                        
ij> call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.database.fullAccessUsers','fullUser1,"***both","aaa-differByCase"');
0 rows inserted/updated/deleted
ij> VALUES SYSCS_UTIL.SYSCS_GET_DATABASE_PROPERTY('derby.database.fullAccessUsers');
1                                                                                                                               
--------------------------------------------------------------------------------------------------------------------------------
fullUser1,"***both","aaa-differByCase"                                                                                          
ij> -- The following should fail as user '***both' can only be in 1 list
-- and it is already defined in the fullAccess users list.
call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.database.readOnlyAccessUsers','readUser1,"***both","AAA-differByCase"');
ERROR 28503: User(s) '"***both"' must not be in both read-only and full-access authorization lists.
ij> -- This should succeed
call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.database.readOnlyAccessUsers','readUser1,"AAA-differByCase"');
0 rows inserted/updated/deleted
ij> VALUES SYSCS_UTIL.SYSCS_GET_DATABASE_PROPERTY('derby.database.readOnlyAccessUsers');
1                                                                                                                               
--------------------------------------------------------------------------------------------------------------------------------
readUser1,"AAA-differByCase"                                                                                                    
ij> --
-- Connect as an unknown user - due to case error (should fail);
connect 'authorize' user '"fulluser1"';
ERROR 04501: Database connection refused.
ij> --
-- Connect as a read only user and verify access
-- Verify the user can't elevate to full access.
connect 'authorize' user 'readUser1' as c2;
ij(C2)> call AUTH_TEST.verifyAccessRO(6);
0 rows inserted/updated/deleted
ij(C2)> readonly off;
ERROR 25505: A read-only user or a user in a read-only database is not permitted to disable read-only mode on a connection.
ij(C2)> call AUTH_TEST.verifyAccessRO(7);
0 rows inserted/updated/deleted
ij(C2)> --
-- Connect as a full user and verify access.
--
connect 'authorize' user '"aaa-differByCase"' as c3;
ij(C3)> call AUTH_TEST.verifyAccessRW(8);
0 rows inserted/updated/deleted
ij(C3)> --
-- Verify the full user can set her connection to readonly 
-- and back.
readonly on;
ij(C3)> call AUTH_TEST.verifyAccessRO(9);
0 rows inserted/updated/deleted
ij(C3)> readonly off;
ij(C3)> call AUTH_TEST.verifyAccessRW(10);
0 rows inserted/updated/deleted
ij(C3)> --
-- Configure the database to have some full users and all unknown
-- users granted read access.
disconnect;
ij> set connection c2;
ij(C2)> disconnect;
ij> set connection c1;