db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Mamta A. Satoor (JIRA)" <derby-...@db.apache.org>
Subject [jira] Updated: (DERBY-1539) As per the functional spec attached to DERBY-1330, a trigger should be dropped when a privilege required by the trigger is revoked.
Date Sun, 30 Jul 2006 07:04:14 GMT
     [ http://issues.apache.org/jira/browse/DERBY-1539?page=all ]

Mamta A. Satoor updated DERBY-1539:
-----------------------------------

    Attachment: DERBY1539V4diffDropTriggerOnRevokeRequiredPrivilege.txt
                DERBY1539V4statDropTriggerOnRevokeRequiredPrivilege.txt

Recently, I had submitted a patch(DERBY1539V3diffDropTriggerOnRevoke.txt) for triggers which
will drop the triggers if a revoke privilege is issued on a table/routine used by the trigger.
That patch dropped the trigger even if the trigger didn't depend on the permission type/column
on the table. And the patch dropped the trigger if the trigger depended on the routine.

eg for current behavior on revoke table level privilege
	mamta1
	create table t1(c11 int, c12 int);
	grant select, update, trigger on t1 to mamta2
	mamta2
	create a trigger on mamta1.t1 with action as select * from some other table
	-- notice that the trigger object above depends only on the trigger privilege on mamta1.t1
	mamta1
	revoke select on t1 from mamta2
	-- this revoke ends up dropping the trigger even though trigger doesnot rely on select permission

eg for current behavior on revoke column level privilege
	mamta1
	create table t1(c11 int, c12 int);
	grant trigger on t1 to mamta2
	create table t2(c21 int, c22 int);
	grant select(c21, c22) on t2 to mamta2
	mamta2
	create a trigger on mamta1.t1 with action as select c21 from mamta1.t2
	-- notice that the trigger object above depends only on the trigger privilege on mamta1.t1,
and 
                      -- select privilege on mamta1.t2.c21
	mamta1
	revoke select(c22) on t2 from mamta2
	-- this revoke ends up dropping the trigger even though trigger doesnot rely on select  
                
                     -- permission on column c22 of table t2

eg for current behavior on revoke column level privilege
	mamta1
	create function f1
	grant execute on f1 to mamta2
	mamta2
	create table t1
	create trigger on t1 with action that executes mamta1.f1
	mamta1
	revoke execute on f1 from mamta2 RESTRICT
	-- this revoke ends up dropping the trigger even though revoke execute is supposed to have

                     -- RESTRICT behavior, which means that
	-- if there are dependent objects, then revoke execute should fail. Couldn't implement this
in earlier 	-- patch because dependents didn't know what kind of revoke was issued. All they
knew was revoke 
                     -- issued against one of the objects that the dependent relied on.

With the idea of working in incremental steps, I submitted the earlier patch as the first
step towards implementing revoke privilege. As the next step, I am attaching another patch
(DERBY1539V4diffDropTriggerOnRevokeRequiredPrivilege.txt ), which will fix the problem mentioned
above in the egs. This patch currently only deals with triggers. The next steps are to implement
similar behavior for views and constraints. The svn stat -q o/p for this patch is attached
as DERBY1539V4statDropTriggerOnRevokeRequiredPrivilege.txt. Note that I have added a new file
in this patch.

The reason for implementing REVOKE EXECUTE ... RESTRICT in this patch is that prior to this
patch, there was
no way of knowing what kind of revoke privilege is issued by the user and hence even on revoke
execute, I was dropping the dependent objects. With this patch, now we know what kind of revoke
privilege has been issued and when the dependent gets revoke execute action, it can now throw
an exception.

Grant revoke tests have run fine with this patch. I fired derbyall suite couple hrs back on
my Windows XP machine with Sun'd jdk1.4 and no errors so far.

More information on the current patch's implementation details is as follows.
1)BasicDependencyManager, TriggerDescriptor
SYSTABLEPERMS has one row per table, grantee, grantor. That row has various fields to indicate
what type of permissions(insert, trigger, update etc) are available for that key. The row
is also uniquely identified by a UUID. Currently, when an object is created and it needs a
particular pemission type on a given table, grantee, grantor, the dependency manager(DM) only
tracks the dependency using UUID and it doesn't keep track of the exact permission type required.
Because of this, currently, any permission type that gets revoked on table, grantee, grantor,
it ends up dropping the dependent object, whether or not the dependent object really needs
that permission type. 
	eg(copying the eg from the beginning of this comment
	mamta1
	create table t1(c11 int, c12 int);
	grant select, update, trigger on t1 to mamta2
	mamta2
	create a trigger on mamta1.t1 with action as select * from some other table
	-- notice that the trigger object above depends only on the trigger privilege on mamta1.t1
	mamta1
	revoke select on t1 from mamta2
	-- this revoke ends up dropping the trigger even though trigger doesnot rely on select permission

The problem also exists for column level permissions. SYSCOLPERMS has one row per table, grantee,
grantor, a permission type and a bit map for columns on which that permission is granted.
The row is also uniquely identified by a UUID. Now, an object might need a permission on only
a subset of table columns and if a revoke is done later on columns that are not used by the
object, then we should not drop the object. Currently, Derby tracks dependency on column level
permissions using just the UUID and does not keep track of exact column subset required by
the dependent object. Because of this, any column that gets revoked for a given UUID, DM drops
all the dependents on that UUID, even if the dependent object does not care about the column
being revoked.
	eg(copying the eg from the beginning of this comment
	mamta1
	create table t1(c11 int, c12 int);
	grant trigger on t1 to mamta2
	create table t2(c21 int, c22 int);
	grant select(c21, c22) on t2 to mamta2
	mamta2
	create a trigger on mamta1.t1 with action as select c21 from mamta1.t2
	-- notice that the trigger object above depends only on the trigger privilege on mamta1.t1,
and select privilege on mamta1.t2.c21
	mamta1
	revoke select(c22) on t2 from mamta2
	-- this revoke ends up dropping the trigger even though trigger doesnot rely on select permission
on column c22 of table t2

	
To fix both these problems, I have enhanced the DM such that when an object is created which
needs a particular permission type on a given permission table's UUID, DM tracks the object's
dependency on the permission type and the row in SYSTABLEPERM identified by it's UUID. Similarly,
when an object is created which needs a particular permission type on a subset of table's
column, the object's dependnecy is tracked on the column subset and the row in SYSCOLPERMS
identified by it's UUID. Both of these solutions follow the existing model that we currently
have to track view's dependency on a subset of a table's columns. 

I have implemented this kind of dependency for SYSTABLEPERMS by adding a new class called
DDPrivilegeTypeDependableFinder.java  which saves the permission type part of the dependency
tracking for an object. For SYSCOLPERMS, I implemented this by modifying existing class DDColumnDependableFinder.java.
This class is currently used for tracking view's dependency on subset of a table's columns.
Now, this class will also get used to track dependency on a subset of columns for column level
privileges.

The changes so far explained covers what happens when DM saves the dependencies. Later on,
when a provider wants to notify it's dependents about an action, DM needs to use all the information
that it collected while saving the dependencies. 

To be specific, later, when a permission is revoked, the DM needs to build a list of dependents
that rely on that permission. For each of the dependents, DM needs to find the exact permission
type or column subset required by that dependent.  That information then should be saved in
the provider object and we should put the provider+dependent object pair into the list of
dependents. Once the list building is finished, each of the dependent objects will get the
revoke invalidation action. The dependent object will check the provider object to see if
the permission type or column subset being revoked is one of  the things that it depends on.
If yes, then it will drop itself. If not, then it will ignore the invalidation action. 

In order to implement this, amon other changes, I had to modify the code recently checked
in by Dan for improving DM performance. Dan made changes to DM so that we do not recreate
the provider object when building the dependents list for the provider because we already
have access to the provider object. With my changes, while building the dependents list, if
the provider is TablePermsDescriptor or ColPermsDescriptor, then for every dependent, I save
into the provider object exactly what kind of permission type/column subset the dependent
depends on. And I put the dependent and this modified Provider pair into the dependent list.
	
With this change in dependency system, subsequently, when a permission type is revoked, the
TablePermsDescriptor(Provider) will have the permission type being revoked and the permission
type required by the dependent object. When the dependent object will receive the REVOKE action
at table level, it will check the TablePermsDescriptor to see if the permission type being
revoked is one of the permissions required by it, and if yes, then the dependent object will
drop itself. Similar thing will happen for ColPermsDescriptor when it is the Provider of an
invalidation action.

2)CoreDDFinderClassInfo, DDPrivilegeTypeDependableFinder, DDColumnDependableFinder - 
We need a special finder class for COLUMNS_PERMISSION_FINDER_V01_ID because along with the
row in the SYSCOLPERMS, we also need to track the exact column list required for a given dependent
object. This is same as what Derby currently does for views, where a view might require only
a subset of columns in a table. And hence, when tracking the view dependency, we need to know
not just the SYSTABLES row but also the subset of columns required by view. Since similar
scheme is required for COLUMNS_PERMISSION_FINDER_V01_ID, I have used existing DDColumnDependableFinder
with some modifications to support additional format id.

For similar reasons, We need special finder class for TABLE_PERMISSION_FINDER_V01_ID because
along with the row in the SYSTABLEPERMS, we also need to track the exact privilege type required
on a table for a given dependent object. Unlike COLUMNS_PERMISSION_FINDER_V01_ID, where I
could use the existing class, DDColumnDependableFinder, I had to add a new dependable finder
class called DDPrivilegeTypeDependableFinder to track exact required privilege type for a
given row in SYSTABLEPERMS.

3)SPSDescriptor
For now, I am having SPSDescriptor ignore all the revoke invalidation actions. May need some
work here when working on	query plan invalidation for revoke privilege.

4)ViewDescriptor, ConstraintDescriptor - 
For now, ignore all the revoke invalidation actions(except REVOKE_EXECUTE_PRIVILEGE). Derby
supports only RESTRICT form of revoke execute and that means that if there are any dependent
objects on execute permission on routine, revoke execute on that routine should fail. That
is why, I have ViewDescriptor, ConstraintDescriptor catch REVOKE_EXECUTE_PRIVILEGE and throw
exception. As for all the other revoke invalidation actions, I Will get to them in subsequent
patch for ViewDescriptor, ConstraintDescriptor.

5)PermissionsDescriptor, RoutinePermsDescriptor
Have each of the PermissionsDescriptor send appropriate revoke invalidation action. For instance,
if SELECT privilege is being revoked, then send REVOKE_SELECT_PRIVILEGE and so on and so forth.
The dependent objects can take desired action depending on the type of privilege being revoked.
In addition, error messages will be more specific since 	for instance, rather than saying
revoke privilege failed, they can say revoke execute privilege failed.

6)RoutinePermsDescriptor, TablePermsDescriptor and ColPermDescriptor
Changed getObjectName method from 
	return "Routine Privilege on " + routineName; 
to 
		return routineName; 
This change in the method makes the error method from revoke execute more readable. With the
original method, if the user tried to revoke the execute permission from the routine when
there were dependent objects relying on that permission, the error message would be
ERROR X0Y25: Operation 'REVOKE EXECUTE PRIVILEGE' cannot be performed on object 'Routine Privilege
on SELECTFROMSPECIFICSCHEMA' because TRIGGER 'TR31T31' is dependent on that object.

With the change in the method, user would get following message
ERROR X0Y25: Operation 'REVOKE EXECUTE PRIVILEGE' cannot be performed on object 'SELECTFROMSPECIFICSCHEMA'
because TRIGGER 'TR31T31' is dependent on that object.


7)DependencyManager - describes all the invalidation actions for revoke.

8)TablePermsDescriptor and ColPermDescriptor have changes to record speicifc privilege requirement
of the dependent. These getter, setter and resetter methods are all called by the dependency
manager to track object's dependency on specific privilege type and subset of table's columns.

9)StatementTablePermission, StatementColumnPermission - 
During the compile phase of a view, trigger, constraint, we collect what privileges are required
by these objects. The information about the required privileges, privilege types, column list
needs to be saved by dependency manager in the execute phase. The change to these classes
are for collecting required information for dependency manager so that it can save the specific
privilege type/column subset requirements in dependency system.

10)RoutinePrivilegeInfo, TablePrivilegeInfo
Call methods on the PermissionDescriptors so that they can send specific revoke invalidation
actions

9)New Tests
1)revoke execute should fail if there are dependent objects on it
2)revoke will drop a trigger only if trigger depends on that specific privilege type.
3)create trigger which depends on the privileges on objects from different schemas. Revoke
on any one those objects should drop the trigger.
4)create trigger which depends on both table and column level privileges. The trigger should
get dropped only if the privileges gets revoked on specific columns required by the trigger
or if privileges gets revoked on specific privilege type required by the trigger on a table.
If not, then trigger should stay untouched.
3)I couldn't add any testing for trigger action as update on tables owned by a different schema.
This is because Derby runs into a NPE. The Jira entry for this specific problem is DERBY-1583.
Once that bug is fixed, we should add tests like
set connection mamta3
create trigger tr11t11 after insert on mamta1.t11TriggerRevokeTest for each statement mode
db2sql
        update mamta2.t21TriggerRevokeTest set c212 = 99;
and then revoke update privileges on mamta2.t21TriggerRevokeTest from mamta3.


I will appreciate if someone can review, commit this patch for me.

> As per the functional spec attached to DERBY-1330, a trigger should be dropped when a
privilege required by the trigger is revoked.
> -----------------------------------------------------------------------------------------------------------------------------------
>
>                 Key: DERBY-1539
>                 URL: http://issues.apache.org/jira/browse/DERBY-1539
>             Project: Derby
>          Issue Type: New Feature
>          Components: SQL
>    Affects Versions: 10.2.0.0
>            Reporter: Mamta A. Satoor
>         Assigned To: Mamta A. Satoor
>             Fix For: 10.2.0.0
>
>         Attachments: DERBY1539V1hashCodeEqualsDiff.txt, DERBY1539V1hashCodeEqualsStat.txt,
DERBY1539V2diffDropTriggerOnRevoke.txt, DERBY1539V2statDropTriggerOnRevoke.txt, DERBY1539V3diffDropTriggerOnRevoke.txt,
DERBY1539V3statDropTriggerOnRevoke.txt, DERBY1539V4diffDropTriggerOnRevoke.txt, DERBY1539V4diffDropTriggerOnRevokeRequiredPrivilege.txt,
DERBY1539V4statDropTriggerOnRevokeRequiredPrivilege.txt
>
>
> A trigger tracks its privileges requirements using Derby's Dependency Manager. If any
one of those required privileges are revoked, the trigger should be dropped automatically.

> I am just creating a new jira entry here so it is easier to track sub items of DERBY-1330.
Will link this Jira entry to DERBY-1330.

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira

        

Mime
View raw message