db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Satheesh Bandaram" <banda...@gmail.com>
Subject Re: Issue with using dependency manager for table level permission tracking
Date Tue, 11 Jul 2006 01:16:31 GMT
Have you considered using PROVIDERFINDER that is part of dependency system?
TableDescriptor, for example, stores column list that a view depends on
using ColumnsInTable finder. A similar mechanism might work for
TablePermDescriptor.

Satheesh

On 7/10/06, Mamta Satoor <msatoor@gmail.com> wrote:
>
> Hi,
>
> I wondered if anyone got a chance to go over my earlier mail and if they
> think of an alternative way to solve the problem than the one suggested by
> me. If I don't hear anything in a day or so, then I will go ahead and
> change SYSTABLEPERMS to look like SYSCOlPERMS. After that change, I will be
> able to associate a view/trigger/constraint's dependnecy on the exact
> privilege type for a give table.
>
> thanks,
> Mamta
>
>
> On 6/28/06, Mamta Satoor <msatoor@gmail.com> wrote:
> >
> >  Hi,
> >
> > I recently submitted a patch (http://www.nabble.com/-PATCH-DERBY-1330-Collect-privilege-requirements-for-views-tf1858264.html#a5075008
> > ) that tracks a view's(view descriptor) dependency on different
> > privileges (PermissionsDescriptor) using the dependency manager which in
> > turn saves these dependencies in SYSDEPENDS.
> >
> > Currently, Derby 10.2 codeline tracks table level privileges per user in
> > SYSTABLEPERMS. If a user has one/more privileges on a table, they are all
> > tracked in one row in SYSTABLEPERMS. The structure of SYSTABLEPRMS looks as
> > follows
> > create table SYS.SYSREQUIREDPERM
> > (
> >     GRANTEE varchar(128) not null,
> >     GRANTOR varchar(128) not null,
> >     TABLEID char(36) not null,
> >     SELECTPRIV char(1) not null,
> >     DELETEPRIV char(1) not null,
> >     INSERTPRIV char(1) not null,
> >     UPDATEPRIV char(1) not null,
> >     REFERENCESPRIV char(1) not null,
> >     TRIGGERPRIV char(1) not null,
> >     primary key( GRANTEE, TABLEID),
> >     foreign key( TABLEID references SYS.SYSTABLES)
> > )
> >
> > Following eg shows how table level privileges are tracked in
> > SYSTABLEPERMS and the problem at hand
> > user1 connects
> > create table t1
> > grant select on t1 to user2
> > --Then at this point, there is one row in SYSTABLEPERMS  for t1 and
> > user2 and that row has SELECTPRIV column set to true.
> > user2 connects
> > create view v1 as select * from user1.t1
> > -- view above depends on SELECTPRIV on user1.t1 and dependency
> > manager keeps track of that dependency by inserting a row for viewdescriptor
> > depending on permissiondescriptor(the uuid column in SYSTABLEPERMS). Notice
> > that there is no differentiation here which says that the view depends only
> > on the SELECTPRIV privilege type on table t1. We just know that view is
> > dependent on SOME privilege type on table t1
> > user1 connects
> > grant insert on t1 to user2
> > --Then at this point, we still have one row in SYSTABLEPERMS for t1 and
> > user2 and that row has both SELECTPRIV and INSERTPRIV set to true.
> > user2 connects
> > create table t2
> > create insert trigger tr1 on t2 which inserts into user1.t1
> >  All the table level privileges are kept in
> >  -- trigger above depends on INSERTPRIV on user1.t1 and dependency
> > manager keeps track of that dependency by inserting a row for
> > triggerdescriptor depending on permissiondescriptor(the uuid column in
> > SYSTABLEPERMS). Notice that there is no differentiation here which says that
> > the trigger depends only on the INSERTPRIV privilege type on table t1. We
> > just know that trigger is dependent on SOME privilege type on table t1
> > user1 connects
> > revoke select on t1 from user2
> > -- at this point, only view user2.v1 should get dropped because it
> > depends on the SELECTPRIV on t1. But dependency manager has no way to know
> > that user2.v1 needs only SELECTPRIV and hence only object affected by
> > this revoke in user2.v1 and there should be no impact on user2.tr2.
> > That's because user2.tr2 needs INSERTPRIV on user1.t1 and that is still
> > in place
> >
> > So, my question is how can I fine tune the dependency manager to know
> > that user2.v1 needs SELECTPRIV on user1.t1? And hence, revoke above
> > should result in dropping user2.v1 but should not touch user2.tr2
> >
> > One solution that comes to my mind is to have one row for each privilege
> > type for a given user in SYSTABLEPERMS. ie in the example above
> >  grant select on t1 to user2
> > --There should be one row in SYSTABLEPERMS  for t1 and user2 with a
> > privilege type column set to S(for select)
> >  grant insert on t1 to user2
> > --There should be another row in SYSTABLEPERMS for t1 and user2 with a
> > privilege type column set to I(for insert). ie at the end of the above 2
> > grant statements, there should be rows in SYSTABLEPERMS for table t1 and
> > user user2.
> >
> > This is what we do for column level privileges in SYSCOLPERMS. That is,
> > there is one row for every type of privilege that is granted on columns
> > level for a given user. If we did the same thing for SYSTABLEPERMS, it will
> > solve my problem. Maybe there is another way to solve the table level
> > privilege type dependency which I am overlooking and would appreciate any
> > suggestion from the community.
> >
> > thanks,
> > Mamta
> >
>
>

Mime
View raw message