Derby
  1. Derby
  2. DERBY-1631

Derby needs to support drop view cascade in order for revoke privilege to function correctly

    Details

    • Type: Improvement Improvement
    • Status: Closed
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: 10.2.1.6
    • Fix Version/s: None
    • Component/s: Documentation, SQL
    • Urgency:
      Normal

      Description

      In Derby, you can not drop a view if there is another view based on it. The dependent view has to be dropped before the provider view can be dropped
      eg
      create table t1
      create view v1 as select * from t1
      create view v2 as select * from v1
      drop view v1
      – the drop view v1 will fail because v2 depends on view v1

      This behavior causes a problem with revoke privilege. If a view is dependent on a privilege, then the revoke of the privilege is supposed to drop the view. But that will not happen if there is another view dependent on the already defined view
      eg
      user1
      create table t1
      grant select on t1 to user2
      user2
      create view v1 as select * from t1
      create view v2 as select * from v1
      user1
      revoke select on t1 from user2
      – the above revoke statement will fail because when it tries to dop view v1, it can't do so because view v2 depends on v1
      – the error message returned will be
      ERROR X0Y23: Operation 'DROP VIEW' cannot be performed on object 'V1' because VIEW 'V2' is dependent on that object.

      Until this issue is resolved. we should document this behavior in grant/revoke + view description

      I will attach this Jira entry to DERBY-1611 which is the higher level jira entry for views and revoke privilege

        Issue Links

          Activity

          Mamta A. Satoor created issue -
          Mamta A. Satoor made changes -
          Field Original Value New Value
          Link This issue is part of DERBY-1611 [ DERBY-1611 ]
          Mamta A. Satoor made changes -
          Derby Info [Release Note Needed]
          Description In Derby, you can not drop a view if there is another view based on it. The dependent view has to be dropped before the provider view can be dropped
          eg
          create table t1
          create view v1 as select * from t1
          create view v2 as select * from v1
          drop view v1
          -- the drop view v1 will fail because v2 depends on view v1

          This behavior causes a problem with revoke privilege. If a view is dependent on a privilege, then the revoke of the privilege is supposed to drop the view. But that will not happen if there is another view dependent on the already defined view
          eg
          user1
          create table t1
          grant select on t1 to user2
          user2
          create view v1 as select * from t1
          create view v2 as select * from v1
          user1
          revoke select on t1 from user2
          -- the above revoke statement will fail because when it tries to dop view v1, it can't do so because view v2 depends on v1

          Until this issue is resolved. we should document this behavior in grant/revoke + view description

          I will attach this Jira entry to DERBY-1611 which is the higher level jira entry for views and revoke privilege
          In Derby, you can not drop a view if there is another view based on it. The dependent view has to be dropped before the provider view can be dropped
          eg
          create table t1
          create view v1 as select * from t1
          create view v2 as select * from v1
          drop view v1
          -- the drop view v1 will fail because v2 depends on view v1

          This behavior causes a problem with revoke privilege. If a view is dependent on a privilege, then the revoke of the privilege is supposed to drop the view. But that will not happen if there is another view dependent on the already defined view
          eg
          user1
          create table t1
          grant select on t1 to user2
          user2
          create view v1 as select * from t1
          create view v2 as select * from v1
          user1
          revoke select on t1 from user2
          -- the above revoke statement will fail because when it tries to dop view v1, it can't do so because view v2 depends on v1
          -- the error message returned will be
          ERROR X0Y23: Operation 'DROP VIEW' cannot be performed on object 'V1' because VIEW 'V2' is dependent on that object.

          Until this issue is resolved. we should document this behavior in grant/revoke + view description

          I will attach this Jira entry to DERBY-1611 which is the higher level jira entry for views and revoke privilege
          Hide
          Mamta A. Satoor added a comment -

          One thing to note is we do not necessarily need the SQL support for drop view cascade. It can be supported at the language layer and revoke functionality can work directly with language layer to use the drop view cascade functionality. So, as far as revoke privilege goes, it will be sufficient to have drop view cascade implemented at the language layer.

          Show
          Mamta A. Satoor added a comment - One thing to note is we do not necessarily need the SQL support for drop view cascade. It can be supported at the language layer and revoke functionality can work directly with language layer to use the drop view cascade functionality. So, as far as revoke privilege goes, it will be sufficient to have drop view cascade implemented at the language layer.
          Mamta A. Satoor made changes -
          Link This issue is related to DERBY-1330 [ DERBY-1330 ]
          Dag H. Wanvik made changes -
          Issue Type New Feature [ 2 ] Improvement [ 4 ]
          Mamta A. Satoor made changes -
          Labels derby_triage10_11
          Urgency Normal [ 10052 ]
          Gavin made changes -
          Workflow jira [ 12377284 ] Default workflow, editable Closed status [ 12801846 ]
          Hide
          Kim Haase added a comment -

          The documentation seems to have been fixed as part of DERBY-5591 (10.9).

          There seem to be no plans to implement DROP VIEW CASCADE.

          Show
          Kim Haase added a comment - The documentation seems to have been fixed as part of DERBY-5591 (10.9). There seem to be no plans to implement DROP VIEW CASCADE.
          Kim Haase made changes -
          Status Open [ 1 ] Closed [ 6 ]
          Resolution Fixed [ 1 ]
          Transition Time In Source Status Execution Times Last Executer Last Execution Date
          Open Open Closed Closed
          2960d 14h 30m 1 Kim Haase 09/Sep/14 17:47

            People

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

              Dates

              • Created:
                Updated:
                Resolved:

                Development