[jira] Updated: (DERBY-1611) As per the functional spec attached to DERBY-1330, a view should be dropped when a privilege required by the view is revoked.

["Mamta A. Satoor (JIRA)" <de...@db.apache.org>]

     [ http://issues.apache.org/jira/browse/DERBY-1611?page=all ]

Mamta A. Satoor updated DERBY-1611:
-----------------------------------

    Attachment: DERBY1611_V1_diff_DropViewOnRevoke.txt
                DERBY1611_V1_stat_DropViewOnRevoke.txt

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)



> As per the functional spec attached to DERBY-1330, a view should be dropped when a privilege required by the view is revoked.
> -----------------------------------------------------------------------------------------------------------------------------
>
>                 Key: DERBY-1611
>                 URL: http://issues.apache.org/jira/browse/DERBY-1611
>             Project: Derby
>          Issue Type: New Feature
>          Components: SQL
>    Affects Versions: 10.2.0.0
>            Reporter: Mamta A. Satoor
>         Assigned To: Mamta A. Satoor
>             Fix For: 10.2.0.0
>
>         Attachments: DERBY1611_V1_diff_DropViewOnRevoke.txt, DERBY1611_V1_stat_DropViewOnRevoke.txt
>
>
> 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. 

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