Return-Path: Delivered-To: apmail-db-derby-commits-archive@www.apache.org Received: (qmail 46827 invoked from network); 22 Sep 2006 15:17:30 -0000 Received: from hermes.apache.org (HELO mail.apache.org) (209.237.227.199) by minotaur.apache.org with SMTP; 22 Sep 2006 15:17:30 -0000 Received: (qmail 68135 invoked by uid 500); 22 Sep 2006 15:17:25 -0000 Delivered-To: apmail-db-derby-commits-archive@db.apache.org Received: (qmail 68109 invoked by uid 500); 22 Sep 2006 15:17:25 -0000 Mailing-List: contact derby-commits-help@db.apache.org; run by ezmlm Precedence: bulk list-help: list-unsubscribe: List-Post: Reply-To: "Derby Development" List-Id: Delivered-To: mailing list derby-commits@db.apache.org Received: (qmail 68081 invoked by uid 99); 22 Sep 2006 15:17:25 -0000 Received: from idunn.apache.osuosl.org (HELO idunn.apache.osuosl.org) (140.211.166.84) by apache.org (qpsmtpd/0.29) with ESMTP; Fri, 22 Sep 2006 08:17:25 -0700 Authentication-Results: idunn.apache.osuosl.org smtp.mail=rhillegas@apache.org; spf=permerror X-ASF-Spam-Status: No, hits=-9.4 required=5.0 tests=ALL_TRUSTED,NO_REAL_NAME Received-SPF: error (idunn.apache.osuosl.org: domain apache.org from 140.211.166.113 cause and error) Received: from [140.211.166.113] ([140.211.166.113:55843] helo=eris.apache.org) by idunn.apache.osuosl.org (ecelerity 2.1.1.8 r(12930)) with ESMTP id 93/3E-06791-34EF3154 for ; Fri, 22 Sep 2006 08:16:23 -0700 Received: by eris.apache.org (Postfix, from userid 65534) id 7CC7D1A981D; Fri, 22 Sep 2006 08:16:04 -0700 (PDT) Content-Type: text/plain; charset="utf-8" MIME-Version: 1.0 Content-Transfer-Encoding: 7bit Subject: svn commit: r448961 [2/4] - in /db/derby/code/branches/10.2/java: demo/ drda/org/apache/derby/impl/drda/ engine/org/apache/derby/impl/sql/compile/ testing/org/apache/derbyTesting/functionTests/harness/ testing/org/apache/derbyTesting/functionTests/mast... Date: Fri, 22 Sep 2006 15:16:01 -0000 To: derby-commits@db.apache.org From: rhillegas@apache.org X-Mailer: svnmailer-1.1.0 Message-Id: <20060922151604.7CC7D1A981D@eris.apache.org> X-Spam-Rating: minotaur.apache.org 1.6.2 0/1000/N Modified: db/derby/code/branches/10.2/java/testing/org/apache/derbyTesting/functionTests/master/grantRevokeDDL2.out URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.2/java/testing/org/apache/derbyTesting/functionTests/master/grantRevokeDDL2.out?view=diff&rev=448961&r1=448960&r2=448961 ============================================================================== --- db/derby/code/branches/10.2/java/testing/org/apache/derbyTesting/functionTests/master/grantRevokeDDL2.out (original) +++ db/derby/code/branches/10.2/java/testing/org/apache/derbyTesting/functionTests/master/grantRevokeDDL2.out Fri Sep 22 08:15:58 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)>