db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Mamta Satoor" <msat...@gmail.com>
Subject Re: Revoke REFERENCES privilege and drop foreign key constraint
Date Wed, 12 Jul 2006 01:10:46 GMT
Hi,

I spent some time prototyping revoke privilege for foreign key constraint
based on my proposal earlier in this thread.
I added following code to ConstraintDescriptor.makeInvalid

  if (action == DependencyManager.REVOKE_PRIVILEGE)
  {
   PreparedStatement ps = lcc.prepareInternalStatement("alter table "+
table.getQualifiedName() + " drop constraint " + constraintName);

   ResultSet rs = ps.execute(lcc, true, 0L);
   rs.close();
   rs.finish();
   return;

  }

This works fine as long as the user who issued the revoke references
privilege is a dba or owner of the table on which foreign key constraint is
defined. But for any other user, the revoke references privilege barfs
saying that user can't perform the operation in that schema.

eg
connect 'jdbc:derby:c:/dellater/dbmaintest2;create=true' user 'mamta3' as
mamta3;
-- notice that mamta3 is dba because mamta3 created the database
create table dummy(c111 int);
connect 'jdbc:derby:c:/dellater/dbmaintest2' user 'mamta1' as mamta1;
create table t11RevokeTest (c111 int not null primary key);
insert into t11RevokeTest values(1),(2);
grant references on t11revoketest to mamta2;
connect 'jdbc:derby:c:/dellater/dbmaintest2' user 'mamta2' as mamta2;
create table t21RevokeTest (c211 int constraint t21c1 references
mamta1.t11RevokeTest);
insert into t21RevokeTest values(1),(2);
set connection mamta1;
revoke references on t11revoketest from mamta2;
-- the revoke statement above will internally issue alter table
mamta2.t21RevokeTest drop constraint t21c1 but since mamta1 is not the owner
of t21RevokeTest, the revoke statement fails.

I am wondering if there is a way I can bypass the authorization for
internally issued sql statements. So, when alter table drop constraint is
issued by revoke statement above, no authorization checking should be done.
Will appreciate if anyone can suggest something here.

thanks,

Mamta

On 7/11/06, Mamta Satoor <msatoor@gmail.com> wrote:

>  Hi,
>
> Did anyone get a chance to go through this mail and see if I am going the
> right track for solving the problem?
>
> thanks,
>  Mamta
>
>
>  On 7/8/06, Mamta Satoor <msatoor@gmail.com> wrote:
> >
> >  Hi,
> >
> > Based on functional specification attached to DERBY-1330, I am working
> > on having REVOKE REFERENCES privilege drop all the foreign key constraints
> > dependent on that privilege.
> >
> > I thought, this would involve going through SYSTABLEPERMS and
> > SYSCOLPERMS in execute phase of of REVOKE statement to find all the
> > privilege descriptors that would get impacted by the REVOKE REFERENCES
> > statement. And then let the depenency manager find all the objects that
> > depend on those privilege descriptors and send a REVOKE_PRIVILEGE to all
> > those dependents. When the  ConstraintDescriptor.makeInvalid method
> > receives REOVKE_PRIVILEGE, it can simply call
> > DataDictionary().dropConstraintDescriptor(...). But this doesn't seem to do
> > the magic and does not clean up everything (for instance, after the REVOKE
> > REFERENCE statement is over, TableDescriptor which had foreign key defined
> > on it still holds on to the foreign key).
> >
> > I looked through alter table constant action to see what happens when a
> > user issues a drop constraint foreignkeyname and it seems like there is lot
> > more involved then simply calling the data dictionary to drop the constraint
> > descriptor. In order to accomplish the same behavior, I am thinking that
> > rather than calling just DataDictionary().dropConstraintDescriptor(...)
> > method in the  ConstraintDescriptor.makeInvalid method, I should issue a
> > sql statement "drop constraint foreignkeyname" inside
> > ConstraintDescriptor.makeInvalid method and let it take care of all the
> > necessary steps.
> >
> > Does anyone have any thoughts on my approach?
> >
> > Thanks,
> >  Mamta
> >
>
>

Mime
View raw message