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 ba...@apache.org on 2006/03/16 23:00:58 UTC
svn commit: r386466 - in
/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests:
master/ master/j9_foundation/ tests/lang/
Author: bandaram
Date: Thu Mar 16 14:00:55 2006
New Revision: 386466
URL: http://svn.apache.org/viewcvs?rev=386466&view=rev
Log:
Address grantRevoke test failures and master updates.
1) Make grantRevokeDDL export table into extinout directory. (may be used for import later) and set useextdirs property.
2) Address test failures on J9 VM... Update master for grantRevokeDDL.sql for J9 and enable getting connection using DataSource to make the test run on J9.
Submitted by Satheesh Bandaram (satheesh@sourcery.org)
Added:
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/j9_foundation/grantRevokeDDL.out (with props)
Modified:
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/grantRevokeDDL.out
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/grantRevoke.java
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/grantRevokeDDL.sql
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/grantRevokeDDL_app.properties
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/grantRevoke_app.properties
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/grantRevokeDDL.out
URL: http://svn.apache.org/viewcvs/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/grantRevokeDDL.out?rev=386466&r1=386465&r2=386466&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/grantRevokeDDL.out (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/grantRevokeDDL.out Thu Mar 16 14:00:55 2006
@@ -463,7 +463,7 @@
ij(SAMCONNECTION)> call SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE('SWIPER', 'MYTAB', 1, 1, 1);
0 rows inserted/updated/deleted
ij(SAMCONNECTION)> -- Try other system routines. All should fail
-call SYSCS_UTIL.SYSCS_EXPORT_TABLE('SAM', 'SAMTABLE' , 'table.dat', null, null, null);
+call SYSCS_UTIL.SYSCS_EXPORT_TABLE('SAM', 'SAMTABLE' , 'extinout/table.dat', null, null, null);
ERROR 2850A: User 'SAM' does not have execute permission on PROCEDURE 'SYSCS_UTIL'.'SYSCS_EXPORT_TABLE'.
ij(SAMCONNECTION)> call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.pageSize', '4096');
ERROR 2850A: User 'SAM' does not have execute permission on PROCEDURE 'SYSCS_UTIL'.'SYSCS_SET_DATABASE_PROPERTY'.
@@ -478,7 +478,7 @@
ij(SATCONNECTION)> grant execute on function SYSCS_UTIL.SYSCS_GET_DATABASE_PROPERTY to sam;
0 rows inserted/updated/deleted
ij(SATCONNECTION)> -- Now these should pass
-call SYSCS_UTIL.SYSCS_EXPORT_TABLE('SAM', 'SAMTABLE' , 'table.dat', null, null, null);
+call SYSCS_UTIL.SYSCS_EXPORT_TABLE('SAM', 'SAMTABLE' , 'extinout/table.dat', null, null, null);
0 rows inserted/updated/deleted
ij(SATCONNECTION)> call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.pageSize', '4096');
0 rows inserted/updated/deleted
Added: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/j9_foundation/grantRevokeDDL.out
URL: http://svn.apache.org/viewcvs/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/j9_foundation/grantRevokeDDL.out?rev=386466&view=auto
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/j9_foundation/grantRevokeDDL.out (added)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/j9_foundation/grantRevokeDDL.out Thu Mar 16 14:00:55 2006
@@ -0,0 +1,490 @@
+ij> connect 'grantRevokeDDL;create=true' user 'satheesh' as satConnection;
+ij(SATCONNECTION)> -- Test table privileges
+create table satheesh.tsat(i int not null primary key, j int);
+0 rows inserted/updated/deleted
+ij(SATCONNECTION)> create index tsat_ind on satheesh.tsat(j);
+0 rows inserted/updated/deleted
+ij(SATCONNECTION)> create table satheesh.table1 (a int, b int, c char(10));
+0 rows inserted/updated/deleted
+ij(SATCONNECTION)> grant select on satheesh.tsat to public;
+0 rows inserted/updated/deleted
+ij(SATCONNECTION)> grant insert on satheesh.tsat to foo;
+0 rows inserted/updated/deleted
+ij(SATCONNECTION)> grant delete on satheesh.tsat to foo;
+0 rows inserted/updated/deleted
+ij(SATCONNECTION)> grant update on satheesh.tsat to foo;
+0 rows inserted/updated/deleted
+ij(SATCONNECTION)> grant update(i) on satheesh.tsat to bar;
+0 rows inserted/updated/deleted
+ij(SATCONNECTION)> select * from sys.systableperms;
+GRANTEE |GRANTOR |TABLEID |&|&|&|&|&|&
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+PUBLIC |SATHEESH |xxxxFILTERED-UUIDxxxx|y|N|N|N|N|N
+FOO |SATHEESH |xxxxFILTERED-UUIDxxxx|N|y|y|y|N|N
+2 rows selected
+ij(SATCONNECTION)> connect 'grantRevokeDDL' user 'bar' as barConnection;
+ij(BARCONNECTION)> -- Following revokes should fail. Only owner can revoke permissions
+revoke select on satheesh.tsat from public;
+ERROR 2850C: User 'BAR' is not the owner of Table/View 'SATHEESH'.'TSAT'.
+ij(BARCONNECTION)> revoke insert on satheesh.tsat from foo;
+ERROR 2850C: User 'BAR' is not the owner of Table/View 'SATHEESH'.'TSAT'.
+ij(BARCONNECTION)> revoke update(i) on satheesh.tsat from foo;
+ERROR 2850C: User 'BAR' is not the owner of Table/View 'SATHEESH'.'TSAT'.
+ij(BARCONNECTION)> revoke update on satheesh.tsat from foo;
+ERROR 2850C: User 'BAR' is not the owner of Table/View 'SATHEESH'.'TSAT'.
+ij(BARCONNECTION)> revoke delete on satheesh.tsat from foo;
+ERROR 2850C: User 'BAR' is not the owner of Table/View 'SATHEESH'.'TSAT'.
+ij(BARCONNECTION)> set connection satConnection;
+ij(SATCONNECTION)> -- Revoke permissions not granted already
+revoke trigger on satheesh.tsat from foo;
+0 rows inserted/updated/deleted
+ij(SATCONNECTION)> revoke references on satheesh.tsat from foo;
+0 rows inserted/updated/deleted
+ij(SATCONNECTION)> -- Following revokes should revoke permissions
+revoke update on satheesh.tsat from foo;
+0 rows inserted/updated/deleted
+ij(SATCONNECTION)> revoke delete on satheesh.tsat from foo;
+0 rows inserted/updated/deleted
+ij(SATCONNECTION)> -- Check success by looking at systableperms directly for now
+select * from sys.systableperms;
+GRANTEE |GRANTOR |TABLEID |&|&|&|&|&|&
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+PUBLIC |SATHEESH |xxxxFILTERED-UUIDxxxx|y|N|N|N|N|N
+FOO |SATHEESH |xxxxFILTERED-UUIDxxxx|N|N|y|N|N|N
+2 rows selected
+ij(SATCONNECTION)> revoke insert on satheesh.tsat from foo;
+0 rows inserted/updated/deleted
+ij(SATCONNECTION)> revoke select on satheesh.tsat from public;
+0 rows inserted/updated/deleted
+ij(SATCONNECTION)> -- Check success by looking at systableperms directly for now
+select * from sys.systableperms;
+GRANTEE |GRANTOR |TABLEID |&|&|&|&|&|&
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+0 rows selected
+ij(SATCONNECTION)> -- Test routine permissions
+CREATE FUNCTION F_ABS(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(SATCONNECTION)> grant execute on function F_ABS to foo;
+0 rows inserted/updated/deleted
+ij(SATCONNECTION)> grant execute on function F_ABS(int) to bar;
+0 rows inserted/updated/deleted
+ij(SATCONNECTION)> revoke execute on function F_ABS(int) from bar RESTRICT;
+0 rows inserted/updated/deleted
+ij(SATCONNECTION)> drop function f_abs;
+0 rows inserted/updated/deleted
+ij(SATCONNECTION)> -- Tests with views
+create view v1 as select * from tsat;
+0 rows inserted/updated/deleted
+ij(SATCONNECTION)> grant select on v1 to bar;
+0 rows inserted/updated/deleted
+ij(SATCONNECTION)> grant insert on v1 to foo;
+0 rows inserted/updated/deleted
+ij(SATCONNECTION)> grant update on v1 to public;
+0 rows inserted/updated/deleted
+ij(SATCONNECTION)> -- Tests for synonym. Not supported currently.
+create synonym mySym for satheesh.tsat;
+0 rows inserted/updated/deleted
+ij(SATCONNECTION)> -- Expected to fail
+grant select on mySym to bar;
+ERROR 42X05: Table 'MYSYM' does not exist.
+ij(SATCONNECTION)> grant insert on mySym to foo;
+ERROR 42X05: Table 'MYSYM' does not exist.
+ij(SATCONNECTION)> CREATE FUNCTION F_ABS(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(SATCONNECTION)> values f_abs(-5);
+1
+-----------
+5
+1 row selected
+ij(SATCONNECTION)> -- Test for AUTHORIZATION option for create schema
+-- GrantRevoke TODO: Need to enforce who can create which schema.
+-- More negative test cases need to be added once enforcing is done.
+CREATE SCHEMA MYDODO AUTHORIZATION DODO;
+0 rows inserted/updated/deleted
+ij(SATCONNECTION)> CREATE SCHEMA AUTHORIZATION DERBY;
+0 rows inserted/updated/deleted
+ij(SATCONNECTION)> select * from sys.sysschemas where schemaname not like 'SYS%';
+SCHEMAID |SCHEMANAME |AUTHORIZATIONID
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+xxxxFILTERED-UUIDxxxx|NULLID |SATHEESH
+xxxxFILTERED-UUIDxxxx|SQLJ |SATHEESH
+xxxxFILTERED-UUIDxxxx|APP |APP
+xxxxFILTERED-UUIDxxxx|SATHEESH |SATHEESH
+xxxxFILTERED-UUIDxxxx|MYDODO |DODO
+xxxxFILTERED-UUIDxxxx|DERBY |DERBY
+6 rows selected
+ij(SATCONNECTION)> -- Now connect as different user and try to do DDLs in schema owned by satheesh
+connect 'grantRevokeDDL;user=Swiper' as swiperConnection;
+ij(SWIPERCONNECTION)> create table swiperTab (i int, j int);
+0 rows inserted/updated/deleted
+ij(SWIPERCONNECTION)> insert into swiperTab values (1,1);
+1 row inserted/updated/deleted
+ij(SWIPERCONNECTION)> set schema satheesh;
+0 rows inserted/updated/deleted
+ij(SWIPERCONNECTION)> -- All these DDLs should fail.
+create table NotMyTable (i int, j int);
+ERROR 2850D: User 'SWIPER' can not perform the operation in schema 'SATHEESH'.
+ij(SWIPERCONNECTION)> drop table tsat;
+ERROR 2850D: User 'SWIPER' can not perform the operation in schema 'SATHEESH'.
+ij(SWIPERCONNECTION)> drop index tsat_ind;
+ERROR 2850D: User 'SWIPER' can not perform the operation in schema 'SATHEESH'.
+ij(SWIPERCONNECTION)> create view myview as select * from satheesh.tsat;
+ERROR 2850D: User 'SWIPER' can not perform the operation in schema 'SATHEESH'.
+ij(SWIPERCONNECTION)> CREATE FUNCTION FuncNotMySchema(P1 INT)
+RETURNS INT NO SQL RETURNS NULL ON NULL INPUT
+EXTERNAL NAME 'java.lang.Math.abs'
+LANGUAGE JAVA PARAMETER STYLE JAVA;
+ERROR 2850D: User 'SWIPER' can not perform the operation in schema 'SATHEESH'.
+ij(SWIPERCONNECTION)> alter table tsat add column k int;
+ERROR 2850D: User 'SWIPER' can not perform the operation in schema 'SATHEESH'.
+ij(SWIPERCONNECTION)> create table swiper.mytab ( i int, j int);
+0 rows inserted/updated/deleted
+ij(SWIPERCONNECTION)> set schema swiper;
+0 rows inserted/updated/deleted
+ij(SWIPERCONNECTION)> -- Some simple DML tests. Should all fail.
+select * from satheesh.tsat;
+ERROR 28508: User 'SWIPER' does not have select permission on column 'I' of table 'SATHEESH'.'TSAT'.
+ij(SWIPERCONNECTION)> insert into satheesh.tsat values (1, 2);
+ERROR 28506: User 'SWIPER' does not have insert permission on table 'SATHEESH'.'TSAT'.
+ij(SWIPERCONNECTION)> update satheesh.tsat set i=j;
+ERROR 28508: User 'SWIPER' does not have update permission on column 'I' of table 'SATHEESH'.'TSAT'.
+ij(SWIPERCONNECTION)> create table my_tsat (i int not null, c char(10), constraint fk foreign key(i) references satheesh.tsat);
+ERROR 28508: User 'SWIPER' does not have references permission on column 'I' of table 'SATHEESH'.'TSAT'.
+ij(SWIPERCONNECTION)> -- Now grant some permissions to swiper
+set connection satConnection;
+ij(SATCONNECTION)> grant select(i), update(j) on tsat to swiper;
+0 rows inserted/updated/deleted
+ij(SATCONNECTION)> grant all privileges on table1 to swiper;
+0 rows inserted/updated/deleted
+ij(SATCONNECTION)> grant references on tsat to swiper;
+0 rows inserted/updated/deleted
+ij(SATCONNECTION)> set connection swiperConnection;
+ij(SWIPERCONNECTION)> -- Now some of these should pass
+select * from satheesh.tsat;
+ERROR 28508: User 'SWIPER' does not have select permission on column 'J' of table 'SATHEESH'.'TSAT'.
+ij(SWIPERCONNECTION)> select i from satheesh.tsat;
+I
+-----------
+0 rows selected
+ij(SWIPERCONNECTION)> select i from satheesh.tsat where j=2;
+ERROR 28508: User 'SWIPER' does not have select permission on column 'J' of table 'SATHEESH'.'TSAT'.
+ij(SWIPERCONNECTION)> select i from satheesh.tsat where 2 > (select count(i) from satheesh.tsat);
+I
+-----------
+0 rows selected
+ij(SWIPERCONNECTION)> select i from satheesh.tsat where 2 > (select count(j) from satheesh.tsat);
+ERROR 28508: User 'SWIPER' does not have select permission on column 'J' of table 'SATHEESH'.'TSAT'.
+ij(SWIPERCONNECTION)> select i from satheesh.tsat where 2 > (select count(*) from satheesh.tsat);
+I
+-----------
+0 rows selected
+ij(SWIPERCONNECTION)> update satheesh.tsat set j=j+1;
+0 rows inserted/updated/deleted
+ij(SWIPERCONNECTION)> update satheesh.tsat set j=2 where i=2;
+0 rows inserted/updated/deleted
+ij(SWIPERCONNECTION)> update satheesh.tsat set j=2 where j=1;
+ERROR 28508: User 'SWIPER' does not have select permission on column 'J' of table 'SATHEESH'.'TSAT'.
+ij(SWIPERCONNECTION)> select * from satheesh.table1;
+A |B |C
+----------------------------------
+0 rows selected
+ij(SWIPERCONNECTION)> select c from satheesh.table1 t1, satheesh.tsat t2 where t1.a = t2.i;
+C
+----------
+0 rows selected
+ij(SWIPERCONNECTION)> select b from satheesh.table1 t1, satheesh.tsat t2 where t1.a = t2.j;
+ERROR 28508: User 'SWIPER' does not have select permission on column 'J' of table 'SATHEESH'.'TSAT'.
+ij(SWIPERCONNECTION)> select * from satheesh.table1, (select i from satheesh.tsat) table2;
+A |B |C |I
+----------------------------------------------
+0 rows selected
+ij(SWIPERCONNECTION)> select * from satheesh.table1, (select j from satheesh.tsat) table2;
+ERROR 28508: User 'SWIPER' does not have select permission on column 'J' of table 'SATHEESH'.'TSAT'.
+ij(SWIPERCONNECTION)> -- GrantRevoke TODO: This one should pass, but currently fails. Bind update expression in two steps.
+update satheesh.tsat set j=i;
+ERROR 28508: User 'SWIPER' does not have update permission on column 'I' of table 'SATHEESH'.'TSAT'.
+ij(SWIPERCONNECTION)> create table my_tsat (i int not null, c char(10), constraint fk foreign key(i) references satheesh.tsat);
+0 rows inserted/updated/deleted
+ij(SWIPERCONNECTION)> -- Some TRIGGER privilege checks. See GrantRevoke.java for more tests
+set connection swiperConnection;
+ij(SWIPERCONNECTION)> -- Should fail
+create trigger trig_sat1 after update on satheesh.tsat for each statement mode db2sql values 1;
+ERROR 28506: User 'SWIPER' does not have trigger permission on table 'SATHEESH'.'TSAT'.
+ij(SWIPERCONNECTION)> create trigger trig_sat2 no cascade before delete on satheesh.tsat for each statement mode db2sql values 1;
+ERROR 28506: User 'SWIPER' does not have trigger permission on table 'SATHEESH'.'TSAT'.
+ij(SWIPERCONNECTION)> -- Grant trigger privilege
+set connection satConnection;
+ij(SATCONNECTION)> grant trigger on tsat to swiper;
+0 rows inserted/updated/deleted
+ij(SATCONNECTION)> -- Try now
+set connection swiperConnection;
+ij(SWIPERCONNECTION)> create trigger trig_sat1 after update on satheesh.tsat for each statement mode db2sql values 1;
+0 rows inserted/updated/deleted
+ij(SWIPERCONNECTION)> create trigger trig_sat2 no cascade before delete on satheesh.tsat for each statement mode db2sql values 1;
+0 rows inserted/updated/deleted
+ij(SWIPERCONNECTION)> drop trigger trig_sat1;
+0 rows inserted/updated/deleted
+ij(SWIPERCONNECTION)> drop trigger trig_sat2;
+0 rows inserted/updated/deleted
+ij(SWIPERCONNECTION)> -- Now revoke and try again
+set connection satConnection;
+ij(SATCONNECTION)> revoke trigger on tsat from swiper;
+0 rows inserted/updated/deleted
+ij(SATCONNECTION)> set connection swiperConnection;
+ij(SWIPERCONNECTION)> create trigger trig_sat1 after update on satheesh.tsat for each statement mode db2sql values 1;
+ERROR 28506: User 'SWIPER' does not have trigger permission on table 'SATHEESH'.'TSAT'.
+ij(SWIPERCONNECTION)> create trigger trig_sat2 no cascade before delete on satheesh.tsat for each statement mode db2sql values 1;
+ERROR 28506: User 'SWIPER' does not have trigger permission on table 'SATHEESH'.'TSAT'.
+ij(SWIPERCONNECTION)> -- Now grant access to public and try again
+set connection satConnection;
+ij(SATCONNECTION)> grant trigger on tsat to public;
+0 rows inserted/updated/deleted
+ij(SATCONNECTION)> set connection swiperConnection;
+ij(SWIPERCONNECTION)> create trigger trig_sat1 after update on satheesh.tsat for each statement mode db2sql values 1;
+0 rows inserted/updated/deleted
+ij(SWIPERCONNECTION)> create trigger trig_sat2 no cascade before delete on satheesh.tsat for each statement mode db2sql values 1;
+0 rows inserted/updated/deleted
+ij(SWIPERCONNECTION)> drop trigger trig_sat1;
+0 rows inserted/updated/deleted
+ij(SWIPERCONNECTION)> drop trigger trig_sat2;
+0 rows inserted/updated/deleted
+ij(SWIPERCONNECTION)> -- Some simple routine tests. See GrantRevoke.java for more tests
+set connection satConnection;
+ij(SATCONNECTION)> values f_abs(-5);
+1
+-----------
+5
+1 row selected
+ij(SATCONNECTION)> select f_abs(-4) from sys.systables where tablename like 'SYSTAB%';
+1
+-----------
+4
+4
+2 rows selected
+ij(SATCONNECTION)> -- Same tests should fail
+set connection swiperConnection;
+ij(SWIPERCONNECTION)> set schema satheesh;
+0 rows inserted/updated/deleted
+ij(SWIPERCONNECTION)> values f_abs(-5);
+ERROR 2850A: User 'SWIPER' does not have execute permission on FUNCTION 'SATHEESH'.'F_ABS'.
+ij(SWIPERCONNECTION)> select f_abs(-4) from sys.systables where tablename like 'SYSTAB%';
+ERROR 2850A: User 'SWIPER' does not have execute permission on FUNCTION 'SATHEESH'.'F_ABS'.
+ij(SWIPERCONNECTION)> -- Now grant execute permission and try again
+set connection satConnection;
+ij(SATCONNECTION)> grant execute on function f_abs to swiper;
+0 rows inserted/updated/deleted
+ij(SATCONNECTION)> set connection swiperConnection;
+ij(SWIPERCONNECTION)> -- Should pass now
+values f_abs(-5);
+1
+-----------
+5
+1 row selected
+ij(SWIPERCONNECTION)> select f_abs(-4) from sys.systables where tablename like 'SYSTAB%';
+1
+-----------
+4
+4
+2 rows selected
+ij(SWIPERCONNECTION)> -- Now revoke permission and try
+set connection satConnection;
+ij(SATCONNECTION)> revoke execute on function f_abs from swiper RESTRICT;
+0 rows inserted/updated/deleted
+ij(SATCONNECTION)> set connection swiperConnection;
+ij(SWIPERCONNECTION)> values f_abs(-5);
+ERROR 2850A: User 'SWIPER' does not have execute permission on FUNCTION 'SATHEESH'.'F_ABS'.
+ij(SWIPERCONNECTION)> select f_abs(-4) from sys.systables where tablename like 'SYSTAB%';
+ERROR 2850A: User 'SWIPER' does not have execute permission on FUNCTION 'SATHEESH'.'F_ABS'.
+ij(SWIPERCONNECTION)> -- Now try public permission
+set connection satConnection;
+ij(SATCONNECTION)> grant execute on function f_abs to public;
+0 rows inserted/updated/deleted
+ij(SATCONNECTION)> set connection swiperConnection;
+ij(SWIPERCONNECTION)> -- Should pass again
+values f_abs(-5);
+1
+-----------
+5
+1 row selected
+ij(SWIPERCONNECTION)> select f_abs(-4) from sys.systables where tablename like 'SYSTAB%';
+1
+-----------
+4
+4
+2 rows selected
+ij(SWIPERCONNECTION)> -- Test schema creation authorization checks
+set connection swiperConnection;
+ij(SWIPERCONNECTION)> -- Negative tests. Should all fail
+create schema myFriend;
+ERROR 2850E: User 'SWIPER' can not create schema 'MYFRIEND'. Only database owner could issue this statement.
+ij(SWIPERCONNECTION)> create schema mySchema authorization me;
+ERROR 2850E: User 'SWIPER' can not create schema 'MYSCHEMA'. Only database owner could issue this statement.
+ij(SWIPERCONNECTION)> create schema myschema authorization swiper;
+ERROR 2850E: User 'SWIPER' can not create schema 'MYSCHEMA'. Only database owner could issue this statement.
+ij(SWIPERCONNECTION)> connect 'grantRevokeDDL;user=sam';
+ij(CONNECTION1)> create schema sam authorization swiper;
+ERROR 2850E: User 'SAM' can not create schema 'SAM'. Only database owner could issue this statement.
+ij(CONNECTION1)> -- Should pass
+create schema authorization sam;
+0 rows inserted/updated/deleted
+ij(CONNECTION1)> connect 'grantRevokeDDL;user=george';
+ij(CONNECTION2)> create schema george;
+0 rows inserted/updated/deleted
+ij(CONNECTION2)> -- Now try as DBA (satheesh)
+set connection satConnection;
+ij(SATCONNECTION)> create schema myFriend;
+0 rows inserted/updated/deleted
+ij(SATCONNECTION)> create schema mySchema authorization me;
+0 rows inserted/updated/deleted
+ij(SATCONNECTION)> create schema authorization testSchema;
+0 rows inserted/updated/deleted
+ij(SATCONNECTION)> select * from sys.sysschemas;
+SCHEMAID |SCHEMANAME |AUTHORIZATIONID
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+xxxxFILTERED-UUIDxxxx|SYSIBM |SATHEESH
+xxxxFILTERED-UUIDxxxx|SYS |SATHEESH
+xxxxFILTERED-UUIDxxxx|SYSCAT |SATHEESH
+xxxxFILTERED-UUIDxxxx|SYSFUN |SATHEESH
+xxxxFILTERED-UUIDxxxx|SYSPROC |SATHEESH
+xxxxFILTERED-UUIDxxxx|SYSSTAT |SATHEESH
+xxxxFILTERED-UUIDxxxx|NULLID |SATHEESH
+xxxxFILTERED-UUIDxxxx|SQLJ |SATHEESH
+xxxxFILTERED-UUIDxxxx|SYSCS_DIAG |SATHEESH
+xxxxFILTERED-UUIDxxxx|SYSCS_UTIL |SATHEESH
+xxxxFILTERED-UUIDxxxx|APP |APP
+xxxxFILTERED-UUIDxxxx|SATHEESH |SATHEESH
+xxxxFILTERED-UUIDxxxx|MYDODO |DODO
+xxxxFILTERED-UUIDxxxx|DERBY |DERBY
+xxxxFILTERED-UUIDxxxx|SWIPER |SWIPER
+xxxxFILTERED-UUIDxxxx|SAM |SAM
+xxxxFILTERED-UUIDxxxx|GEORGE |GEORGE
+xxxxFILTERED-UUIDxxxx|MYFRIEND |SATHEESH
+xxxxFILTERED-UUIDxxxx|MYSCHEMA |ME
+xxxxFILTERED-UUIDxxxx|TESTSCHEMA |TESTSCHEMA
+20 rows selected
+ij(SATCONNECTION)> -- Test implicit creation of schemas.. Should fail
+set connection swiperConnection;
+ij(SWIPERCONNECTION)> create table mywork.t1(i int);
+ERROR 2850E: User 'SWIPER' can not create schema 'MYWORK'. Only database owner could issue this statement.
+ij(SWIPERCONNECTION)> create view mywork.v1 as select * from swiper.swiperTab;
+ERROR 2850E: User 'SWIPER' can not create schema 'MYWORK'. Only database owner could issue this statement.
+ij(SWIPERCONNECTION)> -- Implicit schema creation should only work if creating own schema
+connect 'grantRevokeDDL;user=monica' as monicaConnection;
+ij(MONICACONNECTION)> create table mywork.t1 ( i int);
+ERROR 2850E: User 'MONICA' can not create schema 'MYWORK'. Only database owner could issue this statement.
+ij(MONICACONNECTION)> create table monica.shouldPass(c char(10));
+0 rows inserted/updated/deleted
+ij(MONICACONNECTION)> -- Check if DBA can ignore all privilege checks
+set connection swiperConnection;
+ij(SWIPERCONNECTION)> set schema swiper;
+0 rows inserted/updated/deleted
+ij(SWIPERCONNECTION)> revoke select on swiperTab from satheesh;
+0 rows inserted/updated/deleted
+ij(SWIPERCONNECTION)> revoke insert on swiperTab from satheesh;
+0 rows inserted/updated/deleted
+ij(SWIPERCONNECTION)> set connection satConnection;
+ij(SATCONNECTION)> -- Should still work, as satheesh is DBA
+select * from swiper.swiperTab;
+I |J
+-----------------------
+1 |1
+1 row selected
+ij(SATCONNECTION)> insert into swiper.swiperTab values (2,2);
+1 row inserted/updated/deleted
+ij(SATCONNECTION)> select * from swiper.swiperTab;
+I |J
+-----------------------
+1 |1
+2 |2
+2 rows selected
+ij(SATCONNECTION)> grant select on swiper.swiperTab to sam;
+0 rows inserted/updated/deleted
+ij(SATCONNECTION)> revoke insert on swiper.swiperTab from satheesh;
+0 rows inserted/updated/deleted
+ij(SATCONNECTION)> -- Test system routines. Some don't need explicit grant and others do
+-- allowing for only DBA use by default
+set connection satConnection;
+ij(SATCONNECTION)> -- Try granting or revoking from system tables. Should fail
+grant select on sys.systables to sam;
+ERROR 2850F: Grant or Revoke operation is not allowed on object 'SYS.SYSTABLES'.
+ij(SATCONNECTION)> grant delete on sys.syscolumns to sam;
+ERROR 2850F: Grant or Revoke operation is not allowed on object 'SYS.SYSCOLUMNS'.
+ij(SATCONNECTION)> grant update(alias) on sys.sysaliases to swiper;
+ERROR 2850F: Grant or Revoke operation is not allowed on object 'SYS.SYSALIASES'.
+ij(SATCONNECTION)> revoke all privileges on sys.systableperms from public;
+ERROR 2850F: Grant or Revoke operation is not allowed on object 'SYS.SYSTABLEPERMS'.
+ij(SATCONNECTION)> revoke trigger on sys.sysroutineperms from sam;
+ERROR 2850F: Grant or Revoke operation is not allowed on object 'SYS.SYSROUTINEPERMS'.
+ij(SATCONNECTION)> -- Try granting or revoking from system routines that is expected fail
+grant execute on procedure sysibm.sqlprocedures to sam;
+ERROR 2850F: Grant or Revoke operation is not allowed on object 'SYSIBM.SQLPROCEDURES'.
+ij(SATCONNECTION)> revoke execute on procedure sysibm.sqlcamessage from public restrict;
+ERROR 2850F: Grant or Revoke operation is not allowed on object 'SYSIBM.SQLCAMESSAGE'.
+ij(SATCONNECTION)> -- Try positive tests
+connect 'grantRevokeDDL;user=sam' as samConnection;
+ij(SAMCONNECTION)> create table samTable(i int);
+0 rows inserted/updated/deleted
+ij(SAMCONNECTION)> insert into samTable values 1,2,3,4,5,6,7;
+7 rows inserted/updated/deleted
+ij(SAMCONNECTION)> -- Following should pass... PUBLIC should have access to these
+call SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1);
+0 rows inserted/updated/deleted
+ij(SAMCONNECTION)> call SYSCS_UTIL.SYSCS_SET_STATISTICS_TIMING(1);
+0 rows inserted/updated/deleted
+ij(SAMCONNECTION)> 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(SAMCONNECTION)> call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('SAM', 'SAMTABLE', 1);
+0 rows inserted/updated/deleted
+ij(SAMCONNECTION)> call SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE('SAM', 'SAMTABLE', 1, 1, 1);
+0 rows inserted/updated/deleted
+ij(SAMCONNECTION)> -- Try compressing tables not owned...
+-- INPLACE_COMPRESS currently passes, pending DERBY-1062
+call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('SWIPER', 'MYTAB', 1);
+ERROR 38000: The exception 'SQL Exception: User 'SAM' can not perform the operation in schema 'SWIPER'.' was thrown while evaluating an expression.
+ERROR 2850D: User 'SAM' can not perform the operation in schema 'SWIPER'.
+ij(SAMCONNECTION)> call SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE('SWIPER', 'MYTAB', 1, 1, 1);
+0 rows inserted/updated/deleted
+ij(SAMCONNECTION)> -- Try other system routines. All should fail
+call SYSCS_UTIL.SYSCS_EXPORT_TABLE('SAM', 'SAMTABLE' , 'extinout/table.dat', null, null, null);
+ERROR 2850A: User 'SAM' does not have execute permission on PROCEDURE 'SYSCS_UTIL'.'SYSCS_EXPORT_TABLE'.
+ij(SAMCONNECTION)> call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.pageSize', '4096');
+ERROR 2850A: User 'SAM' does not have execute permission on PROCEDURE 'SYSCS_UTIL'.'SYSCS_SET_DATABASE_PROPERTY'.
+ij(SAMCONNECTION)> values SYSCS_UTIL.SYSCS_GET_DATABASE_PROPERTY('derby.storage.pageSize');
+ERROR 2850A: User 'SAM' does not have execute permission on FUNCTION 'SYSCS_UTIL'.'SYSCS_GET_DATABASE_PROPERTY'.
+ij(SAMCONNECTION)> -- Try after DBA grants permissions
+set connection satConnection;
+ij(SATCONNECTION)> grant execute on procedure SYSCS_UTIL.SYSCS_EXPORT_TABLE to public;
+0 rows inserted/updated/deleted
+ij(SATCONNECTION)> grant execute on procedure SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY to sam;
+0 rows inserted/updated/deleted
+ij(SATCONNECTION)> grant execute on function SYSCS_UTIL.SYSCS_GET_DATABASE_PROPERTY to sam;
+0 rows inserted/updated/deleted
+ij(SATCONNECTION)> -- Now these should pass
+call SYSCS_UTIL.SYSCS_EXPORT_TABLE('SAM', 'SAMTABLE' , 'extinout/table.dat', null, null, null);
+0 rows inserted/updated/deleted
+ij(SATCONNECTION)> call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.pageSize', '4096');
+0 rows inserted/updated/deleted
+ij(SATCONNECTION)> values SYSCS_UTIL.SYSCS_GET_DATABASE_PROPERTY('derby.storage.pageSize');
+1
+--------------------------------------------------------------------------------------------------------------------------------
+4096
+1 row selected
+ij(SATCONNECTION)>
Propchange: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/j9_foundation/grantRevokeDDL.out
------------------------------------------------------------------------------
svn:eol-style = native
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/grantRevoke.java
URL: http://svn.apache.org/viewcvs/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/grantRevoke.java?rev=386466&r1=386465&r2=386466&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/grantRevoke.java (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/grantRevoke.java Thu Mar 16 14:00:55 2006
@@ -2042,7 +2042,9 @@
{
if( ! isPublic)
{
- conn = DriverManager.getConnection( "jdbc:derby:wombat", name, password);
+ String connAttrs = "user=" + name + ";password=" + password;
+// conn = DriverManager.getConnection( "jdbc:derby:wombat", name, password);
+ conn = TestUtil.getConnection("wombat", connAttrs);
stmt = conn.createStatement();
}
}
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/grantRevokeDDL.sql
URL: http://svn.apache.org/viewcvs/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/grantRevokeDDL.sql?rev=386466&r1=386465&r2=386466&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/grantRevokeDDL.sql (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/grantRevokeDDL.sql Thu Mar 16 14:00:55 2006
@@ -341,7 +341,7 @@
-- Try other system routines. All should fail
-call SYSCS_UTIL.SYSCS_EXPORT_TABLE('SAM', 'SAMTABLE' , 'table.dat', null, null, null);
+call SYSCS_UTIL.SYSCS_EXPORT_TABLE('SAM', 'SAMTABLE' , 'extinout/table.dat', null, null, null);
call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.pageSize', '4096');
values SYSCS_UTIL.SYSCS_GET_DATABASE_PROPERTY('derby.storage.pageSize');
@@ -353,7 +353,7 @@
grant execute on function SYSCS_UTIL.SYSCS_GET_DATABASE_PROPERTY to sam;
-- Now these should pass
-call SYSCS_UTIL.SYSCS_EXPORT_TABLE('SAM', 'SAMTABLE' , 'table.dat', null, null, null);
+call SYSCS_UTIL.SYSCS_EXPORT_TABLE('SAM', 'SAMTABLE' , 'extinout/table.dat', null, null, null);
call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.pageSize', '4096');
values SYSCS_UTIL.SYSCS_GET_DATABASE_PROPERTY('derby.storage.pageSize');
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/grantRevokeDDL_app.properties
URL: http://svn.apache.org/viewcvs/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/grantRevokeDDL_app.properties?rev=386466&r1=386465&r2=386466&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/grantRevokeDDL_app.properties (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/grantRevokeDDL_app.properties Thu Mar 16 14:00:55 2006
@@ -2,6 +2,7 @@
ij.showNoConnectionsAtStart=true
derby.database.defaultConnectionMode=sqlStandard
+useextdirs=true
# DataSource properties, only used if ij.dataSource is set
ij.dataSource.databaseName=wombat
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/grantRevoke_app.properties
URL: http://svn.apache.org/viewcvs/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/grantRevoke_app.properties?rev=386466&r1=386465&r2=386466&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/grantRevoke_app.properties (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/grantRevoke_app.properties Thu Mar 16 14:00:55 2006
@@ -7,7 +7,7 @@
ij.showNoCountForSelect=true
# DataSource properties, only used if ij.dataSource is set
-# ij.dataSource.databaseName=wombat
-# ij.dataSource.createDatabase=create
-# ij.dataSource.user=OWNER
-# ij.dataSource.password=BigCheese
+ij.dataSource.databaseName=wombat
+ij.dataSource.createDatabase=create
+ij.dataSource.user=OWNER
+ij.dataSource.password=BigCheese