[ http://issues.apache.org/jira/browse/DERBY-1632?page=comments#action_12443880 ] Laura Stewart commented on DERBY-1632: --------------------------------------
Mamta - Please check these files to see if the documentation for this behavior is complete. If so, then we should make a comment on this issue and remove the "Documentation" component from this issue. http://db.apache.org/derby/docs/10.2/devguide/cdevcsecuregrantrevokeaccess.html > 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.6 > 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
