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

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

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

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

    Attachment: DERBY1612_V1_stat_DropConstraintOnRevoke.txt
                DERBY1612_V1_diff_DropConstraintOnRevoke.txt

This is a patch which will provide partial support for revoke functionality for constraints. If revoke statement finds a constraint dependent on the table/column on which privilege is being revoked, the constraint will be dropped automatically. This functionality is similar to what is supported for triggers and views. And just like triggers and views, more work is required so that constraint will get dropped only if it depends on the particular privilege TYPE or particular column that is being revoked. 

This patch is similar to what has been recently submitted for view (DERBY-1611), triggers (DERBY-1539)

Engine code changes
1)DropConstraintConstantAction
Made this class and it's method dropConstraintAndIndex public. In addition, change that method to take a LanguageConnectionContext rather than an activation. The activation was used merely to get LanguageConnectionContext and hence it is ok to remove activation and simply send LanguageConnectionContext. This also makes it possible for ConstraintDescriptor to be able to call the DropConstraintConstantAction.dropConstraintAndIndex method.

2)The change to pass LanguageConnectionContext rather than the activation to DropConstraintConstantAction.dropConstraintAndIndex required changes in the calling classes. Those classes are
  a)DropTableConstantAction.java
  b)DropIndexConstantAction.java
  c)AlterTableConstantAction.java
3)Have ConstraintDescriptor call DropConstraintConstantAction.dropConstraintAndIndex when a revoke invalidation comes in for the constraint. With that call, the ConstraintDescriptor is dropping itself because of the revoke invalidation action.

Added following tests to grantRevokeDDL.sql
1)Constraint Test - Test1
Give a constraint privilege at table level to a user. Let user define a foreign key constraint based on that privilege.
Later revoke that references privilege and make sure that foreign key constraint gets dropped

2)Constraint Test - Test2
Have user mamta1 give a references privilege to mamta3.
Have user mamta2 give a references privilege to mamta3.
Have mamta3 create a table with 2 foreign key constraints relying on both these granted privileges.
Revoke one of those privileges and make sure that the foreign key constraint defined based on that privilege gets dropped.
Now revoke the 2nd references privilege and make sure that remaining foreign key constraint gets dropped

3)Constraint Test - Test3
Have mamta1 grant REFERENCES privilege on one of it's tables to mamta2
Have mamta2 create a table with primary which references mamta1's granted REFERENCES privilege
Have mamta2 grant REFERENCES privilege on that table to user mamta3
Have mamta3 create a table which references mamta2's granted REFERENCES privilege
So, the tables look as follows
a)mamta1.t11ConstraintTest (primary key)
b)mamta2.t21ConstraintTest (primary key references t11ConstraintTest)
c)mamta3.t31ConstraintTest (primary key references t21ConstraintTest)
Now revoke of granted REFERENCES privilege by mamta1 should drop the foreign key reference   by mamta2's table t21ConstraintTest. It should not impact the foreign key reference by mamta3's table t31ConstraintTest.

4)Constraint Test - Test4
Grant a REFERENCES permission at public level, create constraint, grant same permission at user level 
   and take away the public level permission. It ends up dropping the constraint. DERBY-1632

5)Constraint Test - Test5
Grant refrences privilege and select privilege on a table. Have a constraint depend on the references privilege. Later, a revoke of select privilege will end up dropping the constraint which shouldn't happen. This will be addressed in a subsequent patch

6)Constraint Test - Test6
Have a primary key and a unique key on a table and grant reference on both. Have another table rely on unique key references privilege to create a foreign key constraint. Later, the revoke of primary key reference will end up
dropping the foreign key constraint. This will be fixed in a subsequent patch (same as test5)

7)Miscellaneous test - test1 (this test is not directly related to constraint work but is a general grant revoke test)
Have mutliple objects depend on the same privilege and make sure they all get dropped when the privilege is later revoked.



> As per the functional spec attached to DERBY-1330, a constraint should be dropped when a privilege required by the constraint is revoked.
> -----------------------------------------------------------------------------------------------------------------------------------------
>
>                 Key: DERBY-1612
>                 URL: http://issues.apache.org/jira/browse/DERBY-1612
>             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: DERBY1612_V1_diff_DropConstraintOnRevoke.txt, DERBY1612_V1_stat_DropConstraintOnRevoke.txt
>
>
> A constraint tracks its privileges requirements using Derby's Dependency Manager. If any one of those required privileges are revoked, the constraint 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