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: 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

          Kim Haase made changes -
          Component/s Documentation [ 11406 ]
          Knut Anders Hatlen made changes -
          Link This issue is related to DERBY-2041 [ DERBY-2041 ]
          Gavin made changes -
          Workflow jira [ 12377288 ] Default workflow, editable Closed status [ 12799061 ]
          Kathey Marsden made changes -
          Labels derby_triage10_5_2
          Lily Wei made changes -
          Attachment reproex2.zip [ 12413190 ]
          Knut Anders Hatlen made changes -
          Urgency Normal
          Issue & fix info [Repro attached]
          Kathey Marsden made changes -
          Derby Categories [Deviation from standard]
          Dag H. Wanvik made changes -
          Issue Type Improvement [ 4 ] Bug [ 1 ]
          Mamta A. Satoor made changes -
          Summary When a revoke privilege is issued, the affected objects before dropping themselves should see if there is any other replacement privilege 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.
          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 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.
          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.
          Mamta A. Satoor made changes -
          Link This issue is related to DERBY-1330 [ DERBY-1330 ]
          Mamta A. Satoor made changes -
          Field Original Value New Value
          Link This issue is related to DERBY-1643 [ DERBY-1643 ]
          Mamta A. Satoor created issue -

            People

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

              Dates

              • Created:
                Updated:

                Development