Derby
  1. Derby
  2. DERBY-1611

A view should be dropped when a privilege required by the view is revoked.

    Details

    • Type: Improvement Improvement
    • Status: Closed
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: 10.2.1.6
    • Fix Version/s: 10.2.1.6
    • Component/s: SQL
    • Labels:
      None

      Description

      A view tracks its privileges requirements using Derby's Dependency Manager. If any one of those required privileges are revoked, the view should be dropped automatically.

      I am just creating a new jira entry here so it is easier to track sub items of DERBY-1330. Will link this Jira entry to DERBY-1330.

      See the functional spec attached to DERBY-1330

      1. DERBY1611_V1_stat_DropViewOnRevoke.txt
        0.3 kB
        Mamta A. Satoor
      2. DERBY1611_V1_diff_DropViewOnRevoke.txt
        51 kB
        Mamta A. Satoor

        Issue Links

          Activity

          Hide
          Mamta A. Satoor added a comment -

          I have the next incremental patch for revoke privilege. This patch concentrates on basic revoke functionality for views. If revoke statement finds a view dependent on the table/column/routine on which privilege is being revoked, the view will be dropped automatically. This functionality is similar to what is supported for triggers. And just like triggers, more work is required so that view will get dropped only if it depends on the particular privilege TYPE or particular column that is being revoked.

          svn stat -q o/p is attached as DERBY1611_V1_stat_DropViewOnRevoke.txt

          Other than the test changes, the only file that got changed is ViewDescriptor.java. ViewDescriptor drops itself if it get a revoke invalidation action. This is a pretty straightforward change and would appreciate if anyone with cycles can review/commit this for me. derbyall ran fine with Sun's jdk1.4

          I have added quite a few tests into lang\grantRevokeDDL.sql and have names for the new tests in lang\grantRevokeDDL.sql for easy reference in the description below
          1)Test1
          A view should get dropped when one of the required privileges is revoked
          2)Test2
          Have the dba create a view in a schema owned by another user. The only way that view will get dropped will be via drop view statement. Since the view was created by dba, it doesn't have privilege dependencies and hence no revoke statement can automatically drop that view created by the dba. A drop view statement is the only way to drop a dba owned view.
          3)Test3
          Create a view that relies on a table level permission and a column level permission. Object should get dropped no matter which one of the 2 privileges get revoked first.
          4)Test4
          Create a view that relies on a user-level table privilege and a user-level column privilege. There also exists a PUBLIC-level column privilege but objects at the creation time always first look for the required privilege at the user level(DERBY-1632). This behavior can be confirmed by the this test case where when PUBLIC-level column privilege is revoked, it does not impact the view in anyway because the view is relying on user-level column privilege. Confirm that object is relying on user-level privilege by revoking the user-level privilege and that should drop the object
          5)Test5
          Create a view that relies on a SELECT privilege on only one column of a table. revoke SELECT privilege on another column in that table and it ends up dropping the view. This is happening because the revoke privilege work is not completely finished and any dependent object on that permission type for table's columns get dropped when a revoke privilege is issued against any column of that table
          6)Test6
          Create a view that requires some privileges. Create a trigger that requires privileges on that view. When a privilege is revoked from the view, the view gets dropped. I have expected the trigger to fail when it gets fired next time. But it does not always happen because of the issue with trigger invalidation. DERBY-1613
          7)Test7
          Create a view that relies on a user level table privilege. The view will depend on the user level table privilege. Later grant the table privilege at the PUBLIC level too. So, there are 2 privileges available and the view relies on one of those privileges. Later, revoke the user level table privilege. This will end up dropping the view although there is another privilege available at PUBLIC level which can cover the view's requirements of privileges. But Derby does not support this automatic switching of privilege reliance on another available privilege when revoke is issued. DERBY-1632
          8)Test8
          This test is similar to test7 above. Create a view that relies on a column level privilege. Later on, grant the same privilege at table level. Now, revoke the column level privilege. The view will get dropped automatically even though there is a covering privilege available at the table level.(DERBY-1632)
          9)Test9
          Have SELECT privilege available both at column level and table level. When an object is created which requires the SELECT privilege, Derby is designed to pick up the table level privilege first. Later, when the table level privilege is revoke, the object gets dropped. The object really should start depending on the available column level privilege. DERBY-1632
          10)Test10
          Create a view that relies on some privileges. Create another view based on that view. A revoke privilege on privilege required by the first view will fail because there is another view dependent on the first view. This is because Derby currently does not support cascade view drop (DERBY-1631)

          Show
          Mamta A. Satoor added a comment - I have the next incremental patch for revoke privilege. This patch concentrates on basic revoke functionality for views. If revoke statement finds a view dependent on the table/column/routine on which privilege is being revoked, the view will be dropped automatically. This functionality is similar to what is supported for triggers. And just like triggers, more work is required so that view will get dropped only if it depends on the particular privilege TYPE or particular column that is being revoked. svn stat -q o/p is attached as DERBY1611_V1_stat_DropViewOnRevoke.txt Other than the test changes, the only file that got changed is ViewDescriptor.java. ViewDescriptor drops itself if it get a revoke invalidation action. This is a pretty straightforward change and would appreciate if anyone with cycles can review/commit this for me. derbyall ran fine with Sun's jdk1.4 I have added quite a few tests into lang\grantRevokeDDL.sql and have names for the new tests in lang\grantRevokeDDL.sql for easy reference in the description below 1)Test1 A view should get dropped when one of the required privileges is revoked 2)Test2 Have the dba create a view in a schema owned by another user. The only way that view will get dropped will be via drop view statement. Since the view was created by dba, it doesn't have privilege dependencies and hence no revoke statement can automatically drop that view created by the dba. A drop view statement is the only way to drop a dba owned view. 3)Test3 Create a view that relies on a table level permission and a column level permission. Object should get dropped no matter which one of the 2 privileges get revoked first. 4)Test4 Create a view that relies on a user-level table privilege and a user-level column privilege. There also exists a PUBLIC-level column privilege but objects at the creation time always first look for the required privilege at the user level( DERBY-1632 ). This behavior can be confirmed by the this test case where when PUBLIC-level column privilege is revoked, it does not impact the view in anyway because the view is relying on user-level column privilege. Confirm that object is relying on user-level privilege by revoking the user-level privilege and that should drop the object 5)Test5 Create a view that relies on a SELECT privilege on only one column of a table. revoke SELECT privilege on another column in that table and it ends up dropping the view. This is happening because the revoke privilege work is not completely finished and any dependent object on that permission type for table's columns get dropped when a revoke privilege is issued against any column of that table 6)Test6 Create a view that requires some privileges. Create a trigger that requires privileges on that view. When a privilege is revoked from the view, the view gets dropped. I have expected the trigger to fail when it gets fired next time. But it does not always happen because of the issue with trigger invalidation. DERBY-1613 7)Test7 Create a view that relies on a user level table privilege. The view will depend on the user level table privilege. Later grant the table privilege at the PUBLIC level too. So, there are 2 privileges available and the view relies on one of those privileges. Later, revoke the user level table privilege. This will end up dropping the view although there is another privilege available at PUBLIC level which can cover the view's requirements of privileges. But Derby does not support this automatic switching of privilege reliance on another available privilege when revoke is issued. DERBY-1632 8)Test8 This test is similar to test7 above. Create a view that relies on a column level privilege. Later on, grant the same privilege at table level. Now, revoke the column level privilege. The view will get dropped automatically even though there is a covering privilege available at the table level.( DERBY-1632 ) 9)Test9 Have SELECT privilege available both at column level and table level. When an object is created which requires the SELECT privilege, Derby is designed to pick up the table level privilege first. Later, when the table level privilege is revoke, the object gets dropped. The object really should start depending on the available column level privilege. DERBY-1632 10)Test10 Create a view that relies on some privileges. Create another view based on that view. A revoke privilege on privilege required by the first view will fail because there is another view dependent on the first view. This is because Derby currently does not support cascade view drop ( DERBY-1631 )
          Hide
          Daniel John Debrunner added a comment -

          Patch DERBY1611_V1_diff_DropViewOnRevoke.txt committed Revision: 428536 - Thanks Mamta

          Show
          Daniel John Debrunner added a comment - Patch DERBY1611_V1_diff_DropViewOnRevoke.txt committed Revision: 428536 - Thanks Mamta
          Hide
          Daniel John Debrunner added a comment -

          Cleaning the summary

          Show
          Daniel John Debrunner added a comment - Cleaning the summary
          Hide
          Mamta A. Satoor added a comment -

          Closing this issue since I am finished with the work targeted for 10.2 release. The remaining work for this issue is getting tracked in DERBY-1782. I will link the 2 issues so we can keep track of the correlation between the 2 jira entries.

          Show
          Mamta A. Satoor added a comment - Closing this issue since I am finished with the work targeted for 10.2 release. The remaining work for this issue is getting tracked in DERBY-1782 . I will link the 2 issues so we can keep track of the correlation between the 2 jira entries.

            People

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

              Dates

              • Created:
                Updated:
                Resolved:

                Development