[ 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
