db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Mamta Satoor" <msat...@gmail.com>
Subject Issue with using dependency manager for table level permission tracking
Date Wed, 28 Jun 2006 18:42:04 GMT
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