db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From d..@apache.org
Subject svn commit: r420306 [6/6] - in /db/derby/code/trunk/java: engine/org/apache/derby/catalog/ engine/org/apache/derby/iapi/services/io/ engine/org/apache/derby/iapi/sql/dictionary/ engine/org/apache/derby/impl/sql/catalog/ engine/org/apache/derby/impl/sql...
Date Sun, 09 Jul 2006 16:17:57 GMT
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/grantRevokeDDL.sql
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/grantRevokeDDL.sql?rev=420306&r1=420305&r2=420306&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
Sun Jul  9 09:17:53 2006
@@ -357,3 +357,377 @@
 call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.pageSize', '4096');
 values SYSCS_UTIL.SYSCS_GET_DATABASE_PROPERTY('derby.storage.pageSize');
 
+-- Testing views to make sure we collect their depedencies on privileges in SYSDEPENDS table
+connect 'grantRevokeDDL;create=true' user 'mamta1' as mamta1;
+create table t11 (c111 int not null primary key);
+insert into t11 values(1);
+insert into t11 values(2);
+select * from t11;
+create table t12 (c121 int, c122 char);
+insert into t12 values (1,'1');
+select * from t12;
+create table t13 (c131 int, c132 char);
+insert into t13 values (1,'1');
+select * from t13;
+grant select on t12 to mamta2;
+grant select on t11 to public;
+
+connect 'grantRevokeDDL;create=true' user 'mamta2' as mamta2;
+-- both of following will pass because mamt2 has has required privileges because of PUBLIC
select access of mamta1.t11.
+create view v21 as select t1.c111, t2.c122 from mamta1.t11 as t1, mamta1.t12 as t2;
+create view v22 as select * from mamta1.t11;
+create view v23 as select * from mamta1.t12;
+
+set connection mamta1;
+-- When the create view v23 from mamta2's session is executed in mamta1, there will be only
+--    one row in sysdepends for view v23. That row will be for view's dependency on t12.
+--    There will be no row for privilege dependency because table t12 is owned by the same
+--    user who is creating the view v23 and hence there is no privilege required.
+create view v23 as select * from mamta1.t12;
+
+-- satConnection is dba and hence doesn't need explicit privileges to access ojects in any
schema within the database
+set connection satConnection; 
+-- since satConnection is dba, following will not fail even if satConnection has no explicit
privilege to mamta2.v22
+create view v11 as select * from mamta2.v22;
+connect 'grantRevokeDDL;create=true' user 'mamta3' as mamta3;
+create table t31(c311 int);
+-- since mamta3 is not dba, following will fail because no access to mamta2.v22
+create view v31 as select * from mamta2.v22;
+-- mamta3 has access to mamta1.t11 since there is PUBLIC select access on that table but
there is no access to mamta2.v22
+create view v32 as select v22.c111 as a, t11.c111 as b from mamta2.v22 v22, mamta1.t11 t11;
+-- Try to create a view with no privilege to more than one object. 
+create view v33 as select v22.c111 as a, t11.c111 as b from mamta2.v22 v22, mamta1.t11 t11,
mamta2.v21;
+
+-- connect as mamta2 and give select privilege on v22 to mamta3
+set connection mamta2;
+grant select on v22 to mamta3;
+set connection mamta3;
+-- mamta3 has the required privileges now, so following should work
+create view v31 as select * from mamta2.v22;
+-- following will pass because mamta3 has direct access to v22 and public access to t11
+create view v32 as select v22.c111 as a, t11.c111 as b from mamta2.v22 v22, mamta1.t11 t11;
+-- following will still fail because mamta3 doesn't have access to mamta1.t12.c121
+create view v33 as select v22.c111 as a, t12.c121 as b from mamta2.v22 v22, mamta1.t12 t12;
+
+-- connect as mamta2 and give select privilege on v23 to mamta3
+set connection mamta2;
+grant select on v23 to mamta3;
+set connection mamta3;
+-- although mamta3 doesn't have direct access to mamta1.t12, it can look at it through view
mamta2.v23 since mamta3 has select privilege
+-- on mamta2.v23
+create view v34 as select * from mamta2.v23;
+-- following should work fine because mamta3 has access to all the
+-- objects in it's schema
+create view v35 as select * from v34;
+
+-- Write some views based on a routine
+set connection mamta1;
+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;
+values f_abs1(-5);
+create view v11(c111) as values mamta1.f_abs1(-5);
+grant select on v11 to mamta2;
+select * from v11;
+set connection mamta2;
+create view v24 as select * from mamta1.v11;
+select * from v24;
+-- following will fail because no execute permissions on mamta1.f_abs
+create view v25(c251) as (values mamta1.f_abs1(-1));
+set connection mamta1;
+grant execute on function f_abs1 to mamta2;
+set connection mamta2;
+create view v25(c251) as (values mamta1.f_abs1(-1));
+select * from v25;
+
+-- try column level privileges and views
+-- In this test, user has permission on one column but not on the other
+set connection mamta1;
+create table t14(c141 int, c142 int);
+insert into t14 values (1,1), (2,2);
+grant select(c141) on t14 to mamta2;
+set connection mamta2;
+-- following will fail because no access on column mamta1.t14.c142
+create view v26 as (select * from mamta1.t14 where c142=1);
+-- following will fail for the same reason
+create view v26 as (select c141 from mamta1.t14 where c142=1);
+-- following will pass because view is based on column that it can access
+create view v27 as (select c141 from mamta1.t14);
+select * from v27;
+set connection mamta1;
+-- give access to all the columns in t14 to mamta2
+grant select on t14 to mamta2;
+set connection mamta2;
+-- now following will pass
+create view v26 as (select c141 from mamta1.t14 where c142=1);
+select * from v26;
+
+-- in this column level privilege test, there is a user level permission on one column
+--   and a PUBLIC level on the other column. 
+set connection mamta1;
+create table t15(c151 int, c152 int);
+insert into t15 values(1,1),(2,2);
+grant select(c151) on t15 to mamta2;
+grant select(c152) on t15 to public;
+set connection mamta2;
+create view v28 as (select c152 from mamta1.t15 where c151=1);
+
+-- create trigger privilege collection
+-- TriggerTest
+-- first grant one column level privilege at user level and another at public level and then
define the trigger
+set connection mamta1;
+drop table t11TriggerTest;
+create table t11TriggerTest (c111 int not null primary key, c112 int);
+insert into t11TriggerTest values(1,1);
+insert into t11TriggerTest values(2,2);
+grant select(c111) on t11TriggerTest to mamta2;
+grant select(c112) on t11TriggerTest to public;
+set connection mamta2;
+drop table t21TriggerTest;
+create table t21TriggerTest (c211 int); 
+drop table t22TriggerTest;
+create table t22TriggerTest (c221 int); 
+-- following should pass because all the privileges are in places
+create trigger tr21t21TriggerTest after insert on t21TriggerTest for each statement mode
db2sql
+	insert into t22TriggerTest values (select c111 from mamta1.t11TriggerTest where c112=1);
+insert into t21TriggerTest values(1);
+select * from t21TriggerTest;
+select * from t22TriggerTest;
+drop table t21TriggerTest;
+drop table t22TriggerTest;
+
+-- grant all the privileges at the table level and then define the trigger
+set connection mamta1;
+drop table t11TriggerTest;
+create table t11TriggerTest (c111 int not null primary key);
+insert into t11TriggerTest values(1);
+insert into t11TriggerTest values(2);
+create table t12RoutineTest (c121 int);
+insert into t12RoutineTest values (1),(2);
+grant select on t11TriggerTest to mamta2;
+grant insert on t12RoutineTest to mamta2;
+select * from t11TriggerTest;
+select * from t12RoutineTest;
+set connection mamta2;
+create table t21TriggerTest (c211 int); 
+-- following should pass because all the privileges are in places
+create trigger tr21t21TriggerTest after insert on t21TriggerTest for each statement mode
db2sql
+	insert into mamta1.t12RoutineTest values (select c111 from mamta1.t11TriggerTest where c111=1);
+-- this insert's trigger will cause a new row in mamta1.t12RoutineTest
+insert into t21TriggerTest values(1);
+select * from t21TriggerTest;
+set connection mamta1;
+select * from t11TriggerTest;
+select * from t12RoutineTest;
+set connection mamta2;
+-- following should fail because mamta2 doesn't have trigger permission on mamta1.t11TriggerTest
+create trigger tr11t11TriggerTest after insert on mamta1.t11TriggerTest for each statement
mode db2sql
+        insert into mamta1.t12RoutineTest values (1);
+set connection mamta1;
+grant trigger on t11TriggerTest to mamta2;
+set connection mamta2;
+-- following will pass now because mamta2 has trigger permission on mamta1.t11TriggerTest
+create trigger tr11t11TriggerTest after insert on mamta1.t11TriggerTest for each statement
mode db2sql
+        insert into mamta1.t12RoutineTest values (1);
+-- following will fail becuae mamta2 has TRIGGER privilege but not INSERT privilege on mamta1.t11TriggerTest
+insert into mamta1.t11TriggerTest values(3);
+set connection mamta1;
+delete from t11TriggerTest;
+delete from t12RoutineTest;
+insert into mamta1.t11TriggerTest values(3);
+select * from t11TriggerTest;
+select * from t12RoutineTest;
+drop table t11TriggerTest;
+drop table t12RoutineTest;
+
+-- Test routine and trigger combination. Thing to note is triggers always
+--   run with definer's privileges whereas routines always run with
+--   session user's privileges
+set connection mamta1;
+drop table t12RoutineTest;
+create table t12RoutineTest (c121 int);
+insert into t12RoutineTest values (1),(2);
+drop table t13TriggerTest;
+create table t13TriggerTest (c131 int);
+insert into t13TriggerTest values (1),(2);
+grant select on t12RoutineTest to mamta3;
+grant insert on t13TriggerTest to mamta3;
+drop function selectFromSpecificSchema;
+CREATE FUNCTION selectFromSpecificSchema (P1 INT)
+        RETURNS INT 
+        RETURNS NULL ON NULL INPUT
+        EXTERNAL NAME 'org.apache.derbyTesting.functionTests.util.ProcedureTest.selectFromSpecificSchema'
+        LANGUAGE JAVA PARAMETER STYLE JAVA;
+grant execute on function selectFromSpecificSchema to mamta3;
+set connection mamta3;
+drop table t31TriggerTest;
+create table t31TriggerTest(c11 int);
+-- following will pass because all the required privileges are in place for mamta3
+create trigger tr31t31 after insert on t31TriggerTest for each statement mode db2sql
+        insert into mamta1.t13TriggerTest values (values mamta1.selectFromSpecificSchema(1));
+-- following insert will cause a row to be inserted into mamta1.t13TriggerTest if the session
user
+--    has SELECT privilege on mamta1.t12RoutineTest. This shows that although triggers execute
+--    with definer privileges, routines always execute with session user's privilege, even
when 
+--    called by an object which runs with definer's privilege 
+insert into t31TriggerTest values(1);
+select * from t31TriggerTest;
+set connection mamta1;
+select * from t12RoutineTest;
+select * from t13TriggerTest;
+set connection mamta2;
+-- will fail because mamta2 doesn't have INSERT privilege on mamta3.t31TriggerTest
+insert into mamta3.t31TriggerTest values(1);
+set connection mamta3;
+grant insert on t31TriggerTest to mamta2;
+set connection mamta2;
+-- should still fail because trigger on mamta3.t31TriggerTest accesses a routine which
+--   accesses a table on which mamta2 doesn't have SELECT privilege on. mamta3 doesn't
+--   need execute privilege on routine because it is getting accessed by trigger which runs
+--   with the definer privilege. But the routine itself never runs with definer privilege
and
+--   hence the session user needs access to objects accessed by the routine.
+insert into mamta3.t31TriggerTest values(1);
+set connection mamta1;
+grant select on t12RoutineTest to mamta2;
+set connection mamta2;
+-- mamta2 got the SELECT privilege on mamta1.t12RoutineTest and hence following insert should
pass
+insert into mamta3.t31TriggerTest values(1);
+set connection mamta3;
+select * from t31TriggerTest;
+set connection mamta1;
+select * from t12RoutineTest;
+select * from t13TriggerTest;
+
+-- Test routine and view combination. Thing to note is views always
+--   run with definer's privileges whereas routines always run with
+--   session user's privileges. So, eventhough a routine might be
+--   getting accessed by a view which is running with definer's
+--   privileges, during the routine execution, the session user's
+--   privileges will get used.
+set connection mamta1;
+drop table t12RoutineTest;
+create table t12RoutineTest (c121 int);
+insert into t12RoutineTest values (1),(2);
+grant select on t12RoutineTest to mamta3;
+drop function selectFromSpecificSchema;
+CREATE FUNCTION selectFromSpecificSchema (P1 INT)
+        RETURNS INT 
+        RETURNS NULL ON NULL INPUT
+        EXTERNAL NAME 'org.apache.derbyTesting.functionTests.util.ProcedureTest.selectFromSpecificSchema'
+        LANGUAGE JAVA PARAMETER STYLE JAVA;
+grant execute on function selectFromSpecificSchema to mamta3;
+set connection mamta3;
+drop view v21ViewTest;
+-- following will succeed because mamta3 has EXECUTE privileges on the function
+create view v21ViewTest(c211) as values mamta1.selectFromSpecificSchema(1);
+select * from v21ViewTest; 
+grant select on v21ViewTest to mamta2;
+set connection mamta2;
+-- Although mamta2 has SELECT privileges on mamta3.v21ViewTest, mamta2 doesn't have
+--    SELECT privileges on table mamta1.t12RoutineTest accessed by the routine
+--    (which is underneath the view) and hence select from view will fail
+select * from mamta3.v21ViewTest; 
+set connection mamta1;
+grant select  on t12RoutineTest to mamta2;
+set connection mamta2;
+-- now the view select will succeed
+select * from mamta3.v21ViewTest; 
+
+-- In this test, the trigger is accessing a view. Any user that has insert privilege
+--  on trigger table will be able to make an insert even if that user doesn't have
+--  privileges on objects referenced by the trigger.
+set connection mamta1;
+drop table t11TriggerTest;
+create table t11TriggerTest (c111 int not null primary key);
+insert into t11TriggerTest values(1);
+insert into t11TriggerTest values(2);
+grant select on t11TriggerTest to mamta2;
+set connection mamta2;
+drop view v21ViewTest;
+create view v21ViewTest as select * from mamta1.t11TriggerTest;
+grant select on v21ViewTest to mamta4;
+set connection mamta3;
+drop table t31TriggerTest;
+create table t31TriggerTest (c311 int);
+grant insert on t31TriggerTest to mamta4;
+connect 'grantRevokeDDL;create=true' user 'mamta4' as mamta4;
+drop table t41TriggerTest;
+create table t41TriggerTest (c411 int);
+drop trigger tr41t41;
+create trigger tr41t41 after insert on t41TriggerTest for each statement mode db2sql
+        insert into mamta3.t31TriggerTest (select * from mamta2.v21ViewTest);
+insert into t41TriggerTest values(1);
+insert into t41TriggerTest values(2);
+select * from t41TriggerTest;
+set connection mamta1;
+select * from t11TriggerTest;
+set connection mamta2;
+select * from v21ViewTest;
+set connection mamta3;
+select * from t31TriggerTest;
+-- will fail because no permissions on mamta4.t41TriggerTest
+insert into mamta4.t41TriggerTest values(1);
+-- will fail because no permissions on mamta2.v21ViewTest
+select * from mamta2.v21ViewTest;
+-- will fail because no permissions on mamta1.t11TriggerTest
+select * from mamta1.t11TriggerTest;
+set connection mamta4;
+grant insert on t41TriggerTest to mamta3;
+set connection mamta3;
+-- although mamta3 doesn't have access to the objects referenced by the insert trigger
+--   following insert will still pass because triggers run with definer's privileges.
+insert into mamta4.t41TriggerTest values(1);
+
+-- Test constraints
+set connection mamta1;
+drop table t11ConstraintTest;
+create table t11ConstraintTest (c111 int not null primary key);
+insert into t11ConstraintTest values(1);
+insert into t11ConstraintTest values(2);
+grant references on t11ConstraintTest to mamta3;
+set connection mamta2;
+drop table t21ConstraintTest;
+create table t21ConstraintTest (c111 int not null primary key);
+insert into t21ConstraintTest values(1);
+insert into t21ConstraintTest values(2);
+grant references on t21ConstraintTest to mamta3;
+set connection mamta3;
+create table t31ConstraintTest (c311 int references mamta1.t11ConstraintTest, c312 int references
mamta2.t21ConstraintTest);
+drop table t31ConstraintTest;
+
+-- multi-key foreign key constraint and the REFERENCES privilege granted at user level. This
should cause only
+--   one row in SYSDEPENDS for REFERENCES privilege.
+set connection mamta1;
+drop table t11ConstraintTest;
+create table t11ConstraintTest (c111 int not null, c112 int not null, primary key (c111,
c112));
+grant references on t11ConstraintTest to mamta3;
+set connection mamta3;
+drop table t31ConstraintTest;
+create table t31ConstraintTest (c311 int, c312 int, foreign key(c311, c312) references mamta1.t11ConstraintTest);
+drop table t31ConstraintTest;
+
+-- Same test as above with multi-key foreign key constraint but one column REFERENCES privilege
granted at user level
+--   and other column REFERENCES privilege granted at PUBLIC level. This should cause two
rows in SYSDEPENDS for REFERENCES privilege.
+set connection mamta1;
+drop table t11ConstraintTest;
+create table t11ConstraintTest (c111 int not null, c112 int not null, primary key (c111,
c112));
+grant references(c111) on t11ConstraintTest to mamta3;
+grant references(c112) on t11ConstraintTest to PUBLIC;
+--connect 'jdbc:derby:c:/dellater/dbmaintest2;create=true' user 'mamta3' as mamta3;
+set connection mamta3;
+drop table t31ConstraintTest;
+create table t31ConstraintTest (c311 int,  c312 int, foreign key(c311, c312) references mamta1.t11ConstraintTest);
+drop table t31ConstraintTest;
+-- Same test as above with multi-key foreign key constraint, one column REFERENCES privilege
granted at user level
+--   and other column REFERENCES privilege granted at PUBLIC level. This should cause two
rows in SYSDEPENDS for REFERENCES privilege.
+--   But foreign key reference is added using alter table rather than at create table time
+create table t31constrainttest(c311 int, c312 int);
+alter table t31constrainttest add foreign key (c311, c312) references mamta1.t11constrainttest;
+drop table t31ConstraintTest;
+-- create the table again, but this time one foreign key constraint on one table with single
column primary key and
+--   another foreign key constraint on another table with multi-column primary key
+create table t31constrainttest(c311 int, c312 int, c313 int references mamta2.t21ConstraintTest);
+alter table t31constrainttest add foreign key (c311, c312) references mamta1.t11constrainttest;
+
+

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/util/ProcedureTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/util/ProcedureTest.java?rev=420306&r1=420305&r2=420306&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/util/ProcedureTest.java
(original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/util/ProcedureTest.java
Sun Jul  9 09:17:53 2006
@@ -47,6 +47,15 @@
 		conn.close();
 	}
 
+	public static int selectFromSpecificSchema(int p1) throws SQLException {
+		Connection conn = DriverManager.getConnection("jdbc:default:connection");
+		PreparedStatement ps = conn.prepareStatement("select * from mamta1.t12RoutineTest");
+		ps.executeQuery();
+		ps.close();
+		conn.close();
+		return (1);
+	}
+
 	private static void insertInBig(Connection conn, String A, String B, String C, String D)
throws SQLException {
 		PreparedStatement ps = conn.prepareStatement("insert into big values (?, ?, ?, ?)");
 		ps.setString(1, A);



Mime
View raw message