ij> -- -- Licensed to the Apache Software Foundation (ASF) under one or more -- contributor license agreements. See the NOTICE file distributed with -- this work for additional information regarding copyright ownership. -- The ASF licenses this file to You under the Apache License, Version 2.0 -- (the "License"); you may not use this file except in compliance with -- the License. You may obtain a copy of the License at -- -- http://www.apache.org/licenses/LICENSE-2.0 -- -- Unless required by applicable law or agreed to in writing, software -- distributed under the License is distributed on an "AS IS" BASIS, -- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. -- See the License for the specific language governing permissions and -- limitations under the License. -- -- -- 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'. ERROR XCXC0: Invalid id list. 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'. ERROR XCXC0: Invalid id list. 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; ij> call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.database.defaultConnectionMode','readOnlyACCESS'); 0 rows inserted/updated/deleted ij> VALUES SYSCS_UTIL.SYSCS_GET_DATABASE_PROPERTY('derby.database.defaultConnectionMode'); 1 -------------------------------------------------------------------------------------------------------------------------------- readOnlyACCESS 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> call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.database.readOnlyAccessUsers',null); 0 rows inserted/updated/deleted ij> VALUES SYSCS_UTIL.SYSCS_GET_DATABASE_PROPERTY('derby.database.readOnlyAccessUsers'); 1 -------------------------------------------------------------------------------------------------------------------------------- NULL ij> -- -- Connect as a guest user (name differs from a full user by case) connect 'authorize' user '"fulluser1"' as c2; ij(C2)> call AUTH_TEST.verifyAccessRO(11); 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)> readonly on; ij(C2)> disconnect; ij> -- -- Connect as a full user and verify we can do it all connect 'authorize' user 'fulluser1' as c2; ij(C2)> call AUTH_TEST.verifyAccessRW(12); 0 rows inserted/updated/deleted ij(C2)> readonly on; ij(C2)> call AUTH_TEST.verifyAccessRO(13); 0 rows inserted/updated/deleted ij(C2)> readonly off; ij(C2)> call AUTH_TEST.verifyAccessRW(14); 0 rows inserted/updated/deleted ij(C2)> -- -- Verfify we cannot set the readonly state in an active connection. autocommit off; ij(C2)> insert into AUTH_TEST.t1 values 1,2; 2 rows inserted/updated/deleted ij(C2)> readonly off; ERROR 25501: Unable to set the connection read-only property in an active transaction. ij(C2)> readonly on; ERROR 25501: Unable to set the connection read-only property in an active transaction. ij(C2)> rollback; ij(C2)> autocommit on; ij(C2)> -- -- Verify a read only user can perform a query that uses a -- temp table. insert into AUTH_TEST.t1 values 1,2,3,4,5,6,7; 7 rows inserted/updated/deleted ij(C2)> insert into AUTH_TEST.t1 select * from AUTH_TEST.t1; 7 rows inserted/updated/deleted ij(C2)> insert into AUTH_TEST.t1 select * from AUTH_TEST.t1; 14 rows inserted/updated/deleted ij(C2)> insert into AUTH_TEST.t1 select * from AUTH_TEST.t1; 28 rows inserted/updated/deleted ij(C2)> insert into AUTH_TEST.t1 select * from AUTH_TEST.t1; 56 rows inserted/updated/deleted ij(C2)> readonly on; ij(C2)> select * from AUTH_TEST.t1 order by a; A ----------- 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 6 6 6 6 6 6 6 6 6 6 6 6 6 6 6 6 7 7 7 7 7 7 7 7 7 7 7 7 7 7 7 7 ij(C2)> readonly off; ij(C2)> delete from AUTH_TEST.t1; 112 rows inserted/updated/deleted ij(C2)> -- -- Remove all the authorization properties to prepare to test -- the UserUtility. -- call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.database.defaultConnectionMode',null); 0 rows inserted/updated/deleted ij(C2)> VALUES SYSCS_UTIL.SYSCS_GET_DATABASE_PROPERTY('derby.database.defaultConnectionMode'); 1 -------------------------------------------------------------------------------------------------------------------------------- NULL ij(C2)> call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.database.readOnlyAccessUsers',null); 0 rows inserted/updated/deleted ij(C2)> VALUES SYSCS_UTIL.SYSCS_GET_DATABASE_PROPERTY('derby.database.readOnlyAccessUsers'); 1 -------------------------------------------------------------------------------------------------------------------------------- NULL ij(C2)> call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.database.fullAccessUsers',null); 0 rows inserted/updated/deleted ij(C2)> VALUES SYSCS_UTIL.SYSCS_GET_DATABASE_PROPERTY('derby.database.fullAccessUsers'); 1 -------------------------------------------------------------------------------------------------------------------------------- NULL ij(C2)> -- -- Verify external scalar function getPermission works with no users. values AUTH_TEST.getPermission('myFriend'); 1 -------------------------------------------------------------------------------------------------------------------------------- NULL ij(C2)> -- -- Verify external scalar function getPermission notices when it is -- called with a bad id. (should fail.) values AUTH_TEST.getPermission('***badId'); 1 -------------------------------------------------------------------------------------------------------------------------------- ERROR XCZ02: Invalid parameter 'userName'='***badId'. ERROR XCXA0: Invalid identifier. ij(C2)> values AUTH_TEST.getPermission(null); 1 -------------------------------------------------------------------------------------------------------------------------------- ERROR XCZ02: Invalid parameter 'userName'='null'. ij(C2)> -- -- Verify external scalar function addUserUtility reports an error when called with -- a bad user, a null user and a bad permission mode and a -- null permission mode. call AUTH_TEST.addUserUtility('***badId','READ_ACCESS_PERMISSION'); ERROR XCZ02: Invalid parameter 'userName'='***badId'. ERROR XCXA0: Invalid identifier. ij(C2)> call AUTH_TEST.addUserUtility(null,'READ_ACCESS_PERMISSION'); ERROR XCZ02: Invalid parameter 'userName'='null'. ij(C2)> call AUTH_TEST.addUserUtility('goodUser','badPermission'); ERROR XCZ00: Unknown permission 'badPermission'. ij(C2)> call AUTH_TEST.addUserUtility('goodUser',null); ERROR XCZ02: Invalid parameter 'permission'='null'. ij(C2)> -- -- Add 3 read access users with quoted names and verify they are added. -- As a negative test we add each user twice call AUTH_TEST.addUserUtility('"dAda"','READ_ACCESS_PERMISSION'); 0 rows inserted/updated/deleted ij(C2)> call AUTH_TEST.addUserUtility('"dAda"','READ_ACCESS_PERMISSION'); ERROR 28504: Repeated user(s) '"dAda"' in access list 'derby.database.readOnlyAccessUsers'; ij(C2)> call AUTH_TEST.addUserUtility('"bUnny"','READ_ACCESS_PERMISSION'); 0 rows inserted/updated/deleted ij(C2)> call AUTH_TEST.addUserUtility('"bUnny"','READ_ACCESS_PERMISSION'); ERROR 28504: Repeated user(s) '"bUnny"' in access list 'derby.database.readOnlyAccessUsers'; ij(C2)> call AUTH_TEST.addUserUtility('"jAmes"','READ_ACCESS_PERMISSION'); 0 rows inserted/updated/deleted ij(C2)> call AUTH_TEST.addUserUtility('"jAmes"','READ_ACCESS_PERMISSION'); ERROR 28504: Repeated user(s) '"jAmes"' in access list 'derby.database.readOnlyAccessUsers'; ij(C2)> VALUES SYSCS_UTIL.SYSCS_GET_DATABASE_PROPERTY('derby.database.readOnlyAccessUsers'); 1 -------------------------------------------------------------------------------------------------------------------------------- "dAda","bUnny","jAmes" ij(C2)> VALUES SYSCS_UTIL.SYSCS_GET_DATABASE_PROPERTY('derby.database.fullAccessUsers'); 1 -------------------------------------------------------------------------------------------------------------------------------- NULL ij(C2)> -- -- Verify external scalar function setUserUtility reports an error when called with -- a bad user, a null user, a missing user a bad permission -- mode and a null permission mode. call AUTH_TEST.setUserUtility('***badId','READ_ACCESS_PERMISSION'); ERROR XCZ02: Invalid parameter 'userName'='***badId'. ERROR XCXA0: Invalid identifier. ij(C2)> call AUTH_TEST.setUserUtility(null,'READ_ACCESS_PERMISSION'); ERROR XCZ02: Invalid parameter 'userName'='null'. ij(C2)> call AUTH_TEST.setUserUtility('missingUser','FULL_ACCESS_PERMISSION'); ERROR XCZ01: Unknown user 'missingUser'. ij(C2)> call AUTH_TEST.setUserUtility('"jAmes"','badPermission'); ERROR XCZ00: Unknown permission 'badPermission'. ij(C2)> call AUTH_TEST.setUserUtility('"jAmes"',null); ERROR XCZ02: Invalid parameter 'permission'='null'. ij(C2)> -- -- Get the access level for our users values AUTH_TEST.getPermission('"dAda"'); 1 -------------------------------------------------------------------------------------------------------------------------------- READ_ACCESS_PERMISSION ij(C2)> values AUTH_TEST.getPermission('"DADA"'); 1 -------------------------------------------------------------------------------------------------------------------------------- NULL ij(C2)> --wrong case values AUTH_TEST.getPermission('"bUnny"'); 1 -------------------------------------------------------------------------------------------------------------------------------- READ_ACCESS_PERMISSION ij(C2)> values AUTH_TEST.getPermission('"dAda"'); 1 -------------------------------------------------------------------------------------------------------------------------------- READ_ACCESS_PERMISSION ij(C2)> -- -- Set all our read access users to full access users twice. call AUTH_TEST.setUserUtility('"dAda"','FULL_ACCESS_PERMISSION'); 0 rows inserted/updated/deleted ij(C2)> call AUTH_TEST.setUserUtility('"dAda"','FULL_ACCESS_PERMISSION'); 0 rows inserted/updated/deleted ij(C2)> call AUTH_TEST.setUserUtility('"bUnny"','FULL_ACCESS_PERMISSION'); 0 rows inserted/updated/deleted ij(C2)> call AUTH_TEST.setUserUtility('"bUnny"','FULL_ACCESS_PERMISSION'); 0 rows inserted/updated/deleted ij(C2)> call AUTH_TEST.setUserUtility('"jAmes"','FULL_ACCESS_PERMISSION'); 0 rows inserted/updated/deleted ij(C2)> call AUTH_TEST.setUserUtility('"jAmes"','FULL_ACCESS_PERMISSION'); 0 rows inserted/updated/deleted ij(C2)> VALUES SYSCS_UTIL.SYSCS_GET_DATABASE_PROPERTY('derby.database.readOnlyAccessUsers'); 1 -------------------------------------------------------------------------------------------------------------------------------- NULL ij(C2)> VALUES SYSCS_UTIL.SYSCS_GET_DATABASE_PROPERTY('derby.database.fullAccessUsers'); 1 -------------------------------------------------------------------------------------------------------------------------------- "dAda","bUnny","jAmes" ij(C2)> -- -- Verify external scalar function dropUserUtility reports an error when called with -- a bad user, a null user, a missing user. call AUTH_TEST.dropUserUtility('***badId'); ERROR XCZ02: Invalid parameter 'userName'='***badId'. ERROR XCXA0: Invalid identifier. ij(C2)> call AUTH_TEST.dropUserUtility(null); ERROR XCZ02: Invalid parameter 'userName'='null'. ij(C2)> call AUTH_TEST.dropUserUtility('missingUser'); ERROR XCZ01: Unknown user 'missingUser'. ij(C2)> -- -- Drop each user twice call AUTH_TEST.dropUserUtility('"dAda"'); 0 rows inserted/updated/deleted ij(C2)> call AUTH_TEST.dropUserUtility('"dAda"'); ERROR XCZ01: Unknown user '"dAda"'. ij(C2)> call AUTH_TEST.dropUserUtility('"bUnny"'); 0 rows inserted/updated/deleted ij(C2)> call AUTH_TEST.dropUserUtility('"bUnny"'); ERROR XCZ01: Unknown user '"bUnny"'. ij(C2)> call AUTH_TEST.dropUserUtility('"jAmes"'); 0 rows inserted/updated/deleted ij(C2)> call AUTH_TEST.dropUserUtility('"jAmes"'); ERROR XCZ01: Unknown user '"jAmes"'. ij(C2)> VALUES SYSCS_UTIL.SYSCS_GET_DATABASE_PROPERTY('derby.database.readOnlyAccessUsers'); 1 -------------------------------------------------------------------------------------------------------------------------------- NULL ij(C2)> VALUES SYSCS_UTIL.SYSCS_GET_DATABASE_PROPERTY('derby.database.fullAccessUsers'); 1 -------------------------------------------------------------------------------------------------------------------------------- NULL ij(C2)> -- -- Add set and drop some users with names that -- are not delimited. call AUTH_TEST.addUserUtility('dada','READ_ACCESS_PERMISSION'); 0 rows inserted/updated/deleted ij(C2)> call AUTH_TEST.addUserUtility('DADA','READ_ACCESS_PERMISSION'); ERROR 28504: Repeated user(s) '"DADA"' in access list 'derby.database.readOnlyAccessUsers'; ij(C2)> -- duplicate call AUTH_TEST.addUserUtility('bunny','FULL_ACCESS_PERMISSION'); 0 rows inserted/updated/deleted ij(C2)> call AUTH_TEST.addUserUtility('james','FULL_ACCESS_PERMISSION'); 0 rows inserted/updated/deleted ij(C2)> VALUES SYSCS_UTIL.SYSCS_GET_DATABASE_PROPERTY('derby.database.readOnlyAccessUsers'); 1 -------------------------------------------------------------------------------------------------------------------------------- dada ij(C2)> VALUES SYSCS_UTIL.SYSCS_GET_DATABASE_PROPERTY('derby.database.fullAccessUsers'); 1 -------------------------------------------------------------------------------------------------------------------------------- bunny,james ij(C2)> call AUTH_TEST.setUserUtility('BUNNY','READ_ACCESS_PERMISSION'); 0 rows inserted/updated/deleted ij(C2)> VALUES SYSCS_UTIL.SYSCS_GET_DATABASE_PROPERTY('derby.database.readOnlyAccessUsers'); 1 -------------------------------------------------------------------------------------------------------------------------------- dada,BUNNY ij(C2)> VALUES SYSCS_UTIL.SYSCS_GET_DATABASE_PROPERTY('derby.database.fullAccessUsers'); 1 -------------------------------------------------------------------------------------------------------------------------------- james ij(C2)> values AUTH_TEST.getPermission('dAda'); 1 -------------------------------------------------------------------------------------------------------------------------------- READ_ACCESS_PERMISSION ij(C2)> values AUTH_TEST.getPermission('bunny'); 1 -------------------------------------------------------------------------------------------------------------------------------- READ_ACCESS_PERMISSION ij(C2)> values AUTH_TEST.getPermission('jaMes'); 1 -------------------------------------------------------------------------------------------------------------------------------- FULL_ACCESS_PERMISSION ij(C2)> call AUTH_TEST.dropUserUtility('dada'); 0 rows inserted/updated/deleted ij(C2)> call AUTH_TEST.dropUserUtility('bunny'); 0 rows inserted/updated/deleted ij(C2)> call AUTH_TEST.dropUserUtility('jaMes'); 0 rows inserted/updated/deleted ij(C2)> VALUES SYSCS_UTIL.SYSCS_GET_DATABASE_PROPERTY('derby.database.readOnlyAccessUsers'); 1 -------------------------------------------------------------------------------------------------------------------------------- NULL ij(C2)> VALUES SYSCS_UTIL.SYSCS_GET_DATABASE_PROPERTY('derby.database.fullAccessUsers'); 1 -------------------------------------------------------------------------------------------------------------------------------- NULL ij(C2)> -- clean up drop function AUTH_TEST.getPermission; 0 rows inserted/updated/deleted ij(C2)> drop procedure AUTH_TEST.verifyAccessRW; 0 rows inserted/updated/deleted ij(C2)> drop procedure AUTH_TEST.verifyAccessRO; 0 rows inserted/updated/deleted ij(C2)> drop procedure AUTH_TEST.setUserUtility; 0 rows inserted/updated/deleted ij(C2)> drop procedure AUTH_TEST.addUserUtility; 0 rows inserted/updated/deleted ij(C2)> drop procedure AUTH_TEST.dropUserUtility; 0 rows inserted/updated/deleted ij(C2)>