WARNING 01J14: SQL authorization is being used without first enabling authentication. 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. -- -- ------------------------------------------------------------------- -- GRANT and REVOKE test Part 2 -- ------------------------------------------------------------------- connect 'grantRevokeDDL2;create=true' user 'user1' as user1; WARNING 01J14: SQL authorization is being used without first enabling authentication. ij(USER1)> connect 'grantRevokeDDL2' user 'user2' as user2; WARNING 01J14: SQL authorization is being used without first enabling authentication. ij(USER2)> connect 'grantRevokeDDL2' user 'user3' as user3; WARNING 01J14: SQL authorization is being used without first enabling authentication. ij(USER3)> connect 'grantRevokeDDL2' user 'user4' as user4; WARNING 01J14: SQL authorization is being used without first enabling authentication. ij(USER4)> connect 'grantRevokeDDL2' user 'user5' as user5; WARNING 01J14: SQL authorization is being used without first enabling authentication. ij(USER5)> -- DERBY-1729 -- test grant and revoke in Java stored procedure with triggers. -- Java stored procedure that contains grant or revoke statement -- requires MODIFIES SQL DATA to execute. -- Since only 2 of the 8 Java stored procedures(which contains -- grant or revoke statement) are declared with MODIFIES SQL DATA, -- the rest are expected to fail in this test. -- setup the environment set connection user1; ij(USER1)> -- table used in the procedures drop table t1; ERROR: Failed with SQLSTATE 42Y07 ij(USER1)> create table t1 (i int primary key, b char(15)); 0 rows inserted/updated/deleted ij(USER1)> insert into t1 values (1, 'XYZ'); 1 row inserted/updated/deleted ij(USER1)> insert into t1 values (2, 'XYZ'); 1 row inserted/updated/deleted ij(USER1)> insert into t1 values (3, 'XYZ'); 1 row inserted/updated/deleted ij(USER1)> insert into t1 values (4, 'XYZ'); 1 row inserted/updated/deleted ij(USER1)> insert into t1 values (5, 'XYZ'); 1 row inserted/updated/deleted ij(USER1)> insert into t1 values (6, 'XYZ'); 1 row inserted/updated/deleted ij(USER1)> insert into t1 values (7, 'XYZ'); 1 row inserted/updated/deleted ij(USER1)> insert into t1 values (8, 'XYZ'); 1 row inserted/updated/deleted ij(USER1)> -- table used in this test drop table t2; ERROR: Failed with SQLSTATE 42Y55 ij(USER1)> create table t2 (x integer, y integer); 0 rows inserted/updated/deleted ij(USER1)> create procedure grant_select_proc1() parameter style java dynamic result sets 0 language java NO SQL external name 'org.apache.derbyTesting.functionTests.util.ProcedureTest.grantSelect'; 0 rows inserted/updated/deleted ij(USER1)> create procedure grant_select_proc2() parameter style java dynamic result sets 0 language java CONTAINS SQL external name 'org.apache.derbyTesting.functionTests.util.ProcedureTest.grantSelect'; 0 rows inserted/updated/deleted ij(USER1)> create procedure grant_select_proc3() parameter style java dynamic result sets 0 language java READS SQL DATA external name 'org.apache.derbyTesting.functionTests.util.ProcedureTest.grantSelect'; 0 rows inserted/updated/deleted ij(USER1)> create procedure grant_select_proc4() parameter style java dynamic result sets 0 language java MODIFIES SQL DATA external name 'org.apache.derbyTesting.functionTests.util.ProcedureTest.grantSelect'; 0 rows inserted/updated/deleted ij(USER1)> create procedure revoke_select_proc1() parameter style java dynamic result sets 0 language java NO SQL external name 'org.apache.derbyTesting.functionTests.util.ProcedureTest.revokeSelect'; 0 rows inserted/updated/deleted ij(USER1)> create procedure revoke_select_proc2() parameter style java dynamic result sets 0 language java CONTAINS SQL external name 'org.apache.derbyTesting.functionTests.util.ProcedureTest.revokeSelect'; 0 rows inserted/updated/deleted ij(USER1)> create procedure revoke_select_proc3() parameter style java dynamic result sets 0 language java READS SQL DATA external name 'org.apache.derbyTesting.functionTests.util.ProcedureTest.revokeSelect'; 0 rows inserted/updated/deleted ij(USER1)> create procedure revoke_select_proc4() parameter style java dynamic result sets 0 language java MODIFIES SQL DATA external name 'org.apache.derbyTesting.functionTests.util.ProcedureTest.revokeSelect'; 0 rows inserted/updated/deleted ij(USER1)> -- tests create trigger grant_select_trig AFTER delete on t1 for each STATEMENT mode db2sql call grant_select_proc1(); 0 rows inserted/updated/deleted ij(USER1)> -- should fail delete from t1 where i = 1; ERROR: Failed with SQLSTATE 38000 ERROR: Failed with SQLSTATE XJ001 ij(USER1)> -- check delete failed select * from t1 where i = 1; I |B --------------------------- 1 |XYZ 1 row selected ij(USER1)> drop trigger grant_select_trig; 0 rows inserted/updated/deleted ij(USER1)> set connection user2; ij(USER2)> -- should fail select * from user1.t1 where i = 1; ERROR: Failed with SQLSTATE 28508 ij(USER2)> set connection user1; ij(USER1)> create trigger grant_select_trig AFTER delete on t1 for each STATEMENT mode db2sql call grant_select_proc2(); 0 rows inserted/updated/deleted ij(USER1)> -- should fail delete from t1 where i = 2; ERROR: Failed with SQLSTATE 38000 ERROR: Failed with SQLSTATE XJ001 ij(USER1)> -- check delete failed select * from t1 where i = 2; I |B --------------------------- 2 |XYZ 1 row selected ij(USER1)> drop trigger grant_select_trig; 0 rows inserted/updated/deleted ij(USER1)> set connection user2; ij(USER2)> -- should fail select * from user1.t1 where i = 1; ERROR: Failed with SQLSTATE 28508 ij(USER2)> set connection user1; ij(USER1)> create trigger grant_select_trig AFTER delete on t1 for each STATEMENT mode db2sql call grant_select_proc3(); 0 rows inserted/updated/deleted ij(USER1)> -- should fail delete from t1 where i = 3; ERROR: Failed with SQLSTATE 38000 ERROR: Failed with SQLSTATE XJ001 ij(USER1)> -- check delete failed select * from t1 where i = 3; I |B --------------------------- 3 |XYZ 1 row selected ij(USER1)> drop trigger grant_select_trig; 0 rows inserted/updated/deleted ij(USER1)> set connection user2; ij(USER2)> -- should fail select * from user1.t1 where i = 1; ERROR: Failed with SQLSTATE 28508 ij(USER2)> set connection user1; ij(USER1)> create trigger grant_select_trig AFTER delete on t1 for each STATEMENT mode db2sql call grant_select_proc4(); 0 rows inserted/updated/deleted ij(USER1)> -- ok delete from t1 where i = 4; ERROR: Failed with SQLSTATE 38000 ERROR: Failed with SQLSTATE XJ001 ij(USER1)> -- check delete select * from t1 where i = 4; I |B --------------------------- 4 |XYZ 1 row selected ij(USER1)> drop trigger grant_select_trig; 0 rows inserted/updated/deleted ij(USER1)> set connection user2; ij(USER2)> -- should be successful select * from user1.t1 where i = 1; ERROR: Failed with SQLSTATE 28508 ij(USER2)> set connection user1; ij(USER1)> create trigger revoke_select_trig AFTER delete on t1 for each STATEMENT mode db2sql call revoke_select_proc1(); 0 rows inserted/updated/deleted ij(USER1)> -- should fail delete from t1 where i = 5; ERROR: Failed with SQLSTATE 38000 ERROR: Failed with SQLSTATE XJ001 ij(USER1)> -- check delete failed select * from t1 where i = 5; I |B --------------------------- 5 |XYZ 1 row selected ij(USER1)> drop trigger revoke_select_trig; 0 rows inserted/updated/deleted ij(USER1)> set connection user2; ij(USER2)> -- should be successful select * from user1.t1 where i = 1; ERROR: Failed with SQLSTATE 28508 ij(USER2)> set connection user1; ij(USER1)> create trigger revoke_select_trig AFTER delete on t1 for each STATEMENT mode db2sql call revoke_select_proc2(); 0 rows inserted/updated/deleted ij(USER1)> -- should fail delete from t1 where i = 6; ERROR: Failed with SQLSTATE 38000 ERROR: Failed with SQLSTATE XJ001 ij(USER1)> -- check delete failed select * from t1 where i = 6; I |B --------------------------- 6 |XYZ 1 row selected ij(USER1)> drop trigger revoke_select_trig; 0 rows inserted/updated/deleted ij(USER1)> set connection user2; ij(USER2)> -- should be successful select * from user1.t1 where i = 1; ERROR: Failed with SQLSTATE 28508 ij(USER2)> set connection user1; ij(USER1)> create trigger revoke_select_trig AFTER delete on t1 for each STATEMENT mode db2sql call revoke_select_proc3(); 0 rows inserted/updated/deleted ij(USER1)> -- should fail delete from t1 where i = 7; ERROR: Failed with SQLSTATE 38000 ERROR: Failed with SQLSTATE XJ001 ij(USER1)> -- check delete failed select * from t1 where i = 7; I |B --------------------------- 7 |XYZ 1 row selected ij(USER1)> drop trigger revoke_select_trig; 0 rows inserted/updated/deleted ij(USER1)> set connection user2; ij(USER2)> -- should be successful select * from user1.t1 where i = 1; ERROR: Failed with SQLSTATE 28508 ij(USER2)> set connection user1; ij(USER1)> create trigger revoke_select_trig AFTER delete on t1 for each STATEMENT mode db2sql call revoke_select_proc4(); 0 rows inserted/updated/deleted ij(USER1)> -- ok delete from t1 where i = 8; ERROR: Failed with SQLSTATE 38000 ERROR: Failed with SQLSTATE XJ001 ij(USER1)> -- check delete select * from t1 where i = 8; I |B --------------------------- 8 |XYZ 1 row selected ij(USER1)> drop trigger revoke_select_trig; 0 rows inserted/updated/deleted ij(USER1)> set connection user2; ij(USER2)> -- should fail select * from user1.t1 where i = 1; ERROR: Failed with SQLSTATE 28508 ij(USER2)> set connection user1; ij(USER1)> drop table t2; 0 rows inserted/updated/deleted ij(USER1)> drop table t1; 0 rows inserted/updated/deleted ij(USER1)> -- ------------------------------------------------------------------- -- table privileges (tp) -- ------------------------------------------------------------------- set connection user1; ij(USER1)> create table t1 (c1 int primary key not null, c2 varchar(10)); 0 rows inserted/updated/deleted ij(USER1)> create table t2 (c1 int primary key not null, c2 varchar(10), c3 int); 0 rows inserted/updated/deleted ij(USER1)> create index idx1 on t1(c2); 0 rows inserted/updated/deleted ij(USER1)> insert into t1 values (1, 'a'), (2, 'b'), (3, 'c'); 3 rows inserted/updated/deleted ij(USER1)> insert into t2 values (1, 'Yip', 10); 1 row inserted/updated/deleted ij(USER1)> select * from t1; C1 |C2 ---------------------- 1 |a 2 |b 3 |c 3 rows selected ij(USER1)> CREATE FUNCTION F_ABS1(P1 INT) RETURNS INT NO SQL RETURNS NULL ON NULL INPUT EXTERNAL NAME 'java.lang.Math.abs' LANGUAGE JAVA PARAMETER STYLE JAVA; 0 rows inserted/updated/deleted ij(USER1)> values f_abs1(-5); 1 ----------- 5 1 row selected ij(USER1)> -- grant on a non-existing table, expect error grant select on table t0 to user2; ERROR: Failed with SQLSTATE 42X05 ij(USER1)> -- revoke on a non-existing table, expect error revoke select on table t0 from user2; ERROR: Failed with SQLSTATE 42X05 ij(USER1)> -- grant more than one table, expect error grant select on t0, t1 to user2; ERROR: Failed with SQLSTATE 42X01 ij(USER1)> -- revoke more than one table, expect error revoke select on t0, t1 from user2; ERROR: Failed with SQLSTATE 42X01 ij(USER1)> -- revoking privilege that has not been granted, expect warning revoke select,insert,update,delete,trigger,references on t1 from user2; 0 rows inserted/updated/deleted WARNING 01006: Privilege not revoked from user USER2. ij(USER1)> -- syntax errors, expect errors grant select on t1 from user2; ERROR: Failed with SQLSTATE 42X01 ij(USER1)> revoke select on t1 to user2; ERROR: Failed with SQLSTATE 42X01 ij(USER1)> -- redundant but ok grant select, select on t1 to user2; 0 rows inserted/updated/deleted ij(USER1)> revoke select, select on t1 from user2; 0 rows inserted/updated/deleted ij(USER1)> -- switch to user2 set connection user2; ij(USER2)> -- test SELECT privilege, expect error select * from user1.t1; ERROR: Failed with SQLSTATE 28508 ij(USER2)> -- test INSERT privilege, expect error insert into user1.t1(c1) values 4; ERROR: Failed with SQLSTATE 28506 ij(USER2)> -- test UPDATE privilege, expect error update user1.t1 set c1=10; ERROR: Failed with SQLSTATE 28508 ij(USER2)> -- test DELETE privilege, expect error delete from user1.t1; ERROR: Failed with SQLSTATE 28506 ij(USER2)> -- test REFERENCES privilege, expect error create table t2 (c1 int primary key not null, c2 int references user1.t1); ERROR: Failed with SQLSTATE 28508 ij(USER2)> -- test TRIGGER privilege, expect error create trigger trigger1 after update on user1.t1 for each statement mode db2sql values integer('123'); ERROR: Failed with SQLSTATE 28506 ij(USER2)> -- try to DROP user1.idx1 index, expect error drop index user1.idx1; ERROR: Failed with SQLSTATE 2850D ij(USER2)> -- try to DROP user1.t1 table, expect error drop table user1.t1; ERROR: Failed with SQLSTATE 2850D ij(USER2)> -- non privileged user try to grant privileges on user1.t1, expect error grant select,insert,delete,update,references,trigger on user1.t1 to user2; ERROR: Failed with SQLSTATE 2850C ij(USER2)> -- try to grant privileges for public on user1.t1, expect error grant select,insert,delete,update,references,trigger on user1.t1 to public; ERROR: Failed with SQLSTATE 2850C ij(USER2)> -- try to grant all privileges for user2 on user1.t1, expect error grant ALL PRIVILEGES on user1.t1 to user2; ERROR: Failed with SQLSTATE 2850C ij(USER2)> -- try to grant all privileges on user1.t1 to public, expect error grant ALL PRIVILEGES on user1.t1 to public; ERROR: Failed with SQLSTATE 2850C ij(USER2)> -- try to revoke user1 from table user1.t1, expect error revoke select,insert,delete,update,references,trigger on user1.t1 from user1; ERROR: Failed with SQLSTATE 2850F ij(USER2)> -- try to revoke all privileges from user1 on table user1.t1, expect error revoke ALL PRIVILEGES on user1.t1 from user1; ERROR: Failed with SQLSTATE 2850F ij(USER2)> -- try to revoke execute on a non-existing function on user1.t1, expect error revoke execute on function user1.f1 from user1 restrict; ERROR: Failed with SQLSTATE 2850F ij(USER2)> create table t2 (c1 int); 0 rows inserted/updated/deleted ij(USER2)> -- try revoking yourself from user2.t2, expect error revoke select on t2 from user2; ERROR: Failed with SQLSTATE 2850F ij(USER2)> -- try granting yourself again on user2.t2, expect error. Why? grant select on t2 to user2; ERROR: Failed with SQLSTATE 2850F ij(USER2)> -- try granting yourself multiple times, expect error. Why? grant insert on t2 to user2,user2,user2; ERROR: Failed with SQLSTATE 2850F ij(USER2)> -- try to execute user1.F_ABS1, expect error values user1.F_ABS1(-9); ERROR: Failed with SQLSTATE 2850A ij(USER2)> set connection user1; ij(USER1)> select * from sys.systableperms; TABLEPERMSID |GRANTEE |GRANTOR |TABLEID |&|&|&|&|&|& ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 0 rows selected ij(USER1)> select * from sys.syscolperms; COLPERMSID |GRANTEE |GRANTOR |TABLEID |&|COLUMNS ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 0 rows selected ij(USER1)> select * from sys.sysroutineperms; ROUTINEPERMSID |GRANTEE |GRANTOR |ALIASID |& --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- xxxxFILTERED-UUIDxxxx|PUBLIC |USER1 |xxxxFILTERED-UUIDxxxx|N xxxxFILTERED-UUIDxxxx|PUBLIC |USER1 |xxxxFILTERED-UUIDxxxx|N xxxxFILTERED-UUIDxxxx|PUBLIC |USER1 |xxxxFILTERED-UUIDxxxx|N xxxxFILTERED-UUIDxxxx|PUBLIC |USER1 |xxxxFILTERED-UUIDxxxx|N xxxxFILTERED-UUIDxxxx|PUBLIC |USER1 |xxxxFILTERED-UUIDxxxx|N 5 rows selected ij(USER1)> grant select,update on table t1 to user2, user3; 0 rows inserted/updated/deleted ij(USER1)> grant execute on function F_ABS1 to user2; 0 rows inserted/updated/deleted ij(USER1)> select * from sys.systableperms; TABLEPERMSID |GRANTEE |GRANTOR |TABLEID |&|&|&|&|&|& ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- xxxxFILTERED-UUIDxxxx|USER2 |USER1 |xxxxFILTERED-UUIDxxxx|y|N|N|y|N|N xxxxFILTERED-UUIDxxxx|USER3 |USER1 |xxxxFILTERED-UUIDxxxx|y|N|N|y|N|N 2 rows selected ij(USER1)> select * from sys.syscolperms; COLPERMSID |GRANTEE |GRANTOR |TABLEID |&|COLUMNS ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 0 rows selected ij(USER1)> select * from sys.sysroutineperms; ROUTINEPERMSID |GRANTEE |GRANTOR |ALIASID |& --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- xxxxFILTERED-UUIDxxxx|PUBLIC |USER1 |xxxxFILTERED-UUIDxxxx|N xxxxFILTERED-UUIDxxxx|PUBLIC |USER1 |xxxxFILTERED-UUIDxxxx|N xxxxFILTERED-UUIDxxxx|PUBLIC |USER1 |xxxxFILTERED-UUIDxxxx|N xxxxFILTERED-UUIDxxxx|PUBLIC |USER1 |xxxxFILTERED-UUIDxxxx|N xxxxFILTERED-UUIDxxxx|PUBLIC |USER1 |xxxxFILTERED-UUIDxxxx|N xxxxFILTERED-UUIDxxxx|USER2 |USER1 |xxxxFILTERED-UUIDxxxx|N 6 rows selected ij(USER1)> set connection user2; ij(USER2)> -- try to select from t1, ok select * from user1.t1; C1 |C2 ---------------------- 1 |a 2 |b 3 |c 3 rows selected ij(USER2)> -- try to insert from t1, expect error insert into user1.t1 values (5, 'e'); ERROR: Failed with SQLSTATE 28506 ij(USER2)> -- ok values user1.F_ABS1(-8); 1 ----------- 8 1 row selected ij(USER2)> -- ok update user1.t1 set c2 = 'user2'; 3 rows inserted/updated/deleted ij(USER2)> set connection user1; ij(USER1)> -- add a column to t1, user2 should still be able to select alter table t1 add column c3 varchar(10); 0 rows inserted/updated/deleted ij(USER1)> set connection user2; ij(USER2)> -- ok select * from user1.t1; C1 |C2 |C3 --------------------------------- 1 |user2 |NULL 2 |user2 |NULL 3 |user2 |NULL 3 rows selected ij(USER2)> -- error insert into user1.t1 values (2, 'abc', 'ABC'); ERROR: Failed with SQLSTATE 28506 ij(USER2)> -- ok update user1.t1 set c3 = 'XYZ'; 3 rows inserted/updated/deleted ij(USER2)> set connection user3; ij(USER3)> -- try to select from t1, ok select * from user1.t1; C1 |C2 |C3 --------------------------------- 1 |user2 |XYZ 2 |user2 |XYZ 3 |user2 |XYZ 3 rows selected ij(USER3)> -- user3 does not have permission to execute, expect error values user1.F_ABS1(-8); ERROR: Failed with SQLSTATE 2850A ij(USER3)> -- ok update user1.t1 set c2 = 'user3'; 3 rows inserted/updated/deleted ij(USER3)> set connection user1; ij(USER1)> -- expect warnings revoke update(c2) on t1 from user3; 0 rows inserted/updated/deleted WARNING 01006: Privilege not revoked from user USER3. ij(USER1)> revoke select(c2) on t1 from user3; 0 rows inserted/updated/deleted WARNING 01006: Privilege not revoked from user USER3. ij(USER1)> set connection user2; ij(USER2)> -- ok update user1.t1 set c2 = 'user2'; 3 rows inserted/updated/deleted ij(USER2)> set connection user3; ij(USER3)> -- revoking part of table privilege raises warning, so ok update user1.t1 set c2 = 'user3'; 3 rows inserted/updated/deleted ij(USER3)> -- same as above select * from user1.t1; C1 |C2 |C3 --------------------------------- 1 |user3 |XYZ 2 |user3 |XYZ 3 |user3 |XYZ 3 rows selected ij(USER3)> -- same as above select c2 from user1.t1; C2 ---------- user3 user3 user3 3 rows selected ij(USER3)> set connection user1; ij(USER1)> grant select, update on t1 to PUBLIC; 0 rows inserted/updated/deleted ij(USER1)> set connection user3; ij(USER3)> -- ok, use PUBLIC select * from user1.t1; C1 |C2 |C3 --------------------------------- 1 |user3 |XYZ 2 |user3 |XYZ 3 |user3 |XYZ 3 rows selected ij(USER3)> -- ok, use PUBLIC update user1.t1 set c2 = 'user3'; 3 rows inserted/updated/deleted ij(USER3)> set connection user1; ij(USER1)> grant select on t1 to user3; 0 rows inserted/updated/deleted ij(USER1)> -- revoke select from PUBLIC revoke select on t1 from PUBLIC; 0 rows inserted/updated/deleted ij(USER1)> set connection user3; ij(USER3)> -- ok, privileged select * from user1.t1; C1 |C2 |C3 --------------------------------- 1 |user3 |XYZ 2 |user3 |XYZ 3 |user3 |XYZ 3 rows selected ij(USER3)> -- ok, use PUBLIC update user1.t1 set c2 = 'user3'; 3 rows inserted/updated/deleted ij(USER3)> set connection user1; ij(USER1)> revoke select, update on t1 from user3; 0 rows inserted/updated/deleted ij(USER1)> revoke update on t1 from PUBLIC; 0 rows inserted/updated/deleted ij(USER1)> set connection user3; ij(USER3)> -- expect error select * from user1.t1; ERROR: Failed with SQLSTATE 28508 ij(USER3)> -- expect error update user1.t1 set c2 = 'user3'; ERROR: Failed with SQLSTATE 28508 ij(USER3)> set connection user1; ij(USER1)> declare global temporary table SESSION.t1(c1 int) not logged; 0 rows inserted/updated/deleted ij(USER1)> -- expect error grant select on session.t1 to user2; ERROR: Failed with SQLSTATE XCL51 ij(USER1)> revoke select on session.t1 from user2; ERROR: Failed with SQLSTATE XCL51 ij(USER1)> -- ------------------------------------------------------------------- -- column privileges -- ------------------------------------------------------------------- set connection user1; ij(USER1)> create table t3 (c1 int, c2 varchar(10), c3 int); 0 rows inserted/updated/deleted ij(USER1)> create table t4 (c1 int, c2 varchar(10), c3 int); 0 rows inserted/updated/deleted ij(USER1)> -- grant table select privilege then revoke partially grant select, update on t3 to user2; 0 rows inserted/updated/deleted ij(USER1)> -- expect warning revoke select(c1) on t3 from user2; 0 rows inserted/updated/deleted WARNING 01006: Privilege not revoked from user USER2. ij(USER1)> revoke update(c2) on t3 from user2; 0 rows inserted/updated/deleted WARNING 01006: Privilege not revoked from user USER2. ij(USER1)> set connection user2; ij(USER2)> select * from user1.t3; C1 |C2 |C3 ---------------------------------- 0 rows selected ij(USER2)> set connection user1; ij(USER1)> grant select (c2, c3), update (c2), insert on t4 to user2; 0 rows inserted/updated/deleted ij(USER1)> set connection user2; ij(USER2)> -- expect error select * from user1.t4; ERROR: Failed with SQLSTATE 28508 ij(USER2)> -- expect error select c1 from user1.t4; ERROR: Failed with SQLSTATE 28508 ij(USER2)> -- ok select c2, c3 from user1.t4; C2 |C3 ---------------------- 0 rows selected ij(USER2)> -- expect error update user1.t4 set c1=10, c3=100; ERROR: Failed with SQLSTATE 28508 ij(USER2)> -- ok update user1.t4 set c2='XYZ'; 0 rows inserted/updated/deleted ij(USER2)> set connection user1; ij(USER1)> -- DERBY-1847 -- alter table t4 add column c4 int; -- set connection user2; -- expect error -- select c4 from user1.t4; -- ok -- select c2 from user1.t4; set connection user1; ij(USER1)> -- revoke all columns revoke select, update on t4 from user2; 0 rows inserted/updated/deleted ij(USER1)> set connection user2; ij(USER2)> -- expect error select c2 from user1.t4; ERROR: Failed with SQLSTATE 28508 ij(USER2)> -- expect error update user1.t4 set c2='ABC'; ERROR: Failed with SQLSTATE 28508 ij(USER2)> -- ------------------------------------------------------------------- -- schemas -- ------------------------------------------------------------------- set connection user2; ij(USER2)> -- expect error create table myschema.t5 (i int); ERROR: Failed with SQLSTATE 2850E ij(USER2)> -- ok create table user2.t5 (i int); 0 rows inserted/updated/deleted ij(USER2)> -- expect error CREATE SCHEMA w3 AUTHORIZATION user2; ERROR: Failed with SQLSTATE 2850E ij(USER2)> create table w3.t1 (i int); ERROR: Failed with SQLSTATE 2850E ij(USER2)> -- expect error, already exists CREATE SCHEMA AUTHORIZATION user2; ERROR: Failed with SQLSTATE X0Y68 ij(USER2)> -- expect error CREATE SCHEMA myschema; ERROR: Failed with SQLSTATE 2850E ij(USER2)> -- expect error CREATE SCHEMA user2; ERROR: Failed with SQLSTATE X0Y68 ij(USER2)> set connection user1; ij(USER1)> -- ok CREATE SCHEMA w3 AUTHORIZATION user2; 0 rows inserted/updated/deleted ij(USER1)> CREATE SCHEMA AUTHORIZATION user6; 0 rows inserted/updated/deleted ij(USER1)> CREATE SCHEMA myschema; 0 rows inserted/updated/deleted ij(USER1)> -- DERBY-1858 set connection user5; ij(USER5)> -- expect error DROP SCHEMA w3 RESTRICT; ERROR: Failed with SQLSTATE 2850D ij(USER5)> -- ------------------------------------------------------------------- -- views -- ------------------------------------------------------------------- set connection user1; ij(USER1)> create view sv1 as select * from sys.systables; 0 rows inserted/updated/deleted ij(USER1)> set connection user2; ij(USER2)> -- expect error select tablename from user1.sv1; ERROR: Failed with SQLSTATE 28508 ij(USER2)> set connection user1; ij(USER1)> grant select on sv1 to user2; 0 rows inserted/updated/deleted ij(USER1)> set connection user2; ij(USER2)> -- ok select tablename from user1.sv1; TABLENAME -------------------------------------------------------------------------------------------------------------------------------- SYSCONGLOMERATES SYSTABLES SYSCOLUMNS SYSSCHEMAS SYSCONSTRAINTS SYSKEYS SYSDEPENDS SYSALIASES SYSVIEWS SYSCHECKS SYSFOREIGNKEYS SYSSTATEMENTS SYSFILES SYSTRIGGERS SYSSTATISTICS SYSDUMMY1 SYSTABLEPERMS SYSCOLPERMS SYSROUTINEPERMS T1 T2 T2 T3 T4 T5 SV1 26 rows selected ij(USER2)> set connection user1; ij(USER1)> create table ta (i int); 0 rows inserted/updated/deleted ij(USER1)> insert into ta values 1,2,3; 3 rows inserted/updated/deleted ij(USER1)> create view sva as select * from ta; 0 rows inserted/updated/deleted ij(USER1)> create table tb (j int); 0 rows inserted/updated/deleted ij(USER1)> insert into tb values 2,3,4; 3 rows inserted/updated/deleted ij(USER1)> create view svb as select * from tb; 0 rows inserted/updated/deleted ij(USER1)> grant select on sva to user2; 0 rows inserted/updated/deleted ij(USER1)> set connection user2; ij(USER2)> -- expect error create view svc (i) as select * from user1.sva union select * from user1.svb; ERROR: Failed with SQLSTATE 28508 ij(USER2)> set connection user1; ij(USER1)> grant select on svb to user2; 0 rows inserted/updated/deleted ij(USER1)> set connection user2; ij(USER2)> -- ok create view svc (i) as select * from user1.sva union select * from user1.svb; 0 rows inserted/updated/deleted ij(USER2)> select * from svc; I ----------- 1 2 3 4 4 rows selected ij(USER2)> -- DERBY-1715, DERBY-1631 --set connection user1; --create table t01 (i int); --insert into t01 values 1; --grant select on t01 to user2; --set connection user2; --select * from user1.t01; --create view v01 as select * from user1.t01; --create view v02 as select * from user2.v01; --create view v03 as select * from user2.v02; --set connection user1; --revoke select on t01 from user2; --set connection user2; --select * from user1.t01; --select * from user2.v01; --select * from user2.v02; --select * from user2.v03; --drop view user2.v01; --drop view user2.v02; --drop view user3.v03; -- grant all privileges then create the view set connection user1; ij(USER1)> create table t01ap (i int); 0 rows inserted/updated/deleted ij(USER1)> insert into t01ap values 1; 1 row inserted/updated/deleted ij(USER1)> grant all privileges on t01ap to user2; 0 rows inserted/updated/deleted ij(USER1)> set connection user2; ij(USER2)> -- ok create view v02ap as select * from user1.t01ap; 0 rows inserted/updated/deleted ij(USER2)> -- ok select * from v02ap; I ----------- 1 1 row selected ij(USER2)> -- expect error, don't have with grant option grant select on user2.v02ap to user3; ERROR: Failed with SQLSTATE 2850G ij(USER2)> set connection user3; ij(USER3)> -- expect error create view v03ap as select * from user2.v02ap; ERROR: Failed with SQLSTATE 28508 ij(USER3)> select * from v03ap; ERROR: Failed with SQLSTATE 42Y07 ij(USER3)> -- expect error grant all privileges on v03ap to user4; ERROR: Failed with SQLSTATE 42Y07 ij(USER3)> set connection user4; ij(USER4)> -- expect error create view v04ap as select * from user3.v03ap; ERROR: Failed with SQLSTATE 42Y07 ij(USER4)> select * from v04ap; ERROR: Failed with SQLSTATE 42Y07 ij(USER4)> -- expect error grant select on v04ap to user2; ERROR: Failed with SQLSTATE 42Y07 ij(USER4)> set connection user2; ij(USER2)> select * from user4.v04ap; ERROR: Failed with SQLSTATE 42Y07 ij(USER2)> set connection user4; ij(USER4)> -- expect error revoke select on v04ap from user2; ERROR: Failed with SQLSTATE 42Y07 ij(USER4)> set connection user2; ij(USER2)> -- expect error select * from user4.v04ap; ERROR: Failed with SQLSTATE 42Y07 ij(USER2)> -- ------------------------------------------------------------------- -- references and constraints -- ------------------------------------------------------------------- set connection user1; ij(USER1)> drop table user1.rt1; ERROR: Failed with SQLSTATE 42Y55 ij(USER1)> drop table user2.rt2; ERROR: Failed with SQLSTATE 42Y55 ij(USER1)> create table rt1 (c1 int not null primary key, c2 int not null); 0 rows inserted/updated/deleted ij(USER1)> insert into rt1 values (1, 10); 1 row inserted/updated/deleted ij(USER1)> insert into rt1 values (2, 20); 1 row inserted/updated/deleted ij(USER1)> set connection user2; ij(USER2)> -- expect error create table rt2 (c1 int primary key not null, c2 int not null, c3 int not null, constraint rt2fk foreign key(c1) references user1.rt1); ERROR: Failed with SQLSTATE 28508 ij(USER2)> set connection user1; ij(USER1)> grant references on rt1 to user2; 0 rows inserted/updated/deleted ij(USER1)> set connection user2; ij(USER2)> -- ok create table rt2 (c1 int primary key not null, c2 int not null, c3 int not null, constraint rt2fk foreign key(c2) references user1.rt1); 0 rows inserted/updated/deleted ij(USER2)> insert into rt2 values (1,1,1); 1 row inserted/updated/deleted ij(USER2)> -- expect error insert into rt2 values (3,3,3); ERROR: Failed with SQLSTATE 23503 ij(USER2)> set connection user1; ij(USER1)> revoke references on rt1 from user2; 0 rows inserted/updated/deleted ij(USER1)> set connection user2; ij(USER2)> -- ok, fk constraint got dropped by revoke insert into rt2 values (3,3,3); 1 row inserted/updated/deleted ij(USER2)> select * from rt2; C1 |C2 |C3 ----------------------------------- 1 |1 |1 3 |3 |3 2 rows selected ij(USER2)> -- expect errors create table rt3 (c1 int primary key not null, c2 int not null, c3 int not null, constraint rt3fk foreign key(c1) references user1.rt1); ERROR: Failed with SQLSTATE 28508 ij(USER2)> -- test PUBLIC -- DERBY-1857 --set connection user1; --drop table user3.rt3; --drop table user2.rt2; --drop table user1.rt1; --create table rt1 (c1 int primary key not null, c2 int not null unique, c3 int not null); --insert into rt1 values (1,1,1); --insert into rt1 values (2,2,2); --insert into rt1 values (3,3,3); --grant references(c2, c1) on rt1 to PUBLIC; --set connection user2; --create table rt2 (c1 int primary key not null, constraint rt2fk foreign key(c1) references user1.rt1(c2) ); --insert into rt2 values (1), (2); --set connection user3; --create table rt3 (c1 int primary key not null, constraint rt3fk foreign key(c1) references user1.rt1(c2) ); --insert into rt3 values (1), (2); --set connection user1; --revoke references(c1) on rt1 from PUBLIC; --set connection user2; -- expect constraint error --insert into rt2 values (4); --set connection user3; -- expect constraint error --insert into rt3 values (4); -- test user privilege and PUBLIC set connection user1; ij(USER1)> drop table user3.rt3; ERROR: Failed with SQLSTATE 42Y07 ij(USER1)> drop table user2.rt2; 0 rows inserted/updated/deleted ij(USER1)> drop table user1.rt1; 0 rows inserted/updated/deleted ij(USER1)> create table rt1 (c1 int primary key not null, c2 int); 0 rows inserted/updated/deleted ij(USER1)> insert into rt1 values (1,1), (2,2); 2 rows inserted/updated/deleted ij(USER1)> grant references on rt1 to PUBLIC, user2, user3; 0 rows inserted/updated/deleted ij(USER1)> set connection user2; ij(USER2)> create table rt2 (c1 int primary key not null, constraint rt2fk foreign key(c1) references user1.rt1); 0 rows inserted/updated/deleted ij(USER2)> insert into rt2 values (1), (2); 2 rows inserted/updated/deleted ij(USER2)> set connection user3; ij(USER3)> create table rt3 (c1 int primary key not null, constraint rt3fk foreign key(c1) references user1.rt1); 0 rows inserted/updated/deleted ij(USER3)> insert into rt3 values (1), (2); 2 rows inserted/updated/deleted ij(USER3)> set connection user1; ij(USER1)> -- ok, use the privilege granted to user2 revoke references on rt1 from PUBLIC; 0 rows inserted/updated/deleted ij(USER1)> -- ok, user3 got no privileges, so rt3fk should get dropped. revoke references on rt1 from user3; 0 rows inserted/updated/deleted ij(USER1)> set connection user2; ij(USER2)> -- expect error, FK enforced. insert into rt2 values (3); ERROR: Failed with SQLSTATE 23503 ij(USER2)> set connection user3; ij(USER3)> -- ok insert into rt3 values (3); 1 row inserted/updated/deleted ij(USER3)> -- test multiple FKs -- DERBY-1589? --set connection user1; --drop table user3.rt3; --drop table user2.rt2; --drop table user1.rt1; --create table rt1 (c1 int primary key not null, c2 int); --insert into rt1 values (1,1), (2,2); --grant references on rt1 to PUBLIC, user2, user3; --set connection user2; -- XJ001 occurred at create table rt2... --create table rt2 (c1 int primary key not null, constraint rt2fk foreign key(c1) references user1.rt1); --insert into rt2 values (1), (2); --grant references on rt2 to PUBLIC, user3; --set connection user3; --create table rt3 (c1 int primary key not null, constraint rt3fk1 foreign key(c1) references user1.rt1, -- constraint rt3fk2 foreign key(c1) references user1.rt2); --insert into rt3 values (1), (2); --set connection user1; -- rt3fk1 should get dropped. --revoke references on rt1 from PUBLIC; --revoke references on rt1 from user3; --set connection user2; --revoke references on rt2 from PUBLIC; -- expect error --insert into rt2 values (3); --set connection user3; -- expect error, use user3 references privilege, rt3fk2 still in effect --insert into rt3 values (3); --set connection user2; --revoke references on rt2 from user3; --set connection user3; -- ok, rt3fk2 should be dropped. --insert into rt3 values (3); -- ------------------------------------------------------------------- -- routines and standard builtins -- ------------------------------------------------------------------- set connection user1; ij(USER1)> CREATE FUNCTION F_ABS2(P1 INT) RETURNS INT NO SQL RETURNS NULL ON NULL INPUT EXTERNAL NAME 'java.lang.Math.abs' LANGUAGE JAVA PARAMETER STYLE JAVA; 0 rows inserted/updated/deleted ij(USER1)> -- syntax error grant execute on F_ABS2 to user2; ERROR: Failed with SQLSTATE 42X01 ij(USER1)> -- F_ABS2 is not a procedure, expect errors grant execute on procedure F_ABS2 to user2; ERROR: Failed with SQLSTATE 42Y03 ij(USER1)> set connection user2; ij(USER2)> -- expect errors values user1.F_ABS1(10) + user1.F_ABS2(-10); ERROR: Failed with SQLSTATE 2850A ij(USER2)> set connection user1; ij(USER1)> -- ok grant execute on function F_ABS2 to user2; 0 rows inserted/updated/deleted ij(USER1)> set connection user2; ij(USER2)> -- ok values user1.F_ABS1(10) + user1.F_ABS2(-10); 1 ----------- 20 1 row selected ij(USER2)> -- expect errors revoke execute on function ABS from user2 restrict; ERROR: Failed with SQLSTATE 2850F ij(USER2)> revoke execute on function AVG from user2 restrict; ERROR: Failed with SQLSTATE 42X01 ij(USER2)> revoke execute on function LENGTH from user2 restrict; ERROR: Failed with SQLSTATE 2850F ij(USER2)> set connection user1; ij(USER1)> -- ok revoke execute on function F_ABS2 from user2 restrict; 0 rows inserted/updated/deleted ij(USER1)> revoke execute on function F_ABS1 from user2 restrict; 0 rows inserted/updated/deleted ij(USER1)> set connection user2; ij(USER2)> -- expect error values user1.F_ABS1(10) + user1.F_ABS2(-10); ERROR: Failed with SQLSTATE 2850A ij(USER2)> set connection user1; ij(USER1)> -- ok grant execute on function F_ABS1 to PUBLIC; 0 rows inserted/updated/deleted ij(USER1)> grant execute on function F_ABS2 to PUBLIC; 0 rows inserted/updated/deleted ij(USER1)> set connection user2; ij(USER2)> -- ok values user1.F_ABS1(10) + user1.F_ABS2(-10); 1 ----------- 20 1 row selected ij(USER2)> -- ------------------------------------------------------------------- -- system tables -- ------------------------------------------------------------------- set connection user1; ij(USER1)> -- not allowed. expect errors, sanity check grant ALL PRIVILEGES on sys.sysaliases to user2; ERROR: Failed with SQLSTATE 2850F ij(USER1)> grant ALL PRIVILEGES on sys.syschecks to user2; ERROR: Failed with SQLSTATE 2850F ij(USER1)> grant ALL PRIVILEGES on sys.syscolperms to user2; ERROR: Failed with SQLSTATE 2850F ij(USER1)> grant ALL PRIVILEGES on sys.syscolumns to user2; ERROR: Failed with SQLSTATE 2850F ij(USER1)> grant ALL PRIVILEGES on sys.sysconglomerates to user2; ERROR: Failed with SQLSTATE 2850F ij(USER1)> grant ALL PRIVILEGES on sys.sysconstraints to user2; ERROR: Failed with SQLSTATE 2850F ij(USER1)> grant ALL PRIVILEGES on sys.sysdepends to user2; ERROR: Failed with SQLSTATE 2850F ij(USER1)> grant ALL PRIVILEGES on sys.sysfiles to user2; ERROR: Failed with SQLSTATE 2850F ij(USER1)> grant ALL PRIVILEGES on sys.sysforeignkeys to user2; ERROR: Failed with SQLSTATE 2850F ij(USER1)> grant ALL PRIVILEGES on sys.syskeys to user2; ERROR: Failed with SQLSTATE 2850F ij(USER1)> grant ALL PRIVILEGES on sys.sysroutineperms to user2; ERROR: Failed with SQLSTATE 2850F ij(USER1)> grant ALL PRIVILEGES on sys.sysschemas to user2; ERROR: Failed with SQLSTATE 2850F ij(USER1)> grant ALL PRIVILEGES on sys.sysstatistics to user2; ERROR: Failed with SQLSTATE 2850F ij(USER1)> grant ALL PRIVILEGES on sys.sysstatements to user2; ERROR: Failed with SQLSTATE 2850F ij(USER1)> grant ALL PRIVILEGES on sys.systableperms to user2; ERROR: Failed with SQLSTATE 2850F ij(USER1)> grant ALL PRIVILEGES on sys.systables to user2; ERROR: Failed with SQLSTATE 2850F ij(USER1)> grant ALL PRIVILEGES on sys.systriggers to user2; ERROR: Failed with SQLSTATE 2850F ij(USER1)> grant ALL PRIVILEGES on sys.sysviews to user2; ERROR: Failed with SQLSTATE 2850F ij(USER1)> grant ALL PRIVILEGES on syscs_diag.lock_table to user2; ERROR: Failed with SQLSTATE 2850F ij(USER1)> grant select on sys.sysaliases to user2, public; ERROR: Failed with SQLSTATE 2850F ij(USER1)> grant select on sys.syschecks to user2, public; ERROR: Failed with SQLSTATE 2850F ij(USER1)> grant select on sys.syscolperms to user2, public; ERROR: Failed with SQLSTATE 2850F ij(USER1)> grant select on sys.syscolumns to user2, public; ERROR: Failed with SQLSTATE 2850F ij(USER1)> grant select on sys.sysconglomerates to user2, public; ERROR: Failed with SQLSTATE 2850F ij(USER1)> grant select on sys.sysconstraints to user2, public; ERROR: Failed with SQLSTATE 2850F ij(USER1)> grant select on sys.sysdepends to user2, public; ERROR: Failed with SQLSTATE 2850F ij(USER1)> grant select on sys.sysfiles to user2, public; ERROR: Failed with SQLSTATE 2850F ij(USER1)> grant select on sys.sysforeignkeys to user2, public; ERROR: Failed with SQLSTATE 2850F ij(USER1)> grant select on sys.syskeys to user2, public; ERROR: Failed with SQLSTATE 2850F ij(USER1)> grant select on sys.sysroutineperms to user2, public; ERROR: Failed with SQLSTATE 2850F ij(USER1)> grant select on sys.sysschemas to user2, public; ERROR: Failed with SQLSTATE 2850F ij(USER1)> grant select on sys.sysstatistics to user2, public; ERROR: Failed with SQLSTATE 2850F ij(USER1)> grant select on sys.sysstatements to user2, public; ERROR: Failed with SQLSTATE 2850F ij(USER1)> grant select on sys.systableperms to user2, public; ERROR: Failed with SQLSTATE 2850F ij(USER1)> grant select on sys.systables to user2, public; ERROR: Failed with SQLSTATE 2850F ij(USER1)> grant select on sys.systriggers to user2, public; ERROR: Failed with SQLSTATE 2850F ij(USER1)> grant select on sys.sysviews to user2, public; ERROR: Failed with SQLSTATE 2850F ij(USER1)> grant select on syscs_diag.lock_table to user2, public; ERROR: Failed with SQLSTATE 2850F ij(USER1)> revoke ALL PRIVILEGES on sys.sysaliases from user2; ERROR: Failed with SQLSTATE 2850F ij(USER1)> revoke ALL PRIVILEGES on sys.syschecks from user2; ERROR: Failed with SQLSTATE 2850F ij(USER1)> revoke ALL PRIVILEGES on sys.syscolperms from user2; ERROR: Failed with SQLSTATE 2850F ij(USER1)> revoke ALL PRIVILEGES on sys.syscolumns from user2; ERROR: Failed with SQLSTATE 2850F ij(USER1)> revoke ALL PRIVILEGES on sys.sysconglomerates from user2; ERROR: Failed with SQLSTATE 2850F ij(USER1)> revoke ALL PRIVILEGES on sys.sysconstraints from user2; ERROR: Failed with SQLSTATE 2850F ij(USER1)> revoke ALL PRIVILEGES on sys.sysdepends from user2; ERROR: Failed with SQLSTATE 2850F ij(USER1)> revoke ALL PRIVILEGES on sys.sysfiles from user2; ERROR: Failed with SQLSTATE 2850F ij(USER1)> revoke ALL PRIVILEGES on sys.sysforeignkeys from user2; ERROR: Failed with SQLSTATE 2850F ij(USER1)> revoke ALL PRIVILEGES on sys.syskeys from user2; ERROR: Failed with SQLSTATE 2850F ij(USER1)> revoke ALL PRIVILEGES on sys.sysroutineperms from user2; ERROR: Failed with SQLSTATE 2850F ij(USER1)> revoke ALL PRIVILEGES on sys.sysschemas from user2; ERROR: Failed with SQLSTATE 2850F ij(USER1)> revoke ALL PRIVILEGES on sys.sysstatistics from user2; ERROR: Failed with SQLSTATE 2850F ij(USER1)> revoke ALL PRIVILEGES on sys.sysstatements from user2; ERROR: Failed with SQLSTATE 2850F ij(USER1)> revoke ALL PRIVILEGES on sys.systableperms from user2; ERROR: Failed with SQLSTATE 2850F ij(USER1)> revoke ALL PRIVILEGES on sys.systables from user2; ERROR: Failed with SQLSTATE 2850F ij(USER1)> revoke ALL PRIVILEGES on sys.systriggers from user2; ERROR: Failed with SQLSTATE 2850F ij(USER1)> revoke ALL PRIVILEGES on sys.sysviews from user2; ERROR: Failed with SQLSTATE 2850F ij(USER1)> revoke ALL PRIVILEGES on syscs_diag.lock_table from user2; ERROR: Failed with SQLSTATE 2850F ij(USER1)> revoke select on sys.sysaliases from user2, public; ERROR: Failed with SQLSTATE 2850F ij(USER1)> revoke select on sys.syschecks from user2, public; ERROR: Failed with SQLSTATE 2850F ij(USER1)> revoke select on sys.syscolperms from user2, public; ERROR: Failed with SQLSTATE 2850F ij(USER1)> revoke select on sys.syscolumns from user2, public; ERROR: Failed with SQLSTATE 2850F ij(USER1)> revoke select on sys.sysconglomerates from user2, public; ERROR: Failed with SQLSTATE 2850F ij(USER1)> revoke select on sys.sysconstraints from user2, public; ERROR: Failed with SQLSTATE 2850F ij(USER1)> revoke select on sys.sysdepends from user2, public; ERROR: Failed with SQLSTATE 2850F ij(USER1)> revoke select on sys.sysfiles from user2, public; ERROR: Failed with SQLSTATE 2850F ij(USER1)> revoke select on sys.sysforeignkeys from user2, public; ERROR: Failed with SQLSTATE 2850F ij(USER1)> revoke select on sys.syskeys from user2, public; ERROR: Failed with SQLSTATE 2850F ij(USER1)> revoke select on sys.sysroutineperms from user2, public; ERROR: Failed with SQLSTATE 2850F ij(USER1)> revoke select on sys.sysschemas from user2, public; ERROR: Failed with SQLSTATE 2850F ij(USER1)> revoke select on sys.sysstatistics from user2, public; ERROR: Failed with SQLSTATE 2850F ij(USER1)> revoke select on sys.sysstatements from user2, public; ERROR: Failed with SQLSTATE 2850F ij(USER1)> revoke select on sys.systableperms from user2, public; ERROR: Failed with SQLSTATE 2850F ij(USER1)> revoke select on sys.systables from user2, public; ERROR: Failed with SQLSTATE 2850F ij(USER1)> revoke select on sys.systriggers from user2, public; ERROR: Failed with SQLSTATE 2850F ij(USER1)> revoke select on sys.sysviews from user2, public; ERROR: Failed with SQLSTATE 2850F ij(USER1)> revoke select on syscs_diag.lock_table from user2, public; ERROR: Failed with SQLSTATE 2850F ij(USER1)> -- ------------------------------------------------------------------- -- built-in functions and procedures and routines -- ------------------------------------------------------------------- set connection user3; ij(USER3)> -- test sqlj, only db owner have privileges by default -- expect errors CALL SQLJ.INSTALL_JAR ('bogus.jar','user2.bogus',0); ERROR: Failed with SQLSTATE 2850A ij(USER3)> CALL SQLJ.REPLACE_JAR ('bogus1.jar', 'user2.bogus'); ERROR: Failed with SQLSTATE 2850A ij(USER3)> CALL SQLJ.REMOVE_JAR ('user2.bogus', 0); ERROR: Failed with SQLSTATE 2850A ij(USER3)> -- test backup routines, only db owner have privileges by default -- expect errors CALL SYSCS_UTIL.SYSCS_BACKUP_DATABASE('backup1'); ERROR: Failed with SQLSTATE 2850A ij(USER3)> CALL SYSCS_UTIL.SYSCS_BACKUP_DATABASE_AND_ENABLE_LOG_ARCHIVE_MODE('backup3', 1); ERROR: Failed with SQLSTATE 2850A ij(USER3)> CALL SYSCS_UTIL.SYSCS_BACKUP_DATABASE_AND_ENABLE_LOG_ARCHIVE_MODE_NOWAIT('backup4', 1); ERROR: Failed with SQLSTATE 2850A ij(USER3)> -- test admin routines, only db owner have privileges by default CALL SYSCS_UTIL.SYSCS_FREEZE_DATABASE(); ERROR: Failed with SQLSTATE 2850A ij(USER3)> CALL SYSCS_UTIL.SYSCS_UNFREEZE_DATABASE(); ERROR: Failed with SQLSTATE 2850A ij(USER3)> CALL SYSCS_UTIL.SYSCS_DISABLE_LOG_ARCHIVE_MODE(1); ERROR: Failed with SQLSTATE 2850A ij(USER3)> CALL SYSCS_UTIL.SYSCS_CHECKPOINT_DATABASE(); ERROR: Failed with SQLSTATE 2850A ij(USER3)> -- test statistical routines, available for everyone by default set connection user1; ij(USER1)> -- ok CALL SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1); 0 rows inserted/updated/deleted ij(USER1)> CALL SYSCS_UTIL.SYSCS_SET_STATISTICS_TIMING(1); 0 rows inserted/updated/deleted ij(USER1)> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 -------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: CALL SYSCS_UTIL.SYSCS_SET_STATISTICS_TIMING(1) Parse Time: 0 Bind Time: 0 Optimize Tim& 1 row selected ij(USER1)> CALL SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(0); 0 rows inserted/updated/deleted ij(USER1)> CALL SYSCS_UTIL.SYSCS_SET_STATISTICS_TIMING(0); 0 rows inserted/updated/deleted ij(USER1)> -- ok set connection user3; ij(USER3)> CALL SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1); 0 rows inserted/updated/deleted ij(USER3)> CALL SYSCS_UTIL.SYSCS_SET_STATISTICS_TIMING(1); 0 rows inserted/updated/deleted ij(USER3)> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 -------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: CALL SYSCS_UTIL.SYSCS_SET_STATISTICS_TIMING(1) Parse Time: 0 Bind Time: 0 Optimize Tim& 1 row selected ij(USER3)> CALL SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(0); 0 rows inserted/updated/deleted ij(USER3)> CALL SYSCS_UTIL.SYSCS_SET_STATISTICS_TIMING(0); 0 rows inserted/updated/deleted ij(USER3)> -- test import/export, only db owner have privileges by default create table TABLEIMP1 (i int); 0 rows inserted/updated/deleted ij(USER3)> create table TABLEEXP1 (i int); 0 rows inserted/updated/deleted ij(USER3)> insert into TABLEEXP1 values 1,2,3,4,5; 5 rows inserted/updated/deleted ij(USER3)> CALL SYSCS_UTIL.SYSCS_EXPORT_TABLE ('USER3', 'TABLEEXP1', 'myfile.del', null, null, null); ERROR: Failed with SQLSTATE 2850A ij(USER3)> CALL SYSCS_UTIL.SYSCS_IMPORT_TABLE ('USER3', 'TABLEIMP1', 'myfile.del', null, null, null, 0); ERROR: Failed with SQLSTATE 2850A ij(USER3)> CALL SYSCS_UTIL.SYSCS_EXPORT_QUERY('select * from user3.TABLEEXP1','myfile.del', null, null, null); ERROR: Failed with SQLSTATE 2850A ij(USER3)> CALL SYSCS_UTIL.SYSCS_IMPORT_DATA ('USER3', 'TABLEIMP1', null, '1,3,4', 'myfile.del', null, null, null,0); ERROR: Failed with SQLSTATE 2850A ij(USER3)> -- test property handling routines, only db owner have privileges by default -- expect errors CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY ('derby.locks.deadlockTimeout', '10'); ERROR: Failed with SQLSTATE 2850A ij(USER3)> VALUES SYSCS_UTIL.SYSCS_GET_DATABASE_PROPERTY('derby.locks.deadlockTimeout'); ERROR: Failed with SQLSTATE 2850A ij(USER3)> -- test compress routines, everyone have privilege as long as the user owns the schema -- ok CALL SYSCS_UTIL.SYSCS_COMPRESS_TABLE('USER3', 'TABLEEXP1', 1); 0 rows inserted/updated/deleted ij(USER3)> call SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE('USER3', 'TABLEEXP1', 1, 1, 1); 0 rows inserted/updated/deleted ij(USER3)> -- test check table routines, only db owner have privilege by default VALUES SYSCS_UTIL.SYSCS_CHECK_TABLE('USER3', 'TABLEEXP1'); ERROR: Failed with SQLSTATE 2850A ij(USER3)> -- ------------------------------------------------------------------- -- synonyms -- ------------------------------------------------------------------- set connection user1; ij(USER1)> create synonym s1 for user1.t1; 0 rows inserted/updated/deleted ij(USER1)> create index ii1 on user1.t1(c2); 0 rows inserted/updated/deleted WARNING 01504: The new index is a duplicate of an existing index: IDX1. ij(USER1)> -- not supported yet, expect errors grant select on s1 to user2; ERROR: Failed with SQLSTATE 42X05 ij(USER1)> grant insert on s1 to user2; ERROR: Failed with SQLSTATE 42X05 ij(USER1)> revoke select on s1 from user2; ERROR: Failed with SQLSTATE 42X05 ij(USER1)> revoke insert on s1 from user2; ERROR: Failed with SQLSTATE 42X05 ij(USER1)> set connection user2; ij(USER2)> -- expect errors drop synonym user1.s1; ERROR: Failed with SQLSTATE 2850D ij(USER2)> drop index user1.ii1; ERROR: Failed with SQLSTATE 42X65 ij(USER2)> -- ------------------------------------------------------------------- -- transactions and lock table stmt -- ------------------------------------------------------------------- set connection user1; ij(USER1)> create table t1000 (i int); 0 rows inserted/updated/deleted ij(USER1)> autocommit off; ij(USER1)> grant select on t1000 to user2; 0 rows inserted/updated/deleted ij(USER1)> set connection user2; ij(USER2)> select * from user1.t1000; ERROR: Failed with SQLSTATE 40XL1 ij(USER2)> set connection user1; ij(USER1)> commit; ij(USER1)> set connection user2; ij(USER2)> -- ok select * from user1.t1000; I ----------- 0 rows selected ij(USER2)> set connection user1; ij(USER1)> revoke select on t1000 from user2; 0 rows inserted/updated/deleted ij(USER1)> set connection user2; ij(USER2)> select * from user1.t1000; ERROR: Failed with SQLSTATE 40XL1 ij(USER2)> set connection user1; ij(USER1)> commit; ij(USER1)> set connection user2; ij(USER2)> select * from user1.t1000; ERROR: Failed with SQLSTATE 28508 ij(USER2)> autocommit off; ij(USER2)> -- should fail lock table user1.t1000 in share mode; ERROR: Failed with SQLSTATE 28506 ij(USER2)> -- should fail lock table user1.t1000 in exclusive mode; ERROR: Failed with SQLSTATE 28506 ij(USER2)> commit; ij(USER2)> autocommit on; ij(USER2)> set connection user1; ij(USER1)> grant select on t1000 to user2; 0 rows inserted/updated/deleted ij(USER1)> rollback; ij(USER1)> set connection user2; ij(USER2)> select * from user1.t1000; ERROR: Failed with SQLSTATE 28508 ij(USER2)> set connection user1; ij(USER1)> grant select on t1000 to user2; 0 rows inserted/updated/deleted ij(USER1)> commit; ij(USER1)> revoke select on t1000 from user2; 0 rows inserted/updated/deleted ij(USER1)> rollback; ij(USER1)> set connection user2; ij(USER2)> select * from user1.t1000; I ----------- 0 rows selected ij(USER2)> set connection user1; ij(USER1)> autocommit on; ij(USER1)> drop table t1000; 0 rows inserted/updated/deleted ij(USER1)> set connection user1; ij(USER1)> create table t1000 (c varchar(1)); 0 rows inserted/updated/deleted ij(USER1)> insert into t1000 values 'a', 'b', 'c'; 3 rows inserted/updated/deleted ij(USER1)> grant select on t1000 to user3; 0 rows inserted/updated/deleted ij(USER1)> set connection user2; ij(USER2)> create table t1001 (i int); 0 rows inserted/updated/deleted ij(USER2)> insert into t1001 values 1; 1 row inserted/updated/deleted ij(USER2)> set connection user1; ij(USER1)> select * from user2.t1001; I ----------- 1 1 row selected ij(USER1)> insert into user2.t1001 values 2; 1 row inserted/updated/deleted ij(USER1)> update user2.t1001 set i = 888; 2 rows inserted/updated/deleted ij(USER1)> drop table user1.t1000; 0 rows inserted/updated/deleted ij(USER1)> drop table user2.t1001; 0 rows inserted/updated/deleted ij(USER1)> commit; ij(USER1)> autocommit on; ij(USER1)> -- ------------------------------------------------------------------- -- cursors -- ------------------------------------------------------------------- -- DERBY-1716 --set connection user1; --drop table t1001; --create table t1001 (c varchar(1)); --insert into t1001 values 'a', 'b', 'c'; --grant select on t1001 to user3; --set connection user3; --autocommit off; --GET CURSOR crs1 AS 'select * from user1.t1001'; --next crs1; --set connection user1; -- revoke select privilege while user3 still have an open cursor --revoke select on t1001 from user3; --set connection user3; --next crs1; --next crs1; --close crs1; --autocommit on; -- ------------------------------------------------------------------- -- rename table -- ------------------------------------------------------------------- set connection user1; ij(USER1)> drop table user1.rta; ERROR: Failed with SQLSTATE 42Y55 ij(USER1)> drop table user2.rtb; ERROR: Failed with SQLSTATE 42Y55 ij(USER1)> create table rta (i int); 0 rows inserted/updated/deleted ij(USER1)> grant select on rta to user2; 0 rows inserted/updated/deleted ij(USER1)> set connection user2; ij(USER2)> select * from user1.rta; I ----------- 0 rows selected ij(USER2)> set connection user1; ij(USER1)> rename table rta to rtb; 0 rows inserted/updated/deleted ij(USER1)> set connection user1; ij(USER1)> -- expect error select * from user1.rta; ERROR: Failed with SQLSTATE 42X05 ij(USER1)> -- ok select * from user1.rtb; I ----------- 0 rows selected ij(USER1)> set connection user2; ij(USER2)> -- expect error select * from user1.rta; ERROR: Failed with SQLSTATE 42X05 ij(USER2)> -- ok select * from user1.rtb; I ----------- 0 rows selected ij(USER2)> -- ------------------------------------------------------------------- -- DB owner power =) -- ------------------------------------------------------------------- set connection user2; ij(USER2)> create table ttt1 (i int); 0 rows inserted/updated/deleted ij(USER2)> insert into ttt1 values 1; 1 row inserted/updated/deleted ij(USER2)> set connection user3; ij(USER3)> create table ttt1 (i int); 0 rows inserted/updated/deleted ij(USER3)> insert into ttt1 values 10; 1 row inserted/updated/deleted ij(USER3)> set connection user1; ij(USER1)> -- the following actions are ok select * from user2.ttt1; I ----------- 1 1 row selected ij(USER1)> insert into user2.ttt1 values 2; 1 row inserted/updated/deleted ij(USER1)> update user2.ttt1 set i = 888; 2 rows inserted/updated/deleted ij(USER1)> delete from user2.ttt1; 2 rows inserted/updated/deleted ij(USER1)> drop table user2.ttt1; 0 rows inserted/updated/deleted ij(USER1)> select * from user3.ttt1; I ----------- 10 1 row selected ij(USER1)> insert into user3.ttt1 values 20; 1 row inserted/updated/deleted ij(USER1)> update user3.ttt1 set i = 999; 2 rows inserted/updated/deleted ij(USER1)> delete from user3.ttt1; 2 rows inserted/updated/deleted ij(USER1)> drop table user3.ttt1; 0 rows inserted/updated/deleted ij(USER1)> set connection user4; ij(USER4)> create table ttt1 (i int); 0 rows inserted/updated/deleted ij(USER4)> set connection user1; ij(USER1)> drop table user4.ttt1; 0 rows inserted/updated/deleted ij(USER1)> set connection user2; ij(USER2)> -- DERBY-1858 -- expect error drop schema user4 restrict; ERROR: Failed with SQLSTATE 2850D ij(USER2)> set connection user1; ij(USER1)> -- ok drop schema user4 restrict; 0 rows inserted/updated/deleted ij(USER1)> -- ------------------------------------------------------------------- -- Statement preparation -- ------------------------------------------------------------------- set connection user1; ij(USER1)> create table ttt2 (i int); 0 rows inserted/updated/deleted ij(USER1)> insert into ttt2 values 8; 1 row inserted/updated/deleted ij(USER1)> set connection user2; ij(USER2)> -- prepare statement, ok prepare p1 as 'select * from user1.ttt2'; ij(USER2)> -- expect error execute p1; ERROR: Failed with SQLSTATE 28508 ij(USER2)> remove p1; ij(USER2)> set connection user1; ij(USER1)> grant select on ttt2 to user2; 0 rows inserted/updated/deleted ij(USER1)> set connection user2; ij(USER2)> -- prepare statement, ok prepare p1 as 'select * from user1.ttt2'; ij(USER2)> -- ok execute p1; I ----------- 8 1 row selected ij(USER2)> set connection user1; ij(USER1)> revoke select on ttt2 from user2; 0 rows inserted/updated/deleted ij(USER1)> set connection user2; ij(USER2)> -- expect error execute p1; ERROR: Failed with SQLSTATE 28508 ij(USER2)> remove p1; ij(USER2)> -- ------------------------------------------------------------------- -- Misc -- ------------------------------------------------------------------- set connection user2; ij(USER2)> create table tshared0 (i int); 0 rows inserted/updated/deleted ij(USER2)> -- db owner tries to revoke select access from user2 set connection user1; ij(USER1)> -- expect error revoke select on user2.tshared0 from user2; ERROR: Failed with SQLSTATE 2850F ij(USER1)> set connection user2; ij(USER2)> select * from user2.tshared0; I ----------- 0 rows selected ij(USER2)> set connection user2; ij(USER2)> create table tshared1 (i int); 0 rows inserted/updated/deleted ij(USER2)> grant select, insert, delete, update on tshared1 to user3, user4, user5; 0 rows inserted/updated/deleted ij(USER2)> set connection user3; ij(USER3)> create table tshared1 (i int); 0 rows inserted/updated/deleted ij(USER3)> grant select, insert, delete, update on tshared1 to user2, user4, user5; 0 rows inserted/updated/deleted ij(USER3)> set connection user2; ij(USER2)> insert into user3.tshared1 values 1,2,3; 3 rows inserted/updated/deleted ij(USER2)> update user3.tshared1 set i = 888; 3 rows inserted/updated/deleted ij(USER2)> select * from user3.tshared1; I ----------- 888 888 888 3 rows selected ij(USER2)> delete from user3.tshared1; 3 rows inserted/updated/deleted ij(USER2)> insert into user3.tshared1 values 1,2,3; 3 rows inserted/updated/deleted ij(USER2)> set connection user3; ij(USER3)> insert into user2.tshared1 values 3,2,1; 3 rows inserted/updated/deleted ij(USER3)> update user2.tshared1 set i = 999; 3 rows inserted/updated/deleted ij(USER3)> select * from user2.tshared1; I ----------- 999 999 999 3 rows selected ij(USER3)> delete from user2.tshared1; 3 rows inserted/updated/deleted ij(USER3)> insert into user2.tshared1 values 3,2,1; 3 rows inserted/updated/deleted ij(USER3)> set connection user1; ij(USER1)> update user2.tshared1 set i = 1000; 3 rows inserted/updated/deleted ij(USER1)> update user3.tshared1 set i = 1001; 3 rows inserted/updated/deleted ij(USER1)> delete from user2.tshared1; 3 rows inserted/updated/deleted ij(USER1)> delete from user3.tshared1; 3 rows inserted/updated/deleted ij(USER1)> insert into user2.tshared1 values 0,1,2,3; 4 rows inserted/updated/deleted ij(USER1)> insert into user3.tshared1 values 4,3,2,1; 4 rows inserted/updated/deleted ij(USER1)> set connection user4; ij(USER4)> select * from user2.tshared1; I ----------- 0 1 2 3 4 rows selected ij(USER4)> select * from user3.tshared1; I ----------- 4 3 2 1 4 rows selected ij(USER4)> create view vshared1 as select * from user2.tshared1 union select * from user3.tshared1; 0 rows inserted/updated/deleted ij(USER4)> create view vshared2 as select * from user2.tshared1 intersect select * from user3.tshared1; 0 rows inserted/updated/deleted ij(USER4)> create view vshared3 as select * from user2.tshared1 except select * from user3.tshared1; 0 rows inserted/updated/deleted ij(USER4)> create view vshared4(i) as select * from user3.tshared1 union values 0; 0 rows inserted/updated/deleted ij(USER4)> insert into user2.tshared1 select * from user3.tshared1; 4 rows inserted/updated/deleted ij(USER4)> select * from vshared1; I ----------- 0 1 2 3 4 5 rows selected ij(USER4)> select * from vshared2; I ----------- 1 2 3 4 4 rows selected ij(USER4)> select * from vshared3; I ----------- 0 1 row selected ij(USER4)> select * from vshared4; I ----------- 0 1 2 3 4 5 rows selected ij(USER4)> -- expect errors grant select on vshared1 to user5; ERROR: Failed with SQLSTATE 2850G ij(USER4)> grant select on vshared2 to user5; ERROR: Failed with SQLSTATE 2850G ij(USER4)> grant select on vshared3 to user5; ERROR: Failed with SQLSTATE 2850G ij(USER4)> grant select on vshared4 to user5; ERROR: Failed with SQLSTATE 2850G ij(USER4)> set connection user5; ij(USER5)> select * from user4.vshared1; ERROR: Failed with SQLSTATE 28508 ij(USER5)> select * from user4.vshared2; ERROR: Failed with SQLSTATE 28508 ij(USER5)> select * from user4.vshared3; ERROR: Failed with SQLSTATE 28508 ij(USER5)> select * from user4.vshared4; ERROR: Failed with SQLSTATE 28508 ij(USER5)> set connection user1; ij(USER1)> -- ------------------------------------------------------------------- -- triggers -- ------------------------------------------------------------------- set connection user1; ij(USER1)> -- expect error create trigger tt0a after insert on t1 for each statement mode db2sql grant select on t1 to user2; ERROR: Failed with SQLSTATE 42X01 ij(USER1)> -- expect error create trigger tt0b after insert on t1 for each statement mode db2sql revoke select on t1 from user2; ERROR: Failed with SQLSTATE 42X01 ij(USER1)> -- same schema in trigger action drop table t6; ERROR: Failed with SQLSTATE 42Y55 ij(USER1)> create table t6 (c1 int not null primary key, c2 int); 0 rows inserted/updated/deleted ij(USER1)> grant trigger on t6 to user2; 0 rows inserted/updated/deleted ij(USER1)> set connection user2; ij(USER2)> drop table t7; ERROR: Failed with SQLSTATE 42Y55 ij(USER2)> create table t7 (c1 int, c2 int, c3 int); 0 rows inserted/updated/deleted ij(USER2)> insert into t7 values (1,1,1); 1 row inserted/updated/deleted ij(USER2)> create trigger tt1 after insert on user1.t6 for each statement mode db2sql update user2.t7 set c2 = 888; 0 rows inserted/updated/deleted ij(USER2)> create trigger tt2 after insert on user1.t6 for each statement mode db2sql insert into user2.t7 values (2,2,2); 0 rows inserted/updated/deleted ij(USER2)> set connection user1; ij(USER1)> insert into t6 values (1, 10); 1 row inserted/updated/deleted ij(USER1)> select * from user2.t7; C1 |C2 |C3 ----------------------------------- 1 |888 |1 2 |2 |2 2 rows selected ij(USER1)> -- different schema in trigger action -- this testcase is causing NPE - DERBY-1583 set connection user1; ij(USER1)> drop table t8; ERROR: Failed with SQLSTATE 42Y55 ij(USER1)> drop table t9; ERROR: Failed with SQLSTATE 42Y55 ij(USER1)> create table t8 (c1 int not null primary key, c2 int); 0 rows inserted/updated/deleted ij(USER1)> create table t9 (c1 int, c2 int, c3 int); 0 rows inserted/updated/deleted ij(USER1)> insert into user1.t8 values (1,1); 1 row inserted/updated/deleted ij(USER1)> insert into user1.t9 values (10,10,10); 1 row inserted/updated/deleted ij(USER1)> grant trigger on t8 to user2; 0 rows inserted/updated/deleted ij(USER1)> grant update(c2, c1), insert on t9 to user2; 0 rows inserted/updated/deleted ij(USER1)> set connection user2; ij(USER2)> create trigger tt3 after insert on user1.t8 for each statement mode db2sql update user1.t9 set c2 = 888; 0 rows inserted/updated/deleted ij(USER2)> create trigger tt4 after insert on user1.t8 for each statement mode db2sql insert into user1.t9 values (2,2,2); 0 rows inserted/updated/deleted ij(USER2)> set connection user1; ij(USER1)> -- expect error insert into user1.t8 values (1, 10); ERROR: Failed with SQLSTATE 23505 ij(USER1)> -- ok insert into user1.t8 values (2, 20); 1 row inserted/updated/deleted ij(USER1)> select * from user1.t9; C1 |C2 |C3 ----------------------------------- 10 |888 |10 2 |2 |2 2 rows selected ij(USER1)> -- grant all privileges then create trigger, then revoke the trigger privilege drop table t10; ERROR: Failed with SQLSTATE 42Y55 ij(USER1)> drop table t11; ERROR: Failed with SQLSTATE 42Y55 ij(USER1)> create table t10 (i int, j int); 0 rows inserted/updated/deleted ij(USER1)> insert into t10 values (1,1), (2,2); 2 rows inserted/updated/deleted ij(USER1)> create table t11 (i int); 0 rows inserted/updated/deleted ij(USER1)> grant all privileges on t10 to user2; 0 rows inserted/updated/deleted ij(USER1)> grant all privileges on t11 to user2; 0 rows inserted/updated/deleted ij(USER1)> set connection user2; ij(USER2)> -- ok create trigger tt5 after update on user1.t10 for each statement mode db2sql insert into user1.t11 values 1; 0 rows inserted/updated/deleted ij(USER2)> create trigger tt6 after update of i on user1.t10 for each statement mode db2sql insert into user1.t11 values 2; 0 rows inserted/updated/deleted ij(USER2)> create trigger tt7 after update of j on user1.t10 for each statement mode db2sql insert into user1.t11 values 3; 0 rows inserted/updated/deleted ij(USER2)> update user1.t10 set i=10; 2 rows inserted/updated/deleted ij(USER2)> select * from user1.t10; I |J ----------------------- 10 |1 10 |2 2 rows selected ij(USER2)> select * from user1.t11; I ----------- 1 2 2 rows selected ij(USER2)> set connection user1; ij(USER1)> -- triggers get dropped revoke trigger on t10 from user2; 0 rows inserted/updated/deleted ij(USER1)> set connection user2; ij(USER2)> update user1.t10 set i=20; 2 rows inserted/updated/deleted ij(USER2)> select * from user1.t10; I |J ----------------------- 20 |1 20 |2 2 rows selected ij(USER2)> select * from user1.t11; I ----------- 1 2 2 rows selected ij(USER2)> set connection user1; ij(USER1)> grant trigger on t10 to user2; 0 rows inserted/updated/deleted ij(USER1)> set connection user2; ij(USER2)> create trigger tt8 after update of j on user1.t10 for each statement mode db2sql delete from user1.t11; 0 rows inserted/updated/deleted ij(USER2)> update user1.t10 set j=100; 2 rows inserted/updated/deleted ij(USER2)> select * from user1.t10; I |J ----------------------- 20 |100 20 |100 2 rows selected ij(USER2)> select * from user1.t11; I ----------- 0 rows selected ij(USER2)> delete from user1.t10; 2 rows inserted/updated/deleted ij(USER2)> delete from user1.t11; 0 rows inserted/updated/deleted ij(USER2)> -- test trigger, view and function combo set connection user1; ij(USER1)> drop function F_ABS1; 0 rows inserted/updated/deleted ij(USER1)> CREATE FUNCTION F_ABS1(P1 INT) RETURNS INT NO SQL RETURNS NULL ON NULL INPUT EXTERNAL NAME 'java.lang.Math.abs' LANGUAGE JAVA PARAMETER STYLE JAVA; 0 rows inserted/updated/deleted ij(USER1)> grant execute on function F_ABS1 to user5; 0 rows inserted/updated/deleted ij(USER1)> grant trigger,insert,update,delete,select on t10 to user5; 0 rows inserted/updated/deleted ij(USER1)> grant trigger,insert,update,delete,select on t11 to user5; 0 rows inserted/updated/deleted ij(USER1)> drop view v; ERROR: Failed with SQLSTATE X0X05 ij(USER1)> create view v(i) as values 888; 0 rows inserted/updated/deleted ij(USER1)> grant select on v to user5; 0 rows inserted/updated/deleted ij(USER1)> set connection user5; ij(USER5)> create trigger tt9 after insert on user1.t10 for each statement mode db2sql insert into user1.t11 values (user1.F_ABS1(-5)); 0 rows inserted/updated/deleted ij(USER5)> create trigger tt10 after insert on user1.t10 for each statement mode db2sql insert into user1.t11 select * from user1.v; 0 rows inserted/updated/deleted ij(USER5)> insert into user1.t10 values (1,1); 1 row inserted/updated/deleted ij(USER5)> select * from user1.t10; I |J ----------------------- 1 |1 1 row selected ij(USER5)> select * from user1.t11; I ----------- 5 888 2 rows selected ij(USER5)> -- Related to DERBY-1631 -- cannot revoke execution on F_ABS1 due to X0Y25 (object dependencies) --set connection user1; --revoke execute on function F_ABS1 from user5 restrict; --set connection user5; --insert into user1.t10 values (2,2); --select * from user1.t10; --select * from user1.t11; --set connection user1; --revoke select on v from user5; --set connection user5; --insert into user1.t10 values (3,3); --select * from user1.t10; --select * from user1.t11; --set connection user1; --drop view v; set connection user1; ij(USER1)>