db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Mamta A. Satoor (JIRA)" <derby-...@db.apache.org>
Subject [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.
Date Thu, 03 Aug 2006 23:53:14 GMT
     [ 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

        

Mime
View raw message