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
A view should get dropped when one of the required privileges is revoked
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.
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.
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
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
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.
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
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)
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
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 (