Derby
  1. Derby
  2. DERBY-1632

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.

    Details

    • Type: Bug Bug
    • Status: Open
    • Priority: Major Major
    • Resolution: Unresolved
    • Affects Version/s: 10.2.1.6
    • Fix Version/s: None
    • Component/s: Documentation, SQL
    • Urgency:
      Normal
    • Issue & fix info:
      Repro attached
    • Bug behavior facts:
      Deviation from standard

      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.

        Issue Links

          Activity

          Hide
          Lily Wei added a comment - - edited

          Thanks to all the detail information, I reproduce this bug and thought I just put it here in case anyone needs it.

          Show
          Lily Wei added a comment - - edited Thanks to all the detail information, I reproduce this bug and thought I just put it here in case anyone needs it.
          Hide
          Knut Anders Hatlen added a comment -

          Triaged for 10.5.2.

          Show
          Knut Anders Hatlen added a comment - Triaged for 10.5.2.
          Hide
          Dag H. Wanvik added a comment -

          The problem described by this issue is compounded by adding roles,
          since there are more ways a privilege requirement could be satisfied after
          a revoke action.

          Show
          Dag H. Wanvik added a comment - The problem described by this issue is compounded by adding roles, since there are more ways a privilege requirement could be satisfied after a revoke action.
          Hide
          Dag H. Wanvik added a comment -

          Looking at the privileges work when investigating roles, I looked at this issue.
          I agree with Dan this is a bug, not an improvement. Changing issue type to bug.
          If you don't agree, feel free to change it back, Mamta

          Show
          Dag H. Wanvik added a comment - Looking at the privileges work when investigating roles, I looked at this issue. I agree with Dan this is a bug, not an improvement. Changing issue type to bug. If you don't agree, feel free to change it back, Mamta
          Hide
          Mamta A. Satoor added a comment -

          Laura, the page you mentioned does document the current behavior. But I would like the documentation component to stay on this bug to make sure that when we do fix this bug, we update the documentation to remove the current restriction. Alternatively, we could keep this bug for engine work and open another bug for documentation that should go in hands with this engine bug. What do you think?

          Show
          Mamta A. Satoor added a comment - Laura, the page you mentioned does document the current behavior. But I would like the documentation component to stay on this bug to make sure that when we do fix this bug, we update the documentation to remove the current restriction. Alternatively, we could keep this bug for engine work and open another bug for documentation that should go in hands with this engine bug. What do you think?
          Hide
          Laura Stewart added a comment -

          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

          Show
          Laura Stewart added a comment - 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
          Hide
          Daniel John Debrunner added a comment -

          This a bug, not an improvement?

          SQL Standard 12.7 SR 20) says the view would be abandoned if it no longer has the required permissions.
          Thus if the required permissions still exist after the REVOKE statement the view should not be dropped
          and Derby will drop it in various situations as described above.

          Show
          Daniel John Debrunner added a comment - This a bug, not an improvement? SQL Standard 12.7 SR 20) says the view would be abandoned if it no longer has the required permissions. Thus if the required permissions still exist after the REVOKE statement the view should not be dropped and Derby will drop it in various situations as described above.
          Hide
          Mamta A. Satoor added a comment -

          One final behavior to note until this jira entry is resolved......

          When a required privilege is available both at table level and column level, then the new objects(views, triggers, constraints) will always depend on the table level privilege. Later when that table level privilege is revoked, the objects get dropped. Ideally, they should start relying on the available column level privilege.

          Again, fixing this Jira entry will take care of the behavior above. We might want to document the behavior in the meantime.

          Show
          Mamta A. Satoor added a comment - One final behavior to note until this jira entry is resolved...... When a required privilege is available both at table level and column level, then the new objects(views, triggers, constraints) will always depend on the table level privilege. Later when that table level privilege is revoked, the objects get dropped. Ideally, they should start relying on the available column level privilege. Again, fixing this Jira entry will take care of the behavior above. We might want to document the behavior in the meantime.
          Hide
          Mamta A. Satoor added a comment -

          Another behavior to note until this jira entry is resolved......(do we want to document the following behavior till this Jira entry is resolved?)

          When a privilege is available at both user-level and PUBLIC level, new objects(views, triggers, constraints) would always depend on the user-level privilege. 2 things can happen in terms of revoke later
          1)If PUBLIC level privilege is revoked, it will not affect the object in anyway and the object will remain intact
          2)If user-level privilege is revoked, the object will end up dropping itself.

          This behavior might be useful for a user to know that when they have a privilege available at both user and PUBLIC level at object creation time, only user-level revoke privilege will drop the object and not the PUBLIC level revoke privilege.

          Once this jira issue is resolved, the dependent object will start depending on the next available privilege(if exist) when a revoke privilege is issued and we will not have to worry about this jira comment.

          Show
          Mamta A. Satoor added a comment - Another behavior to note until this jira entry is resolved......(do we want to document the following behavior till this Jira entry is resolved?) When a privilege is available at both user-level and PUBLIC level, new objects(views, triggers, constraints) would always depend on the user-level privilege. 2 things can happen in terms of revoke later 1)If PUBLIC level privilege is revoked, it will not affect the object in anyway and the object will remain intact 2)If user-level privilege is revoked, the object will end up dropping itself. This behavior might be useful for a user to know that when they have a privilege available at both user and PUBLIC level at object creation time, only user-level revoke privilege will drop the object and not the PUBLIC level revoke privilege. Once this jira issue is resolved, the dependent object will start depending on the next available privilege(if exist) when a revoke privilege is issued and we will not have to worry about this jira comment.

            People

            • Assignee:
              Unassigned
              Reporter:
              Mamta A. Satoor
            • Votes:
              0 Vote for this issue
              Watchers:
              0 Start watching this issue

              Dates

              • Created:
                Updated:

                Development