You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-commits@db.apache.org by mi...@apache.org on 2006/09/16 01:54:20 UTC
svn commit: r446789 [1/3] - in
/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests:
master/grantRevokeDDL2.out master/jdk16/grantRevokeDDL2.out
tests/lang/grantRevokeDDL2.sql tests/lang/grantRevokeDDL2_app.properties
Author: mikem
Date: Fri Sep 15 16:54:19 2006
New Revision: 446789
URL: http://svn.apache.org/viewvc?view=rev&rev=446789
Log:
DERBY-1736
contributed by Yip Ng, yipng168@gmail.com
Addtional grant and revoke tests developed during the 10.2 buddy testing.
Modified:
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/grantRevokeDDL2.out
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/jdk16/grantRevokeDDL2.out
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/grantRevokeDDL2.sql
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/grantRevokeDDL2_app.properties
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/grantRevokeDDL2.out
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/grantRevokeDDL2.out?view=diff&rev=446789&r1=446788&r2=446789
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/grantRevokeDDL2.out (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/grantRevokeDDL2.out Fri Sep 15 16:54:19 2006
@@ -1,12 +1,17 @@
-ij> connect 'grantRevokeDDL2;create=true' user 'user1' as user1;
+ij> -- -------------------------------------------------------------------
+-- 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> connect 'grantRevokeDDL2;create=true' user 'user2' as user2;
-WARNING 01J01: Database 'grantRevokeDDL2' not created, connection made to existing database instead.
+ij> connect 'grantRevokeDDL2' user 'user2' as user2;
WARNING 01J14: SQL authorization is being used without first enabling authentication.
-ij(USER2)> connect 'grantRevokeDDL2;create=true' user 'user3' as user3;
-WARNING 01J01: Database 'grantRevokeDDL2' not created, connection made to existing database instead.
+ij(USER2)> connect 'grantRevokeDDL2' user 'user3' as user3;
WARNING 01J14: SQL authorization is being used without first enabling authentication.
-ij(USER3)> -- DERBY-1729
+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.
@@ -258,4 +263,1638 @@
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 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 USER3.
+ij(USER1)> revoke select(c2) on t1 from user3;
+0 rows inserted/updated/deleted
+WARNING 01006: Privilege not revoked from 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 USER2.
+ij(USER1)> revoke update(c2) on t3 from user2;
+0 rows inserted/updated/deleted
+WARNING 01006: Privilege not revoked from 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)> -- -------------------------------------------------------------------
+-- 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;
+-- DERBY-1858
+-- expect error
+-- drop schema user4 restrict;
+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)>