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: Issue with using dependency manager for table level permission tracking
Date Mon, 10 Jul 2006 23:22:13 GMT
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