[ http://issues.apache.org/jira/browse/DERBY-1632?page=all ]
Mamta A. Satoor updated DERBY-1632:
-----------------------------------
Summary: During revoke privilege, Derby does not look for replacement
privilege for the dependent objects and simply drops the dependent objects.
This is not SQL compliant and should be fixed. (was: When a revoke privilege
is issued, the affected objects before dropping themselves should see if there
is any other replacement privilege)
Description:
Currently, when an object (trigger/constraint/view) is created, it depends on
the available required privilege at the user level. If none found, it depends
on the available required privilege at PUBLIC level. If none exist both at user
level or PUBLIC level, then create object fails.
To reiterate, if the privilege is found say at the user level, the object
depends on that privilege. Consider the case, where the privilege also exist at
the PUBLIC level. Later, when a revoke privilege is issued at the user level,
the dependent object gets a revoke invalidation action and the dependent object
drops itself. Instead, the dependent object should make itself depend on the
PUBLIC level privilege. This does not happen in Derby at this point and this
behavior is not SQL compliant and should be fixed.
eg for the problem at hand
user1
create table t1
grant select on t1 to user2, public
user2
create view v1 as select * from user1.t1
-- this view will depend on the user level select privilege on table t1
user1
revoke select on t1 from user2
-- this revoke will end up dropping the view. The view could have made itself
depend on the PUBLIC level select privilege
-- on t1 but that doesn't happen currently
another eg for the same problem
user1
create table t1
grant select on t1 to public
user2
create view v1 as select * from user1.t1
-- this view will depend on the PUBLIC level select privilege on table t1
user1
grant select on to to user2
revoke select on t1 from public
-- this revoke ends up dropping the view user2.v1 eventhough there is a user
level SELECT privilege availble
-- on user1.t1
So, in brief, the problem is that when a dependent object gets a revoke
invalidation action, it does not check if there is another privilege available
to replace the privilege being revoked. Instead, they just go ahead and drop
themselves.
Until we fix this behavior, we should document it so the user will know what to
expect for same privilege being available at different levels.
was:
Currently, when an object (trigger/constraint/view) is created, it depends on
the available required privilege at the user level. If none found, it depends
on the available required privilege at PUBLIC level. If none exist both at user
level or PUBLIC level, then create object fails.
To reiterate, if the privilege is found say at the user level, the object
depends on that privilege. Consider the case, where the privilege also exist at
the PUBLIC level. Later, when a revoke privilege is issued at the user level,
the dependent object gets a revoke invalidation action and the dependent object
drops itself. Instead, the dependent object should make itself depend on the
PUBLIC level privilege. This does not happen in Derby at this point and would
be a very useful feature.
eg for the problem at hand
user1
create table t1
grant select on t1 to user2, public
user2
create view v1 as select * from user1.t1
-- this view will depend on the user level select privilege on table t1
user1
revoke select on t1 from user2
-- this revoke will end up dropping the view. The view could have made itself
depend on the PUBLIC level select privilege
-- on t1 but that doesn't happen currently
another eg for the same problem
user1
create table t1
grant select on t1 to public
user2
create view v1 as select * from user1.t1
-- this view will depend on the PUBLIC level select privilege on table t1
user1
grant select on to to user2
revoke select on t1 from public
-- this revoke ends up dropping the view user2.v1 eventhough there is a user
level SELECT privilege availble
-- on user1.t1
So, in brief, the problem is that when a dependent object gets a revoke
invalidation action, it does not check if there is another privilege available
to replace the privilege being revoked. Instead, they just go ahead and drop
themselves.
Until we fix this behavior, we should document it so the user will know what to
expect for same privilege being available at different levels.
> During revoke privilege, Derby does not look for replacement privilege for
> the dependent objects and simply drops the dependent objects. This is not SQL
> compliant and should be fixed.
> ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>
> Key: DERBY-1632
> URL: http://issues.apache.org/jira/browse/DERBY-1632
> Project: Derby
> Issue Type: Improvement
> Components: Documentation, SQL
> Affects Versions: 10.2.1.0
> Reporter: Mamta A. Satoor
>
> Currently, when an object (trigger/constraint/view) is created, it depends on
> the available required privilege at the user level. If none found, it depends
> on the available required privilege at PUBLIC level. If none exist both at
> user level or PUBLIC level, then create object fails.
> To reiterate, if the privilege is found say at the user level, the object
> depends on that privilege. Consider the case, where the privilege also exist
> at the PUBLIC level. Later, when a revoke privilege is issued at the user
> level, the dependent object gets a revoke invalidation action and the
> dependent object drops itself. Instead, the dependent object should make
> itself depend on the PUBLIC level privilege. This does not happen in Derby at
> this point and this behavior is not SQL compliant and should be fixed.
> eg for the problem at hand
> user1
> create table t1
> grant select on t1 to user2, public
> user2
> create view v1 as select * from user1.t1
> -- this view will depend on the user level select privilege on table t1
> user1
> revoke select on t1 from user2
> -- this revoke will end up dropping the view. The view could have made itself
> depend on the PUBLIC level select privilege
> -- on t1 but that doesn't happen currently
> another eg for the same problem
> user1
> create table t1
> grant select on t1 to public
> user2
> create view v1 as select * from user1.t1
> -- this view will depend on the PUBLIC level select privilege on table t1
> user1
> grant select on to to user2
> revoke select on t1 from public
> -- this revoke ends up dropping the view user2.v1 eventhough there is a user
> level SELECT privilege availble
> -- on user1.t1
> So, in brief, the problem is that when a dependent object gets a revoke
> invalidation action, it does not check if there is another privilege
> available to replace the privilege being revoked. Instead, they just go ahead
> and drop themselves.
> Until we fix this behavior, we should document it so the user will know what
> to expect for same privilege being available at different levels.
--
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
