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 dj...@apache.org on 2006/07/14 20:37:21 UTC
svn commit: r421981 - in /db/derby/code/trunk/java:
engine/org/apache/derby/impl/sql/catalog/
engine/org/apache/derby/impl/sql/execute/
testing/org/apache/derbyTesting/functionTests/master/
testing/org/apache/derbyTesting/functionTests/tests/lang/
Author: djd
Date: Fri Jul 14 11:37:20 2006
New Revision: 421981
URL: http://svn.apache.org/viewvc?rev=421981&view=rev
Log:
DERBY-1330 (partial) Move the resetting of permission descriptor's uuid into DataDictionary.addRemovePermissionsDescriptor method.
Patch submitted by Mamta Satoor
Modified:
db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/catalog/DataDictionaryImpl.java
db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/RoutinePrivilegeInfo.java
db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/TablePrivilegeInfo.java
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/grantRevokeDDL.sql
Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/catalog/DataDictionaryImpl.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/catalog/DataDictionaryImpl.java?rev=421981&r1=421980&r2=421981&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/catalog/DataDictionaryImpl.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/catalog/DataDictionaryImpl.java Fri Jul 14 11:37:20 2006
@@ -9929,6 +9929,23 @@
TransactionController tc)
throws StandardException
{
+ // It is possible for grant statements to look like following
+ // grant execute on function f_abs to mamata2, mamata3;
+ // grant all privileges on t11 to mamata2, mamata3;
+ // This means that dd.addRemovePermissionsDescriptor will be called
+ // twice for TablePermsDescriptor and twice for RoutinePermsDescriptor,
+ // once for each grantee.
+ // First it's called for mamta2. When a row is inserted for mamta2
+ // into the correct system table for the permission descriptor, the
+ // permission descriptor's uuid gets populated with the uuid of
+ // the row that just got inserted into the system table for mamta2
+ // Now, before dd.addRemovePermissionsDescriptor leaves so it can
+ // get called for MAMTA3, we should reset the Permission Descriptor's
+ // uuid to null or otherwise, for the next call to
+ // dd.addRemovePermissionDescriptor, we will think that there is a
+ // duplicate row getting inserted for the same uuid.
+ // Same logic applies to ColPermsDescriptor
+
int catalogNumber = perm.getCatalogNumber();
perm.setGrantee( grantee);
@@ -9955,7 +9972,15 @@
if( existingRow == null)
{
if( ! add)
+ //we didn't find an entry in system catalog and this is revoke
+ //so that means there is nothing to revoke. Simply return.
+ //No need to reset permission descriptor's uuid because
+ //no row was ever found in system catalog for the given
+ //permission and hence uuid can't be non-null
return;
+ //We didn't find an entry in system catalog and this is grant so
+ //so that means we have to enter a new row in system catalog for
+ //this grant.
ExecRow row = ti.getCatalogRowFactory().makeRow( perm, (TupleDescriptor) null);
int insertRetCode = ti.insertRow(row, tc, true /* wait */);
if( SanityManager.DEBUG)
@@ -9973,7 +9998,12 @@
else
changedColCount = rf.removePermissions( existingRow, perm, colsChanged);
if( changedColCount == 0)
- return;
+ {
+ //grant/revoke privilege didn't change anything and hence just
+ //return after resetting the uuid in the permission descriptor
+ perm.setUUID(null);
+ return;
+ }
if( changedColCount < 0)
{
// No permissions left in the current row
@@ -10002,6 +10032,8 @@
Cacheable cacheEntry = getPermissionsCache().findCached( perm);
if( cacheEntry != null)
getPermissionsCache().remove( cacheEntry);
+ //Before leaving, reset the uuid in the permission descriptor
+ perm.setUUID(null);
} // end of addPermissionsDescriptor
/**
Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/RoutinePrivilegeInfo.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/RoutinePrivilegeInfo.java?rev=421981&r1=421980&r2=421981&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/RoutinePrivilegeInfo.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/RoutinePrivilegeInfo.java Fri Jul 14 11:37:20 2006
@@ -76,21 +76,7 @@
dd.startWriting(lcc);
for( Iterator itr = grantees.iterator(); itr.hasNext();)
{
- // It is possible for grant statement to look like following
- // grant execute on function f_abs to mamata2, mamata3;
- // This means that dd.addRemovePermissionsDescriptor will be called
- // twice for routinePermsDesc, once for each grantee.
- // First it's called for mamta2. After a row is inserted for mamta2
- // into SYS.SYSROUTINEPERMS, the routinePermsDesc's uuid will get
- // populated with the uuid of the row that just got inserted into
- // SYS.SYSROUTINEPERMS for mamta2
- // Next, before dd.addRemovePermissionsDescriptor gets called for
- // MAMTA3, we should set the routinePermsDesc's uuid to null or
- // otherwise, we will think that there is a duplicate row getting
- // inserted for the same uuid.
String grantee = (String) itr.next();
- if(grant)
- routinePermsDesc.setUUID(null);
dd.addRemovePermissionsDescriptor( grant, routinePermsDesc, grantee, tc);
}
} // end of executeConstantAction
Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/TablePrivilegeInfo.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/TablePrivilegeInfo.java?rev=421981&r1=421980&r2=421981&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/TablePrivilegeInfo.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/TablePrivilegeInfo.java Fri Jul 14 11:37:20 2006
@@ -126,19 +126,6 @@
// Add or remove the privileges to/from the SYS.SYSTABLEPERMS and SYS.SYSCOLPERMS tables
for( Iterator itr = grantees.iterator(); itr.hasNext();)
{
- // It is possible for grant statement to look like following
- // grant all privileges on t11 to mamata2, mamata3;
- // This means that dd.addRemovePermissionsDescriptor will be called
- // twice for tablePermsDesc, once for each grantee.
- // First it's called for mamta2. After a row is inserted for mamta2
- // into SYS.SYSTABLEPERMS, the tablePermsDesc's uuid will get
- // populated with the uuid of the row that just got inserted into
- // SYS.SYSTABLEPERMS for mamta2
- // Next, before dd.addRemovePermissionsDescriptor gets called for
- // MAMTA3, we should set the tablePermsDesc's uuid to null or
- // otherwise, we will think that there is a duplicate row getting
- // inserted for the same uuid.
- // Same logic applies to colPermsDescriptor
String grantee = (String) itr.next();
if( tablePermsDesc != null)
{
@@ -147,9 +134,7 @@
TablePermsDescriptor tempTablePermsDesc =
dd.getTablePermissions(td.getUUID(), grantee);
tablePermsDesc.setUUID(tempTablePermsDesc.getUUID());
-
- } else
- tablePermsDesc.setUUID(null);
+ }
dd.addRemovePermissionsDescriptor( grant, tablePermsDesc, grantee, tc);
}
for( int i = 0; i < columnBitSets.length; i++)
@@ -161,9 +146,7 @@
ColPermsDescriptor tempColPermsDescriptor =
dd.getColumnPermissions(td.getUUID(), colPermsDescs[i].getType() ,grant, grantee);
colPermsDescs[i].setUUID(tempColPermsDescriptor.getUUID());
-
- } else
- colPermsDescs[i].setUUID(null);
+ }
dd.addRemovePermissionsDescriptor( grant, colPermsDescs[i], grantee, tc);
}
}
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/grantRevokeDDL.out
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/grantRevokeDDL.out?rev=421981&r1=421980&r2=421981&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 Fri Jul 14 11:37:20 2006
@@ -487,13 +487,127 @@
--------------------------------------------------------------------------------------------------------------------------------
4096
1 row selected
-ij(SATCONNECTION)> -- Testing views to make sure we collect their depedencies on privileges in SYSDEPENDS table
+ij(SATCONNECTION)> -- grant one permission on table to user1 and another permission to user3,
+-- then grant another permission on that same table to user1 and
+-- user2(this is the first permission to user2 on the table) and user3
+-- (this user already has the permission being granted). Notice that
+-- the first 2 grant statements created a row in SYSTABLEPERMS for
+-- user1 and user3. Third grant is going to update the pre-existing
+-- row for user1. The third grant is going to insert a new row for
+-- user2 in SYSTABLEPERMS and the third grant is going to be a no-op
+-- for user3.
+-- So, basically, this is to test that one single grant statment can
+-- update and insert and no-op rows into SYSTABLEPERMS for different users.
connect 'grantRevokeDDL;create=true' user 'mamta1' as mamta1;
WARNING 01J01: Database 'grantRevokeDDL' not created, connection made to existing database instead.
ij(MAMTA1)> create table t11 (c111 int not null primary key);
0 rows inserted/updated/deleted
ij(MAMTA1)> insert into t11 values(1);
1 row inserted/updated/deleted
+ij(MAMTA1)> grant select on t11 to mamta2;
+0 rows inserted/updated/deleted
+ij(MAMTA1)> grant insert on t11 to mamta3;
+0 rows inserted/updated/deleted
+ij(MAMTA1)> grant insert on t11 to mamta2, mamta3, mamta4;
+0 rows inserted/updated/deleted
+ij(MAMTA1)> connect 'grantRevokeDDL;create=true' user 'mamta2' as mamta2;
+WARNING 01J01: Database 'grantRevokeDDL' not created, connection made to existing database instead.
+ij(MAMTA2)> select * from mamta1.t11;
+C111
+-----------
+1
+1 row selected
+ij(MAMTA2)> insert into mamta1.t11 values(2);
+1 row inserted/updated/deleted
+ij(MAMTA2)> select * from mamta1.t11;
+C111
+-----------
+1
+2
+2 rows selected
+ij(MAMTA2)> connect 'grantRevokeDDL;create=true' user 'mamta3' as mamta3;
+WARNING 01J01: Database 'grantRevokeDDL' not created, connection made to existing database instead.
+ij(MAMTA3)> -- following select will fail because no permissions
+select * from mamta1.t11;
+ERROR: Failed with SQLSTATE 28508
+ij(MAMTA3)> insert into mamta1.t11 values(3);
+1 row inserted/updated/deleted
+ij(MAMTA3)> connect 'grantRevokeDDL;create=true' user 'mamta4' as mamta4;
+WARNING 01J01: Database 'grantRevokeDDL' not created, connection made to existing database instead.
+ij(MAMTA4)> -- following select will fail because no permissions
+select * from mamta1.t11;
+ERROR: Failed with SQLSTATE 28508
+ij(MAMTA4)> insert into mamta1.t11 values(4);
+1 row inserted/updated/deleted
+ij(MAMTA4)> set connection mamta1;
+ij(MAMTA1)> revoke all privileges on t11 from PUBLIC;
+0 rows inserted/updated/deleted
+ij(MAMTA1)> select * from mamta1.t11;
+C111
+-----------
+1
+2
+3
+4
+4 rows selected
+ij(MAMTA1)> drop table t11;
+0 rows inserted/updated/deleted
+ij(MAMTA1)> -- now test the column level permissions
+set connection mamta1;
+ij(MAMTA1)> create table t11 (c111 int not null primary key, c112 int, c113 int, c114 int);
+0 rows inserted/updated/deleted
+ij(MAMTA1)> insert into t11 values(1,1,1,1);
+1 row inserted/updated/deleted
+ij(MAMTA1)> grant select(c111) on t11 to mamta2;
+0 rows inserted/updated/deleted
+ij(MAMTA1)> grant select(c112) on t11 to mamta2, mamta3;
+0 rows inserted/updated/deleted
+ij(MAMTA1)> grant update(c112) on t11 to mamta2, mamta3, mamta4;
+0 rows inserted/updated/deleted
+ij(MAMTA1)> grant update on t11 to mamta2;
+0 rows inserted/updated/deleted
+ij(MAMTA1)> set connection mamta2;
+ij(MAMTA2)> update mamta1.t11 set c113 = 2 where c111=1;
+1 row inserted/updated/deleted
+ij(MAMTA2)> select c111,c112 from mamta1.t11;
+C111 |C112
+-----------------------
+1 |1
+1 row selected
+ij(MAMTA2)> -- following will fail because no select permissions on all the columns
+select * from mamta1.t11;
+ERROR: Failed with SQLSTATE 28508
+ij(MAMTA2)> set connection mamta3;
+ij(MAMTA3)> -- following will fail because no update permission on column c113
+update mamta1.t11 set c113=3;
+ERROR: Failed with SQLSTATE 28508
+ij(MAMTA3)> select c112 from mamta1.t11;
+C112
+-----------
+1
+1 row selected
+ij(MAMTA3)> set connection mamta4;
+ij(MAMTA4)> -- following will fail because no select permission on column c112
+select c112 from mamta1.t11;
+ERROR: Failed with SQLSTATE 28508
+ij(MAMTA4)> set connection mamta1;
+ij(MAMTA1)> select * from mamta1.t11;
+C111 |C112 |C113 |C114
+-----------------------------------------------
+1 |1 |2 |1
+1 row selected
+ij(MAMTA1)> revoke select on t11 from mamta2, mamta3, mamta4;
+0 rows inserted/updated/deleted
+ij(MAMTA1)> revoke update(c111, c112) on t11 from mamta2, mamta3, mamta4;
+0 rows inserted/updated/deleted
+ij(MAMTA1)> drop table t11;
+0 rows inserted/updated/deleted
+ij(MAMTA1)> -- Testing views to make sure we collect their depedencies on privileges in SYSDEPENDS table
+set connection mamta1;
+ij(MAMTA1)> create table t11 (c111 int not null primary key);
+0 rows inserted/updated/deleted
+ij(MAMTA1)> insert into t11 values(1);
+1 row inserted/updated/deleted
ij(MAMTA1)> insert into t11 values(2);
1 row inserted/updated/deleted
ij(MAMTA1)> select * from t11;
@@ -524,8 +638,7 @@
0 rows inserted/updated/deleted
ij(MAMTA1)> grant select on t11 to public;
0 rows inserted/updated/deleted
-ij(MAMTA1)> connect 'grantRevokeDDL;create=true' user 'mamta2' as mamta2;
-WARNING 01J01: Database 'grantRevokeDDL' not created, connection made to existing database instead.
+ij(MAMTA1)> set connection mamta2;
ij(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;
0 rows inserted/updated/deleted
@@ -545,8 +658,7 @@
ij(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;
0 rows inserted/updated/deleted
-ij(SATCONNECTION)> connect 'grantRevokeDDL;create=true' user 'mamta3' as mamta3;
-WARNING 01J01: Database 'grantRevokeDDL' not created, connection made to existing database instead.
+ij(SATCONNECTION)> set connection mamta3;
ij(MAMTA3)> create table t31(c311 int);
0 rows inserted/updated/deleted
ij(MAMTA3)> -- since mamta3 is not dba, following will fail because no access to mamta2.v22
@@ -1016,8 +1128,7 @@
0 rows inserted/updated/deleted
ij(MAMTA3)> grant insert on t31TriggerTest to mamta4;
0 rows inserted/updated/deleted
-ij(MAMTA3)> connect 'grantRevokeDDL;create=true' user 'mamta4' as mamta4;
-WARNING 01J01: Database 'grantRevokeDDL' not created, connection made to existing database instead.
+ij(MAMTA3)> set connection mamta4;
ij(MAMTA4)> drop table t41TriggerTest;
ERROR: Failed with SQLSTATE 42Y07
ij(MAMTA4)> create table t41TriggerTest (c411 int);
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=421981&r1=421980&r2=421981&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 Fri Jul 14 11:37:20 2006
@@ -357,10 +357,70 @@
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
+-- grant one permission on table to user1 and another permission to user3,
+-- then grant another permission on that same table to user1 and
+-- user2(this is the first permission to user2 on the table) and user3
+-- (this user already has the permission being granted). Notice that
+-- the first 2 grant statements created a row in SYSTABLEPERMS for
+-- user1 and user3. Third grant is going to update the pre-existing
+-- row for user1. The third grant is going to insert a new row for
+-- user2 in SYSTABLEPERMS and the third grant is going to be a no-op
+-- for user3.
+-- So, basically, this is to test that one single grant statment can
+-- update and insert and no-op rows into SYSTABLEPERMS for different users.
connect 'grantRevokeDDL;create=true' user 'mamta1' as mamta1;
create table t11 (c111 int not null primary key);
insert into t11 values(1);
+grant select on t11 to mamta2;
+grant insert on t11 to mamta3;
+grant insert on t11 to mamta2, mamta3, mamta4;
+connect 'grantRevokeDDL;create=true' user 'mamta2' as mamta2;
+select * from mamta1.t11;
+insert into mamta1.t11 values(2);
+select * from mamta1.t11;
+connect 'grantRevokeDDL;create=true' user 'mamta3' as mamta3;
+-- following select will fail because no permissions
+select * from mamta1.t11;
+insert into mamta1.t11 values(3);
+connect 'grantRevokeDDL;create=true' user 'mamta4' as mamta4;
+-- following select will fail because no permissions
+select * from mamta1.t11;
+insert into mamta1.t11 values(4);
+set connection mamta1;
+revoke all privileges on t11 from PUBLIC;
+select * from mamta1.t11;
+drop table t11;
+
+-- now test the column level permissions
+set connection mamta1;
+create table t11 (c111 int not null primary key, c112 int, c113 int, c114 int);
+insert into t11 values(1,1,1,1);
+grant select(c111) on t11 to mamta2;
+grant select(c112) on t11 to mamta2, mamta3;
+grant update(c112) on t11 to mamta2, mamta3, mamta4;
+grant update on t11 to mamta2;
+set connection mamta2;
+update mamta1.t11 set c113 = 2 where c111=1;
+select c111,c112 from mamta1.t11;
+-- following will fail because no select permissions on all the columns
+select * from mamta1.t11;
+set connection mamta3;
+-- following will fail because no update permission on column c113
+update mamta1.t11 set c113=3;
+select c112 from mamta1.t11;
+set connection mamta4;
+-- following will fail because no select permission on column c112
+select c112 from mamta1.t11;
+set connection mamta1;
+select * from mamta1.t11;
+revoke select on t11 from mamta2, mamta3, mamta4;
+revoke update(c111, c112) on t11 from mamta2, mamta3, mamta4;
+drop table t11;
+
+-- Testing views to make sure we collect their depedencies on privileges in SYSDEPENDS table
+set connection 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);
@@ -372,7 +432,7 @@
grant select on t12 to mamta2;
grant select on t11 to public;
-connect 'grantRevokeDDL;create=true' user 'mamta2' as mamta2;
+set connection 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;
@@ -389,7 +449,7 @@
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;
+set connection 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;
@@ -651,7 +711,7 @@
drop table t31TriggerTest;
create table t31TriggerTest (c311 int);
grant insert on t31TriggerTest to mamta4;
-connect 'grantRevokeDDL;create=true' user 'mamta4' as mamta4;
+set connection mamta4;
drop table t41TriggerTest;
create table t41TriggerTest (c411 int);
drop trigger tr41t41;